Privilege to Delete Rows in Another Schema in Oracle

Q

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

✍: FYIcenter.com

A

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:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer

SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.FYI';
ORA-01031: insufficient privileges

SQL> disconnect
SQL> connect SYSTEM/fyicenter

SQL> GRANT DELETE ANY TABLE TO dev;
Grant succeeded.

SQL> disconnect
SQL> CONNECT DEV/developer

SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.FYI';
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.

⇒ Show Privileges of the Current User in Oracle

⇐ Privilege to Insert Rows in Another Schema in Oracle

⇑ Managing Oracle User Accounts, Schema and Privileges

⇑⇑ Oracle Database Tutorials

2019-06-11, 7572🔥, 0💬

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, 6974🔥, 0💬

Database Table: Rows of Columns in Oracle
What Is a Database Table in Oracle? A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, a... 2019-06-01, 3445🔥, 0💬

Privilege to Create Indexes in Oracle
What Privilege Is Needed for a User to Create Indexes in Oracle? For a user to create indexes, he/she needs the same privilege as the creating tables. Just make sure he/she has the CREATE TABLE privilege. The following tutorial exercise gives you a good example on creating view privilege: >.\... 2019-06-29, 3427🔥, 0💬

Privilege to Create Views in Oracle
What Privilege Is Needed for a User to Create Views in Oracle? To be able to create views in a user's own schema, the user needs to have the CREATE VIEW privilege, or the CREATE ANY VIEW privilege, which is more powerful, and allows the user to create views in other user's schema. The following tuto... 2019-06-29, 3261🔥, 0💬

Managing Oracle Database Tables
Where to find answers to frequently asked questions on Managing Oracle Database Tables? Here is a list of frequently asked questions and their answers compiled by FYIcenter.com DBA team on Managing Oracle Database Tables. Clear answers are provided together with tutorial exercises to help beginners ... 2019-06-11, 3190🔥, 0💬

Assign a Tablespace to a User in Oracle
How To Assign a Tablespace to a Users in Oracle? When you create a new user, Oracle will assign the SYSTEM tablespace to the user by default. If you want to change this, you can assign a different table space to a user using the ALTER USER command. The following tutorial exercise changes user dev's ... 2019-06-29, 3144🔥, 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, 2876🔥, 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, 2805🔥, 0💬

Create a New Table in Your Schema in Oracle
How To Create a New Table in Your Schema in Oracle? If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table: >.\bin\sqlplus /nolog SQL> connect HR/fyice... 2019-06-01, 2756🔥, 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.