Drop Data Grants

Use the DROP DATA GRANT command to remove a named data grant and all associated data privileges from the database.

Dropping a data grant created without FILTER removes the authorization it supplied. Dropping a data grant filter removes its restriction from subsequent authorization checks. Removing a filter can therefore increase the rows or column values available through the remaining applicable data grants.

Required privilege

• No specific privilege is required to drop a data grant in the executing user’s own schema.

• The DROP ANY DATA GRANT system privilege to drop a data grant in another schema (can be granted at the system level or for a specific schema).

Syntax

DROP DATA GRANT [ FILTER ] [IF EXISTS] [schema.]grant_name;

Parameters

Parameter/Keyword	Description
FILTER	A keyword that indicates that the data grant being dropped was created with FILTER. Omit FILTER when dropping a data grant that was created without it.
schema	The schema name of the data grant. Must be a valid database user. If omitted, the current schema is used. Error ORA-52553 is raised if the schema (owner) of the data grant is invalid, that is: The owner is not a valid database user, or An Oracle Deep Data Security user (whose schema is XS$NULL) tries to drop a data grant without specifying a schema name.
grant_name	The name of the data grant to drop.

Usage notes and restrictions

• When IF EXISTS is specified:

  • If the data grant does not exist, the statement is a no-op. No error is raised.

  • If the data grant exists, it is dropped.

• When IF EXISTS is omitted:

  • If the data grant does not exist, ORA-52552 is raised.

  • If the data grant exists, it is dropped.

• Dropping a cross-table data grant removes the child authorization rule created by that data grant. It does not drop the parent object, the child object, or any data grants that provide privileges on the parent object.

• Dropping a data grant on a parent object does not drop the cross-table data grants that depend on it. The dependent cross-table data grants remain defined and continue to appear in the data dictionary views. However, because a cross-table data grant authorizes access only when the required privileges are held on the parent object, end users derive no access through the dependent data grants until the required privileges on the parent object are granted again.

For syntax diagrams and additional details, see DROP DATA GRANT in Oracle AI Database SQL Language Reference.

Example: Drop a data grant in own schema

DROP DATA GRANT IF EXISTS manager_direct_reports;

Example: Drop a data grant in another schema

DROP DATA GRANT IF EXISTS hr.manager_direct_reports;

Example: Drop a data grant with filter

The following statement drops the manager_employee_filter data grant if it exists:

DROP DATA GRANT FILTER IF EXISTS hr.manager_employee_filter;