Home » SQL & PL/SQL » SQL & PL/SQL » Reg Grants to the Synonym created in one user to another user (Oracle 10g)
Reg Grants to the Synonym created in one user to another user [message #439723] Tue, 19 January 2010 04:05 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

Is it possible to give garants for the synonym created in current user to another user in oracle 10g

For ex:

1. Creating a synonym xxx to user test from test1

CREATE SYNONYM xxx FOR test.xxx;


now iam giving the grants for xxx table to another user test2

grant select on test1.xxx to test2


For better understanding

1. I am creating a synonym as below for one user test1,and also given grant all permission for xxx table in test user

CREATE SYNONYM xxx FOR test.xxx;


2. now i need to give grants for the table xxx (synonym table)to another user test2 like below script

grant select on test1.xxx to test2


but i am getting insufficient privillege error for it

is the above scenario is possible pls reply for this

thanks in advance

[Updated on: Tue, 19 January 2010 04:52]

Report message to a moderator

Re: Reg Grants to the Synonym created in one user to another user [message #439734 is a reply to message #439723] Tue, 19 January 2010 05:09 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
WITH GRANT OPTION might be what you are looking for.
Re: Reg Grants to the Synonym created in one user to another user [message #439735 is a reply to message #439723] Tue, 19 January 2010 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not clear who creates what and who grants what.

Post the SQL*Plus session that does it, including the connect or show user statements, then we can see exactly what you do (which may not be what you are thinking you're doing).

Regards
Michel
Re: Reg Grants to the Synonym created in one user to another user [message #439759 is a reply to message #439735] Tue, 19 January 2010 06:06 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> sho user
USER is "SCOTT"
SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> CREATE OR REPLACE SYNONYM simple FOR emp;

Synonym created.

SQL> grant select on scott.emp to raghav;

Grant succeeded.

SQL> conn raghav/raghav
Connected.
SQL> desc scott.emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> desc emp
ERROR:
ORA-04043: object emp does not exist


SQL> conn scott/bhanutej
Connected.
SQL> grant select on scott.simple to raghav;

Grant succeeded.

if i read it properly....
is this you are looking for?


sriram Smile
Re: Reg Grants to the Synonym created in one user to another user [message #439760 is a reply to message #439735] Tue, 19 January 2010 06:10 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
My scenario

1.creating a table in user test

create table t as select '1' as col from dual;


and then giving the grants to test1 user as below

grant all on test.t to test1; 


2. And now i am creating a synonym for test.t table from test1

CREATE SYNONYM t FOR test..t;  


3.And again now i am giving the grants to another user test2 as below

  grant select on t to fxcnosstdf
Error at line 1
ORA-01031: insufficient privileges


This is what i am getting is this correct or any privilege is there to correct this

Thanks,
Re: Reg Grants to the Synonym created in one user to another user [message #439762 is a reply to message #439734] Tue, 19 January 2010 06:19 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Littlefoot wrote on Tue, 19 January 2010 12:09
WITH GRANT OPTION might be what you are looking for.

Gee, why don't you read what's being said?
SQL> connect scott/tiger@ora10
Connected.

SQL> create table t as select '1' col from dual;

Table created.

SQL> grant all on t to mike with grant option;     --> WITH GRANT OPTION

Grant succeeded.

Second user:
SQL> connect mike/lion@ora10
Connected.

SQL> create synonym t for scott.t;

Synonym created.

SQL> grant all on t to test2;

Grant succeeded.                                  --> Look, mum, it works!

SQL>
Re: Reg Grants to the Synonym created in one user to another user [message #439783 is a reply to message #439762] Tue, 19 January 2010 07:04 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

Thanks for the reply now it works.

but is it possible to do without the "with grant option"

Thanks
Re: Reg Grants to the Synonym created in one user to another user [message #439785 is a reply to message #439783] Tue, 19 January 2010 07:11 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, you have to allow the user to grant the privilege and this is the purpose of grant option, or let the first user (and owner) make all grants.

Regards
Michel

[Updated on: Tue, 19 January 2010 07:11]

Report message to a moderator

Previous Topic: How to merge more columns into a single row in Oracle? (merged)
Next Topic: UPDATE GLOBAL INDEXES vs. UPDATE INDEXES
Goto Forum:
  


Current Time: Tue Sep 27 21:11:38 CDT 2016

Total time taken to generate the page: 0.12507 seconds