SQL & PL/SQL :: Grant Privileges Remotely

May 25, 2010

Say we have db1 and db2 two databases installed on two different servers.For internal needs, I have to insert some data from a table t1 on db1 to a table t2 in db2. This can be done by issuing from a user on db1 :

insert into t2@dblink2
select *
from t1;

where dblink2 is a correct database link that points to u2 (the t2 owner for example) on db2.

Now what I want to do is to grant privileges remotely.Is there a way to issue somthing like

execute@dblink2 'grant select on t2 to public';

It would be like u2 has issued the statment...

I don't want to use OS scripts (.sh or .bat).

View 14 Replies


ADVERTISEMENT

Security :: ORA-01929 / No Privileges To GRANT

Jun 28, 2011

When i try to grant all privilege to a object i get error

SQL> GRANT ALL PRIVILEGES ON tab1 TO PUBLIC;
GRANT ALL PRIVILEGES ON tab1 TO PUBLIC
*
ERROR at line 1:ORA-01929: no privileges to GRANT

how to check the user has privilege to use grant privilege or grant all privileges to object and what privileges exist in ALL PRIVILEGES.

View 5 Replies View Related

Security :: Grant Privileges To A User?

Dec 10, 2010

I am a java developer and would like to install data locally in my computer for testing purpose.

I login as sysdba in sqlplus, created a user.

In the installation documentation it said :

This user should have at least the following privileges.

CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE USER, CREATE ROLE, CREATE SEQUENCE, CREATE SESSION CREATE TRIGGER, AND CREATE TYPE.

If you choose, you can grant DBA role to this user.

Note: Coeus Schema owner should have CREATE USER and ALTER USER right explicitly granted to it, not through a role like DBA.

1)How to grant all this prvileges to that user ?

2)How this user will have CREATE USER and ALTER USER right explicitly granted to it, not through a role like DBA ?

View 5 Replies View Related

SQL & PL/SQL :: Grant Privileges For Select On View From Different Schemes

Oct 27, 2011

My need is to grant priveleges for select on view without granting on nested tables.

Actual problem is that I can grant privileges for particular user, but can't grant privileges for user group.

View 4 Replies View Related

SQL & PL/SQL :: Grant Privileges To Subprogram Via Role - Should Not Work

Jun 8, 2010

I bought Selftestsoftware for 1z0-147 for 9i and 10g. Selftestsoftware is endorsed by Oracle, should be high quality.

But its below sample question and answer seem to be wrong: It says that privilege for subprogram can be granted via role. But from Urman 9i book: the grant the execute privilege must be done explicitly and not through a role.

Did Selftestsoftware made a mistake? Or the question did not mention or assume that the subprogram is based on invoker rights not definer right?

Quote:
Question: All users in the HR_EMP role have UPDATE privileges on the EMPLOYEE table. You create the UPDATE_EMPLOYEE procedure. HR_EMP users should only be able to update the EMPLOYEE table using this procedure. Which two statements should you execute? (Choose two.)

GRANT UPDATE ON employee TO hr_emp;
GRANT SELECT ON employee to hr_emp;
REVOKE UPDATE ON employee FROM hr_emp;
REVOKE UPDATE ON employee FROM public;
GRANT EXECUTE ON update_employee TO hr_emp;

Explanation:
The two statements you should execute are:
REVOKE UPDATE ON employee FROM hr_emp;
GRANT EXECUTE ON update_employee TO hr_emp;

Unless you are the owner of the PL/SQL construct, you must be granted the EXECUTE object privilege to run it or have the EXECUTE ANY PROCEDURE system privilege. By default, a PL/SQL procedure executes under the security domain of its owner. This means that a user can invoke the procedure without privileges on the procedures underlying objects. To allow HR_EMP users to execute the procedure, you must issue the GRANT EXECUTE ON update_employee TO hr_emp; statement. To prevent HR_EMP users from updating the EMPLOYEE table unless they are using the UPDATE_EMPLOYEE procedure, you must issue the REVOKE UPDATE ON employee FROM hr_emp;

All of the other options are incorrect because they will not meet the specified requirements.

View 7 Replies View Related

Security :: Grant Privileges To Public User

Jul 9, 2011

grant insert,update,delete,select on staging_tb1 to public;

What is public here? i know something about public user like it is users exist in the database .

View 5 Replies View Related

Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

have an automated process which runs on an Oracle 8i database server as user abc. This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.

The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.

What grants/priviliges or whatever do I have to do to the abc schema?

View 2 Replies View Related

Schema Permissions - Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

I have an automated process which runs on an Oracle 8i database server as user abc.This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.

The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.

What grants/priviliges or whatever do I have to do to the abc schema?

View 1 Replies View Related

Security :: Difference Between Grant All On Any Table And Grant All On Table_name

Oct 31, 2012

I would like to know what happens when we fire:

grant all on any table to user_name;

and

