REVOKE (storage group authority) statement

This form of the REVOKE statement revokes authority on a storage group.

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 authority held by the authorization ID of the statement must include at least one of the following authorities: ACCESSCTRL or SECADM authority.

Syntax

Read syntax diagramSkip visual syntax diagramREVOKE TBSPACEADM ON STOGROUPstorage-group-name FROM,USERGROUPROLEauthorization-nameBY ALL

Description

TBSPACEADM ON
Revokes the authority to create, manage, and drop the automatic storage table spaces within the storage group.
STOGROUP storagegroup-name
Identifies the storage group from which the TBSPACEADM authority is to be revoked.
FROM
Specifies from whom the TBSPACEADM authority is revoked.
USER
Specifies that the authorization-name identifies a user.
GROUP
Specifies that the authorization-name identifies a group.
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.

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

BY ALL
Revokes the privilege from all named users who were explicitly granted that privilege, regardless of who granted it. This is the default behavior.

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.

Notes

Revoking the TBSPACEADM authority does not necessarily revoke the ability to manage table spaces. A user might still be able to manage table spaces in that storage group if the user has a higher level of authority, such as SYSADM.

Examples

The following example shows the command syntax for revoking the privilege to manage table spaces within the storage group STOG1 from the user, AMY:
REVOKE TBSPACEADM ON STOGROUP STOG1 FROM USER AMY