Oracle General - What Privilege Is Needed for a User to Query Tables in Another Schema
Interview Question Database For Software Developers
|
|
| What Privilege Is Needed for a User to Query Tables in Another Schema | | What Privilege Is Needed for a User to Query Tables in Another Schema? - Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges | | By: FYIcenter.com | 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).
| | ID: 707 | Rank: 1212 | Votes: 0 | Views: 58 | Submitted: 20070501 |
Copyright © 2010 FYIcenter.com
All rights in the contents of this Website are reserved by the individual author.
No part of the contents may be reproduced in any form without author's permission.
|
|
|