What Privilege Is Needed for a User to Query Tables in Another Schema?
Submitted by: AdministratorFor 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:
>.insqlplus /nolog
SQL> CONNECT DEV/developer
SQL> SELECT COUNT(*) FROM hr.employees;
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/globalguideline
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).
Submitted by: Administrator
>.insqlplus /nolog
SQL> CONNECT DEV/developer
SQL> SELECT COUNT(*) FROM hr.employees;
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/globalguideline
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).
Submitted by: Administrator
Read Online Oracle Database Job Interview Questions And Answers
Top Oracle Database Questions
☺ | What Happens to Indexes If You Drop a Table? |
☺ | How To Recover a Dropped Table in Oracle? |
☺ | How Run SQL*Plus Commands That Are Stored in a Local File? |
☺ | How To Use Values from Other Tables in UPDATE Statements using Oracle? |
☺ | What Is the Relation of a User Account and a Schema? |
Top DB Oracle Categories
☺ | Oracle PL-SQL Interview Questions. |
☺ | Oracle DBA Interview Questions. |
☺ | Oracle D2K Interview Questions. |
☺ | OCI Interview Questions. |
☺ | Oracle RMAN Interview Questions. |