Encrypting every user column in a database

Abstract: A simple isql script to encrypt every column in a database, or every column in a table

Q. How do I encrypt every column in my database or every column in a table?

A. For all encryptions you first need to connect to your database as the owner of the database or sysdba and enable end user authentication and create a sysdso user:

isql employee.ib -user SYSDBA -pass masterkey

ALTER DATABASE ADD ADMIN OPTION;

CREATE USER SYSDSO SET PASSWORD 'sysdso';

EXIT;

 

Next, you need to connect to the database create a system encryption password if one does not exist, create the encryption key and grant rights for encryption on that key:

isql employee.ib -user SYSDSO -pass sysdso

ALTER DATABASE SET SYSTEM ENCRYPTION PASSWORD 'Secret Password';

CREATE ENCRYPTION Col_Level_Key FOR DES;

GRANT ENCRYPT ON ENCRYPTION Col_Level_Key TO SYSDBA;

EXIT;


Finally, you need to alter the columns in your database to add encryption. If you want to alter every single column in your database, you'll probably find this to be a time consuming job. However, the RDB$RELATION_FIELDS table has a list of all fields in your database, so by querying it, you can create a script to do the job for you.

isql employee.ib -user SYSDBA -pass masterkey

SET LIST ON;
OUTPUT Encryptions.sql;
SELECT 'alter table ' || rdb$relation_name || 'alter column ' || rdb$field_name || 'encrypt with Col_Level_Key;' FROM RDB$RELATION_FIELDS a INNER JOIN rdb$RELATIONS b ON a.RDB$RELATION_NAME=b.RDB$RELATION_NAME WHERE b.RDB$SYSTEM_FLAG=0 AND b.RDB$RELATION_TYPE='PERSISTENT';
OUTPUT;
INPUT Encryptions.sql;
EXIT;


This creates a file named Encryptions.sql and then uses the SQL commands contained in it alter all the columns in your database. If you open Encryptions.sql, you will see something like this for each table in your database::

                                alter table SALES
                    alter column QTY_ORDERED
       encrypt with Col_Level_Key;

Finally, you can easil alter the script above to only encrypt one table. Below is an example where only the SALES table is encrypted:

isql employee.ib -user SYSDBA -pass masterkey

SET LIST ON;
OUTPUT Encryptions.sql;
SELECT 'alter table ' || rdb$relation_name || 'alter column ' || rdb$field_name || 'encrypt with Col_Level_Key;' FROM RDB$RELATION_FIELDS a INNER JOIN rdb$RELATIONS b ON a.RDB$RELATION_NAME=b.RDB$RELATION_NAME WHERE b.RDB$SYSTEM_FLAG=0 AND b.RDB$RELATION_TYPE='PERSISTENT' AND b.RDB$RELATION_NAME='SALES';
OUTPUT;
INPUT Encryptions.sql;
EXIT;