Table of Contents
This chapter explains how to use and manage the data encryption functions in Tibero.
As described in “Chapter 5. User Management and Database Security”, Tibero provides user accounts, permissions, and roles for data security. However, these do not securely protect data if a data file is directly accessed from the operating system instead of the database. To protect against this, Tibero provides the ability to encrypt data and save it to disk.
If the DBA designates data such as table columns or tablespaces to be encrypted, Tibero encrypts the data before saving it and decrypts the data before using it. Users and applications do not need to consider whether data is encrypted or not.
To use data encryption, a security wallet must be created in advance. A security wallet has a master key used for encryption. Tibero creates an encryption key to encrypt data from the master key and encrypts data using the encryption key.
Encrypting data uses the following process:
Creating a security wallet
To create a security wallet, execute the program $TB_HOME/bin/tbwallet_gen and enter a file name and password for the security wallet.
[Example 11.1] Creating a Security Wallet
$ tbwallet_gen [ Tibero Security Wallet Generator ] Enter wallet file name: TBWALLET Enter wallet password: tibero generate wallet success
Specifying a location for a security wallet
To specify the security wallet's location, add the
initialization parameter WALLET_FILE in the file
$TB_SID.tip.
[Example 11.2] Specifying a Location for a Security Wallet: <$TB_SID.tip>
WALLET_FILE=/path/to/TBWALLET
Using a security wallet
To open the created security wallet and use it, a user who has DBA authority needs to execute the following command. The password followed by IDENTIFIED BY must be identical to the password entered when the security wallet is created.
[Example 11.3] Opening a Security Wallet
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY 'tibero';
System altered.
To close the security wallet and not use data encryption, a user who has the DBA authority needs to execute the following command:
[Example 11.4] Closing a Security Wallet
SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;
System altered.
A security wallet is closed when a database instance is terminated, so it should be reopened whenever an instance starts. Each node in the TAC must have the same security wallet.
Column encryption encrypts data from a specific column in a table and saves the data. It is specified when a DDL statement that creates or modifies a table is executed.
When column encryption is specified, the encryption algorithm and the use of SALT can be specified.
The encryption algorithm can be specified within the range that Tibero supports. By default, AES192 is used. Several encrypted columns can exist in a single table, but only one algorithm can be used for a table.
The SALT option causes identical data to have a different encryption result. Using this option improves security. However, an index cannot be created in a column that is encrypted with the SALT option. SALT is used by default. In addition, performance is lowered for DML and SELECT statements because the database is internally performing encryption and decryption.
The column data types that can be encrypted are as follows:
CHAR
VARCHAR2
NUMBER
DATE
TIMESTAMP
INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
RAW
NCHAR
NVARCHAR2
When a column that will be encrypted is defined in the CREATE TABLE statement, the table must be created with the ENCRYPT clause.
[Example 11.5] Creating a Table With Encrypted Columns with Default Encryption Options (AES192, SALT)
SQL> CREATE TABLE customer (
cust_id CHAR(4) CONSTRAINT cust_id_pk PRIMARY KEY NOT NULL,
cust_name VARCHAR(20) NULL,
cust_type VARCHAR(18) NULL,
cust_addr VARCHAR(40) NULL,
cust_tel VARCHAR(15) ENCRYPT NULL,
reg_date DATE NULL
);
Table 'CUSTOMER' created.
The encryption algorithm can be specified with the USING clause and the SALT option can also be specified. If a different algorithm is specified for another column, an error will occur, because only one encryption algorithm can be specified for a table.
[Example 11.6] Creating a Table With Encrypted Columns with AES256 and No SALT
SQL> CREATE TABLE customer (
cust_id CHAR(4) CONSTRAINT cust_id_pk PRIMARY KEY NOT NULL,
cust_name VARCHAR(20) NULL,
cust_type VARCHAR(18) NULL,
cust_addr VARCHAR(40) ENCRYPT USING 'AES256' NULL,
cust_tel VARCHAR(15) ENCRYPT NO SALT NULL,
reg_date DATE NULL
);
Table 'CUSTOMER' created.
If the ENCRYPT clause is specified when a column is added in the ALTER TABLE statement, the column is encrypted. If an encrypted column already exists in the table, the same encryption algorithm is used. However, if there are no encrypted columns, a new algorithm can be specified. The SALT option can also be specified.
[Example 11.7] Adding an Encrypted Column
SQL> ALTER TABLE customer ADD (cust_password VARCHAR(12) ENCRYPT NO SALT);
Table 'CUSTOMER' altered.
If the ENCRYPT clause is specified when a column is changed in the ALTER TABLE statement, the column is encrypted. If an encrypted column already exists in the table, the same encryption algorithm is used, like adding an encrypted column to a table. However, if there are no encrypted columns, a new algorithm can be specified. The SALT option can also be specified.
[Example 11.8] Changing a General Column to an Encrypted Column
SQL> ALTER TABLE customer MODIFY (reg_date ENCRYPT NO SALT);
Table 'CUSTOMER' altered.
If the DECRYPT clause is specified when a column is changed in the ALTER TABLE statement, the column becomes a general column.
[Example 11.9] Changing an Encrypted Column to a General Column
SQL> ALTER TABLE customer MODIFY (reg_date DECRYPT);
Table 'CUSTOMER' altered.
If the REKEY clause is specified in the ALTER TABLE statement, the encryption algorithm of all encrypted columns in the corresponding table is changed.
[Example 11.10] Changing an Encryption Algorithm for All Encrypted Columns
SQL> ALTER TABLE customer REKEY USING '3DES168';
Table 'CUSTOMER' altered.
An index can be created on an encrypted column with the following restrictions.
The SALT option cannot be specified on the encrypted column, and only the EQUAL('=') condition can be used with the index to perform an INDEX RANGE SCAN.
[Example 11.11] Index on Encrypted Column
SQL> CREATE TABLE customer (
cust_id CHAR(4) CONSTRAINT cust_id_pk PRIMARY KEY NOT NULL,
cust_name VARCHAR(20) NULL,
cust_type VARCHAR(18) NULL,
cust_addr VARCHAR(40) ENCRYPT USING 'AES256' NULL,
cust_tel VARCHAR(15) ENCRYPT NO SALT NULL,
reg_date DATE NULL
);
Table 'CUSTOMER' created.
SQL> CREATE INDEX XI_CUSTOMER ON CUSTOMER (CUST_ADDR);
TBR-7288: Unable to encrypt index key column(s) with SALT.
SQL> CREATE INDEX XI_CUSTOMER ON CUSTOMER (CUST_TEL);
Index 'XI_CUSTOMER' created.
SQL> SELECT COUNT (*) FROM CUSTOMER WHERE CUST_TEL = '010-2233-9999';
Execution Plan
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:1, %%CPU:0, Rows:1)
2 SORT AGGR (Cost:1, %%CPU:0, Rows:1)
3 INDEX (RANGE SCAN): XI_CUSTOMER (Cost:1, %%CPU:0, Rows:1)
SQL> SELECT COUNT (*) FROM CUSTOMER WHERE CUST_TEL <= '010-2233-XXXX';
Execution Plan
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:1, %%CPU:0, Rows:1)
2 SORT AGGR (Cost:1, %%CPU:0, Rows:1)
3 FILTER (Cost:1, %%CPU:0, Rows:10)
4 INDEX (FULL): XI_CUSTOMER (Cost:1, %%CPU:0, Rows:100)
Tablespace encryption is another way to protect data in a database in the same way as column encryption. The difference is that the entire tablespace is encrypted instead of only a table or column.
Tablespace encryption and decryption are performed immediately when reading from and writing to a disk. Because SQL in a database is not is handled any differently, this allows all column types to be encrypted.
Because all data blocks of the tablespace are encrypted and decrypted, performance can be reduced when the tablespace is large or the tablespace is accessed or changed often. Encryption cannot be selectively turned on or off as it can be with column encryption. Therefore, select an appropriate encryption method based on the data.
If the ENCRYPT clause is added to a CREATE TABLESPACE statement, the tablespace is encrypted. The encryption algorithm can be specified with the USING clause. The following can be used: 3DES168, AES128, AES192, and AES256. The default value is AES128.
[Example 11.12] Creating an Encrypted Tablespace with the 3DES168 Algorithm
SQL> CREATE TABLESPACE encrypted_space
DATAFILE '/usr/tibero/data/encrypted001.dtf' SIZE 50M
AUTOEXTEND ON NEXT 1M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
ENCRYPTION USING '3DES168'
DEFAULT STORAGE (ENCRYPT);
Tablespace 'ENCRYPTED_SPACE' created.
Note the following points when creating an encrypted tablespace:
Specifying an encryption algorithm
There are fewer encryption algorithms for tablespaces than there are for columns. The following encryption algorithms can be selected to create an encrypted tablespace: 3DES168, AES128, AES192, and AES256.
Opening the security wallet
The security wallet must be open before an encrypted tablespace is created. If the CREATE TABLESPACE statement is executed while the security wallet is not open, the following error will occur and the tablespace and data file are not created. To resolve this issue, open the security wallet and create the tablespace again.
[Example 11.13] Encrypted Tablespace - Creation Failure
SQL> CREATE TABLESPACE encrypted_space
DATAFILE '/usr/tibero/data/encrypted001.dtf' SIZE 50M
AUTOEXTEND ON NEXT 1M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
ENCRYPTION USING '3DES168'
DEFAULT STORAGE (ENCRYPT);
TBR-12073: wallet not opened.
When there is insufficient space for an encrypted tablespace, a new data file can be added to the tablespace using the ADD DATAFILE clause in the ALTER TABLESPACE statement. The new data file has the same password and encryption algorithm as was specified when the tablespace was created.
[Example 11.14] Encrypted Tablespace - Adding a Data File
SQL> ALTER TABLESPACE encrypted_space
ADD DATAFILE '/usr/tibero/data/encrypted002.dtf' SIZE 50M;
Tablespace 'ENCRYPTED_SPACE' altered.
It is not possible to change an unencrypted tablespace to an encrypted tablespace or an encrypted tablespace to an unencrypted tablespace. It is also not possible to encrypt only part of the data or to use different algorithms in a single tablespace. Note these things when creating a tablespace.
To change an unencrypted tablespace to an encrypted tablespace, create a new encrypted tablespace and move data from the unencrypted tablespace to the encrypted tablespace.
If an encrypted tablespace is specified when a table or index is created, the corresponding segment is physically saved in the tablespace. Data encryption and decryption is automatically performed when data blocks are read and written.
[Example 11.15] Encrypted Tablespace - Creating a Table
SQL> CREATE TABLE customer (
cust_id CHAR(4) NOT NULL CONSTRAINT cust_id_pk PRIMARY KEY,
cust_name VARCHAR(20) NULL,
cust_type VARCHAR(18) NULL,
cust_addr VARCHAR(40) NULL,
cust_tel VARCHAR(15) NULL,
reg_date DATE NULL
) TABLESPACE secure_space;
Table 'CUSTOMER' created.
The security wallet must be open when a table is created in an encrypted tablespace and when SQL statements are performed on the data. If an operation that includes an encrypted tablespace is performed when the security wallet is not open, the following error will occur: [Example 11.13].
Some tablespaces cannot be encrypted. The SYSTEM, UNDO, and TEMP tablespaces cannot be encrypted. Redo log files also cannot be encrypted. However, UNDO and Redo logs that are created when data that is included in an encrypted tablespace is changed can be encrypted and saved to disk. When the two logs are necessary, they are automatically decrypted and used.
If data that is temporarily saved in the TEMP tablespace is included in an encrypted tablespace during a sort operation, the TEMP area is automatically encrypted and decrypted. Therefore, data from an encrypted tablespace can be protected and not be exposed through other files such as UNDO, Redo, and TEMP.
Tibero provides the following views to manage encrypted tablespace data:
For more information about static and dynamic views, refer to Tibero Reference Guide.
An index creation on an encrypted column is restricted to columns encrypted with NO_SALT option, and INDEX RANGE SCAN cannot be performed on such columns. By using encrypted tablespaces, an index can be created on encrypted columns without such restrictions to ensure data security.
[Example 11.16] Indexing Encrypted Column in Encrypted Tablespaces
SQL> CREATE TABLE customer (
cust_id CHAR(4) CONSTRAINT cust_id_pk PRIMARY KEY NOT NULL,
cust_name VARCHAR(20) NULL,
cust_type VARCHAR(18) NULL,
cust_addr VARCHAR(40) ENCRYPT USING 'AES256' NULL,
cust_tel VARCHAR(15) ENCRYPT NO SALT NULL,
reg_date DATE NULL
);
Table 'CUSTOMER' created.
SQL> CREATE TABLESPACE encrypted_space
DATAFILE '/usr/data/encrypted001.dtf' SIZE 50
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
ENCRYPTION USING '3DES168'
DEFAULT STORAGE (ENCRYPT);
Tablespace 'ENCRYPTED_SPACE' created.
SQL> CREATE INDEX XI_CUSTOMER_01 ON CUSTOMER (CUST_ADDR) ENABLE RANGE;
TBR-7002: Unsupported DDL
SQL> CREATE INDEX XI_CUSTOMER_01 ON CUSTOMER (CUST_ADDR)
TABLESPACE ENCRYPTED_SPACE ENABLE RANGE;
Index 'XI_CUSTOMER_01' created.
SQL> CREATE INDEX XI_CUSTOMER_02 ON CUSTOMER (CUST_TEL)
TABLESPACE ENCRYPTED_SPACE ENABLE RANGE;
Index 'XI_CUSTOMER' created.
SQL> SELECT COUNT (*) FROM CUSTOMER WHERE CUST_TEL <= '010-2233-XXXX';
Execution Plan
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:1, %%CPU:0, Rows:1)
2 SORT AGGR (Cost:1, %%CPU:0, Rows:1)
3 INDEX (RANGE SCAN): XI_CUSTOMER_02 (Cost:1, %%CPU:0, Rows:1)