ExamFinch.com My Study

Study guide

14.2 Object Privileges

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

14.2 Object Privileges

Key Takeaways Explain This Topic AI

Object privileges grant specific operational permissions (DML, DDL, or execution) on individual database schema objects such as tables, views, sequences, and procedures.

The schema object owner inherently holds all privileges on their own objects and cannot revoke these privileges from themselves.

Column-level privilege granting is supported exclusively for INSERT, UPDATE, and REFERENCES privileges; SELECT, DELETE, ALTER, and INDEX cannot be column-restricted.

The WITH GRANT OPTION clause allows individual users to delegate object privileges to others, but it is strictly prohibited from being granted to a database role (ORA-01931).

The Critical 1Z0-071 Cascading Revocation Rule: Revoking an object privilege from a user who delegated it to others using WITH GRANT OPTION automatically cascades, revoking the privilege from all downstream grantees.

Last updated: August 2026

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:

  • Object Privilege Tables Views Sequences Procedures / Functions / Packages Synonyms
  • SELECT Yes Yes Yes (retrieve NEXTVAL / CURRVAL ) No Inherited
  • INSERT Yes (Column-level) Yes No No Inherited
  • UPDATE Yes (Column-level) Yes No No Inherited
  • DELETE Yes Yes No No Inherited
  • ALTER Yes No Yes No Inherited
  • INDEX Yes No No No Inherited
  • REFERENCES Yes (Column-level) No No No Inherited
  • EXECUTE No No No Yes Inherited
  • READ Yes Yes No No Inherited
  • FLASHBACK Yes Yes No No Inherited

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:

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.

UPDATE (column_list) : Authorizes updating values only in the specified columns.

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:

  • HR grants SELECT ON employees TO User_A WITH GRANT OPTION;
  • User_A grants SELECT ON hr.employees TO User_B WITH GRANT OPTION;
  • User_B grants SELECT ON hr.employees TO User_C;
  • HR executes: REVOKE SELECT ON employees FROM User_A;

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
  • Architectural Dimension System Privileges Object Privileges
  • Scope Database-wide or schema-wide actions Specific operations on designated schema objects

Target Objects General DDL/DML capabilities ( CREATE TABLE , CREATE SESSION ) Concrete objects ( SELECT ON hr.employees , EXECUTE ON pkg )

Delegation Clause WITH ADMIN OPTION WITH GRANT OPTION

Grantable to Roles with Option? YES ( WITH ADMIN OPTION allowed on roles) NO ( WITH GRANT OPTION forbidden on roles - ORA-01931 )

  • Revocation Behavior DOES NOT CASCADE AUTOMATICALLY CASCADES
  • Column-Level Granularity? No Yes (for INSERT , UPDATE , REFERENCES )
  • 2. WITH ADMIN OPTION vs. WITH GRANT OPTION
  • Feature WITH ADMIN OPTION WITH GRANT OPTION
  • Applies To System Privileges and Role Grants Object Privileges only
  • Grantee Target Users and Roles Users and PUBLIC only (Roles strictly forbidden)
  • Downstream Revocation Non-cascading Cascading

Revocation Capability Grantee can revoke the privilege from any user database-wide Grantee can only revoke privileges they personally granted

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

View Name Description

USER_TAB_PRIVS Object privileges where the current user is the grantor, grantee, or object owner.

USER_TAB_PRIVS_MADE Object privileges granted on objects owned by the current user.

USER_TAB_PRIVS_RECD Object privileges granted to the current user.

USER_COL_PRIVS Column-level object privileges where the user is grantor, grantee, or owner.

USER_COL_PRIVS_MADE Column-level privileges granted on objects owned by the current user.

USER_COL_PRIVS_RECD Column-level privileges granted to the current user.

ALL_TAB_PRIVS Object privileges for which the current user is grantee, grantor, owner, or where PUBLIC is grantee.

DBA_TAB_PRIVS All object privileges granted across the entire database.

Oracle 1Z0-071 Object Privilege Error Reference Matrix

Error Code Error Message Text Root Cause on 1Z0-071

ORA-01931 cannot grant WITH GRANT OPTION to a role Attempted to execute GRANT obj_priv ON obj TO role_name WITH GRANT OPTION .

ORA-00942 table or view does not exist User lacks SELECT (or other required) object privilege on the referenced schema table, or table does not exist.

ORA-01031 insufficient privileges User attempted an operation (such as UPDATE or DELETE ) without holding the appropriate object privilege.

ORA-01981 CASCADE CONSTRAINTS must be specified to revoke REFERENCES Attempted to revoke REFERENCES privilege on a table without specifying CASCADE CONSTRAINTS when foreign keys exist.

ORA-01749 you may not GRANT/REVOKE privileges to/from yourself Object owner attempted to grant or revoke object privileges to/from their own user account.