What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
Submitted by: AdministratorFor 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" schema:
>.insqlplus /nolog
SQL> CONNECT DEV/developer
SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.ggl';
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/globalguideline
SQL> GRANT DELETE ANY TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.ggl';
1 row deleted.
As you can see, "dev" can delete rows in any schema now. But you should be careful when giving this privilege to a regular developer.
Submitted by: Administrator
>.insqlplus /nolog
SQL> CONNECT DEV/developer
SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.ggl';
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/globalguideline
SQL> GRANT DELETE ANY TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.ggl';
1 row deleted.
As you can see, "dev" can delete rows in any schema now. But you should be careful when giving this privilege to a regular developer.
Submitted by: Administrator
Read Online Oracle Database Job Interview Questions And Answers
Top Oracle Database Questions
☺ | How To Write Date and Time Interval Literals in Oracle? |
☺ | What Happens to Indexes If You Drop a Table? |
☺ | How To Use "OUT" Parameter Properly? |
☺ | How To Use Values from Other Tables in UPDATE Statements using Oracle? |
☺ | What Is the Relation of a User Account and a Schema? |
Top DB Oracle Categories
☺ | Oracle PL-SQL Interview Questions. |
☺ | Oracle DBA Interview Questions. |
☺ | Oracle D2K Interview Questions. |
☺ | OCI Interview Questions. |
☺ | Forms Reports Interview Questions. |