Repository authentication with repository UDF authorization

You can use IBM® Data Server Manager (DSM) to delegate authentication with repository UDF authorization to the Db2® repository database.

Enabling repository authentication with repository UDF authorization in DSM

To leverage the Db2 repository database server as the authentication server for DSM, you must add it to DSM as the recognized repository database, and then enable the repository authentication feature on your DSM server.

Enable authentication on your repository database as follows:
1. From the \Config folder of your DSM installation, select and open the dswebserver_override.properties file.
2. Add the following parameter:
  dsweb.customauth.repository=true
3. Navigate to the \Config folder of your DSM installation and open the dswebserver.properties file.
4. Set the following parameter to false:
  dsweb.customauth.privilegesinjson=false
5. Save the files and restart DSM.

Create the DSWEBSECURITY_CANADMINISTER and DSWEBSECURITY_CANVIEW UDFs in the Db2 repository database as follows:

CREATE FUNCTION DSWEBSECURITY.CANADMINISTER()
RETURNS INT
SPECIFIC DSWEBSECURITY_CANADMINISTER
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN 1;

CREATE FUNCTION DSWEBSECURITY.CANVIEW()
RETURNS INT
SPECIFIC DSWEBSECURITY_CANVIEW
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN 1;

Ensure the user has CONNECT privilege for the DSM repository database.
Assign a DSM role to the user in the Db2 server side.
To assign the DSM Administrator role to the user, grant the EXECUTE privilege of UDF DSWEBSECURITY_CANADMINISTER to this user.
For example, to assign the DSM Administrator role to the user (dsmadmin), run the following command.
```
GRANT EXECUTE ON FUNCTION DSWEBSECURITY.CANADMINISTER() TO user dsmadmin;
```
To assign the DSM User role to the user, grant the EXECUTE privilege of UDF DSWEBSECURITY_CANVIEW to this user.
For example, to assign the DSM User role to the user (dsmuser1), run the following command.
```
GRANT EXECUTE ON FUNCTION DSWEBSECURITY.CANVIEW() TO user dsmuser1;
```

Result: The user can log into DSM repository successfully.

Separation of duty

In general, it is best practice to have separation of security duty from SYSADM (or other authority) with SECADM. Therefore, it is recommended that the DSWEBSECURITY_CANADMINISTER and DSWEBSECURITY_CANVIEW UDFs be created by the SECADM user. It is also the responsibility of the SECADM user to grant execution privileges to other users who need to access DSM with specific privileges. To know more about separation of duty and SECADM authority in Db2, see Security administration authority (SECADM).

Adding and removing DSM default admin user

You can add or remove the default admin user created during DSM install.

Note: This procedure is applicable to DSM repository hosted on Db2 server.

To remove the DSM default admin user:

Navigate to the \Config folder of your DSM installation and open the dswebserver.properties file.
Delete the admin.userkey and dsweb.customauth.password property settings.
Save the file and restart DSM.

To add back the DSM default admin user:

Run the following command to generate an encrypted password.
```
<dsm_install_location>/dsutil/bin/crypt.sh <clear_txt_pwd>
```
Navigate to the \Config folder of your DSM installation and open the dswebserver.properties file.
Add the property admin.user with the default admin user name. For example:
```
admin.user=admin 
```
Add the property dsweb.customauth.password with the generated encrypted password from Step 1. For example:
```
dsweb.customauth.password= <generated encrypted password>
```
Save the file and restart DSM.

Important: After installing DSM, ensure that the dswebserver.properties and dswebserver_override.properties files do not have write permission. This is to prevent other users from modifying the content of dswebserver.properties and dswebserver_override.properties files.

Migrating from DSM version 2.1.4 with Repository authentication to DSM version 2.1.5 patch with Repository authentication with UDF authorization

Record all the user's role.
Perform migration.
Change the settings to enable DSM to Repository UDF authorization mode.
Grant the related execute UDF privileges to the above users in the repository database.
Add the recorded users in the Users and Privileges page manually.

Note: After migration, if the user initially used to create the DSM repository does not have the privilege to grant UDF privilege to other users, do the following steps:

Connect the repository database with the user initially created the repository database.

Run the following commands to drop the two UDFs.

drop FUNCTION DSWEBSECURITY.CANADMINISTER();
drop FUNCTION DSWEBSECURITY.CANVIEW();

Connect the repository database with the SECADM user.

Create the following UDFs:

CREATE FUNCTION DSWEBSECURITY.CANADMINISTER()
RETURNS INT
SPECIFIC DSWEBSECURITY_CANADMINISTER
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN 1;

CREATE FUNCTION DSWEBSECURITY.CANVIEW()
RETURNS INT
SPECIFIC DSWEBSECURITY_CANVIEW
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN 1;

Grant Execute privileges to individual users.