Start of change

REVOKE (schema privileges)

This form of the REVOKE statement removes the privileges on a schema.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

  • For each schema identified in the statement:
    • Every privilege specified in the statement
    • The system authority of *OBJMGT on the schema
  • Database administrator authority
  • Security administrator authority

Syntax

Read syntax diagramSkip visual syntax diagram REVOKE ALLPRIVILEGES,CREATEINUSAGE ON SCHEMA ,schema-name FROM ,USERGROUPauthorization-namePUBLIC

Description

ALL or ALL PRIVILEGES
Revokes one or more schema privileges from each authorization-name. The privileges revoked are those privileges on the identified schemas that were granted to the authorization-names. Note that revoking ALL PRIVILEGES on a schema is not the same as revoking the system authority of *ALL.

If you do not use ALL, you must use one or more of the keywords listed below. Each keyword revokes the privilege described.

CREATEIN
Revokes the privilege to create objects in the schema.
USAGE
Revokes the privilege to use the schema. USAGE privilege is required to reference any objects that exist in the schema.
ON SCHEMA schema-name
Identifies the schema from which the privilege is revoked.
FROM
Identifies from whom the privileges are revoked.
USER
Specifies that the authorization-name identifies a user profile. If USER is specified, authorization-name must be a user profile.
GROUP
Specifies that the authorization-name identifies a group profile. If GROUP is specified, authorization-name must be a group profile.
authorization-name,…
Lists one or more authorization IDs. Do not specify the same authorization-name more than once.
PUBLIC
Revokes a grant of the privilege to PUBLIC. For more information, see Authorization, privileges and object ownership.

Notes

Multiple grants: If you revoke a privilege on a schema, it nullifies any grant of the privilege on that schema, regardless of who granted it.

Revoking WITH GRANT OPTION: The only way to revoke the WITH GRANT OPTION is to revoke ALL.

Privilege warning: Revoking a specific privilege from a user does not necessarily prevent that user from performing an action that requires that privilege. For example, the user may still have the privilege through PUBLIC privileges or database administrator authority.

Corresponding system authorities: When a schema privilege is revoked, the corresponding system authorities are revoked. For information on the system authorities that correspond to SQL privileges see GRANT (schema privileges).

Example

Example 1: Revoke the CREATEIN privilege on schema T_SCORES from user JONES. 

REVOKE CREATEIN
  ON SCHEMA T_SCORES
  FROM JONES;
End of change