grant all on table_name to user_name;

Actually I was performing "grant all on table_name to user_name;" to grant the privilege but 1 of my friends suggested "grant all on any table to user_name;"

View 5 Replies View Related

Enterprise Manager :: How To Use OEM Remotely

Oct 21, 2011

How can I use OEM remotely?

my DB server is on 172.17.1.4 IBM-AIX

and my computer is 172.17.1.53

Because I am tired of using sqlplus :@

View 2 Replies View Related

PL/SQL :: How To Create New Users Remotely

Feb 27, 2013

I've been issuing commands like these by using a session on my guest Oracle VM that is hosted by Windows 8.

sqlplus / as sysdba
create user PocketGuide3rdEd identified by top-secret-password ;
grant connect to PocketGuide3rdEd ;
grant create table to PocketGuide3rdEd ;
grant create view to PocketGuide3rdEd ;
grant create trigger to PocketGuide3rdEd ;
grant create sequence to PocketGuide3rdEd ;
grant unlimited tablespace to PocketGuide3rdEd ;

I've discovered that I cannot do "sqlplus / as sysdba" remotely. Is there a way I can create another account like sysdba that can issue the above commands from windows so I don't have to log into the oracle guest VM?

View 4 Replies View Related

Enterprise Manager :: Managing OEM Remotely

Aug 23, 2013

I want to manage my database remotely, through Internet. Is it possible to do that with OEM? Where can I find information related to this? Is there another monitoring software that allows me to perform this?

View 10 Replies View Related

Cannot Connect Oracle Instance Remotely In LAN

Jun 24, 2012

I have installed oracle 10g database in the my local laptop ( Windows 7) . This laptop is in LAN. I am not able to connect from other machines in LAN. When i open sqlplus, SQL Developer from the same machine, i was able to login to the instance.

But when i want to login from the other machines in LAN. I was not able to connect. I was able to ping the server on which the database was installed from other machines. why i am not able to connect ?

View 8 Replies View Related

Not Able To Connect DB Remotely Using Oracle Client?

Aug 20, 2013

I am trying to connect to database remotely. I have installed Oracle client on my local machine(Windows 7) and trying to connect to db.

I am able to do tnsping. Below is the log.

But when i try to connect i am getting ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA error.

Below is the complete log.

C:\Windows\System32>tnsping DEVDB

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 20-AUG-2

013 14:18:42

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:

D:\app\swanand_kulkarni\product\11.2.0\client_1\network\admin\sqlnet.ora

Used EZCONNECT adapter to resolve the alias

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO

COL=TCP)(HOST=10.111.0.121)(PORT=1521)))

OK (560 msec)

