GRANT (sequence privileges) statement

This form of the GRANT statement grants 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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • The WITH GRANT OPTION for each identified privilege on sequence-name
  • ACCESSCTRL or SECADM authority

Syntax

Read syntax diagramSkip visual syntax diagramGRANT,USAGEALTERON SEQUENCEsequence-nameTO ,USERGROUPROLEauthorization-namePUBLIC WITH GRANT OPTION

Description

USAGE
Grants the privilege to reference a sequence using nextval-expression or prevval-expression.
ALTER
Grants the privilege to alter sequence properties using the ALTER SEQUENCE statement.
ON SEQUENCE sequence-name
Identifies the sequence on which the specified privileges are to be granted. 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 (SQLSTATE 42704) is returned.
TO
Specifies to whom the specified privileges are granted.
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. The role name must exist at the current server (SQLSTATE 42704).
authorization-name,...
Lists the authorization IDs of one or more users, groups, or roles.
PUBLIC
Grants the specified privileges to a set of users (authorization IDs).
WITH GRANT OPTION
Allows the specified authorization-name to grant the specified privileges to others.
If the WITH GRANT OPTION is omitted, the specified authorization-name can only grant the specified privileges to others if they:
  • have SYSADM or DBADM authority or
  • received the ability to grant the specified privileges from some other source.

Rules

  • For each authorization-name specified, if neither USER, GROUP, nor ROLE is specified, then:
    • If the security plug-in in effect for the instance cannot determine the status of the authorization-name, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined as ROLE in the database, and as either GROUP or USER according to the security plug-in in effect, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined according to the security plug-in in effect as both USER and GROUP, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined according to the security plug-in in effect as USER only, or if it is undefined, USER is assumed.
    • If the authorization-name is defined according to the security plug-in in effect as GROUP only, GROUP is assumed.
    • If the authorization-name is defined in the database as ROLE only, ROLE is assumed.
  • In general, the GRANT statement will process the granting of privileges that the authorization ID of the statement is allowed to grant, returning a warning (SQLSTATE 01007) if one or more privileges is not granted. If no privileges are granted, an error is returned (SQLSTATE 42501). (If the package used for processing the statement was precompiled with LANGLEVEL set to SQL92E or MIA, a warning is returned (SQLSTATE 01007), unless the grantor has no privileges on the object of the grant operation.)

Notes

  • Privileges granted to a group: A privilege that is granted to a group is not used for authorization checking on:
    • Static DML statements in a package
    • A base table while processing a CREATE VIEW statement
    • A base table while processing a CREATE TABLE statement for a materialized query table
    • Create SQL routine
    • Create trigger

Examples

  • Example 1: Grant any user the USAGE privilege on a sequence called ORG_SEQ. 
       GRANT USAGE ON SEQUENCE ORG_SEQ TO PUBLIC
  • Example 2: Grant user BOBBY the ability to alter a sequence called GENERATE_ID, and to grant this privilege to others. 
       GRANT ALTER ON SEQUENCE GENERATE_ID TO BOBBY WITH GRANT OPTION