14.2 Object Privileges

14.2 Object Privileges

While system privileges authorize database-wide or schema-wide actions, Object Privileges grant specific, fine-grained access rights to perform operations on designated schema objects. Every object in an Oracle database (tables, views, sequences, procedures, functions, packages, synonyms, and object types) is protected by an object privilege security layer.

Anatomy and Scope of Object Privileges

An object privilege specifies a particular action that a grantee is authorized to perform on a specific object owned by a schema.

Key Principles of Object Ownership:

• Inherent Owner Rights: The creator/owner of a schema object automatically holds all object privileges on that object. The owner can query, modify, alter, index, or drop the object without needing explicit grants.
• Irrevocable Owner Privileges: An object owner cannot revoke privileges on their own objects from themselves.
• Schema Qualification: When a grantee accesses an object owned by another schema, the object name must be qualified with the schema name (e.g., SELECT * FROM hr.employees;), unless a synonym has been created.

Object Privilege Matrix by Target Object Type

Not all object privileges apply to all object types. The table below details which privileges can be granted on each major Oracle object type:

Exam Note on Synonyms: Privileges cannot be granted directly on a synonym. When you execute a GRANT on a synonym name, Oracle translates the grant directly to the underlying base table, view, sequence, or procedure.

Syntax for Granting Object Privileges

Object privileges are granted using the GRANT statement with the ON clause specifying the target object:

GRANT { object_privilege [(column_list)] [, ...] | ALL [PRIVILEGES] }
ON [schema_name.]object_name
TO { user_name | role_name | PUBLIC } [, ...]
[WITH GRANT OPTION]
[WITH HIERARCHY OPTION];

Examples:

-- 1. Grant SELECT and INSERT on the employees table to user clerk_john
GRANT SELECT, INSERT ON hr.employees TO clerk_john;

-- 2. Grant all applicable object privileges on orders table to sales_role
GRANT ALL ON hr.orders TO sales_role;

-- 3. Grant EXECUTE on a calculation package to all database users
GRANT EXECUTE ON hr.tax_calculator_pkg TO PUBLIC;

-- 4. Grant SELECT on a sequence to allow generating primary key values
GRANT SELECT ON hr.orders_seq TO app_user;

Column-Level Object Privileges

Oracle allows administrators and object owners to restrict DML privileges to specific columns within a base table. This enables column-level security without requiring the creation of intermediate views.

Privileges That Support Column-Level Specification:

1. INSERT (column_list): Authorizes inserting data into only the specified columns. Any omitted columns must either permit NULL values or have a default value defined.
2. UPDATE (column_list): Authorizes updating values only in the specified columns.
3. REFERENCES (column_list): Authorizes creating foreign key constraints that reference only the designated columns in the parent table.

Privileges That DO NOT Support Column-Level Specification:

• SELECT: Column-level SELECT does not exist in standard Oracle SQL. Attempting GRANT SELECT (salary) ON employees TO user; raises a syntax error (ORA-00969: missing ON keyword). To restrict SELECT access to specific columns, you must create a View projecting only those columns or use Oracle Virtual Private Database (VPD) / Oracle Data Redaction.
• DELETE: DELETE removes entire rows, so column-level restriction is conceptually meaningless.
• ALTER, INDEX, EXECUTE: Apply to the entire object structure.

Column-Level Grant Examples:

-- Legal: Grant UPDATE on specific compensation columns only
GRANT UPDATE (salary, commission_pct) ON hr.employees TO payroll_clerk;

-- Legal: Grant INSERT on specific columns
GRANT INSERT (employee_id, last_name, email, hire_date, job_id) 
ON hr.employees TO hr_assistant;

-- Legal: Grant REFERENCES on primary key column to allow foreign key creation
GRANT REFERENCES (department_id) ON hr.departments TO project_schema;

The WITH GRANT OPTION Clause & Strict Role Prohibition

The WITH GRANT OPTION clause allows the grantee to delegate the granted object privilege to other users or roles.

CRITICAL EXAM RULE: WITH GRANT OPTION CANNOT Be Granted to a Role!

• A user account can receive an object privilege WITH GRANT OPTION.
• A role can NEVER receive an object privilege WITH GRANT OPTION.
• Attempting to grant an object privilege to a role WITH GRANT OPTION causes Oracle to immediately fail with error ORA-01931: cannot grant WITH GRANT OPTION to a role.

-- LEGAL: Granting to an individual user with grant option
GRANT SELECT, UPDATE ON hr.employees TO manager_bob WITH GRANT OPTION;

-- ILLEGAL: Granting to a role with grant option fails!
GRANT SELECT ON hr.employees TO dev_role WITH GRANT OPTION;
-- ORA-01931: cannot grant WITH GRANT OPTION to a role

