Oracle - Privilege to Insert Rows in Another Schema in Oracle

Privilege to Insert Rows in Another Schema in Oracle

Q

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

✍: 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.

⇒Managing Oracle User Accounts, Schema and Privileges

⇒⇒Oracle Database Tutorials

2019-06-11, 515👍, 0💬

💬 2017-07-10 sasi: Nice tutorial..

Show Privileges of the Current User in Oracle
How To Find Out What Privileges a User Currently Has in Oracle? Privileges granted to users are listed in two system views: DBA_SYS_PRIVS, and USER_SYS_PRIVS. You can find out what privileges a user currently has by running a query on those views as shown in the tutorial exercise below: >.\bi... 2019-06-11, 691👍, 0💬

Privilege to Insert Rows in Another Schema in Oracle
What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema in Oracle? 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" sch... 2019-06-11, 516👍, 0💬

Privilege to Query Tables in Another Schema in Oracle
What Privilege Is Needed for a User to Query Tables in Another Schema in Oracle? 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 i... 2019-06-11, 516👍, 0💬

Privilege to Delete Rows in Another Schema in Oracle
What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema in Oracle? 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 you a good example of granting "dev" to delete rows in "hr" s... 2019-06-11, 492👍, 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.