Example of Using KlustronDB Table File Encryption Feature

Note:

Unless otherwise specified, the version numbers in the text can be replaced with the version numbers of any released version. All released versions can be found at: Release_notes.

Overview

The main content is first to introduce the characteristics of database table file encryption, explain how to use the configuration steps for table file encryption, how to install the encryption plugin in KlustronDB, then test the use of table file encryption with test cases, compare encrypted and non-encrypted table files at the database level, and finally directly view and compare the data content of encrypted and non-encrypted table data files at the operating system level.

01 KlustronDB Introduction to Table File Encryption

The storage engine in KlustronDB provides table-level encryption options, which can protect table data through file encryption. The following are the methods and features of table file encryption:

1.1 Table File Encryption:

Table file encryption is a method of encrypting the data files of specific tables. It allows you to enable data file-level encryption for one or more tables, thereby protecting the data stored on the disk.

1.2 Features:

a) Transparency: It is transparent to the application. The application can interact with the encrypted tables without any modifications.

b) Table-level encryption: The encryption operation is performed at the data file level of the table, and it only affects the selected tables for encryption, not the entire database.

c) Performance Impact: Encryption and decryption operations can affect performance because they require additional computational resources. The specific performance impact will be influenced by hardware performance, encryption algorithms, and workload.

d) Key Management: Table file encryption uses a master key to perform encryption and decryption operations. You can use the key manager built into the KlustronDB storage engine to generate and manage the master key.

1.3 How to use table files for encryption:

The steps for actually using table files for encryption are roughly as follows:

a) Configure KlustronDB to use the InnoDB storage engine.

b) Generate or import the master key for encryption and decryption operations. This can be done through KlustronDB's built-in key manager.

c) Create an encrypted table and store the table data in it. This operation is usually performed automatically when the table is created.

In summary, table file encryption is a way to protect table-level data security in KlustronDB. It provides a method to encrypt specific tables at the storage file level.

02 KlustronDB Install Encryption Plugin

2.1 Install the encryption plugin on the storage node and create a directory '/kunlun/mysql-keyring/' to store the encryption keyring files

[root@kunlun2 ~]# su - kunlun


[kunlun@kunlun2 ~]$ mkdir /kunlun/mysql-keyring/

2.2 Modify all storage node configuration files, and add the following content in the configuration files:

[kunlun@kunlun2 ~]$ vi /kunlun/storage_datadir/57003/data/57003.cnf

early-plugin-load=keyring_file.so
keyring_file_data=/kunlun/mysql-keyring/keyring

2.3 All storage node configuration files need to be modified, for example, here it was also modified

[kunlun@kunlun2 ~]$ vi /kunlun/storage_datadir/57005/data/57005.cnf

[kunlun@kunlun2 ~]$ vi /kunlun/storage_datadir/57007/data/57007.cnf

2.4 Restart the storage node after modifying the configuration file

[kunlun@kunlun2 ~]$ cd /kunlun/instance_binaries/storage/57003/Kunlun-storage-1.2.1/dba_tools

[kunlun@kunlun2 ~]$ ./stopmysql.sh 57003

[kunlun@kunlun2 ~]$ ./startmysql.sh 57003

[kunlun@kunlun2 ~]$ cd /kunlun/instance_binaries/storage/57005/Kunlun-storage-1.2.1/dba_tools

[kunlun@kunlun2 ~]$ ./stopmysql.sh 57005

[kunlun@kunlun2 ~]$ ./startmysql.sh 57005

[kunlun@kunlun2 ~]$ cd /kunlun/instance_binaries/storage/57007/Kunlun-storage-1.2.1/dba_tools

[kunlun@kunlun2 ~]$ ./stopmysql.sh 57007

[kunlun@kunlun2 ~]$ ./startmysql.sh 57007

2.5 Verify successful installation of the encryption plugin after restarting the storage node

[kunlun@kunlun2 ~]$ mysql -h 192.168.56.113 -P57003 -upgx -ppgx_pwd

mysql> select * from information_Schema.plugins where plugin_name like '%keyring_file%'\G

mysql> show global variables like '%keyring%';

2.6 Check that the keyring file has been generated

[kunlun@kunlun2 ~]$ ls -l /kunlun/mysql-keyring

Complete the installation of the encryption plugin.

03 Case Study of Table File Encryption

This is a test case based on table file encryption. Create a business table named product, which is not encrypted, and create a business table named product_encryp, which is encrypted; then insert equivalent data into both the non-encrypted product table and the encrypted product_encryp table, check the encryption attributes of these two tables, and compare the data in both types of tables.

3.1 Connect to the database, create a database user, create a test database, and grant permissions to the user.

