GRANT_USER_TNT_ACCESS

Grants access on specific tenant codes within a sandbox to a user. In addition, the procedure specifies whether the user should get access to sensitive columns within this tenant code or not. A tenant code is a value in a row that is used to decide on access rights. This procedure may also be used for changing sensitive column access.

If access to the sandbox has not previously been granted, this will occur implicitly. In this case, the user will get read-only access to the sandbox. Sensitive column access on the sandbox is granted according to parameter p_scol_access in the procedure call.

The procedure is overloaded, you may, therefore, provide a single tenant code (p_tnt_code) or a list of tenant codes (p_tnt_code_list).

If an ALL tenant code is provided then all specific tenant codes will be deleted. Inversely, when a specific tenant code is provided then a previous grant to an ALL tenant code will be removed.

Note: if more than one tenant groups exist for the same sandbox, the user will see only the intersection of the grants. For example if access is granted on tenants ‘102’, ‘103’ and ‘104’ for one tenant group and ‘101’, ‘102’ and ‘105’ for the other tenant group, the user will only see tenant ‘102’ within the sandbox (plus the default tenant code if it is the same in both tenant groups).

Note: if more than one tenant group exist for the same table the user must be granted access to all tenant values of a row to see this row.

The user must be either a human or a generic user (HU or GU). No other user type is allowed (technical user (TU) or an unclassified user). The user must not be a sandbox master.

Users cannot execute this procedure for themselves. The exception is when the user has been granted the ‘exempt_4eyes’ privilege, as stored in table ADM_ADMIN_PRIVS. By default, this is granted to the DBA role.

The sandbox containing the tenant must exist (see table SBX_REPO).

The tenant must exist (see table REP_TENANTS). The context type of the tenant group must be activated for the security context (‘SEC’). This means that column SEC_CTX in table REP_TENANT_GROUPS must be set to ‘Y’.

If the entry does not already exist, the procedure inserts a row with the username and object group name in table SBX_USER_OG_ACCESS.

This procedure inserts or updates a row in table SBX_USER_TNT_ACCESS.

Parameter Name

Required

DefaultValue

Values

Description

p_sbx_name

yes

The name of the sandbox.

The sandbox name is converted to upper case while granting.

p_username

yes

The username of the human (HU) or generic (GU) user to be granted the privilege.

The username is case sensitive.

p_tnt_group

yes

The name of the tenant group.

The name is case sensitive.

p_tnt_code

yes

A single tenant codes.

See example 1 below.

p_scol_access

no

FALSE

  • TRUE

  • FALSE

Whether access to sensitive columns inside the object group should be granted.

Possible values:

  • TRUE to grant access to sensitive columns,

  • FALSE to deny access to sensitive column.

Example 1

Grant access on specific tenant codes 1, 2, 3, 4 and 5 in tenant group ‘INSTITUT’ and sandbox ‘MIGR’ to user ‘NGRAUPNER’. The user may see sensitive columns only within 1. A possibly existing grant for all tenants will be removed automatically:

EXEC p_sbx_admin.grant_user_tnt_access
    ( 'MIGR'
    , 'NGRAUPNER'
    , 'INSTITUT'
    , 1
    , TRUE );

EXEC p_sbx_admin.grant_user_tnt_access
    ( 'MIGR'
    , 'NGRAUPNER'
    , 'INSTITUT'
    , scurty_t_lov (2, 3, 4, 5)
    , FALSE );

Example 2

Grant access on all tenants in tenant group ‘INSTITUT’ and sandbox ‘MIGR’ to user ‘NGRAUPNER’, including sensitive columns. Any existing grants for specific tenants will be removed automatically:

EXEC p_sbx_admin.grant_user_tnt_access
    ( 'MIGR'
    , 'NGRAUPNER'
    , 'INSTITUT'
    , 'ALL'
    , TRUE );