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

Q

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

✍: FYIcenter.com

A

For a user to insert rows into tables of someone else's schema, he/she needs the INSERT ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to insert rows in "hr" schema:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer

SQL> INSERT INTO hr.jobs 
  VALUES ('DV.FYI', 'Dev FYI Consultant', 7700, 8800);
ORA-01031: insufficient privileges

SQL> disconnect
SQL> connect SYSTEM/fyicenter

SQL> GRANT INSERT ANY TABLE TO dev;
Grant succeeded.

SQL> disconnect
SQL> CONNECT DEV/developer

SQL> INSERT INTO hr.jobs 
  VALUES ('DV.FYI', 'Dev FYI Consultant', 7700, 8800);
1 row created.

As you can see, "dev" can insert rows in any schema now. But you should be careful when giving this privilege to a regular developer.

2007-05-01, 4475👍, 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, 5114👍, 0💬

What Are the Types PL/SQL Code Blocks
What Are the Types PL/SQL Code Blocks? - Oracle DBA FAQ - Understanding PL/SQL Language Basics There are 3 types of PL/SQL code blocks: Anonymous Block - A block of codes with no name. It may contain a declaration part, an execution part, and exception handlers. Stored Program Unit - A block of code... 2007-05-01, 5047👍, 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, 4916👍, 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, 4680👍, 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, 4535👍, 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, 4519👍, 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, 4503👍, 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, 4495👍, 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, 4480👍, 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.