REVOKE (sequence privileges)

This form of the REVOKE statement revokes the privileges on a user-defined sequence.

Syntax for REVOKE (sequence privileges)

Read syntax diagramSkip visual syntax diagram REVOKE ,ALTERUSAGE1ON SEQUENCE,sequence-name FROM ,authorization-nameROLErole-namePUBLIC BY,authorization-nameROLErole-nameALLINCLUDING DEPENDENT PRIVILEGESNOT INCLUDING DEPENDENT PRIVILEGESRESTRICT
Notes:
  • 1 The keyword SELECT is an alternative keyword for USAGE.

Description for REVOKE (sequence privileges)

ALTER
Revokes the privilege to alter a sequence or record a comment on a sequence.
USAGE
Revokes the USAGE privilege to use a sequence. This privilege is needed when the NEXT VALUE or PREVIOUS VALUE expression is invoked for a sequence name.
SEQUENCE sequence-name
Identifies the sequence. The name, including the implicit or explicit schema qualifier, must uniquely identify an existing sequence at the current server. If no sequence by this name exists in the explicitly or implicitly specified schema, an error occurs. sequence-name must not be the name of an internal sequence object that is generated by the system for an identity column.
FROM
Refer to REVOKE for a description of the FROM clause.
BY
Refer to REVOKE 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.

RESTRICT
Prevents the USAGE privilege from being revoked on a sequence if the revokee owns one of the following objects and does not have the USAGE privilege from another source:
  • A trigger that specifies the sequence in a NEXT VALUE or PREVIOUS VALUE expression
  • An inline SQL function that specifies the sequence in a NEXT VALUE or PREVIOUS VALUE expression

Examples for REVOKE (sequence privileges)

Example 1: Revoke USAGE privilege on sequence MYNUM to user JONES. 
   REVOKE USAGE
     ON SEQUENCE MYNUM
     FROM JONES;
Example 2: Revoke the USAGE privilege on sequence ORDER_SEQ from role ROLE1: 
   REVOKE USAGE
     ON SEQUENCE ORDER_SEQ
     FROM ROLE ROLE1;