![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
In this article, I will create a sample DB2 database using standard table-space files. I also demonstrate how to encrypt the storage table-spaces files and introduce some EFS commands along the way.
In a nutshell, the encryption works like this. A user generates a key which is held in a keystore; the key is used to encrypt files. The file can be accessed only by the owner of the key, unless access is given by the owner for others to access the file either individually or by group. When another user tries to access the file, that user's keys are check and verified. If authentication is satisfied, access is granted or else access is denied. A similar operation is carried out for decryption of files. The keys are kept in a keystore which is protected by the user's own keystore password.
When EFS is ran in the default mode of admin, root has access to all users and group keystores.
There are no keys held on a client machine, it is all done on the AIX host. You can tell if a user is EFS (Encrypted filesystem) ready by looking at their user attributes with the lsuser command:
# lsuser aixdev aixdev id=206 pgrp=db2grp1 groups=db2grp1,staff home=/home/aixdev shell=/usr/bin … ... pwdchecks= dictionlist= default_roles= efs_initialks_mode=admin efs_key store_algo=RSA_1024 efs_keystore_access=file efs_adminks_access=file efs_allowks modechangebyuser=true efs_file_algo=AES_128_CBC fsize=2097151 cpu=-1 data=262144 stack=65536 core=2097151 rss=65536 nofiles=2000 time_last_login=1306170057 time _last_unsuccessful_login=1306070798 tty_last_login= tty_last_unsuccessful_login= /dev/pts/0 host_last_login=rs6000 host_last_unsuccessful_login=honeysuckle unsuccessful_login_count=0 roles= |
For detailed information on EFS, see the Resources section.
The user keys are held in a keystore located in:
# pwd /var/efs/users # ls .lock alpha db2fenc1 echo root zulu aixdev dasusr1 dxtans oscar xray insadm |
The group keys keystore are located in:
# pwd /var/efs/groups # ls .lock audit cron db2fgrp1 lp staff sys app1 bin dasadm1 db2grp1 security syb system |
Let's create a sample database; the data will reside on table-spaces within the filesystem that is encrypted. The database system I am using is DB2 v9.1.
For information about DB2 and instance owner attributes see the Resources section.
Assume DB2 has been installed and an instance has been created. The owner of the instance (and the database) is aixdev and the primary group is db2grp1. We will create three filesystems that are not encrypted at this time (encryption is done later):
-
/db2/db2data: This holds the database (data storage) table-spaces. -
/db2/db2logs: This holds the database logs. -
/db2/db2diag: This holds the diag logs.
The filesystems mentioned above are created as such:
# crfs -v jfs2 -A yes -g apps_vg -m /db2/db2diag -a size=32M File system created successfully. # crfs -v jfs2 -A yes -g apps_vg -m /db2/db2logs -a size=64M File system created successfully. # crfs -v jfs2 -A yes -g apps_vg -m /db2/db2data -a size=128M File system created successfully. |
The filesystems reside in the apps_vg volume group. They have been created with the auto-mount option on, so that they are automatically mounted upon system start-up. We need to do this since DB2 is automatically started via inittab. If the filesystems are not present, errors can occur in the database. Note that we have not enable EFS at this point.
# mount /db2/db2data # mount /db2/db2logs # mount /db2/db2diag # df -m|grep -w db2 /dev/fslv06 32.00 18.50 43% 18 1% /db2/db2diag /dev/fslv07 128.00 77.64 40% 5 1% /db2/db2data /dev/fslv08 64.00 11.53 82% 20 1% /db2/db2logs |
Next we need to set the permissions and ownerships:
# cd / # chmod -R 775 /db2 # chown -R aixdev:db2grp1 /db2 |
I have used 775 for the directory permissions. We need to give the group this access, because at some point in time, other databases instances will no doubt be created and will use the above filesystem. These databases instances will also have db2grp1 as their primary group. Using group access is more efficient when dealing with application file changes.
All database related commands in this demonstration are ran by the instance owner, aixdev, unless otherwise stated.
$ cd /db2 $ ls -l total 0 drwxrwxr-x 3 aixdev db2grp1 256 May 06 19:56 db2data drwxrwxr-x 4 aixdev db2grp1 256 May 05 21:08 db2diag drwxrwxr-x 3 aixdev db2grp1 256 May 06 19:36 db2logs |
Using the database create command, we point the table-space files to be created in the /db2/db2data/mtrbikes directory. Here's how to create that directory:
$ id uid=206(aixdev) gid=204(db2grp1) groups=1(staff) $ pwd $ /db2/db2data $ mkdir mtrbikes |
The actual database is called mtrbikes. It holds a small amount of motorbike data that will suffice for this demonstration. The default database path is the instance $HOME/aixdev directory.
The following command creates the database; the command is held in the create_db file.
$ cat create_db CREATE DATABASE mtrbikes ON '/home/aixdev' CATALOG TABLESPACE MANAGED BY SYSTEM USING ( '/db2/db2data/mtrbikes/cat_ts' ) USER TABLESPACE MANAGED BY SYSTEM USING ( '/db2/db2data/mtrbikes/user_ts' ) TEMPORARY TABLESPACE MANAGED BY SYSTEM USING ( '/db2/db2data/mtrbikes/tmp_ts' ) ; |
To create the database, use:
$ db2 -tvf create_db CREATE DATABASE mtrbikes ON '/home/aixdev' CATALOG TABLESPACE MANAGED BY SYSTEM USING ( '/db2/db2data/mtrbikes/syscat_ts' ) USER TABLESPACE MANAGED BY SYSTEM USING ( '/db2/db2data/mtrbikes/user_ts' ) TEMPORARY TABLESPACE MANAGED BY SYSTEM USING ( '/db2/db2data/mtrbikes/tmp_ts' ) DB20000I The CREATE DATABASE command completed successfully. |
Test aixdev and see if the instance owner can connect to the database.
$ db2 connect to mtrbikes Database Connection Information Database server = DB2/AIX64 9.1.5 SQL authorization ID = AIXDEV Local database alias = MTRBIKES |
Next, create the additional table-space that will hold our motorbike data. The table-space is created to a size of 50MB. The actual physical file is called mtrbikes1_ts:
$ db2 "create tablespace mtrtblsp_ts managed by database using (file '/db2 /db2data/mtrbikes/mtrbikes1_ts' 50M)"; DB20000I The SQL command completed successfully. |
Confirm the table-space has been created:
$ pwd /db2/db2data/mtrbikes $ du -ms * 50.00 mtrbikes1_ts 29.95 syscat_ts 0.0tmp_ts 0.1user_ts |
So now that we have all of the mtrbikes database table-spaces in one place, the location is:
/db2/db2data/mtrbikes |
Update the dbm diag path entry and point the diag/error log files to /db2/db2diag/aixdev:
$ cd /db2/db2diag $ mkdir aixdev $ db2 get dbm cfg|grep DIAGPATH Diagnostic data directory path (DIAGPATH) = /home/aixdev/sqllib/db2dump $ db2 update dbm cfg using DIAGPATH /db2/db2diag/aixdev DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. |
Update the dbm sysadmin group entry to include the DB2 primary group for this instance, which is db2grp1:
$ db2 get dbm cfg|grep SYSADM SYSADM group name (SYSADM_GROUP) = $ db2 update dbm cfg using SYSADM_GROUP db2grp1 DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed |
For our database backups (restores), we inform DB2 to give authorization for a group to do this task. For this we use the AIX group sysmaint, whose only member is echo:
$ lsgroup -a users sysmaint sysmaint users=echo |
Update the dbm sysmaint group entry to include this group:
$ db2 get dbm cfg|grep SYSMAINT SYSMAINT group name (SYSMAINT_GROUP) = $ db2 update dbm cfg using SYSMAINT_GROUP sysmaint DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. |
Now for the database log files. Update the db cfg log path entry; the logs go to /db2/db2log/mtrbikes:
$ cd /db2/db2log $ mkdir mtrbikes $ db2 get db cfg for mtrbikes|grep "Path to log files" Path to log files = /home/aixdev/aixdev/N ODE0000/SQL00001/SQLOGDIR/ $ db2 update db cfg for mtrbikes using NEWLOGPATH /db2/db2logs/mtrbikes DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. |
Lastly, do the TCP/IP setting so remote users can connect. When the instance aixdev was created, TCP/IP entries for the instance were automatically appended to /etc/services, like so:
$ grep aixdev /etc/services DB2_aixdev 60000/tcp DB2_aixdev_1 60001/tcp DB2_aixdev_2 60002/tcp DB2_aixdev_END 60003/tcp |
We need to add a port number (generally the next incremented number) which in our demonstration is 60004. This needs to be postfixed with the entry db2c_<instance_name>. Add the following entry to the services file:
db2c_aixdev 60004/tcp |
TCP/IP can now be enabled for the instance. Update the dbm TCP/IP entry with the db2c_aixdev entry taken from /etc/services. Also, an entry for the DB2 environment variables needs to be set using the db2set utility:
$ db2 get dbm cfg |grep TCP TCP/IP Service name (SVCENAME) = $ db2 "update dbm cfg using SVCENAME db2c_aixdev" DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. $ db2set DB2COMM=tcpip |
Now, you are done. Stop and start the instance for the values to take effect:
$ db2stop force 05/07/2011 15:20:33 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful $ db2start 05/07/2011 15:20:37 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. |
The table called motorbike is now created; the create table statement is contained in the cr_table file. The table is created on the table-space mtrblsp_ts that we just created. The DB2 command to execute this file is the db2 -tvf command line utility:
$ cat cr_table CREATE TABLE motorbike ( makeof Varchar(10), modelcc Varchar(10), colour Varchar (20)) in mtrtblsp_ts; |
Connect to the database first before creating the table:
$ db2 connect to mtrbikes $ db2 -tvf cr_table CREATE TABLE motorbike ( makeof Varchar(10), modelcc Varchar(10), colour Varchar (20)) in mtrtblsp_ts DB20000I The SQL command completed successfully. |
Next, we will insert some values into the motorbike table. As before, the data is inserted via a file; in this demonstration, the file is called insert_dat:
$ cat insert_dat
insert into aixdev.motorbike values
('bmw','f800 1200','yellow/black'),
('bmw','gs 1200','white/blue'),
('bmw','gsa 1200','grey');
|
Next, insert those values:
$ db2 -tvf insert_dat
insert into aixdev.motorbike values ('bmw','f800 1200','yellow/black'), ('bmw','
gs 1200','white/blue'), ('bmw','gsa 1200','grey')
DB20000I The SQL command completed successfully. |
Confirm that data can be read from the database:
$ db2 "select * from motorbike" MAKEOF MODELCC COLOUR ---------- ---------- -------------------- bmw f800 1200 yellow/black bmw gs 1200 white/blue bmw gsa 1200 grey 3 record(s) selected. |
Confirm that the mtrtblsp_ts table-space actually holds our motorbike table. This can be achieved by querying the syscat.tables, like so:
$ db2 "select TABSCHEMA, TABNAME, TBSPACEID, TBSPACE from syscat.tables where TBSPACE='MTRTBLSP_TS'"; TABSCHEMA TABNAME TBSPACEID TBSPACE ------------------------------------------------ AIXDEV MOTORBIKE 3 MTRTBLSP_TS |
Now we have the TBSPACEID value of 3, we can query the location of that table-space:
$ db2 list tablespace containers for 3
Tablespace Containers for Tablespace 3
Container ID = 0
Name = /db2/db2data/mtrbikes/mtrbikes1_ts
Type = File
|
We can now see that the table motorbike, resides on the table-space mtrtblsp_ts.
That concludes the database configuration.
Before encrypting our DB2 files, let's first go over some common encryption commands with examples. For encrypting operations, the commands to use are:
- efsenable: Manages the activation of EFS.
- efsmgr: Manages encryption and decryption.
- efskeymgr: Manages the keystore (keys) and groups.
AIX uses the AES (Advanced Encryption Standard) for encryption. To enable EFS, use the following command on your system:
# efsenable -a |
When prompted, enter a keystore password.
When a user is initially created, their keystore is automatically created for them; the keystore password will be the same as the login password.
To change the password of your keystore, use the efskeymgr command. In the following example, the user aixdev changes his own keystore password. He is first prompted for the current keystore password:
$ efskeymgr -n aixdev's EFS password: Enter new password for aixdev's keystore: Enter the same password again: |
When a user changes their keystore password, they are still able to access all their previous encrypted files. However, this is not be the case if the keystore file is deleted or gets corrupted or depreciated keys are removed. Creating a new keystore does not decrypt the users previously encrypted files. Making regular backups of the user and group keystore is a must. Backup the directory /var/efs to tape and disk regularly so these can be retrieved quickly if key corruption occurs or via accidental deletion of a users keystore.
You also need your keystore when moving your encrypted files to another host that has EFS enabled filesystem, and you wish to decrypt those files.
The following commands copies the user/group/admin keystores to the /admin directory:
cp -rp /var/efs/users /admin/users_key$(date +"%d-%b-%y") cp -rp /var/efs/groups /admin/grp_key$(date +"%d-%b-%y") cp -rp /var/efs/efs_admin /admin/admin_key$(date +"%d-%b-%y") |
To replace a previous keystore that was corrupted or lost, do the following. Assume the user in question is insadm and the backup keystores are held in /admin/users_key28-May-11:
# pwd /admin/users_key28-May-11/insadm # ls .lock keystore # cp keystore /var/efs/users/insadm/keystore |
User insadm then logs out and back in again. User insadm is now able to access his previous encrypted files.
To check that your keystore is loaded and displays the keys in your session, use:
$ id uid=210(zulu) gid=1(staff) $ efskeymgr -V List of keys loaded in the current process: Key #0: Kind ..................... User key Id (uid / gid) ......... 210 Type ..................... Private key Algorithm ................ RSA_1024 Validity ................. Key is valid Fingerprint .............. 66e34c2d:3687eefb:3e30ee55 |
In the above example, the Kind attribute indicates it is a user key. The id attribute is the owner of the key (zulu) as denoted by the id command output previously run.
If the key does not load automatically when executing the efskeymgr, this could be because the user has su'ed from another account, or your login and /keystore password are not the same. To load your key manually, use the efskeymgr with the '-o' option. The format of the command is:
efskeymgr -o <command> |
Where typically the command is the shell of your choice, this can be done interactively or non-interactively using the '-p' option. In the following example bash is used, but you could also use ksh as the shell command. Assume the password to be used is zulu123:
$ efskeymgr -o bash -p zulu123 |
or
$ efskeymgr -o bash zulu's EFS password: |
The first example is useful when running jobs in batch or cron, and you need to load the keys automatically; the other method is interactive.
To list your current keystore, use:
$ efskeymgr -v Keystore content: Keystore owner ............ : uid 210 Keystore mode ............. : admin: managed by EFS administrator Password changed last on .. : 05/12/11 at 17:35:59 Private key: Algorithm : RSA_1024 Fingerprint : 66e34c2d:3687eefb:3e30ee55:34f4f4b6:95f76bca Validity : This key is valid. |
To encrypt files, use the efsmgr command. Let's now demonstrate how user zulu creates a file then encrypts it. The format of this command is:
efsmgr -e <file_to_encrypt> |
First create the file zuluf
$ id uid=210(zulu) gid=1(staff) $ echo "My file" > zuluf |
Now encrypt it:
$ efsmgr -e zuluf |
Confirm it has been encrypted. One method to test this is to view the last attributes from the permission listing, using the ls -Ul command. If the last attribute is an 'e', then the file is encrypted, like so:
$ ls -Ul zuluf -rw-r--r--e 1 zulu staff 1392 May 12 17:44 zuluf |
When a file is encrypted by the owner, and the owner decides to rewrite over the contents of the file, the file is still encrypted.
Next, let's see if user xray can view the file:
$ id uid=209(xray) gid=208(appgrp) groups=1(staff) |
We can see from the id command that users xray and zulu both belong to the group staff, and the file zuluf has read permission for both group and other.
$ cat zuluf cat: 0652-050 Cannot open zuluf. |
User xray cannot view the file that was encrypted by user zulu.
Allowing and sharing user key access
For user xray to view the file, user zulu needs to add access to that file to allow user xray to view it. Groups can also be granted access. The format to allow user access is:
efsmgr -a <filename_to_access> -u <user_list>: $ efsmgr -a zuluf -u xray |
Now if we list the encryption details of the file, we should see that user xray will have access:
$ id uid=210(zulu) gid=1(staff) $ efsmgr -l zuluf EFS File information: Algorithm: AES_128_CBC List of keys that can open the file: Key #1: Algorithm : RSA_1024 Who : uid 209 Key fingerprint : 3975781d:52056f10:d86aa005:57e24f27:daa20bab Key #2: Algorithm : RSA_1024 Who : uid 210 Key fingerprint : 66e34c2d:3687eefb:3e30ee55:34f4f4b6:95f76bca |
From the previous efsmgr output, we can now see the file zuluf, which has zulu's key, but also the user with a uid of 209, which is user xray, also has access.
To confirm user xray can view the file:
$ id uid=209(xray) gid=208(appgrp) groups=1(staff) $ cat zuluf My file |
User xray can now view the file.
Even if a user has been given access to another user's encrypted file, but the basic file (owner/group/other) permissions do not permit this, then access is denied.
When zulu revokes access to the file, the format for this command is:
efsmgr -r <filename> -u <user_name_to_revoke> $ efsmgr -r zuluf -u xray |
Listing encryption details of the file contained in the following output shows that the only key allowed to access the file belongs to uid 210, which is user zulu.
$ efsmgr -l zuluf EFS File information: Algorithm: AES_128_CBC List of keys that can open the file: Key #1: Algorithm : RSA_1024 Who : uid 210 Key fingerprint : 66e34c2d:3687eefb:3e30ee55:34f4f4b6:95f76bca |
To provide access via a group, which in the real AIX and UNIX world is what most system administrators would do, first create the AIX group. In this demonstration, the group appgrp has the members xray and oscar.
# mkgroup -'A' users='xray,oscar' appgrp # lsgroup -a id users appgrp appgrp id=208 users=xray,oscar |
Next, using the efskeymgr command (as root), create the keystore for the group. The format of the command is:
efskeymgr -C <group_name> # efskeymgr -C appgrp |
As a group member of appgrp, user xray lists his keys. We can see from the following output that there is now a group keystore for the group appgrp:
$ id
uid=209(xray) gid=208(appgrp) groups=1(staff)
$ efskeymgr -v
Keystore content:
Keystore owner ............ : uid 209
Keystore mode ............. : admin: managed by EFS
administrator
Password changed last on .. : 05/12/11 at 17:36:20
Private key:
Algorithm : RSA_1024
Fingerprint : 3975781d:52056f10:d86aa005:57e24f27:daa20bab
Validity : This key is valid.
Access key to keystore group/appgrp
|
Next, push the key to members of the group to allow member access. This does not mean you have to include all members, but in this demonstration, we are. The format of the command to do this is:
efskeymgr -k group/<group_name> -s user/<user_name> |
The keys are pushed to the users oscar and xray who belong to the group appgrp:
# efskeymgr -k group/appgrp -s user/oscar # efskeymgr -k group/appgrp -s user/xray |
User oscar now logs out and logs in for the changes to take effect, or he can run the following to refresh the environment:
$ efskeymgr -o bash |
User oscar now views his keys; he has the group keystore key with a gid 208 which is the group appgrp (as demonstrated earlier):
$ efskeymgr -V
List of keys loaded in the current process:
Key #0:
Kind ..................... User key
Id (uid / gid) ......... 211
Type ..................... Private key
Algorithm ................ RSA_1024
Validity ................. Key is valid
Fingerprint .............. f56f1e61:112c691f:961f771b
:b2a2582e:b905aa03
Key #1:
Kind ..................... Group key
Id (uid / gid) ......... 208
Type ..................... Private key
Algorithm ................ RSA_1024
Validity ................. Key is valid
Fingerprint .............. 2424a8a8:ce107236:ad2c708c
:59289399:e3022f6c
|
Allowing and sharing group keystore access
Assume user zulu wishes to give access to the appgrp group to a file that is about to be encrypted. First, user zulu encrypts that file:
$ efsmgr -e zuluf |
The group contains the following members:
# lsgroup -a users appgrp appgrp users=xray,oscar |
User zulu now gives group appgrp key access to the file zuluf. The format for this command is:
efsmgr -a <user_name> -g <group_name> $ efsmgr -a zuluf -g appgrp |
Looking at the file access keys for the file zuluf we have the group keystore with the gid 208, which is the appgrp group.
$ efsmgr -l zuluf EFS File information: Algorithm: AES_128_CBC List of keys that can open the file: Key #1: Algorithm : RSA_1024 Who : uid 210 Key fingerprint : 66e34c2d:3687eefb:3e30ee55:34f4f4b6:95f76bca Key #2: Algorithm : RSA_1024 Who : gid 208 Key fingerprint : 2424a8a8:ce107236:ad2c708c:59289399:e3022f6c $ id uid=211(oscar) gid=1(staff) groups=208(appgrp) $ cat zuluf My file |
In the previous example, user oscar can now access the zuluf file because he is a member of the appgrp group, and that group key has been given access to zuluf.
Revoking group keystore members
Now assume we wish to remove user oscar from that group keystore key. The format of the command is:
efskeymgr -k user/<user_name> -S group/<group_name> # efskeymgr -k user/oscar -S group/appgrp |
User oscar logs out and logs in again for the changes to take effect; user oscar's group key has now been removed:
$ id
uid=211(oscar) gid=1(staff) groups=208(appgrp)
$ efskeymgr -V
List of keys loaded in the current process:
Key #0:
Kind ..................... User key
Id (uid / gid) ......... 211
Type ..................... Private key
Algorithm ................ RSA_1024
Validity ................. Key is valid
Fingerprint .............. f56f1e61:112c691f:961f771b
:b2a2582e:b905aa03
$ efskeymgr -v
Keystore content:
Keystore owner ............ : uid 211
Keystore mode ............. : admin: managed by EFS
administrator
Password changed last on .. : 05/19/11 at 19:01:44
Private key:
Algorithm : RSA_1024
Fingerprint : f56f1e61:112c691f:961f771b:b2a2582e:b905aa03
Validity : This key is valid.
|
If user oscar tries to access the file now, he is denied:
$ cat zuluf cat: 0652-050 Cannot open zuluf. $ ls -lU zuluf -rw-r—r--e 1 zulu staff 8 May 19 19:35 zuluf |
To revoke full group access, the format of the command is:
efsmgr -r <filename> -g <group_name> |
To revoke access to the whole appgrp group to the file zuluf use the following:
$ efsmgr -r zuluf -g appgrp |
Now the file zuluf only has access by the originator, user zulu, who encrypted it:
$ efsmgr -l zuluf EFS File information: Algorithm: AES_128_CBC List of keys that can open the file: Key #1: Algorithm : RSA_1024 Who : uid 210 Key fingerprint : 66e34c2d:3687eefb:3e30ee55:34f4f4b6:95f76bca |
If user zulu wishes to remove encryption altogether from the zuluf, he could use:
$ efsmgr -d zuluf $ ls -Ul zuluf -rw-r--r--- 1 zulu staff 8 May 19 18:06 zuluf |
Enabling encryption of a filesystem can be done either at the filesystem creation level, or if already present, modified with the chfs command. To encrypt an already defined filesystem use the chfs command. The following enables EFS on the /db2/db2data filesystem:
# chfs -a efs=yes /db2/db2data |
To create a new filesystem with EFS enabled use the '-a efs=yes' option, like so:
# crfs -v jfs2 -a efs=yes -A yes -g apps_vg -m /db2/db2data -a size=128M |
To confirm the filesystem is enabled for encryption use the lsfs command:
# lsfs -q |grep db2data /dev/fslv07 --/db2/db2data jfs2 262144 rw yes no (lv size: 262144, fs size: 262144, block size: 4096, sparse files: yes, inline log: no, inline log size: 0, EAformat: v2, Quota: no, DMAPI: no, VIX: yes, EFS: yes, ISNAPSHOT: no, MAXEXT: 0) |
In the previous output, note the EFS entry for EFS:yes. This means it is EFS enabled.
If all files within a filesystem are to be encrypted, these could be done individually. However, encrypting each single file is time consuming. This method is also prone to missing some files you want encrypted. Another method is to encrypt the whole filesystem or directory with inheritance set. This way any new files that are created from a user with a valid key will automatically have their file(s) encrypted.
To enable inheritance encryption for a filesystem, the command format is:
efsmgr -s -E </filesystem> |
For example to allow inheritance on the /db2/db2data filesystem, I could use:
# efsmgr -s -E /db2/db2data |
To confirm it is set use:
# efsmgr -s -L /db2/db2data EFS inheritance is set with algorithm: AES_128_CBC |
To apply inheritance to a directory, the command format is:
efsmgr -E </directory/directory/..> |
To enable inheritance on the /db2/holding directory, I could use:
# efsmgr -E /db2/holding |
To confirm it is set, use:
# efsmgr -L /db2/holding EFS inheritance is set with algorithm: AES_128_CBC |
When inheritance is disabled, all previously encrypted files remain encrypted; any new files are not automatically encrypted. To disable inheritance on a filesystem, the command format is:
efsmgr -s -D </filesystem> |
To disable inheritance on the /db2/db2data filesystem, I could use:
# efsmgr -s -D /db2/db2data |
To disable directory inheritance, the command format is:
efsmgr -D </directory/directory/..> |
To disable inheritance on the /db2/holding directory, I could use:
# efsmgr -D /db2/holding |
In this demonstration, we will enable encryption on the /db2/db2data filesystem. This filesystem holds the table-spaces created earlier by the database. As data grows, more table-spaces are added to this filesystem.
If not already done so, make the /db2/db2data filesystem EFS enabled.
To enable EFS on an already defined filesystem, use the chfs command like so:
# chfs -a efs=yes /db2/db2data |
The default action under DB2 is that all writes to the table-spaces will be under the instance owner (as demonstrated in aixdev).
The /db2/db2data/mtrbikes directory will hold our soon to be encrypted DB2 table-space files. Here we need to decided whether to:
- enable inheritance for the whole filesystem;
- enable inheritance for the directory; or
- encrypt each individual table space.
For this demonstration we are enabling directory inheritance.
First, stop the aixdev DB2 instance:
$ db2stop force 05/22/2011 14:42:34 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. $ db2_kill ipclean: Removing DB2 engine and client's IPC resources for aixdev. |
Next, enable inheritance on the /db2/db2data/mtrbikes directory and, then verify that it is set:
# efsmgr -E /db2/db2data/mtrbikes # efsmgr -L /db2/db2data/mtrbikes EFS inheritance is set with algorithm: AES_128_CBC |
To further test this, user aixdev (the instance owner) creates a file in the directory that now has inheritance set. This file is immediately encrypted using his key like so:
$ pwd /db2/db2data/mtrbikes $ id uid=206(aixdev) gid=204(db2grp1) groups=1(staff) $ ls >myfile $ ls -Ul total 102480 -rw-------- 1 aixdev db2grp1 52428800 May 23 19:02 mtrbikes1_ts -rw-r--r--e 1 aixdev db2grp1 50 May 23 19:06 myfile drwxr-x---- 2 aixdev db2grp1 16384 May 23 18:19 syscat_ts drwxr-x---- 2 aixdev db2grp1 256 May 23 18:19 tmp_ts drwxr-x---- 2 aixdev db2grp1 256 May 23 18:19 user_ts |
Notice in the above output that none of the table-space files have been encrypted yet. For this to happen we must manually encrypt those. As there are many files to encrypt, the find command utility is your friend here. We must also do the directories, as well. The following two find commands achieve this by first encrypting the directories, followed by encrypting the files from the /db2/db2data/mtrbikes source directory down. One directory has already been encrypted using the inheritance command carried out previously; however, we need to make sure all the rest are encrypted. Note, it does not cause issues repeating the command on the same directory.
$ find /db2/db2data/mtrbikes -type d -exec efsmgr -E {} \;
$ find /db2/db2data/mtrbikes –type f -exec efsmgr -e {} \;
|
Note that the '{}' in the above find commands contains the file name when evaluated by exec.
After executing the above find commands, the files have now been encrypted:
$ ls -Ul|more total 63696 -rw-------e 1 aixdev db2grp1 4096 May 23 19:22 SQL00001.DAT -rw-------e 1 aixdev db2grp1 262144 May 23 19:22 SQL00002.DAT |
And from the top directory of mtrbikes, we have:
$ pwd /db2/db2data/mtrbikes $ ls -Ul -rw-------e 1 aixdev db2grp1 52428800 May 23 19:27 mtrbikes1_ts drwxr-x---e 2 aixdev db2grp1 16384 May 23 19:24 syscat_ts drwxr-x---e 2 aixdev db2grp1 256 May 23 19:25 tmp_ts drwxr-x---e 2 aixdev db2grp1 256 May 23 19:26 user_ts |
All done, so next start up the DB2 instance:
$ db2start 05/23/2011 19:38:12 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. |
Next, check that the instance owner can connect and select data:
$ db2 connect to mtrbikes Database Connection Information Database server = DB2/AIX64 9.1.5 SQL authorization ID = AIXDEV Local database alias = MTRBIKES $ db2 "select * from motorbike" MAKEOF MODELCC COLOUR ---------- ---------- -------------------- bmw f800 1200 yellow/black bmw gs 1200 white/blue bmw gsa 1200 grey 3 record(s) selected. |
Creating a keystore group for DB2 instance
Next, let's give access to these files via a group membership; not all files will be group writable but some will. The user insadm is a DB2 admin user and thus needs group key access.
In this demonstration, all files are granted group keystore access.
There is already an AIX db2grp1 group which insadm is a member of the current members are:
$ lsgroup -a users db2grp1 db2grp1 users=db2fenc1,dasusr1,aixdev,echo,insadm |
As root, create the keystore for the group db2grp1 but only include the member insadm. You can expand the group at a later time if more instances are created.
# efskeymgr -C db2grp1 |
As root, push the keys only to user insadm:
# efskeymgr -k group/db2grp1 -s user/insadm |
We next need to stop the DB2 instance so we can change the table-space files for encryption group access:
$ db2stop 05/22/2011 14:42:34 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. $ db2_kill ipclean: Removing DB2 engine and client's IPC resources for aixdev. |
User aixdev gives group access to the DB2 table-space files using the find command:
$ find /db2/db2data/mtrbikes -type f -exec efsmgr -a {} -g db2grp1 \;
|
Confirm the group key by checking the file encryption access on a couple of the files:
$ efsmgr -l mtrbikes1_ts EFS File information: Algorithm: AES_128_CBC List of keys that can open the file: Key #1: Algorithm : RSA_1024 Who : uid 206 Key fingerprint : 8167ebb6:d4e0e799:c9c96642:f46b31d2:5f652183 Key #2: Algorithm : RSA_1024 Who : gid 204 Key fingerprint : cbe66fe1:387bad46:48fe5ad4:a58510b1:12e851ac $ pwd /db2/db2data/mtrbikes/syscat_ts $ efsmgr -l SQL00019.DAT EFS File information: Algorithm: AES_128_CBC List of keys that can open the file: Key #1: Algorithm : RSA_1024 Who : uid 206 Key fingerprint : 8167ebb6:d4e0e799:c9c96642:f46b31d2:5f652183 Key #2: Algorithm : RSA_1024 Who : gid 204 Key fingerprint : cbe66fe1:387bad46:48fe5ad4:a58510b1:12e851ac |
From the previous output we can tell the file ownership key belongs to user aixdev (uid 206), and the keystore group db2grp1 (gid 204) also has access. The encryption group member is insadm.
As inheritance has been set on the directory, any new files that now gets created by aixdev has the group key associated with it.
User insadm logs in to check the newly granted group key:
$ id
uid=207(insadm) gid=204(db2grp1) groups=206(db2fgrp1)
$ efskeymgr -V
List of keys loaded in the current process:
Key #0:
Kind ..................... User key
Id (uid / gid) ......... 207
Type ..................... Private key
Algorithm ................ RSA_1024
Validity ................. Key is valid
Fingerprint .............. 4667f3a5:cba463fe:90b1d6d5
:3c834724:e143e70b
Key #1:
Kind ..................... Group key
Id (uid / gid) ......... 204
Type ..................... Private key
Algorithm ................ RSA_1024
Validity ................. Key is valid
Fingerprint .............. cbe66fe1:387bad46:48fe5ad4
:a58510b1:12e851ac
|
From the previous output, we see that user insadm has the group key (db2grp1, gid 204).
User aixdev now starts up the DB2 instance and connects to the database as a quick sanity test:
$ db2start 05/22/2011 14:46:49 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. $ db2 connect to mtrbikes Database Connection Information Database server = DB2/AIX64 9.1.5 SQL authorization ID = AIXDEV Local database alias = MTRBIKES $ db2 terminate |
To test the encryption, we give a user called alpha DB2 privilege to update the motorbike table with some values. User alpha does not belong to the keystore group or to the AIX db2grp1 group, nor does he share any keys with anyone. As all the table-space writes are done under the instance owner aixdev, the encryption/decryption is transparent to the user.
User aixdev now gives table access to user alpha to select and insert on table motorbike:
$ db2 connect to mtrbikes $ db2 "grant select,insert on table aixdev.motorbike to user alpha"; DB20000I The SQL command completed successfully. |
Next, user alpha connects and inserts some values to the table which resides on the encrypted table-space file:
$ id
uid=212(alpha) gid=1(staff)
$ db2 "connect to mtrbikes user alpha using alpha123"
Database Connection Information
Database server = DB2/AIX64 9.1.5
SQL authorization ID = ALPHA
Local database alias = MTRBIKES
$ db2 "insert into aixdev.motorbike values('bmw','gs 650','yellow')";
DB20000I The SQL command completed successfully.
$ db2 "select * from aixdev.motorbike"
MAKEOF MODELCC COLOUR
---------- ---------- --------------------
bmw f800 1200 yellow/black
bmw gs 1200 white/blue
bmw gsa 1200 grey
bmw gs 650 yellow
|
Figure 1 and figure 2 below show user alpha connecting from a windows client and running the same query as demonstrated in the previous example:
Figure 1. User alpha executes a select on the motorbike table
Figure 2. User alpha retrieves the decrypted data
The table-spaces are encrypted, ordinary users can still write to the table-spaces, as the updates are done under user aixdev. If there are other users that require file access, be sure to add them to the keystore group and review the table-space permissions.
If you don't have the correct key loaded that was used to encrypt initially the DB2 table-space files, when the instance is restarted and you connect to the database, expect an error similar to the following:
$db2 connect to mtrbikes SQL0901N The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed. (Reason "".) SQLSTATE=58004 |
Further investigation in the DB2 diag.log reveals authentications issues with your table-spaces, like so:
OSERR : ENOATTR (112) "Cannot find the requested security attribute." DATA #1 : File name, 42 bytes /db2/db2data/mtrbikes/syscat_ts/SQLTAG.NAM FUNCTION: DB2 UDB, oper system services, sqloopenp, probe:80 MESSAGE : ZRC=0x83000071=-2097151887 CALLED : OS, -, open OSERR : ESAD (113) "Security authentication is denied." DATA #1 : File name, 42 bytes |
The diag.log states that it cannot find the requested security attribute. You need to recover the key that was used when you encrypted the table-space files; recovery of keys was discussed earlier in the article.
Earlier in this article, we informed DB2 to allow the group sysmaint the authorization to backup the database. User echo is the only member of the group sysmaint, and the user also belongs to the group db2grp1. However the user does not have the keystore group access of db2grp1. The user is still able to perform a backup as the write of the dump is carried out under the instance owner, which is aixdev. Assume all users have logged off from the database; user echo now takes a backup of the database:
$ id uid=213(echo) gid=204(db2grp1) groups=1(staff),207(sysmaint) $ db2 connect to mtrbikes user echo using echo123 Database Connection Information Database server = DB2/AIX64 9.1.5 SQL authorization ID = ECHO Local database alias = MTRBIKES $ db2 "backup db mtrbikes to '/hold'" Backup successful. The timestamp for this backup image is : 20110529151536 $pwd /hold $ ls -Ul -rw-r----- 1 aixdev db2grp1 78684160 May 29 15:15 MTRBIKES.0.aixdev.NODE 0000.CATN0000.20110529151536.001 |
Notice the dump file is owned by aixdev, and it is not encrypted. This is because it has been dumped to a non-EFS filesystem.
The instance owner, aixdev, now restores the database over the existing database:
$ db2 "restore db mtrbikes from '/hold/' taken at 20110529151536" SQL2539W Warning! Restoring to an existing database that is the same as the backup image database. The database files will be deleted. Do you want to continue ? (y/n) y DB20000I The RESTORE DATABASE command completed successfully. $ db2 "connect to mtrbikes" Database Connection Information Database server = DB2/AIX64 9.1.5 SQL authorization ID = AIXDEV Local database alias = MTRBIKES |
Looking at the table-space files, we see that they are all still encrypted after the restore.
$ ls -Ul total 108088 -rw-------e 1 aixdev db2grp1 52428800 May 29 15:52 mtrbikes1_ts drwxr-x---e 2 aixdev db2grp1 16384 May 29 15:48 syscat_ts drwxr-x---e 2 aixdev db2grp1 256 May 28 08:39 tmp_ts drwxr-x---e 2 aixdev db2grp1 256 May 28 08:39 user_ts |
If you drop the database, then restore it from a backup image (assuming aixdev's key has not changed, the table-space files will be restored and encrypted as before). In the following example, user aixdev now drops the database and does a redirect restore from the previous backup image. Notice that after the drop of the database all table-space files have been deleted. The drop of the database is confirmed by not having a database entry in the db directory:
$ id uid=206(aixdev) gid=204(db2grp1) groups=1(staff) $ db2 drop db mtrbikes DB20000I The DROP DATABASE command completed successfully. $ pwd /db2/db2data/mtrbikes $ ls $ $ db2 list db directory SQL1057W The system database directory is empty. SQLSTATE=01606 |
Now for the redirect restore:
$ db2 -tvf MTRBIKESredirect.sql restore db MTRBIKES from '/hold' taken at 20110529191939 into MTRBIKES redirect without rolling forward without prompting SQL1277W A redirected restore operation is being performed. Table space configuration can now be viewed and table spaces that do not use automatic storage can have their containers reconfigured. DB20000I The RESTORE DATABASE command completed successfully. set tablespace containers for 0 using (Path '/db2/db2data/mtrbikes/syscat_ts') DB20000I The SET TABLESPACE CONTAINERS command completed successfully. set tablespace containers for 1 using (Path '/db2/db2data/mtrbikes/tmp_ts') DB20000I The SET TABLESPACE CONTAINERS command completed successfully. set tablespace containers for 2 using (Path '/db2/db2data/mtrbikes/user_ts') DB20000I The SET TABLESPACE CONTAINERS command completed successfully. set tablespace containers for 3 using (File '/db2/db2data/mtrbikes/mtrbikes1_ts'12800) DB20000I The SET TABLESPACE CONTAINERS command completed successfully. restore db MTRBIKES continue DB20000I The RESTORE DATABASE command completed successfully |
After the restore, the table-spaces are restored with encryption still present.
$ pwd /db2/db2data/mtrbikes $ ls -Ul total 102496 -rw-------e 1 aixdev db2grp1 52428800 May 30 13:14 mtrbikes1_ts drwxr-x---e 2 aixdev db2grp1 16384 May 30 13:14 syscat_ts drwxr-x---e 2 aixdev db2grp1 256 May 30 13:14 tmp_ts drwxr-x---e 2 aixdev db2grp1 256 May 30 13:14 user_ts |
Using EFS to encrypt DB2 storage files provides privacy of your data. The process is transparent to the users connecting to the database. If you have group members who belong to the DB2 SYSCTRL and SYSMAINT groups, it maybe advantageous to give them group key access as well.
Learn
- DB2 V9
Information Center
- The AIX V6 Advanced Security Features Redbook
highlights AIX 6 security features including EFS keystore.
Get products and technologies
-
Try out IBM
software for free. Download a trial version, log into an online trial, work with
a product in a sandbox environment, or access it through the cloud. Choose from over 100 IBM product trials.
Discuss
- Follow developerWorks on Twitter.
-
Participate in developerWorks blogs and get involved in the developerWorks community.
- Get involved in the My developerWorks community.
-
Participate in the AIX and UNIX® forums:
- AIX Forum
- AIX Forum for developers
- Cluster Systems Management
- Performance Tools Forum
- Virtualization Forum
- More AIX and UNIX Forums
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
