Database encryption and decryption
1. In Interactive SQL, connect to a database other than the one you want to decrypt 2 Execute a CREATE DECRYPTED DATABASE statement.
When you execute a CREATE DECRYPTED DATABASE statement, you do not decrypt (overwrite) the file; you create a copy of the file in decrypted form. If there are transaction logs, transaction log mirrors, or dbspaces associated with the database, decrypted copies of those files are made as well.
Encryption keys
It is best to choose an encryption key value that cannot be easily guessed. The key can be of arbitrary length, but generally the longer the key, the better because a shorter key is easier to
guess than a longer one. As well, including a combination of numbers, letters, and special characters decreases the chances of someone guessing the key.
Encryption keys are always case sensitive, and they cannot contain leading or trailing spaces or semicolons.
You must supply this key each time you want to start the database. Lost or forgotten keys result in completely inaccessible databases.
You can choose whether the encryption key is entered at a command prompt (the default) or into a prompt box. Choosing to enter the key in a prompt box provides an extra measure of security because the key is never visible in plain sight. Clients are required to specify the key each time they start the database. If the database administrator starts the database, clients never need to have access to the key.
Warning! For strongly encrypted databases, store a copy of the key in a safe location. If you
lose the encryption key, there is no way to access the data—even with the assistance of Technical Support. The database must be discarded and you must create a new database.
Changing the encryption key for a database
You can change the encryption key for an encrypted database, or for a database for which table encryption has been enabled, by using the CREATE ENCRYPTED DATABASE statement. Changing the encryption key does not overwrite the existing file, but creates a copy of the file encrypted with the new key.
Prerequisites
By default, you must have the SERVER OPERATOR system privilege to execute the CREATE ENCRYPTED DATABASE statement. The required privileges can be changed by using the -gu database server option.
Task
Change the encryption key for an encrypted database using the CREATE ENCRYPTED DATABASE statement.
The encryption key is changed.
Security and performance issues
Performance of SAP Sybase IQ is slower when the database is encrypted. The performance impact depends on how often pages are read from or written to disk, and can be minimized by ensuring that the server is using an adequate cache size.
You can increase the starting size of the cache with the -c option when you start the server. For operating systems that support dynamic resizing of the cache, the cache size that is used may be restricted by the amount of memory that is available; to increase the cache size, increase the available memory.
Column and table encryption
If you only want to encrypt portions of your database, you can choose to encrypt columns or tables.
Column encryption can be performed on any column in any table at any time. Table encryption requires that the database have table encryption enabled. Table encryption is enabled at database creation (initialization) time.
• To encrypt tables – You can use the following:
• Initialization utility (iqinit). • CREATE DATABASE statement. • ALTER DATABASE statement.
• CREATE ENCRYPTED TABLE DATABASE statement. • To encrypt columns – ENCRYPT function.
• To encrypt materialized views – ALTER MATERIALIZED VIEW statement.
Column encryption
To encrypt columns in your database, use the ENCRYPT function. The ENCRYPT function uses the same AES strong encryption algorithm that is used for database encryption to encrypt values that are passed to it.
Encrypted data can be decrypted with the DECRYPT function. You must use the same key that was specified in the ENCRYPT function. Both of these functions return LONG BINARY values. If you require a different data type, you can use the CAST function to convert the value to the required data type.
The ENCRYPT and DECRYPT functions also support raw encryption. You can encrypt data inside the database server into a format that can be exported and decrypted outside of the server.
If database users need to access the data in decrypted form, but you do not want them to have access to the encryption key, you can create a view that uses the DECRYPT function. This allows users to access the decrypted data without knowing the encryption key. If you create a view or stored procedure that uses the table, you can use the SET HIDDEN parameter of the ALTER VIEW and ALTER PROCEDURE statements to ensure that users cannot access the encryption key by looking at the view or procedure definition.
Column encryption example
The following example uses triggers to encrypt a column that stores passwords in a table called user_info. The user_info table is defined as follows:
CREATE TABLE user_info (
employee_ID INTEGER NOT NULL PRIMARY KEY, user_name CHAR(80),
Two triggers are added to the database to encrypt the value in the user_pwd column, either when a new user is added or an existing user's password is updated.
• The encrypt_new_user_pwd trigger fires each time a new row is added to the user_info_table:
CREATE TRIGGER encrypt_new_user_pwd BEFORE INSERT
ON user_info
REFERENCING NEW AS new_pwd FOR EACH ROW
BEGIN
SET new_pwd.user_pwd=ENCRYPT(new_pwd.user_pwd, '8U3dkA'); END;
• The encrypt_updated_pwd trigger fires each time the user_pwd column is updated in the user_info table:
CREATE TRIGGER encrypt_updated_pwd BEFORE UPDATE OF user_pwd
ON user_info
REFERENCING NEW AS new_pwd FOR EACH ROW
BEGIN
SET new_pwd.user_pwd=ENCRYPT(new_pwd.user_pwd, '8U3dkA'); END;
Add a new user to the database:
INSERT INTO user_info
VALUES ( '1', 'd_williamson', 'abc123');
If you issue a SELECT statement to view the information in the user_info table, the value in the user_pwd column is binary data (the encrypted form of the password) and not the value abc123 that was specified in the INSERT statement.
If this user's password is changed, then the encrypt_updated_pwd trigger fires and the encrypted form of the new password appears in the user_pwd column.
UPDATE user_info SET user_pwd='xyz' WHERE employee_ID='1';
The original password can be retrieved by issuing the following SQL statement. This statement uses the DECRYPT function and the encryption key to decrypt the data, and the CAST function to convert the value from a LONG BINARY to a CHAR value:
SELECT CAST (
DECRYPT( user_pwd, '8U3dkA' ) AS CHAR(100))
FROM user_info
WHERE employee_ID = '1';
Raw encryption
Raw encryption allows you to encrypt data inside the database server into a format that can be exported and decrypted outside of the database server. The encrypted format is referred to as
vector, and optionally a padding format. To decrypt the data, you must specify the same parameter values.
You can also use the DECRYPT function to decrypt the data inside the database server. Raw encryption is useful when:
• You want to prevent database users from having access to the data – You can use raw
encryption to encrypt sensitive data that you do not want even your database administrators to have access to, and then decrypt the data using a client application without the use of the database server. Raw encryption is not recommended when the data needs to be encrypted and decrypted only by the database server.
• You cannot use TLS encryption – You can use raw encryption instead of TLS encryption.
Unlike TLS encryption, raw encryption cannot prevent replay or person-in-the-middle attacks, nor can it authenticate database servers.
Example
You need to send data from the binary_data column of the SensitiveData table in your database to a client that does not use databases. Because the data is sensitive, you encrypt the data into raw format using the following SQL statement:
SELECT ENCRYPT( binary_data, 'TheEncryptionKey','AES(FORMAT=RAW)', 'ThisIsTheIV' )FROM SensitiveData;
You copy the encrypted data to the client along with an application that can decrypt the contents. You also provide the encryption key (TheEncryptionKey) and the initialization vector (ThisIsTheIV) to the client to use with the application. The client uses the application to decrypt the data and view it.
Table encryption
Table encryption allows you to encrypt tables or materialized views with sensitive data without the performance impact that encrypting the entire database might cause. When table encryption is enabled, table pages for the encrypted table, associated index pages, and temporary file pages are encrypted. The transaction log pages that contain transactions on encrypted tables are also encrypted.
To encrypt tables in your database, you must have table encryption enabled. Enabling table encryption must be done at database initialization. To see whether table encryption is enabled, query the EncryptionScope database property using the DB_PROPERTY function, as follows:
SELECT DB_PROPERTY( 'EncryptionScope' );
If the return value is TABLE, table encryption is enabled.
To see the encryption algorithm in effect for table encryption, query the Encryption database property using the DB_PROPERTY function, as follows:
Performance impact of table encryption
For encrypted tables, each table page is encrypted when written to the disk, and is decrypted when read in from the disk. This process is invisible to applications. However, there may be a slight negative impact on performance when reading from, or writing to, encrypted tables. Encrypting or decrypting existing tables can take a long time, depending on the size of the table.
Index pages for indexes on columns in an encrypted table are also encrypted, as are transaction log pages containing transactions on the encrypted table, and all pages in the temporary file for the database. All other database and transaction log pages are unencrypted.
Encrypted tables can contain compressed columns. In this case, the data is compressed before it is encrypted.
Encrypting tables does not impact storage requirements.
Starting a database that has table encryption enabled
Starting a database that has table encryption enabled is the same as starting an encrypted database. For example, if the database is started with the -ek option, a key must be specified. If the database is started with the -ep option, you are prompted for the key.
Enabling table encryption in a database (SQL)
Create a database with table encryption by using the CREATE DATABASE statement, or enable table encryption in an existing database by using the CREATE ENCRYPTED TABLE DATABASE statement.
Prerequisites
By default, you must have the SERVER OPERATOR system privilege to execute the CREATE DATABASE statement and the CREATE ENCRYPTED TABLE DATABASE statement. The required privileges can be changed by using the -gu database server option.
Task
Table encryption must be enabled and configured at database creation time. If your database does not have table encryption enabled, or if you have database encryption in effect, using the CREATE ENCRYPTED TABLE DATABASE statement creates a copy of the database with table encryption enabled, and does not overwrite the original database file.
Create a database with table encryption, or enable table encryption on an existing database.
Option Action
Create a database with table encryption Create a database with the CREATE DATABASE statement, and specify a key and an encryption algorithm.
Option Action
Enable table encryption for an existing database Create a copy of the database with the CREATE ENCRYPTED TABLE DATABASE statement, and specify a key.
Table encryption is enabled.
Next
You create an encrypted table by using the CREATE TABLE statement, or by altering an existing table to be encrypted by using the ALTER TABLE statement. When you encrypt a table, the key and/or algorithm specified when enabling table encryption is used.
Enabling table encryption in a database (iqinit utility)
You can enable table encryption during the creation of a database, using the command line.
Prerequisites
Table encryption must be enabled and configured at database creation time. You must re- create the database with table encryption enabled if your database does not have table encryption enabled, or if you have database encryption in effect.
Task
Create a database with the iqinit -et and -ek options, and specify a key and an encryption algorithm.
Table encryption is enabled.
Encrypting a table
You can create an encrypted table using the CREATE TABLE statement, or encrypt an existing table using the ALTER TABLE statement.
Prerequisites
To use the CREATE TABLE statement, you must have one of the following system privileges: CREATE TABLE
CREATE ANY TABLE CREATE ANY OBJECT
To use the ALTER TABLE statement, you must be the owner of the table being altered or have one of the following privileges:
ALTER privilege on the table ALTER ANY TABLE
ALTER ANY OBJECT
To encrypt tables in your database, table encryption must already be enabled in the database.
Task
When you encrypt a table, the encryption algorithm and key that were specified at database creation time are used.
You can either create a table with encryption, or encrypt an existing table.
Option Action
Create a table with encryption Create a table using the ENCRYPTED clause of the CREATE TABLE statement.
Encrypt an existing table Encrypt a table with the ENCRYPTED clause of the ALTER TABLE statement.
The table is encrypted.