16.6 Data Grant Behavior on Dropping Users or Objects

Learn how existing data grants are affected when standard database users, local end users, data roles, tables, views, or columns are dropped. Understanding this behavior is important for maintaining a consistent and predictable security configuration as your schema evolves.

Topics:

16.6.1 Impact of Dropping Database Users

When you drop a standard database user using DROP USER, the database automatically drops the following data grants as a cascading effect.

  • All data grants created in the schema of the dropped user (that is, all grants for which the dropped user is the schema or owner).
  • All data grants created on objects owned by the dropped user.

Note:

The cascade removal of data grants occurs automatically as part of the standard DROP USER cascade semantics. No separate cleanup is required.

16.6.2 Impact of Dropping End Users or Data Roles

When you drop a local end user or a data role, the following changes are made to existing data grants.

  • The dropped end user or data role is removed from the grantee list of existing data grants.
  • If the dropped end user or data role was the only grantee in a data grant, the entire data grant is dropped. This behavior ensures that orphaned data grants (grants with no remaining grantees) do not persist after a grantee is removed.

16.6.3 Impact of Dropping Tables and Views

When you drop a table or view, all data grants that reference the dropped object in their ON clause are automatically dropped as well.

  • This applies regardless of which schema owns the data grants.
  • All data grants on the dropped object are removed, including those created by different administrators in different schemas.

Note:

Dropping a table with associated data grants bypasses the recycle bin and permanently deletes all related objects. Neither the table nor its data grants can be recovered. This prevents security vulnerabilities that could occur if a table were restored without its associated data grants.

16.6.4 Impact of Dropping or Renaming Columns

When you drop or rename a column in a table, any column-level data grant that references that column becomes invalid and returns an error at run time.

For example, consider the following data grant:

CREATE OR REPLACE DATA GRANT employees_own_record AS
    SELECT,
    UPDATE (first_name, phone),
    INSERT (ALL COLUMNS EXCEPT ssn, salary)
    ON hr.employees
    WHERE email = ORA_END_USER_CONTEXT.username
    TO employee_role;

After dropping the phone column: 

ALTER TABLE hr.employees DROP COLUMN phone;

The column-level data grant for the phone column becomes invalid and returns an error at run time.

You can query the DBA_DATA_GRANTS view to identify data grants with invalid columns and replace the data grants as needed. Adding the column back to the table definition also resolves the invalid data grant issue.

16.6.5 Impact of Creating or Replacing Views

When a view is replaced using CREATE OR REPLACE VIEW and a column referenced by a data grant with column-level privileges is no longer present in the new view definition, the data grant becomes invalid and returns an error at run time.

For example, consider the following view and data grant:

CREATE OR REPLACE VIEW hr.employees_view AS
  SELECT * FROM hr.employees;
 
CREATE OR REPLACE DATA GRANT employees_own_view_record AS
  SELECT, UPDATE (first_name, phone),
  INSERT (ALL COLUMNS EXCEPT ssn, salary)
  ON hr.employees_view
  WHERE email = ORA_END_USER_CONTEXT.username
  TO employee_role;

After replacing the view with a definition that removes the phone column: 

CREATE OR REPLACE VIEW hr.employees_view AS
  SELECT employee_id, first_name, last_name, ssn, salary, email, manager
  FROM hr.employees;

The column-level data grant for the phone column becomes invalid and returns an error at run time.

You can query the DBA_DATA_GRANTS view to identify data grants with invalid columns and replace the data grants as needed. Adding the column back to the view definition also resolves the invalid data grant issue.