17.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:

17.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. This includes cross-table data grants whose child object (the object named in the first ON clause) is owned by the dropped user.
  • All cross-table data grants whose parent object (the object named in the GRANTED ON clause) is owned by the dropped user.
The removal of grants does not propagate transitively along a chain of cross-table data grants. For example, consider the following two cross-table data grants, which form a hierarchy from customers in the customer_app schema, to orders in the order_app schema, to order_items in the fulfillment_app schema:
CREATE DATA GRANT order_app.orders_by_customer
  AS SELECT
  ON order_app.orders
  WHEN SELECT GRANTED ON customer_app.customers
  WHERE order_app.orders.customer_id = customer_app.customers.customer_id;

CREATE DATA GRANT fulfillment_app.order_items_by_order
  AS SELECT
  ON fulfillment_app.order_items
  WHEN SELECT GRANTED ON order_app.orders
  WHERE fulfillment_app.order_items.order_id = order_app.orders.order_id;

If customer_app is dropped, the database drops order_app.orders_by_customer because its parent object is customer_app.customers. The database does not drop fulfillment_app.order_items_by_order because neither its child object nor its parent object is directly owned by customer_app.

Note:

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

17.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.

17.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 and all cross-table data grants that reference the dropped object as their parent object in the GRANTED 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.

17.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 runtime.

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 runtime.

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.

Cross-table data grants

For cross-table data grants, the behavior depends on whether the dropped or renamed column belongs to the child object or to the parent object. In both cases, the data grant definition remains as is.
  • Child object column: If a dropped or renamed column of the child object is part of the cross-table data grant, end users receive an error when they access the child object. You can identify the invalid columns by querying the INVALID_COLUMN_NAME column in the DBA_DATA_GRANTS view.
  • Parent object column: If a dropped or renamed column of the parent object is referenced by the cross-table data grant, either in the required parent privilege list or in the predicate, queries on the child object fail because the referenced parent column no longer exists. You can identify the invalid parent columns by querying the INVALID_COLUMN_NAME column in the DBA_REQUIRED_PARENT_DATA_PRIVILEGES view.
For example, consider the following cross-table data grant with column privileges on both the child object and the parent object. The example uses a customer-to-order hierarchy across the customer_app and order_app schemas: orders in order_app inherit access from matching customers in customer_app.
CREATE DATA GRANT order_app.orders_by_customer
    AS SELECT (order_id)
    ON order_app.orders
    WHEN SELECT (customer_id) GRANTED ON customer_app.customers
    WHERE order_app.orders.customer_id = customer_app.customers.customer_id;
If the order_id column is dropped from the child object order_app.orders, queries on order_app.orders fail with the following error:
ORA-52568: An invalid column "ORDER_ID" is being specified as part of data grant for "ORDER_APP.ORDERS" object.
If the customer_id column is dropped from the parent object customer_app.customers, queries on the child object order_app.orders fail with the following error because the referenced parent column no longer exists:
ORA-52561: Invalid predicate in data grant.

When the column is added back or renamed to its original name, or when the data grant is replaced to exclude the invalid column, DML statements and queries from users work properly again.

17.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 runtime.

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 runtime.

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.

This behavior also applies to cross-table data grants, whether the replaced view serves as the child object or as the parent object. In particular, if the replaced view is the parent object and the new view definition no longer contains a column referenced in the required parent privilege list or in the predicate, queries on the child object fail at runtime even though the child object itself is unchanged. In both cases the data grant definition remains as is, and the identification and recovery steps described in Impact of Dropping or Renaming Columns apply.