C:\Windows\System32>sqlplus test1/********@devdb

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 20 14:18:47 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

ERROR:

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

View 8 Replies View Related

Networking And Gateways :: Connect Remotely Database?

Jul 14, 2010

i have a inventory system and trying to connect remote database one by one throught two static ips. If first ip is not ping then go to 2nd ip only when open the login.fmx page

following examples are my tnsnames.ora

IP1.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 117.65.48.12)(PORT = 1521))
(CONNECT_DATA = (SID = ORCL))

IP2.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 117.65.48.13)(PORT = 1521))
(CONNECT_DATA = (SID = ORCL))

i am not understand what i write the code on-logon trigger

View 8 Replies View Related

Networking And Gateways :: DBCA Installation And Remotely From Off-shore?

Sep 22, 2011

Iam facing a issue on DBCA Installation, when i tried remotely from off-shore. Steps I followed

a. I Connected a onsite desktop through a remote desktop connection (RDP) by using Corp-VPN from off-shore.
b. In the Remote Desktop, again by using production / Model VPN , connected the server which i need to Install DBCA
c. Using X11-Forwarding of putty, Started the DBCA installation.
d. Applied all the Parameters and on Finally clicking Finish to Start the database Creation, Finish button not responding and i can't continue the DBCA Installation.

View 1 Replies View Related

Connecting To DB Remotely Through Toad Using Oracle Instant Client?

May 5, 2013

trying to connect to DB remotely through Toad..

"instantclient" already downloaded and configured as below :

dll files like "oci.dll,... etc" are here : C:Oracleinstantclientinstantclient_11_2
Created both tnsnames.ora & listener.ora are here : C:Oracleinstantclient
etworkadmin

And did the below in user/system variables :

User Variables :
=========
TNS_ADMIN with value "C:Oracleinstantclient
etworkadmin"

System Variables :
===========
TNS_ADMIN with value "C:Oracleinstantclient
etworkadmin"
ORACLE_HOME with value "C:Oracleinstantclient"
Path edited and added this value "C:Oracleinstantclientinstantclient_11_2"

But i am still facing the below error :

ORA-12541: TNS:no listener

View 21 Replies View Related

Networking And Gateways :: Find Out Users Hostname Who Are Connecting To Database Remotely

Apr 22, 2010

i got to find out the users hostname who are connecting to database through remote,i tried with v$session bt it is showing null values in the terminal column.how to find out ?

View 8 Replies View Related

Grant Permissions On Oracle 11.2.0.3?

Apr 20, 2012

wanted to grant dbms_scheduler permission to the system user in oracle 11.2.0.3,but it is showing insufficient privileges, but my System has Sysdba rights.

SQL> SELECT * FROM v$pwfile_users WHERE sysdba='TRUE';
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE

[code]....

Revoke succeeded.

SQL> conn system
Enter password:
Connected.

[code]...

while i ran this query
"
SQL> select * from session_privs;"

for system user i have 202 rows of different privileges,but the same query for sysdba has 208 rows...

View 6 Replies View Related

SQL Query To Grant Permission?

Oct 6, 2012

I want sql querys for following scenarios

Owner of the compant wants to create 5 users,3 in following fashion

1) 2 -Must have authority of admin

2) 2- Normal user

3) 1 - User whose password is blocked for 15 days

View 10 Replies View Related

SQL & PL/SQL :: Grant Privilege To Role

Jun 3, 2010

I have a stored procedure which when executed creates a user and grants some roles to the user and also makes certain roles DEFAULT using "alter user"

The issue comes when i execute the procedure.

I have a User who has a role and this role has the "alter user" privilege, for example lets say that the user1 has the role ABC, and role ABC has the "alter user" privilege.

SQL> grant alter user to ABC;
Grant succeeded.
SQL> grant ABC to user1;
Grant succeeded.

Now, when i run the stored procedure as the user1, it gives me an error on a line saying "insufficient privileges", when i check the line, its this line:
alter user user1 DEFAULT ROLE "ROLE1", "ROLE2"

But as far as i know the user1 has the "alter user" privilege

I want to make those two roles ROLE1, ROLE2 DEFAULT because i don't want the other roles ROLE3,ROLE4 to be default, as you know if there are many roles and if we alter user with default for certain roles other roles become DEFAULT=NO.

So i get the error at "alter user" statement though the user has the "alter user" privilege, what do you think might be wrong?

OR is there anyway to grant roles to the user with default=NO option?

View 9 Replies View Related

SQL & PL/SQL :: Grant Alter Table?

Jul 26, 2011

if a user have alter table gant but could not alter .. what additional grant it need

SQL> alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3));
alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3))
*
ERROR at line 1:
ORA-00942: table or view does not exist

View 11 Replies View Related

PL/SQL :: How To Grant Privilege In DBA_TAB_PRIVS

Apr 2, 2013

I logged in as system and I just execute below script, however Im getting error "SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist""

GRANT execute ON UTL_FILE TO USER1;

How to grant privilege on ult_file?

View 5 Replies View Related

PL/SQL :: Grant Table Access

Aug 15, 2013

I have a schema TEST1 and it has a view 'TEST_VIEW'. I granted read only permission on the view to TEST2 schema. grant select on TEST_VIEW to TEST2. 

But TEST2 is used  by an application which requires to read view metadata and then sync with application afterwords start reading. Is there any special permission to grant to access metadata of the view / table.

View 4 Replies View Related

SQL & PL/SQL :: Grant Permission Revoke?

Dec 16, 2011

Is there a way to find out who and how was the GRANT permission revoked from user.Why i am asking is , i see a grant permission exist for a user and has been revoked later.

View 13 Replies View Related

SQL & PL/SQL :: Give Select Grant For Few Tables?

Jul 8, 2010

I created a user and granted connect,resource priviliges. I gave access to this user for only 5 tables. when i check it later, other tables are also given access. How can i avoid this and give access to selected tables.

View 9 Replies View Related

SQL & PL/SQL :: Grant Execute On More Than One Procedure To A Role

Aug 9, 2010

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

Is there a way to grant EXECUTE on a group of procedures/functions/packages to a particular role , in a single statement ? Or we have to do it one by one... like
GRANT EXECUTE on event_main to role1,
GRANT EXECUTE on event_main2 to role1,
GRANT EXECUTE on event_main3 to role1,

View 2 Replies View Related

SQL & PL/SQL :: Grant Table Permissions To Another User

Jul 11, 2012

I have two user a and b. I have to Grant select update insert Delete Permission to all tables of user b To User a. how can it possible?

View 5 Replies View Related

Security :: How To Grant A Percentage Tablespace

Mar 13, 2011

Is it possible to grant a user a percentage amount of a specified tablespace ? for instance granting a user called userx 5% of USERS tablespace. How can I do that ?

View 3 Replies View Related

SQL & PL/SQL :: Grant Access On Index To User2?

Feb 10, 2011

I have an Index in User1 schema. can i grant access on this Index to User2. if so, what privs i can give..?

for proc's we will give "Grant Execute on proce1 to User2". Like this how on Indexes..

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved