GRANT Privilege In SQL

Question

Fast :

GRANT SELECT ON SYSTEM.* TO appadmin;

I want to grant AppAdmin the rights of SELECT on all tables of the database

I'm using Oracle SQL, why does my statement not work ?

Do you want to give the `AppAdmin` permission to query any table in the database? Any table in the `system` schema? Or any data dictionary table? The first is possible though the privilege is very powerful and should generally be avoided. The second isn't possible in a single statement and doesn't make a whole lot of sense unless you're really using a different schema. The last would be wither the `SELECT ANY DICTIONARY` privilege or the `SELECT_CATALOG_ROLE` role. — Justin Cave, Aug 11 '15 at 15:12
I have an error : _invalid table name at *_. I want to give `appadmin` the select privilege on any tables of database . — Forrest, Aug 11 '15 at 15:26
That depends. Exactly which tables are you trying to give the user access to? — Justin Cave, Aug 12 '15 at 02:29

score 1 · Answer 1 · edited Oct 27 '15 at 14:51

Using the ANY keyword in reference to a system privilege means that the user can perform the privilege on any objects owned by any user except for SYS. By default, if you are granted a privilege, you cannot assign your privilege to others. You cannot grant or revoke that privilege to or from anyone else.

Sometimes you want to grant privileges to users and have them be able to grant those privileges to other users. When this is the case, we include the with admin keyword in the grant command. When this keyword is used, it will allow the user granted the privilege to grant that privilege to other users.

Here is an example of the usage of the with admin option keyword.

GRANT SELECT ANY TABLE TO User;

score 0 · Answer 2 · answered Oct 27 '15 at 12:02

0

GRANT SELECT ANY TABLE TO YOUR_USER;

answered Oct 27 '15 at 12:02

Tedezed

421
5
7

GRANT Privilege In SQL

2 Answers2