Database Encryption
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.
To use database encryption, use the createdb...-encrypt command to encrypt the data and define the encryption passphrase. To enable access to the database, use the ENABLE PASSPHRASE statement.
The passphrase applies to the database.
Disable Access to an Encrypted Database
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
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
The following example creates an encrypted database and locks 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 an 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
The following example enables access to the data in an encrypted database:
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.
Last modified date: 01/26/2023