Database encryption using AIX encrypted file system (EFS)

If you are running a Db2® system on the AIX® operating system, you have the option to set up an encrypted database by using AIX encrypted file system (EFS). For detailed information about EFS, see your AIX documentation.

Note: If you are working in a partitioned database environment, to use EFS, your database should be in a single database partition.

You can encrypt the operating system files that contain the data in database tables by using the underlying EFS with JFS2 file system.

To set up encryption, the steps are as follows:

Enable EFS on the system.
Load the keystores for the user account under which the Db2 database daemons run.
Enable EFS on the database file system.
Determine the operating system file to encrypt.
Encrypt the file that contains the database table that requires EFS protection.

Enabling EFS on the system

Before you enable EFS, the clic.rte fileset must be installed. The clic.rte install image can be found on the Expansion Pack CD.

Run the following command as root to enable EFS on the system:

% efsenable -a

You need to run the efsenable command only once.

Loading the keystores

In the following configuration examples, the Db2 user account under which the database daemons run is called abst. The user abst must have a keystore and any group that abst is a member of must also have a keystore.

All keystores must be associated with the abst process before starting the Db2 daemons.

You can verify that they are associated by using the efskeymgr -V command, as shown in the following example:

# lsuser abst
abst id=203 pgrp=abstgp groups=abstgp,staff ...

# efskeymgr -V
List of keys loaded in the current process:
  Key #0:
                    Kind ..................... User key
                    Id (uid / gid) ......... 203
                    Type ..................... Private
key
                    Algorithm ................ RSA_1024
                    Validity ................. Key is
valid
                    Fingerprint ..............
24c88df2:d91cb6a2:c3e11b6a:4c13f8b4:666fabd8

  Key #1:
                    Kind ..................... Group
key
                    Id (uid / gid) ......... 1
                    Type ..................... Private
key
                    Algorithm ................ RSA_1024
                    Validity ................. Key is
valid
                    Fingerprint ..............
03fead42:57e7646e:a1715626:cfa56c8e:8abed1c1

  Key #2:
                    Kind ..................... Group
key
                    Id (uid / gid) ......... 212
                    Type ..................... Private
key
                    Algorithm ................ RSA_1024
                    Validity ................. Key is
valid
                    Fingerprint ..............
339dfb19:bc850f4c:5551c975:7fe4961b:2dddf3bc

If there are no keystores shown as associated with the abst process, try loading the keystores using the command: % efskeymgr -o ksh
This command prompts for the keystore password, which is initially set to the login password.
Confirm that the user and group keys are loaded by rerunning the command: % efskeymgr -V
Both the user and group keys should be listed. If the group keystores are still not listed, continue with Step 4.
Depending on how a group was created, the group keystore may not exist. If the efskeymgr -V command does not list the user's group keystores, you must create the group keystores.
As root or the RBAC role aix.efs_admin, create the group keystore:
```
% efskeymgr -C group_name
```
Assign group keystore access to each applicable user:
```
% efskeymgr -k group /group_name -s user/user_name
```
If a user is already logged in, they will not immediately have access to the group keystore, and they should reload their keystore using the efskeymgr -o ksh command, or re-login.

Enabling EFS on the database file system

EFS only runs on JFS2 file systems and must be specifically enabled.

If your database resides on an existing file system, run the % chfs -a efs=yes filesystem command to enable EFS, for example:

% chfs -a efs=yes /test01

If you are creating a new file system, you can enable EFS using the -a efs=yes option with the smit command or the crfs command. For example:

% crfs -v jfs2 -a efs=yes -m mount_point -d device -A yes

EFS is now enabled on the file system but is not turned on. Turn on EFS only for the particular database tables requiring encrypted data (for more information, see your AIX EFS documentation about the efsmgr command and inheritance).

Determining the file to encrypt

To determine which file contains a particular database table that you want to protect with EFS encryption, follow these steps that use the EMPLOYEE table as an example.

Use a query similar to the following example to find the TBSPACEID for the table:
```
SELECT TABNAME, TBSPACEID FROM syscat.tables WHERE tabname='EMPLOYEE'
```
Assume the results of this query are as follows:

TABNAME TBSPACEID

EMPLOYEE 2
Look up the table spaces for that TBSPACEID with a query similar to the following example:
```
LIST TABLESPACE CONTAINERS FOR 2
```
Assume the results of this query are as follows:

Container ID Name Type

0 /test01/abst/NODE0000/BAR/T0000002/C0000000.LRG File

You now know that this table space is contained in the operating system file called /test01/abst/NODE0000/BAR/T0000002/C0000000.LRG. This is the file you need to encrypt.

TABNAME	TBSPACEID
EMPLOYEE	2

Container ID	Name	Type
0	/test01/abst/NODE0000/BAR/T0000002/C0000000.LRG	File

Encrypting the file

First, as you would do before making any major change to data or databases, back up your database.

Follow these steps to encrypt the file:

List the file, for example:

# ls -U /test01/abst/NODE0000/BAR/T0000002/C0000000.LRG

-rw-------- 1 abst abstgp 33554432 Jul 30 18:01
/test01/abst/NODE0000/BAR/T0000002/C0000000.LRG

Encrypt the file using the efsmgr command, for example:

# efsmgr -e /test01/abst/NODE0000/BAR/T0000002/C0000000.LRG

If you list the file again you will see an e at the end of the permissions string that indicates the file is encrypted. For example:

# ls -U /test01/abst/NODE0000/BAR/T0000002/C0000000.LRG

-rw-------e 1 abst abstgp 33554432 Jul 30 18:03
/test01/abst/NODE0000/BAR/T0000002/C0000000.LRG

Start the Db2 database manager and use it as normal. All data added to the EMPLOYEE table and this encrypted table space will be encrypted by EFS in the underlying file system. Whenever the data is retrieved, it will be decrypted and presented as normal through the Db2 database manager.