What Privilege Is Needed for a User to Query Tables in Another Schema?
SQL tutorial | Interview questions | Oracle :: Interview questions :: Oracle DBA Interview questions
Page 1 of 1
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).
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).
Similar topics
» What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
» What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
» How To List All Tables in Your Schema?
» How To Join Two Tables in a Single Query?
» What Is the Relation of a User Account and a Schema?
» What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
» How To List All Tables in Your Schema?
» How To Join Two Tables in a Single Query?
» What Is the Relation of a User Account and a Schema?
SQL tutorial | Interview questions | Oracle :: Interview questions :: Oracle DBA Interview questions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
Sun Jan 31, 2010 12:35 am by Micheal
» Accessing tables of other users in SQL
Sun Jan 31, 2010 12:34 am by Micheal
» Grouping commands in SQL
Sun Jan 31, 2010 12:32 am by Micheal
» Operations on Result Sets in SQL
Sun Jan 31, 2010 12:31 am by Micheal
» Subqueries in SQL
Sun Jan 31, 2010 12:30 am by Micheal
» Joining Relations in SQL
Sun Jan 31, 2010 12:28 am by Micheal
» Delete comand in SQL
Sun Jan 31, 2010 12:27 am by Micheal
» Update command in SQL
Sun Jan 31, 2010 12:26 am by Micheal
» Insert command in SQL
Sun Jan 31, 2010 12:23 am by Micheal