How Database Level Encryption Works (X100 Only)
You create an encrypted database by using the -encrypt flag on the createdb command. At that time, you are prompted to enter an initial passphrase that will be used to lock and unlock the encrypted database. Createdb leaves an encrypted database unlocked until the DISABLE PASSPHRASE statement is issued and the X100 server is terminated and restarted.
When an encrypted database is created, an AES key is randomly generated. This key is protected by encrypting it with an AES key derived from the specified PASSPHRASE.
The database is unlocked using the ENABLE PASSPHRASE statement that specifies the correct passphrase. The protected AES key is decrypted and kept in memory only for encryption and decryption of data. The DISABLE PASSPHRASE statement or a server shutdown clears the decrypted AES key from the memory and the database is locked.The ENABLE PASSPHRASE statement must be issued to access the encrypted database again.
Database encryption encrypts the values in all columns in all tables of the database, including temporary tables. Database encryption is transparent and done at the DBMS Server level.
Disable Access to an Encrypted Database (X100 Only)
To lock an encrypted database, disable the passphrase with the DISABLE PASSPHRASE statement. If the password contains blanks, enclose it in single quotes:
DISABLE PASSPHRASE 'my secret passphrase';
Note: The DISABLE PASSPHRASE statement does not take effect for running database sessions. It takes effect only for the new database sessions initiated after the statement is run. For an immediate effect on a running database session, issue the CALL X100(TERMINATE) statement to terminate this session's X100 server.
Enable Access to an Encrypted Database (X100 Only)
To enable access to a locked encrypted database, for example, after server startup, unlock the database with the ENABLE PASSPHRASE statement. If the password contains blanks, enclose it in single quotes:
ENABLE PASSPHRASE ''my secret passphrase';
Create an Encrypted Database and Lock It (X100 Only)
The following example creates an encrypted database and enables access to its data:
1. Issue the following command at the command prompt:
createdb mydb -encrypt
When prompted, enter an initial passphrase. The passphrase can contain blanks, but trailing blanks are ignored.
Note: Createdb leaves the encrypted database unlocked.
2. Connect to the database:
sql mydb
3. Lock the database:
DISABLE PASSPHRASE 'my passphrase';
4. Stop the X100 server:
CALL X100(TERMINATE);
The database is locked.
Unlock an Encrypted Database (X100 Only)
1. Connect to the database:
sql mydb -no_x100
Note: When connecting to a locked encrypted database you must connect with the -no_x100 option to enable the passphrase.
2. Use your passphrase to unlock the database.
Note: If the passphrase contains blanks, it must be enclosed in single quotes:
ENABLE PASSPHRASE 'my passphrase';
3. Alternatively, you can connect to iidbdb to unlock the database:
sql iidbdb
ENABLE PASSPHRASE 'my passphrase' ON DATABASE mydb
The following example creates a table in an encrypted database, inserts rows in a table, and then selects them:
CREATE TABLE socsec1
(fname CHAR(10),
lname CHAR(20),
socsec CHAR(11)
INSERT INTO socsec1 VALUES ('John', 'Smith', '012-33-4567');
INSERT INTO socsec1 VALUES ('Lois', 'Lane', '010-40-1234');
INSERT INTO socsec1 VALUES ('Charlie', 'Brown', '012-44-9876');
SELECT * FROM socsec1;
The following results are returned:
+----------+--------------------+-----------+
|fname |lname |socsec |
+----------+--------------------+-----------+
|John |Smith |012-33-4567|
|Lois |Lane |010-40-1234|
|Charlie |Brown |012-44-9876|
+----------+--------------------+-----------+
(3 rows)
The encryption is transparent to the application (in this case, the SQL terminal monitor), as shown by the plain text values in all columns.