OraFAQ Forum: Security » GRANT SELECT ON all

Home » RDBMS Server » Security » GRANT SELECT ON all_views not working (SQL Developer, Version 17.3.2.341, Build 341.0937)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

GRANT SELECT ON all_views not working [message #683176]

Thu, 10 December 2020 06:08

cfairtp
Messages: 15
Registered: July 2020

Junior Member

User in query window can select rows from all_views. User gets permission error from view I created that selects rows from all_views.

User is in a group that has SELECT permissions from my view. GRANT SELECT ON CHRIS.VIEWX TO VIEW_USER_GROUP ;

Is there something special I must do because all_views is a system table?

Thank you

Report message to a moderator

Re: GRANT SELECT ON all_views not working [message #683177 is a reply to message #683176]

Thu, 10 December 2020 06:15

Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use SQL*Plus and copy and paste what you do and get when you have the error.

Quote:

User gets permission error from view I created that selects rows from all_views.

Have you you the privilege to grant a privilege on all_views?
Have you the privilege to grant select on all objects in all_views definition?

Note that ALL accounts have the privilege to query ALL_VIEWS by default, unless you modified the standard Oracle privileges which is not recommended.

Report message to a moderator

Re: GRANT SELECT ON all_views not working [message #683178 is a reply to message #683176]

Thu, 10 December 2020 06:25

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

It sounds to me as though you are trying to do something that is deliberately forbidden Smile

Consider the situation where I grant you select on one of my tables. If you create a view on that table, and grant select on the view to another user, that would allow them to see my table - even though they have no permission to do so. For this to work, you would have to have been granted select on my table WITH GRANT OPTION, only then can you pass your privilege on via a view.

[Updated on: Thu, 10 December 2020 06:28]

Report message to a moderator

Re: GRANT SELECT ON all_views not working [message #683179 is a reply to message #683176]

Thu, 10 December 2020 06:33

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

It occurs to me that there is another possibility: you could create your view with BEQUEATH CURRENT_USER and then it will run with the privileges of the invoker. Since he does, by default, have select on the ALL_% views, that should work. You will of course have to grant yourself INHERIT PRIVILEGES on the other user first.

Report message to a moderator

Re: GRANT SELECT ON all_views not working [message #683180 is a reply to message #683177]

Thu, 10 December 2020 06:34

cfairtp
Messages: 15
Registered: July 2020

Junior Member

Interesting. When I try to apply the GRANT I get this error

Error report -
ORA-01720: grant option does not exist for 'SYS.ALL_VIEWS'
01720. 00000 - "grant option does not exist for '%s.%s'"
*Cause: A grant was being performed on a view or a view was being replaced
and the grant option was not present for an underlying object.
*Action: Obtain the grant option on all underlying objects of the view or
revoke existing grants on the view.

Report message to a moderator

Re: GRANT SELECT ON all_views not working [message #683181 is a reply to message #683180]

Thu, 10 December 2020 06:38

cfairtp
Messages: 15
Registered: July 2020

Junior Member

Now trying John's suggestions. They look promising. Will report back

Report message to a moderator

Re: GRANT SELECT ON all_views not working [message #683182 is a reply to message #683181]

Thu, 10 December 2020 06:45

cfairtp
Messages: 15
Registered: July 2020

Junior Member

Thank you John!!!!!!!!!!!!!

The BEQUEATH idea did the trick

Report message to a moderator

Re: GRANT SELECT ON all_views not working [message #683183 is a reply to message #683182]

Thu, 10 December 2020 06:47

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Can you post your solution? Or write it all up as a blog? It is quite an advanced area of security, and others night find it helpful.

Report message to a moderator

Re: GRANT SELECT ON all_views not working [message #683184 is a reply to message #683183]

Thu, 10 December 2020 07:01

cfairtp
Messages: 15
Registered: July 2020

Junior Member

Oy!

I successfully ran the SELECT from my admin account. Things failed when I ran it with my user account.

Report message to a moderator

Re: GRANT SELECT ON all_views not working [message #683185 is a reply to message #683184]

Thu, 10 December 2020 07:28

Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Show us what you do an get otherwise we can't know what is wrong.

And post your Oracle version.

[Updated on: Thu, 10 December 2020 07:28]

Report message to a moderator

Previous Topic:	Role Based Access for User Management
Next Topic:	ACCESSIBLE BY clause on CREATE procedural object

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 12 19:13:16 CST 2024