REVOKE (tenant privileges) statement

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

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:
  • ACCESSCTRL authority on the database
  • SECADM authority

To revoke tenant ACCESSCTRL, the authorization must have SECADM or database ACCESSCTRL authority.

Syntax

Read syntax diagramSkip visual syntax diagramREVOKEALLPRIVILEGES,ACCESSCTRLALTERINCREATEINDATAACCESSDELETEINDROPINEXECUTEININSERTINLOADSELECTINTENANTADMUPDATEINUSAGEON TENANTtenant-name FROM ,USERGROUPROLEauthorization-namePUBLICBY ALL

Description

ACCESSCTRL
Revokes the authority to grant and revoke tenant-level privileges.
ALTERIN
Revokes the privilege to alter or comment on objects in the tenant.
CREATEIN
Revokes the privilege to create objects in the tenant.
DATAACCESS
Revokes the authority to access data in the tenant.
DELETEIN
Revokes the privilege to delete all objects in the tenant.
DROPIN
Revokes the privilege to drop objects in the tenant.
EXECUTEIN
Revokes the privilege to execute user-defined functions, methods, procedures, packages, and modules defined in the tenant.
INSERTIN
Revokes the privilege to insert data in to all objects in the tenant.
SELECTIN
Revokes the privilege to select from all objects in the tenant.
TENANTADM
Revokes the tenant administrator authority.
UPDATEIN
Revokes the privilege to update all objects in the tenant.
LOAD
Revokes LOAD authority from tables in the tenant.
USAGE
Revokes the privilege to use the tenant as the target of the SET TENANT statement in order to allow the current session to switch to the tenant.
ON TENANT tenant-name
Specifies the name of the tenant on which privileges are to be revoked.
FROM
Indicates from whom the privileges are 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 an existing role 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).
PUBLIC
Revokes the privileges from PUBLIC.
BY ALL
Revokes each named privilege from all named users who were explicitly granted those privileges, regardless of who granted them. This is the default behavior.

Notes

Revoking a specific privilege does not necessarily revoke the ability to run the task. A user can proceed with a task if other privileges are held by PUBLIC, a group, or a role, or if the user holds a higher-level authority such as DBADM.

Examples

The following example shows the command syntax for removing the ability to associate with the tenant APPLE from the user ID FRANK:
REVOKE USAGE ON TENANT APPLE FROM USER FRANK BY ALL