GRANT (tenant privileges) statement

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

Syntax

Description

ACCESSCTRL

Grants the access control authority on the tenant.

The tenant ACCESSCTRL authority allows the holder to grant and revoke privileges, both for objects defined in the tenant and all tenant-level privileges, except tenant ACCESSCTRL.

ALL or ALL PRIVILEGES

Grants all of the following tenant privileges on the tenant that is named in the ON clause:

• ALTERIN
• CREATEIN
• DELETEIN
• DROPIN
• EXECUTEIN
• INSERTIN
• SELECTIN
• UPDATEIN

If ALL is not specified, one or more of the keywords in the list of privileges must be specified.

ALTERIN

Grants the privilege to alter or comment on all objects in the tenant. The owner of an explicitly created tenant automatically receives ALTERIN privilege.

CREATEIN

Grants the privilege to create objects in the tenant. Other authorities or privileges required to create the object, such as CREATETAB, are still required. The owner of an explicitly created tenant automatically receives CREATEIN privilege. An implicitly created tenant has CREATEIN privilege automatically granted to PUBLIC.

DATAACCESS

Grants the authority to access data in the tenant.

The tenant DATAACCESS authority allows the holder to run the following operations:

• Select, insert, update, delete, and load data from tables or views defined in the tenant.
• Run any package defined in the tenant.
• Run any routine, except audit routines, defined in the tenant.

DELETEIN

Grants the privilege to delete data in the table objects in the tenant. The owner of the tenant (explicitly or implicitly created) does not automatically receive DELETEIN privilege.

DROPIN

Grants the privilege to drop all objects in the tenant. The owner of an explicitly created tenant automatically receives DROPIN privilege.

EXECUTEIN

Grants the privilege to execute all existing and future user-defined functions, methods, procedures, packages, or modules defined in the tenant. The owner of the tenant (explicitly or implicitly created) does not automatically receive EXECUTEIN privilege.

INSERTIN

Grants the privilege to insert rows and to run the IMPORT utility on all existing and future tables or views defined in the tenant. The owner of the tenant (explicitly or implicitly created) does not automatically receive INSERTIN privilege.

LOAD

Grants LOAD authority on tables in the tenant.

SELECTIN

Grants the privilege to select from all existing and future tables or views defined in the tenant. The owner of the tenant (explicitly or implicitly created) does not automatically receive SELECTIN privilege.

TENANTADM

Grants the tenant administrator authority.

A tenant administrator holds privileges on nearly all objects in the tenant. The only exceptions are those privileges that are part of the tenant ACCESSCTRL and tenant DATAACESS authorities.

TENANTADM authority cannot be granted to PUBLIC (SQLSTATE 42508).

UPDATEIN

Grants the privilege to use the UPDATE statement on all existing and future tables or updatable views defined in the tenant. The owner of the tenant, whether explicitly or implicitly created, does not automatically receive the UPDATEIN privilege.

USAGE

Grants 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 the authorities are to be granted. Authorities cannot be granted on any tenant beginning with the SYS prefix or with the same name as the database (SQLSTATE 42501).

TO

Specifies to whom the role is granted.

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

Grants the privileges to a set of users (authorization IDs). For more information, see Authorization, privileges and object ownership.

Rules

• For each authorization-name specified, if none of the keywords USER, GROUP, or ROLE is specified:
  • 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 in the operating system, an error is returned (SQLSTATE 56092).
  • If the authorization-name is defined as both USER and GROUP according to the security plug-in in effect, an error is returned (SQLSTATE 56092).
  • If the authorization-name is defined as USER only according to the security plug-in in effect, or if it is undefined, USER is assumed.
  • If the authorization-name is defined as GROUP only according to the security plug-in in effect, 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 was not granted. If no privileges were granted, an error is returned (SQLSTATE 42501). If the package used for processing the statement was precompiled with LANGLEVEL set to SQL92E for MIA, a warning is returned (SQLSTATE 01007), unless the grantor has no privileges on the object of the grant operation.

Examples

GRANT USAGE ON TENANT APPLE TO USER FRANK