[All]
Encrypting every user column in a database
Resumo: 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;
|
