Collections:
Privilege to Query Tables in Another Schema in Oracle
What Privilege Is Needed for a User to Query Tables in Another Schema in Oracle?
✍: 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).
2016-10-16, 479👍, 0💬
Popular Posts:
What are DDL (Data Definition Language) statements for tables in SQL Server? DDL (Data Definition La...
How To Convert Numeric Expression Data Types using the CAST() Function in SQL Server Transact-SQL? I...
How To Calculate DATETIME Value Differences Using the DATEDIFF() Function in SQL Server Transact-SQL...
Where to find answers to frequently asked questions on Transaction Management: Commit or Rollback in...
What Are the Underflow and Overflow Behaviors on FLOAT Literals in SQL Server Transact-SQL? If you e...