Oracle Database 12c
Privileges and Roles
In an Oracle database, privileges control access to both the actions a user can perform and the objects in the database. Privileges that control access to actions in the database are called system privileges, whereas privileges that control access to data and other objects are called object privileges.
To make assignment and management of privileges easier for the DBA, a database role groups
privileges together. To put it another way, a role is a named group of privileges. In addition, a role can itself have roles assigned to it.
Privileges and roles are granted and revoked with the GRANT and REVOKE commands. The user group PUBLIC is neither a user nor a role, nor can it be dropped; however, when privileges are granted to PUBLIC, they are granted to every user of the database, both present and future.
System Privileges
System privileges grant the right to perform a specific type of action in the database, such as creating users, altering tablespaces, or dropping any view. Here is an example of granting a system privilege:
grant drop any table to scott
with admin option;
The user SCOTT can drop anyone’s table in any schema. The WITH GRANT OPTION clause
allows SCOTT to grant his newly granted privilege to other users.
Object Privileges
Object privileges are granted on a specific object in the database. The most common object
privileges are SELECT, UPDATE, DELETE, and INSERT for tables, EXECUTE for a PL/SQL stored object, and INDEX for granting index-creation privileges on a table.
In the following example, the user RJB can perform any DML on the JOBS table owned by the HR schema:
grant select, update, insert, delete
on hr.jobs to rjb;