Revoking Object Privileges & The Cascading Revocation Rule

Object privileges are revoked using the REVOKE statement:

REVOKE { object_privilege [, ...] | ALL [PRIVILEGES] }
ON [schema_name.]object_name
FROM { user_name | role_name | PUBLIC } [, ...]
[CASCADE CONSTRAINTS] [FORCE];

The Mandatory Cascading Revocation Rule on 1Z0-071

In stark contrast to system privileges, revoking an object privilege from a user who granted that privilege to others using WITH GRANT OPTION AUTOMATICALLY CASCADES throughout the entire delegation lineage.

+-------------------------------------------------------------------------+
|          OBJECT PRIVILEGE DELEGATION & CASCADING REVOCATION             |
+-------------------------------------------------------------------------+
|                                                                         |
|  [ Table Owner: HR ]                                                    |
|        |                                                                |
|        |  GRANT SELECT ON employees TO User A WITH GRANT OPTION;        |
|        v                                                                |
|  [ User A ] (Has SELECT + GRANT OPTION)                                 |
|        |                                                                |
|        |  GRANT SELECT ON hr.employees TO User B WITH GRANT OPTION;     |
|        v                                                                |
|  [ User B ] (Has SELECT + GRANT OPTION)                                 |
|        |                                                                |
|        |  GRANT SELECT ON hr.employees TO User C;                       |
|        v                                                                |
|  [ User C ] (Has SELECT)                                                |
|                                                                         |
|  .....................................................................  |
|  REVOCATION EVENT:                                                      |
|  HR executes:                                                           |
|  REVOKE SELECT ON employees FROM User A;                                |
|                                                                         |
|  CASCADING AFTERMATH:                                                   |
|  - User A LOSES SELECT privilege.                                       |
|  - User B AUTOMATICALLY LOSES SELECT privilege! (Cascaded)              |
|  - User C AUTOMATICALLY LOSES SELECT privilege! (Cascaded)              |
|                                                                         |
+-------------------------------------------------------------------------+

Detailed Cascading Scenario:

1. HR grants SELECT ON employees TO User_A WITH GRANT OPTION;
2. User_A grants SELECT ON hr.employees TO User_B WITH GRANT OPTION;
3. User_B grants SELECT ON hr.employees TO User_C;
4. HR executes: REVOKE SELECT ON employees FROM User_A;
5. Result: User_A, User_B, and User_C all lose their SELECT privilege on hr.employees.

Exam Trap: What if User_C had ALSO received SELECT ON hr.employees directly from HR or another independent grantor? User_C would retain that independent grant, but the grant path derived through User_A -> User_B is completely severed.

Revoking the REFERENCES Privilege & CASCADE CONSTRAINTS

The REFERENCES privilege allows a grantee to create foreign key constraints in their own tables that reference the primary or unique key of the grantor's table.

When revoking the REFERENCES privilege:

• If a user has already created a foreign key constraint referencing the grantor's table, a standard REVOKE REFERENCES statement will fail because dependent constraints exist (ORA-02292 / ORA-01981).
• The grantor must append the CASCADE CONSTRAINTS clause to the REVOKE statement.
• Specifying CASCADE CONSTRAINTS automatically drops all foreign key constraints created by the grantee that reference the grantor's table.

-- Attempting to revoke REFERENCES without CASCADE CONSTRAINTS fails if foreign keys exist
REVOKE REFERENCES ON hr.departments FROM app_schema;
-- ORA-01981: CASCADE CONSTRAINTS must be specified to revoke REFERENCES

-- Correct syntax: Revoke REFERENCES and drop dependent foreign key constraints
REVOKE REFERENCES ON hr.departments FROM app_schema CASCADE CONSTRAINTS;

Side-by-Side Comparison Matrices

1. System Privileges vs. Object Privileges

2. WITH ADMIN OPTION vs. WITH GRANT OPTION

3. Cascading vs. Non-Cascading Revocation Summary

+-------------------------------------------------------------------------+
|                 SUMMARY OF REVOCATION CASCADE BEHAVIOR                  |
+-------------------------------------------------------------------------+
|                                                                         |
|  TYPE OF PRIVILEGE REVOKED    | DOES IT CASCADE TO DOWNSTREAM USERS?    |
|  -----------------------------+---------------------------------------  |
|  System Privilege             | NO  (Downstream users retain privilege) |
|  Role Grant                   | NO  (Downstream users retain role)      |
|  Object Privilege             | YES (Downstream users lose privilege)   |
|  REFERENCES Object Privilege  | YES (Must use CASCADE CONSTRAINTS)      |
|                                                                         |
+-------------------------------------------------------------------------+

Data Dictionary Views for Object Privileges

Oracle 1Z0-071 Object Privilege Error Reference Matrix