[root@kunlun1 ~]# su - kunlun
[kunlun@kunlun1 ~]$ psql -h 192.168.56.112 -p 47001 postgres

create user kunlun_user with password 'kunlun';

create database testdb_encypt;

grant all privileges on database testdb_encypt to kunlun_user;

\c testdb_encypt kunlun_user

3.2 Create the non-encrypted table 'product' and the encrypted table 'product_encryp', and insert data into the tables.

Create a non-encrypted table 'product' and insert data:

CREATE TABLE product(
  pro_id int8,
  pro_name varchar(100),
  pro_type varchar(100),
  price int8
  );

insert into product (pro_id,pro_name,pro_type,price) values (10001,'ipad','padnote',4500);
insert into product (pro_id,pro_name,pro_type,price) values (10002,'ipad8','padnote',6000);
insert into product (pro_id,pro_name,pro_type,price) values (10003,'ipone','phone',8000);
insert into product (pro_id,pro_name,pro_type,price) values (10004,'ipone14','phone',8800);
insert into product (pro_id,pro_name,pro_type,price) values (10005,'notebook','computer',10000);
insert into product (pro_id,pro_name,pro_type,price) values (10006,'notebook2','computer',12000);

Create the encryption table product_encryp and insert data:

CREATE TABLE product_encryp(
  pro_id int8,
  pro_name varchar(100),
  pro_type varchar(100),
  price int8
  ) with (
  engine=innodb,
  compression=lz4,
  row_format= COMPACT,
  ENCRYPTION = 'Y');

insert into product_encryp (pro_id,pro_name,pro_type,price) values (10001,'ipad','padnote',4500);
insert into product_encryp (pro_id,pro_name,pro_type,price) values (10002,'ipad8','padnote',6000);
insert into product_encryp (pro_id,pro_name,pro_type,price) values (10003,'ipone','phone',8000);
insert into product_encryp (pro_id,pro_name,pro_type,price) values (10004,'ipone14','phone',8800);
insert into product_encryp (pro_id,pro_name,pro_type,price) values (10005,'notebook','computer',10000);
insert into product_encryp (pro_id,pro_name,pro_type,price) values (10006,'notebook2','computer',12000);

3.3 Check the database data dictionary, compare the encryption attributes of the non-encrypted table 'product' and the encrypted table 'product_encryp', and see from the attributes that the 'product_encryp' table has been encrypted.

[root@kunlun2 ~]# su – Kunlun

[kunlun@kunlun2 ~]$ mysql -h 192.168.56.113 -P57005 -upgx -ppgx_pwd

SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM information_schema.TABLES WHERE TABLE_NAME LIKE '%PROD%';

SELECT SPACE, NAME, SPACE_TYPE, ENCRYPTION FROM information_schema.INNODB_TABLESPACES WHERE NAME LIKE '%PROD%';

3.4 Directly view the data of the unencrypted table product and the encrypted table product_encryp from the client, and compare the data.

From the query results, it can be seen that the encryption table is transparent to the application's front-end queries. By directly using the table encryption feature, the application does not need to make any modifications or changes.

04 Directly Viewing Encrypted Table Files in the Operating System

After the table files are encrypted, the security of the data is well ensured. The table files are encrypted at the operating system level, and viewing the files from the operating system level shows only ciphertext. Even if the files are stolen or copied to another location, the content of the files is all ciphertext, ensuring the security of the data; here we compare the data content of unencrypted table files and encrypted table files directly from the operating system level.

4.1 From the operating system level, directly viewing the system file of the unencrypted table 'product' allows one to directly see the plaintext information of the data stored in the file.

[root@kunlun2 ~]# su - kunlun

[kunlun@kunlun2 ~]$ cd /kunlun/storage_datadir/57005/data/testdb_encypt_@0024@0024_public

[kunlun@kunlun2 testdb_encypt_@0024@0024_public]$ ls
product_encryp.ibd  product.ibd

[kunlun@kunlun2 testdb_encypt_@0024@0024_public]$ hexdump -C product.ibd

4.2 Directly viewing the system files of the encrypted table product_encryp from the operating system level shows that the data stored in the files is already encrypted, and it is impossible to obtain the data information from the files.

[root@kunlun2 ~]# su - kunlun

[kunlun@kunlun2 ~]$ cd /kunlun/storage_datadir/57005/data/testdb_encypt_@0024@0024_public

[kunlun@kunlun2 testdb_encypt_@0024@0024_public]$ ls
product_encryp.ibd  product.ibd

[kunlun@kunlun2 testdb_encypt_@0024@0024_public]$ hexdump -C product_encryp.ibd

The encrypted product_encryp table file only shows ciphertext, and the plaintext data of the file cannot be viewed.

Complete the test case based on database table file encryption.

END