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

GRANT SQL Definition Privileges Usage

Wildcarding Table-Name

For table definition privileges, only the closest matching wildcarded grant is used. If CREATE privilege has been granted on HR.EMP* and HR.EMPV*, then only the grant on HR.EMPV* is used to verify the privilege to create HR.EMPVU_SALARY.

The DEFINE Keyword

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

When you use the DEFINE keyword with a REVOKE statement, you revoke all of the privileges in the set that have been previously granted to 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 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.

Granting Privileges on a Schema

The following GRANT statement gives the ALTER, CREATE, DISPLAY, and DROP privileges on all schemas that begin with 'DSF' to user DSF. The statement also gives user DSF the privilege of granting the same privileges to other users.

grant define
   on schema dsf*
   to dsf
   with grant option;