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.
You can encrypt the operating system files that contain the data in database tables by using the underlying EFS with JFS2 file system.
- 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.
% efsenable -a
You need to run the efsenable command only once.
Loading the keystores
- 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.
% chfs -a efs=yes filesystem
command
to enable EFS, for example:% chfs -a efs=yes /test01
-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.
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 ane
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.