REVOKE statement (schema privileges)

This form of the REVOKE statement revokes privileges on schemas.

Syntax for REVOKE (schema privileges)

Read syntax diagramSkip visual syntax diagram REVOKE ,ALTERINCREATEINDROPIN ON SCHEMA ,schema-name* FROM ,authorization-nameROLErole-namePUBLICBY,authorization-nameROLErole-nameALLINCLUDING DEPENDENT PRIVILEGESNOT INCLUDING DEPENDENT PRIVILEGES

Description for REVOKE (schema privileges)

ALTERIN
Revokes the privilege to alter sequences, stored procedures, and user-defined functions, or specify a comment for distinct types, cast functions that are generated for distinct types, sequences, stored procedures, triggers, and user-defined functions in the designated schemas.
CREATEIN
Revokes the privilege to create distinct types, sequences, stored procedures, triggers, and user-defined functions in the designated schemas.
DROPIN
Revokes the privilege to drop distinct types, sequences, stored procedures, triggers, and user-defined functions in the designated schemas.
SCHEMA schema-name
Identifies the schema on which the privilege is revoked.
SCHEMA *
Indicates that the specified privilege on all schemas is revoked. You (or the indicated grantors) must have previously granted the specified privilege on SCHEMA * to all identified users (including PUBLIC if specified). Privileges granted on specific schemas are not affected.
FROM
Refer to REVOKE statement for a description of the FROM clause.
BY
Refer to REVOKE statement for a description of the BY clause.
INCLUDING DEPENDENT PRIVILEGES or NOT INCLUDING DEPENDENT PRIVILEGES
Specifies whether revoking a privilege or an authority from an authorization ID or a role also results in revoking the grants that were made by that user. The default value is based on the authority that is being revoked and the REVOKE_DEP_PRIVILEGES system parameter:
  • When ACCESSCTRL, DATAACCESS, or system DBADM authority is revoked, NOT INCLUDING DEPENDENT PRIVILEGES is assumed and the clause must be specified on the REVOKE statement.
  • When the REVOKE_DEP_PRIVILEGES system parameter is set to NO, NOT INCLUDING DEPENDENT PRIVILEGES is assumed and an error is returned if the statement includes INCLUDING DEPENDENT PRIVILEGES.
  • Otherwise, INCLUDING DEPENDENT PRIVILEGES is assumed and the clause must be specified on the REVOKE statement.
INCLUDING DEPENDENT PRIVILEGES
Specifies that revoking a privilege or an authority from an authorization ID or a role also results in revoking dependent privileges. This means that any grants that were made by the user will continue to be revoked, until all grants in the chain have been revoked.

INCLUDING DEPENDENT PRIVILEGES cannot be specified if the system parameter REVOKE_DEP_PRIVILEGES is set to NO, which enforces the behavior to not include the dependent privileges.

NOT INCLUDING DEPENDENT PRIVILEGES
Specifies that revoking a privilege or an authority from an authorization ID or a role does not cause the grants that were made by the user to be revoked. However, for the revoked privileges, all implications of the privilege being revoked are applied. For example, if the revoked privileges were required to bind a package successfully, that package would continue to be invalidated as a result of the package owner losing these privileges. An object might be dropped if a privilege is revoked that was used to create the object.

NOT INCLUDING DEPENDENT PRIVILEGES must be specified when ACCESSCTRL, DATAACCESS, or system DBADM authority is revoked.

NOT INCLUDING DEPENDENT PRIVILEGES cannot be specified if the system parameter REVOKE_DEP_PRIVILEGES is set toYES, which enforces the behavior to include dependent privileges in the revoke.

Notes for REVOKE (schema privileges)

For considerations that apply to all REVOKE statement forms, see Notes for REVOKE.

Examples for REVOKE (schema privileges)

Example 1: Revoke the CREATEIN privilege on schema T_SCORES from user JONES. 
   REVOKE CREATEIN ON SCHEMA T_SCORES FROM JONES;
Example 2: Revoke the CREATEIN privilege on schema VAC from all users at the current server. 
   REVOKE CREATEIN ON SCHEMA VAC FROM PUBLIC;
Example 3: Revoke the ALTERIN privilege on schema DEPT from the administrative assistant. 
   REVOKE ALTERIN ON SCHEMA DEPT FROM ADMIN_A;
Example 4: Revoke the ALTERIN and DROPIN privileges on schemas NEW_HIRE, PROMO, and RESIGN from HR (Human Resources). 
   REVOKE ALTERIN, DROPIN ON SCHEMA NEW_HIRE, PROMO, RESIGN FROM HR;
Example 5: Revoke the ALTERIN privilege on schemas EMPLOYEE from role ROLE1: 
   REVOKE ALTERIN ON SCHEMA EMPLOYEE FROM ROLE ROLE1;