What Privilege Is Needed for a User to Query Tables in Another Schema

Q

What Privilege Is Needed for a User to Query Tables in Another Schema? - Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges

✍: FYIcenter.com

A

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).

2007-05-01, 4803👍, 0💬

How To Place Comments in PL/SQL
How To Place Comments in PL/SQL? - Oracle DBA FAQ - Understanding PL/SQL Language Basics There are two ways to place comments into PL/SQL codes: SQL Statement Style: Starts you comment any where in the line but prefixed with '--'. The comment ends at the end of the line. C Language Style: Starts you... 2007-05-01, 5488👍, 0💬

How To Lock and Unlock a User Account
How To Lock and Unlock a User Account? - Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges If you want to lock a user account for a short period of time, and unlock it later, you can use the ALTER USER ... ACCOUNT command. The following sample script shows how to use this command... 2007-05-01, 5289👍, 0💬

What Is PL/SQL Language Case Sensitive
What Is PL/SQL Language Case Sensitive? - Oracle DBA FAQ - Understanding PL/SQL Language Basics PL/SQL language is not case sensitive: Reserved words are not case sensitive. For example: CASE and Case are identical. Variable names and other names are not case sensitive. For example: TOTAL_SALARY and... 2007-05-01, 5285👍, 0💬

How To Assign a Tablespace to a Users
How To Assign a Tablespace to a Users? - Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges When you create a new user, Oracle will assign the SYSTEM tablespace to the user by default. If you want to change this, you can assign a different table space to a user using the ALTER USE... 2007-05-01, 5045👍, 0💬

What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema
What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema? - Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges For a user to delete rows from tables of someone else's schema, he/she needs the DELETE ANY TABLE privilege. The following tutorial exercise gives... 2007-05-01, 4939👍, 0💬

What Privilege Is Needed for a User to Create Tables
What Privilege Is Needed for a User to Create Tables? - Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges To be able to create tables in a user's own schema, the user needs to have the CREATE TABLE privilege, or the CREATE ANY TABLE privilege, which is more powerful, and allows t... 2007-05-01, 4821👍, 0💬

What Privilege Is Needed for a User to Create Indexes
What Privilege Is Needed for a User to Create Indexes? - Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges For a user to create indexes, he/she needs the same privilege as the creating tables. Just make sure he/she has the CREATE TABLE privilege. The following tutorial exercise g... 2007-05-01, 4810👍, 0💬

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 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 exercis... 2007-05-01, 4804👍, 0💬

What Privilege Is Needed for a User to Create Views
What Privilege Is Needed for a User to Create Views? - Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges To be able to create views in a user's own schema, the user needs to have the CREATE VIEW privilege, or the CREATE ANY VIEW privilege, which is more powerful, and allows the u... 2007-05-01, 4775👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.