Home » RDBMS Server » Security » ORA-01720: grant option does not exist for '%%' (Oracle 11g)
ORA-01720: grant option does not exist for '%%' [message #595269] Mon, 09 September 2013 15:19 Go to next message
leeland
Messages: 2
Registered: August 2013
Location: USA
Junior Member
First off, want to say hi and thanks for this site. I am new to the Oracle world, moving over from the MSSQL side of things after 8 years.

I am pretty green when it comes to Oracle, haven't received any training at this point, that is coming very soon...

In the mean-time I am forced to be a google DBA on the days my senior Oracle partner isn't available.

I have such an event that isn't working out and I wanted to explain it out and see if I can get peoples opinion on what I am doing wrong.

Scenerio: Trying to grant SELECT rights to a view and getting an error. (ORA-01720: grant option does not exist for '%%')


Have a view (already in place) - schema_1.view_some_name

select
x.1, y.1
from
schema_a.table_1 as x,
schema_a.table_2 as y



have an account I wish to grant access to the view on called Grant_View_Access


I granted the following first because I didn't know

grant select on schema_a.table_1 to Grant_View_Access;
grant select on schema_a.table_2 to Grant_View_Access;

-- both succeed

-- then run
GRANT SELECT ON schema_1.view_some_name TO Grant_View_Access;

-- raises the following
ERROR at line 1:
ORA-01720: grant option does not exist for 'schema_a.table_1'

After reading some, it seems like I needed to use the 'with grant option' with the statement but the result is the same.

GRANT SELECT ON schema_1.view_some_name TO Grant_View_Access with grant option;

The above statement errors out with the "grant option does not exist" error...


Can someone walk me through this to see where I am not connecting?


Thanks in advance,

Lee





Re: ORA-01720: grant option does not exist for '%%' [message #595270 is a reply to message #595269] Mon, 09 September 2013 15:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

You did a fine job of obfuscating reality.

It works out best when schema owner actually issues the necessary GRANT for objects it own.
You should NOT be (ab)using SYS schema for daily activity & maintenance.

Re: ORA-01720: grant option does not exist for '%%' [message #595271 is a reply to message #595269] Mon, 09 September 2013 15:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
You could grant privilege on a view ONLY if you have same privilege on the underlying objects WITH GRANT OPTION.
Re: ORA-01720: grant option does not exist for '%%' [message #595272 is a reply to message #595269] Mon, 09 September 2013 15:35 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This is not valid Oracle SQL:
select
x.1, y.1
from
schema_a.table_1 as x,
schema_a.table_2 as y
can you show what you are actually doing?
Re: ORA-01720: grant option does not exist for '%%' [message #595273 is a reply to message #595270] Mon, 09 September 2013 16:25 Go to previous messageGo to next message
leeland
Messages: 2
Registered: August 2013
Location: USA
Junior Member
BlackSwan wrote on Mon, 09 September 2013 15:25


Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

You did a fine job of obfuscating reality.

It works out best when schema owner actually issues the necessary GRANT for objects it own.
You should NOT be (ab)using SYS schema for daily activity & maintenance.



Thanks for the link, I did read that and I did search, there were only two other entries I could see with that ORA-01720.

Outside of the link I am not sure why the harsh comments...obfuscating reality? Really? Bet you feel good about yourself writing stupid comments like that.



John Watson wrote on Mon, 09 September 2013 15:35
This is not valid Oracle SQL:
select
x.1, y.1
from
schema_a.table_1 as x,
schema_a.table_2 as y
can you show what you are actually doing?



Sorry if what i wrote was not syntactically 100% correct...


The view I am referring to has the following syntax which I changed the names for posting. It is two tables joined together by one column. Both in the same schema.

-- view definition, I changed the actual column names just for reference.


CREATE OR REPLACE FORCE VIEW "schema_2"."V_view" ("order_no","invoice_no","customer_no") AS
SELECT
order_no,
invoice_no,
customer_no
FROM
schema_1.table_1,
schema_1.table_2
WHERE
order_no=order_no



My first try I ran the grant statements against the tables with my dba account.

GRANT SELECT ON schema_1.table_1 TO srv_acct_1;
GRANT SELECT ON schema_1.F4211 TO srv_acct_1;

grant succeeded
-- The first two worked.

GRANT SELECT ON schema_2.V_view TO srv_acct_1;
-- this fails


me@database_name SQL> GRANT SELECT ON schema_2.V_view TO srv_acct_1;
GRANT SELECT ON schema_2.V_view TO srv_acct_1
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'schema_1.table_1'

That is where I am at.

This is a non-prod environment, I used the sysadmin account to simply test to see if the command would work and it did but the security didn't seem to apply as that account cannot select off that view.

Hope this clears up my obfuscation of reality.
Re: ORA-01720: grant option does not exist for '%%' [message #595274 is a reply to message #595273] Mon, 09 September 2013 16:33 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
WITH GRANT OPTION.

Already suggested, did you try? Any luck?
Re: ORA-01720: grant option does not exist for '%%' [message #595275 is a reply to message #595274] Mon, 09 September 2013 16:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
With Oracle everything is forbidden; except that which has been explicitly GRANTED.

what any user can do depends upon who they are & which GRANTs have been issued to them.

> I used the sysadmin account
Oracle DB does not come with any "sysdmin" account by default.

>to simply test to see if the command would work and it did but the security didn't seem to apply as that account cannot select off that view.

do NOT tell us what you think you did & report what you think you saw! use COPY & PASTE to show us exactly what was done & how Oracle responded.
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

The USER issuing the SELECT must previously have GRANT for both tables plus the VIEW.

Re: ORA-01720: grant option does not exist for '%%' [message #595297 is a reply to message #595269] Tue, 10 September 2013 00:17 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Please do not just tell us what you do, SHOW us: Use SQL*Plus and copy and paste your session, the WHOLE session, including a "show user" for each session.

Regards
Michel
Previous Topic: Virtual Private Database update policy on complex views
Next Topic: VPD function for column masking with inner-select
Goto Forum:
  


Current Time: Thu Mar 28 09:42:38 CDT 2024