Map database user or role to CipherTrust Manager user

CDP enables an authorized database user’s access to encryption keys. You have the option of mapping a database user or a database role directly to a CipherTrust Manager user. You can also map an AD user or AD group to a CipherTrust Manager user.

Important Notes

• The Group Mapping or Role Mapping feature works only on the database roles. The feature does not work for the UDFs such as ing_gn_rndm, where the database name (required for mapping) is not passed.

• In case the user belongs to multiple roles, the user inherits the key rights from the roles in an alphabetical order.

• If the user is mapped individually and also belongs to a database role, then the individual user mapping takes precedence.

• If an AD user is a member of both a database role and an AD group (which is a member of another database role), and if both these database roles are mapped to an NAE user, then the database role containing the individual AD user takes higher priority, irrespective of its position when sorted alphabetically.

• The mapping priority takes precedence in the following order: individual > database role > AD group > Default Mapping.

• The deletion of any database user from the database role is applicable only after executing the ing_flush_metadata_cache (a stored procedure) from the Metadata Database.

Prerequisites

To understand the instructions given below, consider you have:

• An encrypted table in the Testdatabase database.

• Two database users (User01 and User02) having access permissions on the database, Testdatabase.

• Two Windows AD users (sfnt\ADUser01 and sfnt\ADUser02) having access permissions on Testdatabase.

• A database role, DBRole (consisting of User01, User02, and sfnt\ADUser01), defined on Testdatabase.

• An AD group, ADGroup (consisting of sfnt\ADUser01 and sfnt\ADUser02), having access permissions on Testdatabase.

• A database role, DBRole2 (consisting of ADGroup), defined on Testdatabase.

Steps

Map database role to NAE user

To map the database role, DBRole, to the NAE user, dbusr (This CipherTrust Manager user has access to the required key):

1. Log on to the CipherTrust Manager GUI.

2. Click Database Protection to open the application. The Databases screen displays the list of existing database connections, if any.

3. Click the overflow icon corresponding to the desired database connection.

4. Click Manage User Mapping. The List of users screen is displayed.

5. On the List of users screen, click Map User.

6. Enter the database role (DBRole) in the Database User field.

7. Enter the local user (CipherTrust Manager user) name (dbusr) and corresponding password.

8. Click Save. The new user mapping appears on the List of users screen.

   After the database role is mapped, the database users, User01 and User02, and the AD user, sfnt\ADUser01 (mapped to the role), can execute the select queries on the CipherTrust Manager. To verify:

   • Log on to SQL Server Management Studio as User01, User02, or sfnt\ADUser01.

   • Execute a select query.

Map AD group to NAE user

Before mapping an AD group (ADGroup) to an NAE user, you must configure ADSI and add Fully Qualified Domain Name (FQDN) to the Domain_Name parameter of the properties file.

Configuring ADSI

To configure ADSI, perform the following steps on SQL Server:

• Add the linked server. Execute the following query:

EXEC sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', @provider = 'ADSDSOObject', @datasrc = 'adsdatasource'

• Add serverlogin credential details. Execute the following query:

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'False', @locallogin = 'sa', @rmtuser = 'sfnt\administrator', @rmtpassword = 'password'

• Verify whether the users/groups are correctly fetched from the AD or not. Make sure to execute the following query using the user that was used to configure ADSI:

select * from openquery(ADSI,'select * from ''LDAP://dc= sfnt, dc=test'' WHERE objectClass=''Group'' or objectClass=''User''')

Here, the domain name is sfnt.test. Refer to the Microsoft Documentation for details.

Add FQDN to the Domain_Name Parameter

After configuring ADSI, add FQDN to the Domain_Name parameter of the properties file, CDP_MSSQLServer.properties. For example, set Domain_Name=sfnt.test.

Map AD Group to NAE User

Now, you are ready to map an AD Group (consisting of multiple AD users) to an NAE user.

In CDP, you can map an AD group to an NAE user in two ways. You can either directly map an AD group to an NAE user or through database role mapping, as described in the following two topics:

• Map an AD Group Directly to an NAE User

• Map an AD Group through a Database Role

Map an AD Group Directly to an NAE User

1. Log on to the CipherTrust Manager GUI.

2. Click Database Protection to open the application. The Databases screen displays the list of existing database connections, if any.

3. Click the overflow icon corresponding to the desired database connection.

4. Click Manage User Mapping. The List of users screen is displayed.

5. On the List of users screen, click Map User.

6. Enter the AD group (sfnt\ADGroup) in the Database User field. Make sure to include the domain name followed by “\” (backslash). In our example, sfnt is the domain name.

7. Enter the local user (CipherTrust Manager user) name (dbusr) and corresponding password. This user has access to the required key.

8. Click Save. The new user mapping appears on the List of users screen.

   Once the AD group is mapped, the AD users, ADUser01 and ADUser02, can execute the select queries on the CipherTrust Manager. To verify:

   • Log on to SQL Server Management Studio as sfnt\ADUser01, or sfnt\ADUser02.

   • Execute a select query.

Map an AD Group through a Database Role

To map an AD group to an NAE user through a database role, you first need to add the AD group to the database role. After the AD group is added to the database role, map the database role to an NAE user.

1. Add the AD group, ADGroup, to the role, DBRole2.

2. Log on to the CipherTrust Manager GUI.

3. Click Database Protection to open the application. The Databases screen displays the list of existing database connections, if any.

4. Click the overflow icon corresponding to the desired database connection.

5. Click Manage User Mapping. The List of users screen is displayed.

6. On the List of users screen, click Map User.

7. Enter the database role (DBRole2) in the Database User field.

8. Enter the local user (CipherTrust Manager user) name (dbusr) and corresponding password. This user has access to the required key.

9. Click Save. The new user mapping appears on the List of users screen.

   After the AD group is mapped, the AD users, ADUser01 and ADUser02, can execute the select queries on the CipherTrust Manager. To verify:

   • Log on to SQL Server Management Studio as sfnt\ADUser01, or sfnt\ADUser02.

   • Execute a select query.

   If any changes are made to AD Groups and Users on Domain Controller (for example, any group/user is deleted from AD or any user is moved from one AD group to another), then you must clear the metadata cache either by restarting the SQL Server or by executing the ing_flush_metadata_cache stored procedure.

   To clear the metadata cache, execute the following commands:

       DECLARE @exitStatus int SET @exitStatus=1
       EXEC metadb.dbo.ing_flush_metadata_cache @exitStatus
   

   In the above command, metadb is the metadata and dbo is the schema name.