Securing your DB2 file systems with EFS

AIX encryption with DB2

From AIX 6.1 onwards, user created filesystems can now be encrypted, which is implemented at the file system level. This approach allows for some applications to be encrypted, with not much overhead maintenance. One such product that can be used for data encryption is DB2. The need to encrypt application data is becoming more common, especially if backups are taken and are moved off site, or the application resides on external sites. SOX even recommends that to maintain the confidentially of the data, encryption should be strongly considered. This article demonstrates how to create a DB2 database and encrypt it under AIX and looks at common EFS commands.

David Tansley (david.tansley@btinternet.com), System Administrator, Ace Europe

David TansleyDavid Tansley is a freelance writer. He has 15 years of experience as a UNIX administrator, using AIX the last eight years. He enjoys playing badminton, then relaxing watching Formula 1, but nothing beats riding and touring on his GSA motorbike with his wife.


developerWorks Contributing author
        level

25 October 2011

Also available in Chinese

Introduction

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

Creating a sample database

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.

Create the filesystems

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

Do the permissions

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

Create the database

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.

Insert some data

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.


Getting to know EFS

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.

Root password and keystore password

For ease of administration, it makes good sense to have your root password and keystore password the same to keep them in sync. This also applies to ordinary users on the system when using their own keystore passwords.

The keystore and user login password are different in operation. The keystore password protects the user's own keystore which holds the users key(s).

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:

Keys, keys, keys

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.

Load and list your keystore

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.

User encryption

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.

Revoking user access

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

Using group keystore access

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

Decrypt files

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

filesystems and directories

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

Encrypting DB2 files

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).

When other instances are created, the table-spaces should be created in /db2/db2data/<DB>. Each directory can then be encrypted per database directory.

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.

Setting inheritance

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

Encrypting the database files

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

Test it

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
Screen shot of user alpha executing a select on the motorbike table
Figure 2. User alpha retrieves the decrypted data
Screen shot of user alpha retrieving 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.


Not the right key

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.


DB2 backup and restore

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

Conclusion

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.

Resources

Learn

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into AIX and Unix on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=AIX and UNIX, Security
ArticleID=766714
ArticleTitle=Securing your DB2 file systems with EFS
publish-date=10252011