Home » SQL & PL/SQL » SQL & PL/SQL » grant option does not exist
grant option does not exist [message #264455] Mon, 03 September 2007 07:11 Go to next message
lokhande.dinesh29
Messages: 36
Registered: May 2007
Member
Hi all,

I am having a problem with a grant. Here is the scenario.

User A has created view V in user A's schema. View V is based on table T in schema B. User C has DBA privileges. User C tries to grant delete on view V to user D and gets the following error:

ORA-01720 grant option does not exist for 'B.T'

please reply me on this....

Regards,
Dinesh
Re: grant option does not exist [message #264468 is a reply to message #264455] Mon, 03 September 2007 08:12 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
ORA-01720 grant option does not exist for 'string.string'
Cause: A grant was being performed on a view and the grant option 
was not present for an underlying object.
Action: Obtain the grant option on all underlying objects of the view.
Re: grant option does not exist [message #264478 is a reply to message #264468] Mon, 03 September 2007 08:38 Go to previous messageGo to next message
lecorr
Messages: 17
Registered: October 2006
Location: Courbevoie, France
Junior Member
Hi,

To access a view, you need to have also the grants on the undeliyng objects (tables, views...).
To give access to another user through your own object, you have to have the grant option.

So in your case, you should do:
grant select ... on table B.T to A WITH GRANT OPTION;

THEN

grant select on view A.V to B;

Regards,

Christian

Re: grant option does not exist [message #264531 is a reply to message #264478] Mon, 03 September 2007 11:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since it is the DBA getting the error, I agree with the original poster that this is a weird situation.
Re: grant option does not exist [message #264538 is a reply to message #264531] Mon, 03 September 2007 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not if OP has a version less than 9i.

Regards
Michel
Re: grant option does not exist [message #264549 is a reply to message #264538] Mon, 03 September 2007 13:25 Go to previous messageGo to next message
lecorr
Messages: 17
Registered: October 2006
Location: Courbevoie, France
Junior Member
Hi,

I don't know.

I searched to confirm my point on view. It seem metalink agree with my views on this message.

We have to wait on the test made by Dinesh.

Regards,

Christian
Re: grant option does not exist [message #264552 is a reply to message #264549] Mon, 03 September 2007 13:46 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To be able to grant access to a view you need to have direct access to the underlying objects with grant option, if you are in 8i or below.
In 9i and upper, DBA has GRANT ANY OBJECT PRIVILEGE privilege and so can do it.

MICHEL> create or replace view v as select * from t;

View created.

MICHEL> connect system/michel
Connected.
SYSTEM> grant delete on michel.v to test;

Grant succeeded.

SYSTEM> 


Regards
Michel
Previous Topic: Help Needed with a subquery
Next Topic: ORA-06519:Active autonomous transaction detected and rolled back : Error
Goto Forum:
  


Current Time: Sat Dec 10 09:03:19 CST 2016

Total time taken to generate the page: 0.07827 seconds