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).
⇒ Privilege to Insert Rows in Another Schema in Oracle
⇐ Privilege to Create Indexes in Oracle
2019-06-11, 1612🔥, 0💬
Popular Posts:
How To Create a Table Index in Oracle? If you have a table with a lots of rows, and you know that on...
What Happens If the UPDATE Subquery Returns Multiple Rows in MySQL? If a subquery is used in a UPDAT...
How To Generate CREATE VIEW Script on an Existing View in SQL Server? If you want to know how an exi...
How To Assign Debug Privileges to a User in Oracle? In order to run SQL Developer in debug mode, the...
How To Verify Your PHP Installation in MySQL? PHP provides two execution interfaces: Command Line In...