Home » RDBMS Server » Security » GRANT SELECT ON all_views not working (SQL Developer, Version 17.3.2.341, Build 341.0937)
icon9.gif  GRANT SELECT ON all_views not working [message #683176] Thu, 10 December 2020 06:08 Go to next message
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

Re: GRANT SELECT ON all_views not working [message #683177 is a reply to message #683176] Thu, 10 December 2020 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 67881
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: GRANT SELECT ON all_views not working [message #683178 is a reply to message #683176] Thu, 10 December 2020 06:25 Go to previous messageGo to next message
John Watson
Messages: 8559
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 Go to previous messageGo to next message
John Watson
Messages: 8559
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.
Re: GRANT SELECT ON all_views not working [message #683180 is a reply to message #683177] Thu, 10 December 2020 06:34 Go to previous messageGo to next message
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.
Re: GRANT SELECT ON all_views not working [message #683181 is a reply to message #683180] Thu, 10 December 2020 06:38 Go to previous messageGo to next message
cfairtp
Messages: 15
Registered: July 2020
Junior Member
Now trying John's suggestions. They look promising. Will report back
Re: GRANT SELECT ON all_views not working [message #683182 is a reply to message #683181] Thu, 10 December 2020 06:45 Go to previous messageGo to next message
cfairtp
Messages: 15
Registered: July 2020
Junior Member
Thank you John!!!!!!!!!!!!!

The BEQUEATH idea did the trick
Re: GRANT SELECT ON all_views not working [message #683183 is a reply to message #683182] Thu, 10 December 2020 06:47 Go to previous messageGo to next message
John Watson
Messages: 8559
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.
Re: GRANT SELECT ON all_views not working [message #683184 is a reply to message #683183] Thu, 10 December 2020 07:01 Go to previous messageGo to next message
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.
Re: GRANT SELECT ON all_views not working [message #683185 is a reply to message #683184] Thu, 10 December 2020 07:28 Go to previous message
Michel Cadot
Messages: 67881
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Tue Jun 15 02:20:41 CDT 2021