17.3 Drop Data Grants

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

After a data grant is dropped, the end users or data roles of the grant no longer hold the data privileges it provided, and they cannot exercise the corresponding data access on the target object.

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 [IF EXISTS] [schema.]grant_name;

Parameters

Parameter Description

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 17-18 Drop a data grant in own schema

DROP DATA GRANT IF EXISTS manager_direct_reports;

Example 17-19 Drop a data grant in another schema

DROP DATA GRANT IF EXISTS hr.manager_direct_reports;