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

  • Users can drop data grants in their own schema without any specific privilege.
  • 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.

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

Example 16-12 Drop a data grant in own schema

DROP DATA GRANT IF EXISTS manager_direct_reports;

Example 16-13 Drop a data grant in another schema

DROP DATA GRANT IF EXISTS hr.manager_direct_reports;