REVOKE (sequence privileges)
This form of the REVOKE statement revokes the privileges on a user-defined sequence.
Syntax
.-,-------------. V | >>-REVOKE----+-ALTER-----+-+----ON SEQUENCE---------------------> | (1) | '-USAGE-----' .-,-------------. .-,----------------------. V | V | >----sequence-name-+--FROM----+-authorization-name-+-+----------> +-ROLE--role-name----+ '-PUBLIC-------------' >--+------------------------------------+-----------------------> | .-,----------------------. | | V | | '-BY--+---+-authorization-name-+-+-+-' | '-ROLE--role-name----' | '-ALL------------------------' .-RESTRICT-. >--+------------------------------------+--+----------+-------->< +-INCLUDING DEPENDENT PRIVILEGES-----+ '-NOT INCLUDING DEPENDENT PRIVILEGES-'
Notes:
- The keyword SELECT is an alternative keyword for USAGE.
Description
- 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
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;