Syntax for Securing Database Resources › REVOKE SQL Definition Privileges › Usage

Usage

The DEFINE Keyword

When you use the DEFINE keyword with a GRANT statement, you grant a set of definition privileges on a resource to one or more users or groups.

When you use the DEFINE keyword with a REVOKE statement, you revoke all definition privileges that have been previously granted on the resource from the specified users or groups.

This means that if you GRANT CREATE privilege on a resource, you can revoke the privilege with either a REVOKE CREATE statement or a REVOKE DEFINE statement. Using REVOKE DEFINE is an efficient technique when you intend to revoke all definition privileges on the resource from a user or group, whether the privileges were granted singly or as a set.

Similarly, you can GRANT DEFINE on a resource to a user and then REVOKE DROP on the resource from the same user as a way to grant all but one definition privilege.

Revoking Privileges on a Schema

The following statement revokes the ALTER, CREATE, DISPLAY, and DROP privileges on all schemas that begin with 'DSF' from user DSF:

revoke define
   on schema dsf*
   from dsf;