Managing Roles   «Prev  Next»

Lesson 2 System privileges
ObjectiveExplain how system privileges differ from object privileges

System Privileges compared to Object Privileges

Oracle has two fundamental types of privileges:
  1. Object privileges
  2. System privileges

Object privileges control access to tables, indexes, and other objects. If you want a user to be able to select from a table, you grant him access to that table. Because the privilege applies to a specific object, a table in this case, it is referred to as an object privilege. The users who own the objects manage the object privileges, whereas system privileges are ultimately managed by the DBA, and are used to control the actions a user can take while connected to a database. For example, to allow a user to create a table in the first place, you need to grant that user the CREATE TABLE system privilege.

The following table contrasts the two types of privileges:
Object privileges allow a user to: System privileges allow a DBA to:
  1. Insert rows into a table
  2. Query a table
  3. Create foreign-key constraints on a table
  1. Create tables and indices
  2. Create stored procedures
  3. Create users
  4. Modify users' access

Granting Object Privileges

Object privileges render to the grantee the permission to use a schema object[1] owned by another user in a particular way. As you will see, there are several types of object privileges, some of which apply only to certain schema objects. For example, the INDEX privilege applies only to tables, and the SELECT privilege applies to tables, views, and sequences. The following object privileges can be granted individually, can be granted grouped in a list, or can be granted with the keyword ALL to implicitly grant all available object privileges for a particular schema object.
Warning: Be careful when using the key term ALL since it may implicitly grant powerful privileges.

Table Object Privileges

Oracle Database 12c provides several object privileges for tables. These privileges give the table owner considerable flexibility in controlling how schema objects are used and by whom.

Commonly Granted Privileges

The following privileges are commonly granted, and you should know them well:
  1. SELECT: This is the most commonly used privilege for tables. With this privilege, the table owner permits the grantee to query the specified table with a SELECT statement
  2. INSERT: This permits the grantee to create new rows in the specified table with an INSERT statement.
  3. UPDATE: This permits the grantee to modify existing rows in the specified table with an UPDATE statement.
  4. DELETE: This permits the grantee to remove rows from the specified table with a DELETE statement.

[1]Schema objects:Schema objects are logical data storage structures. Schema objects do not have a one-to-one correspondence to physical files on disk that store their information.