What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
SQL tutorial | Interview questions | Oracle :: Interview questions :: Oracle DBA Interview questions
Page 1 of 1
What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
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.
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.
Similar topics
» What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
» What Privilege Is Needed for a User to Query Tables in Another Schema?
» How To List All Tables in Your Schema?
» What Is the Relation of a User Account and a Schema?
» What is a Database Schema?
» What Privilege Is Needed for a User to Query Tables in Another Schema?
» How To List All Tables in Your Schema?
» What Is the Relation of a User Account and a Schema?
» What is a Database Schema?
SQL tutorial | Interview questions | Oracle :: Interview questions :: Oracle DBA Interview questions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
Sun Jan 31, 2010 12:35 am by Micheal
» Accessing tables of other users in SQL
Sun Jan 31, 2010 12:34 am by Micheal
» Grouping commands in SQL
Sun Jan 31, 2010 12:32 am by Micheal
» Operations on Result Sets in SQL
Sun Jan 31, 2010 12:31 am by Micheal
» Subqueries in SQL
Sun Jan 31, 2010 12:30 am by Micheal
» Joining Relations in SQL
Sun Jan 31, 2010 12:28 am by Micheal
» Delete comand in SQL
Sun Jan 31, 2010 12:27 am by Micheal
» Update command in SQL
Sun Jan 31, 2010 12:26 am by Micheal
» Insert command in SQL
Sun Jan 31, 2010 12:23 am by Micheal