REVOKE (sequence privileges)

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

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include ACCESSCTRL or SECADM authority or ACCESSCTRL authority on the schema containing the sequence-name.

Syntax

Read syntax diagramSkip visual syntax diagramREVOKE,ALTERUSAGEON SEQUENCEsequence-nameFROM ,USERGROUPROLEauthorization-namePUBLIC BY ALLRESTRICT

Description

ALTER
Revokes the privilege to change the properties of a sequence or to restart sequence number generation using the ALTER SEQUENCE statement.
USAGE
Revokes the privilege to reference a sequence using nextval-expression or prevval-expression.
ON SEQUENCE sequence-name
Identifies the sequence on which the specified privileges are to be revoked. The sequence name, including an implicit or explicit schema qualifier, must uniquely identify an existing sequence at the current server. If no sequence by this name exists, an error is returned (SQLSTATE 42704).
FROM
Specifies from whom the privileges are revoked.
USER
Specifies that the authorization-name identifies a user.
GROUP
Specifies that the authorization-name identifies a group name.
ROLE
Specifies that the authorization-name identifies a role name.
authorization-name,...
Lists the authorization IDs of one or more users, groups, or roles.

The list of authorization IDs cannot include the authorization ID of the user issuing the statement (SQLSTATE 42502).

PUBLIC
Revokes the specified privileges from PUBLIC.
BY ALL
Revokes each specified privilege from all named users who were explicitly granted those privileges, regardless of who granted them. This is the default behavior.
RESTRICT
This optional keyword indicates that the statement will fail if any objects depend on the privilege being revoked.

Rules

  • For each authorization-name specified, if neither USER, GROUP, nor ROLE is specified, then:
    • For all rows for the specified object in the SYSCAT.SEQUENCEAUTH catalog view where the grantee is authorization-name:
      • If all rows have a GRANTEETYPE of 'U', USER is assumed.
      • If all rows have a GRANTEETYPE of 'G', GROUP is assumed.
      • If all rows have a GRANTEETYPE of 'R', ROLE is assumed.
      • If all rows do not have the same value for GRANTEETYPE, an error is returned (SQLSTATE 56092).

Notes

  • Revoking a privilege on a sequence from the authorization ID under which a package was bound will cause the package to become invalid if the authorization ID does not continue to hold the privilege on the sequence through different means; for example, through membership in a role that holds the privilege.
  • Revoking a specific privilege does not necessarily remove the ability to perform an action. A user can proceed if other privileges are held by PUBLIC or by a group to which the user belongs, or if the user has a higher level of authority, such as DBADM.

Examples

  • Example 1: Revoke the USAGE privilege on a sequence called GENERATE_ID from user ENGLES. There is one row in the SYSCAT.SEQUENCEAUTH catalog view for this sequence and grantee, and the GRANTEETYPE value is U. 
       REVOKE USAGE ON SEQUENCE GENERATE_ID FROM ENGLES
  • Example 2: Revoke alter privileges on sequence GENERATE_ID that were previously granted to all local users. (Grants to specific users are not affected.) 
       REVOKE ALTER ON SEQUENCE GENERATE_ID FROM PUBLIC
  • Example 3: Revoke all privileges on sequence GENERATE_ID from users PELLOW and MLI, and from group PLANNERS. 
       REVOKE ALTER, USAGE ON SEQUENCE GENERATE_ID
     FROM USER PELLOW, USER MLI, GROUP PLANNERS