Syntax for Securing Global Resources › GRANT Definition Privileges › GRANT Definition Privileges Usage

GRANT Definition Privileges Usage

Granting Definition Privilege with a Wildcard

By wildcarding the resource name when you grant a definition privilege, you allow a user to define multiple resources that are named with the same beginning characters.

For example, if you grant DEFINE privilege on user profile HR_* to group HR_ADMIN (see the following example), you allow the human resources administrative group to create, alter, and drop profiles that begin with the characters 'HR_'.

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. This is an efficient technique for granting all but one definition privilege.

The following GRANT statement gives the HR_ADMIN group the privilege to define user profiles that begin 'HR_':

grant define
  on user profile hr_*
  to hr_admin;