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.
2016-10-16, 464👍, 0💬
Related Topics:
