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.

Syntax

           .-,---------.                               
           V           |                               
>>-REVOKE----+-ALTER-+-+--ON SEQUENCE--sequence-name------------>
             '-USAGE-'                                 

         .-,---------------------------------.                 
         V                                   |  .-RESTRICT-.   
>--FROM----+-+-------+--authorization-name-+-+--+----------+---><
           | +-USER--+                     |                   
           | +-GROUP-+                     |                   
           | '-ROLE--'                     |                   
           '-PUBLIC------------------------'                   

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.

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

   REVOKE USAGE ON SEQUENCE GENERATE_ID FROM ENGLES

   REVOKE ALTER ON SEQUENCE GENERATE_ID FROM PUBLIC

   REVOKE ALTER, USAGE ON SEQUENCE GENERATE_ID
     FROM USER PELLOW, USER MLI, GROUP PLANNERS