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

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

Using a Wildcard When Revoking Definition Privilege

If you use a wildcard in the resource name in a REVOKE statement, the wildcarded name must match the wildcarded name used in a previous GRANT statement. Similarly, if definition privilege was granted on a wildcarded resource name, it can be revoked only by specifying the same name in the REVOKE statement.

For example, assume that this grant has been made:

grant define
  on user profile hr*
  to user1, user2, user3;

• To revoke the privilege, you must specify HR* in the REVOKE statement.
• If you revoke DEFINE on user profile HRA from USER1, it has no effect on the privilege granted in the preceding example.

The following statement revokes the privilege to define certain user profiles from the hr_corp group:

revoke define
  on user profile hr_*
  from hr_corp;