Answers

Question and Answer:

  Home  Oracle Database

⟩ What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?

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" 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.

 146 views

More Questions for you: