REVOKE (schema privileges)
This form of the REVOKE statement removes the privileges on a schema.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
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
- 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.
- Revokes the privilege to create objects in the schema.
- 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.
- Identifies from whom the privileges are revoked.
- Specifies that the authorization-name identifies a user profile. If USER is specified, authorization-name must be a user profile.
- Specifies that the authorization-name identifies a group profile. If GROUP is specified, authorization-name must be a group profile.
- Lists one or more authorization IDs. Do not specify the same authorization-name more than once.
- Revokes a grant of the privilege to PUBLIC. For more information, see Authorization, privileges and object ownership.
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 1: Revoke the CREATEIN privilege on schema T_SCORES from user JONES.
REVOKE CREATEIN ON SCHEMA T_SCORES FROM JONES;