DROP DATA GRANT

Purpose

Use DROP DATA GRANT to drop a data grant and remove the access defined by it to end users and data roles.

Dropping the data grant removes all the data privileges granted in the named grant along with the predicates. Once the data grant is dropped, access provided by the grant is removed.

Prerequisites

You must have the DROP ANY DATA GRANT privilege at the system level or for the specific schema to drop a data grant in a schema not your own. You can drop a data grant in your own schema without any specific privilege.

Syntax

Description of the illustration drop_data_grant.eps

Semantics

• IF EXISTS: If you specify IF EXISTS, the data grant is dropped, if it exists. If the data grant does not exist, no error is raised.

  If you do not specify IF EXISTS, the data grant is dropped, if it exists. If the data grant does not exist, an error is raised.

• schema: specifies the owner of the data grant. If omitted, the current schema is used. The schema must be a valid database user.

  If the specified schema is not valid, then the statement raises an error.

• grant_name: specifies the name of the data grant to be dropped.

Example

CREATE OR REPLACE DATA GRANT
  app_admin.ManagerDirectReports AS
  SELECT (ALL COLUMNS EXCEPT ssn)
  ON hr.employees 
  WHERE manager = ORA_APP_USER.username
  TO app_manager_role; 

DROP DATA GRANT IF EXISTS app_admin.ManagerDirectReports;