Managing Db2 Warehouse database privileges for Cloud Pak for Data users

You can manage Db2 Warehouse database privileges for Cloud Pak for Data users and groups.

The Db2 Warehouse service works directly with the Cloud Pak for Data user management service. You can use Cloud Pak for Data users and groups in addition to the built-in BLUADMIN and BLUUSERS groups.

When you grant access to an instance, the ADMIN role maps to the BLUADMIN group and the USER role maps to the BLUUSERS group. Any Cloud Pak for Data groups are recognized for the user and are available for use in grant statements.

Procedure

If you plan to link an external LDAP server to Cloud Pak for Data, follow these steps:
1. Configure LDAP in the Cloud Pak for Data web console by clicking Administration > User management > Configure LDAP.
2. To be able to assign LDAP users to Cloud Pak for Data groups, check Use LDAP group and supply the necessary additional information. Include mapping for first and last name and email address. Without these mappings, Cloud Pak for Data users must be created for any LDAP user that you want to use. Password management is still done by using LDAP in this case.
  LDAP users must belong to a Cloud Pak for Data group, either directly or by using an LDAP group membership, to show up as a Cloud Pak for Data user in the next step.
To add users to the Db2 Warehouse instance:
1. From the Databases page, click Manage Access on the database instance menu.
2. Click Add users, Click Add users, select the user, and choose a role for the user.
  - Select the role of either Admin or User. These roles correspond to group membership of BLUADMIN or BLUUSERS.
  - Any Cloud Pak for Data groups that the user belongs to will be available for granting privileges in Db2 Warehouse.
Grant privileges to users or groups in Db2 Warehouse.

Example 1: Cloud Pak for Data users without external LDAP

This procedure also works for Cloud Pak for Data users with LDAP password management but no attribute mapping.

Create a Cloud Pak for Data user, for example monty.
Add the user monty to a Cloud Pak for Data group.
Add user monty to the Db2 Warehouse instance with a role of either ADMIN or USER.

Run the following command to view the groups that the user monty is part of:

db2 "SELECT * FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('MONTY')) AS T"

A list similar to the following example is returned:

GROUP
-------------------------------------
BLUUSERS
CP4D_GROUP
ALL USERS

  3 record(s) selected.

You can then issue GRANT statements that are similar to the following examples:

grant select on table test to user monty

grant selectin on schema CP4D to group CP4D_GROUP

Note: When you are granting privileges on Db2 Warehouse, you must specify the type of user to whom you are granting the privileges, either user, role, or group.

Example 2: Mapped LDAP users without using groups

Add the user monty to a Cloud Pak for Data group. You can search for users by selecting Add LDAP users from the drop-down menu next to Add users.
Add user monty to the Db2 Warehouse instance with a role of either ADMIN or USER.
You might need to wait for the user to appear in the list of available users.
Run the following command to view the groups that the user monty is part of:
```
db2 "SELECT * FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('MONTY')) AS T"
```
You can then issue GRANT statements (see Example 1).

Example 3: Users added through LDAP groups

Add an LDAP group to a Cloud Pak for Data group. You can search for groups by selecting Add LDAP groups from the drop-down menu next to Add users.
Add user monty to the Db2 Warehouse instance with a role of either ADMIN or USER.
You might need to wait for the user to appear in the list of available users.
Run the following command to view the groups that the user monty is part of:
```
db2 "SELECT * FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('MONTY')) AS T"
```
You can then issue GRANT statements (see Example 1).