| ORA-01720: grant option does not exist for '%%' [message #595269] |
Mon, 09 September 2013 15:19  |
 |
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 #595273 is a reply to message #595270] |
Mon, 09 September 2013 16:25  |
|
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:35This is not valid Oracle SQL:select
x.1, y.1
from
schema_a.table_1 as x,
schema_a.table_2 as y
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.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
