Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6

(Continued from previous part...)

What Privilege Is Needed for a User to Query Tables in Another Schema?

For a user to run queries (SELECT statements) on tables of someone else's schema, he/she needs the SELECT ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to query tables in "hr" schema:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer

SQL> SELECT COUNT(*) FROM hr.employees;
ORA-01031: insufficient privileges

SQL> disconnect
SQL> connect SYSTEM/fyicenter

SQL> GRANT SELECT ANY TABLE TO dev;
Grant succeeded.

SQL> disconnect
SQL> CONNECT DEV/developer

SQL> SELECT COUNT(*) FROM hr.employees;
  COUNT(*)
----------
       107

As you can see, "dev" can query tables in any schema now.

You also need to remember that table name must be prefixed with the schema name (same as owner user name).

What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?

For a user to insert rows into tables of someone else's schema, he/she needs the INSERT ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to insert rows in "hr" schema:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer

SQL> INSERT INTO hr.jobs 
  VALUES ('DV.FYI', 'Dev FYI Consultant', 7700, 8800);
ORA-01031: insufficient privileges

SQL> disconnect
SQL> connect SYSTEM/fyicenter

SQL> GRANT INSERT ANY TABLE TO dev;
Grant succeeded.

SQL> disconnect
SQL> CONNECT DEV/developer

SQL> INSERT INTO hr.jobs 
  VALUES ('DV.FYI', 'Dev FYI Consultant', 7700, 8800);
1 row created.

As you can see, "dev" can insert rows in any schema now. But you should be careful when giving this privilege to a regular developer.

What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?

For a user to delete rows from tables of someone else's schema, he/she needs the DELETE ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to delete rows in "hr" schema:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer

SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.FYI';
ORA-01031: insufficient privileges

SQL> disconnect
SQL> connect SYSTEM/fyicenter

SQL> GRANT DELETE ANY TABLE TO dev;
Grant succeeded.

SQL> disconnect
SQL> CONNECT DEV/developer

SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.FYI';
1 row deleted.

As you can see, "dev" can delete rows in any schema now. But you should be careful when giving this privilege to a regular developer.

(Continued on next part...)

Part: 1 2 3 4 5 6

More...