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. The key is then encrypted using an AES key derived from the specified PASSPHRASE.
The database is unlocked using the ENABLE PASSPHRASE statement that specifies the correct passphrase. An in-memory-only decrypted key is created for use by the encryption and decryption code. At server shutdown, this decrypted key is cleared, and the encrypted data is effectively locked. At server startup, the ENABLE PASSPHRASE must be issued again to access the encrypted data.
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';
An attempt to access the data will result in an error message.
Note: The DISABLE PASSPHRASE statement does not take effect until the running X100 server for the database is terminated and restarted. If you want to immediately terminate the X100 server for the database, connect to the database with the Terminal Monitor and issue the statement: CALL X100(TERMINATE).
Enable Access to an Encrypted Database (X100 Only)
To unlock an encrypted database, enable the passphrase after server startup 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 an encrypted database to enable the passphrase, you must connect with -no_x100.
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.