Home » RDBMS Server » Security » ORA-01720: grant option does not exist for 'SYS.DUAL' (Oracle,10g,Linux)
ORA-01720: grant option does not exist for 'SYS.DUAL' [message #391211] Wed, 11 March 2009 08:04 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,

i have created bus_ui_v view in one user i.e master. i given insert,update,delete privilage to other user.i.e slave.i got the ORA-01720: grant option does not exist for 'SYS.DUAL' error for doing this.

scripts:

create or replace view bus_ui_v
as
select 1 as ticket
from dual;

grant insert.update,delete on bus_ui_v to slave.

ORA-01720: grant option does not exist for 'SYS.DUAL'


please give me the your suggestion
Re: ORA-01720: grant option does not exist for 'SYS.DUAL' [message #391217 is a reply to message #391211] Wed, 11 March 2009 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can grant privileges on a view ONLY if you have the same privileges on the underlying objects WITH GRANT OPTION.

Regards
Michel
Re: ORA-01720: grant option does not exist for 'SYS.DUAL' [message #391219 is a reply to message #391211] Wed, 11 March 2009 08:22 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

SQL> create or replace view babu_v as select * from dual;

View created.

SQL>
SQL> show user
USER is "SYS"
SQL>
 
SQL> grant insert,delete,update on babu_v to scott;

Grant succeeded.

==

SQL> create or replace view tst_v as select 1 as tic from dual;

View created.

SQL> select * from tst_v;

       TIC
----------
         1

SQL>
SQL> grant insert,update,delete on tst_v to scott;
grant insert,update,delete on tst_v to scott
                              *
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.DUAL'
Re: ORA-01720: grant option does not exist for 'SYS.DUAL' [message #391230 is a reply to message #391219] Wed, 11 March 2009 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does this show? What does this explain? We don't even know the user that has executed the second part.

Regards
Michel
Re: ORA-01720: grant option does not exist for 'SYS.DUAL' [message #391231 is a reply to message #391217] Wed, 11 March 2009 09:06 Go to previous messageGo to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
sorry, i did'nt understand..

Could u explain briefly.
Re: ORA-01720: grant option does not exist for 'SYS.DUAL' [message #391234 is a reply to message #391211] Wed, 11 March 2009 09:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Normally a user can not grant access on one of their objects (bus_ui_v in this case) to another user if that object references other objects that belong to a third user (dual in this case as it belongs to sys).

The only way around that is to make use of WITH GRANT OPTION when granting access to the sys object.
You should read up on grants in the documentation.

That said - what is the point of this view?
And you should never grant insert/update/delete access to dual to anyone ever.
Re: ORA-01720: grant option does not exist for 'SYS.DUAL' [message #391239 is a reply to message #391211] Wed, 11 March 2009 09:33 Go to previous messageGo to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
yes.. i have execueted these scripts in sys account.the privilages are execueted sucessfully.

Can u tell me your idea with scripts. i did'nt understand the your ideas.please suggest me.
Re: ORA-01720: grant option does not exist for 'SYS.DUAL' [message #391257 is a reply to message #391239] Wed, 11 March 2009 09:57 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Concepts

Read "Overview of Security Features" section , and first section of chapter 20: "Introduction to Database Security".
Then follow the link in this section.

Regards
Michel
Previous Topic: SQL
Next Topic: Defining a Oracle User password to never expire
Goto Forum:
  


Current Time: Thu Mar 28 16:15:03 CDT 2024