Best practices for Data Security #

Application Scenarios #

Data Encryption #

Data Encryption is a reliable method for protecting information, which uses cryptographic techniques to encrypt data, achieving information confidentiality and ensuring information security. Data Encryption is suitable for enterprise-level applications that have security requirements for their data assets, such as the following two common business scenarios:

1. New business, requiring encryption of incoming data.

To reduce complexity on the application side and be able to adapt to future changes in encryption requirements, encryption can be achieved using the data layer encryption provided by SphereEx-DBPlusEngine. This way, encryption rules are transparent to the business application, and no modification to the application is needed when encryption requirements change, making it easier to implement flexible system architecture.

2. Existing business, with plaintext data.

Encrypting new data while also completing the encryption of existing data for a system already running in production is a very challenging task. Based on the encryption engine and encryption migration solutions provided by SphereEx-DBPlusEngine, it is possible to achieve a complete, secure, transparent, and low-cost data transformation, quickly bringing existing businesses into compliance with enterprise and regulatory security requirements.

Global Permission #

SphereEx-DBPlusEngine provides various levels of user permission management capabilities, which can be used to set up database-level, table-level, and column-level operation permissions for users, meeting the requirements of fine-grained security management. The global permission function is suitable for the following application scenarios:

1. Need to limit login hosts

Limiting a user’s login host ensures that they can only connect to SphereEx-DBPlusEngine from a trusted address and rejects access from illegal addresses, greatly enhancing the access security of the database system.

2. Need to manage permissions according to users

By setting up database, table, and column operation permissions for each database user separately, fine-grained permission management can be achieved.

3. Manage permissions according to roles

When users can be classified by role, authorizing the role can enable every user under that role to have the same operation permissions. This method achieves fine-grained permission control and simplifies the management process by avoiding repeated authorization operations on multiple users.

It should be noted that the global permission management capability is applicable to SphereEx-DBPlusEngine Proxy, and the Driver does not have this feature.

System Planning #

Deploying a new data encryption and permission management scenario that meets the following requirements:

• Encrypt the id_number field in the t_user user table.
• Create a new user “enc” who can only perform INSERT and SELECT operations on t_user.

Choose encryption algorithm. #

SphereEx-DBPlusEngine supports multiple encryption algorithms, including MD5, AES, RC4, SM3, SM4, etc. Custom algorithms can also be integrated based on business needs.

The following example uses the commonly used AES algorithm to encrypt the id_number field.

Select the Permission Controller #

SphereEx-DBPlusEngine currently provides three security-level permission controllers:

• ALL_PERMITTED: Grants all permissions to all users without any security interception;
• DATABASE_PERMITTED: Restricts user access to the logical database and isolates data at the logical database level;
• SphereEx: PERMITTED: An enterprise-level permission control provided by SphereEx, which can restrict user operations on databases, tables, and columns.

Since this example requires granting specific table permissions to the ’enc’ user, we choose to use SphereEx: PERMITTED.

Impact on Development #

SphereEx-DBPlusEngine can automatically encrypt data during insertion and decrypt data during a query without affecting business development.

Prerequisites #

1. Have ZooKeeper as a governance center;
2. At least one accessible data storage node, such as a MySQL database demo_ds.

Steps #

1. Install and configure the SphereEx-DBPlusEngine Proxy as “cluster mode”;
2. Configure the AUTHORITY rule in server.yaml, set privilege.type to SphereEx: PERMITTED, and set the initial password for the root user;
3. Start the SphereEx-DBPlusEngine Proxy;
4. Log in to the SphereEx-DBPlusEngine Proxy using the root user.

mysql -h ${host} -P ${port} -u root -p

5. Create a logic database encrypt_db;

mysql> CREATE DATABASE encrypt_db;
Query OK, 0 rows affected (0.20 sec)

mysql> USE encrypt_db;
Database changed
mysql>

6. Add the MySQL storage node demo_ds to encrypt_db;

mysql> REGISTER STORAGE UNIT ds_0 (
    -> HOST="${demo_ds_host}",
    -> PORT=${demo_ds_port},
    -> DB="demo_ds",
    -> USER="${demo_ds_user}",
    -> PASSWORD="${demo_ds_password}"
    -> );
Query OK, 0 rows affected (0.10 sec)

7. Create an encryption rule for the business table t_user to encrypt the id_number field;

mysql> CREATE ENCRYPT RULE t_user (
    -> COLUMNS(
    -> (NAME=id_number,CIPHER=id_number,TYPE(NAME="AES",PROPERTIES("aes-key-value"="123456abc")))
    -> ), QUERY_WITH_CIPHER_COLUMN=false);
Query OK, 0 rows affected (0.46 sec)

8. Creat table t_user；

mysql> CREATE TABLE `t_user` (
    ->     `id` int(11) NOT NULL,
    ->     `name` varchar(32) NOT NULL,
    ->     `id_number` varchar(128) NOT NULL,
    ->     PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.21 sec)

9. Create user ’enc’, and grant INSERT and SELECT permissions to the t_user table;

mysql> CREATE DIST USER 'enc'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.15 sec)

mysql> GRANT DIST SELECT,INSERT ON encrypt_db.t_user TO 'enc'@'%';
Query OK, 0 rows affected (0.02 sec)

10. Deployment is completed, log in with the ’enc’ user for verification:

-- Connect
mysql -h ${host} -P ${port} -u enc -p
-- USE db
mysql> USE encrypt_db;
Database changed
-- INSERT
mysql> INSERT INTO t_user values(1,'张三','110111************');
Query OK, 1 row affected (0.08 sec)
-- SELECT
mysql> SELECT * FROM t_user;
+----+-------+--------------------------+
| id | name  | id_number                |
+----+-------+--------------------------+
|  1 | name1 | 2H385oL4Y16CB5ec41FaHQ== |
+----+-------+--------------------------+
1 row in set (0.00 sec)
-- UPDATE
mysql> UPDATE t_user set name = '张三丰' WHERE id = 1;
ERROR 1997 (C1997): Runtime exception: [SQL checking failed. Error message: Access denied for operation [UPDATE] of subject encrypt_db.t_user:name.]

Configuration Example #

server.yaml

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_ds
      server-lists: localhost:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500

authority:
  users:
    - user: root@%
      password: root
    - user: sharding
      password: sharding
  privilege:
    type: SphereEx:PERMITTED

FAQ #

1. What is the relationship between the global permissions provided by SphereEx-DBPlusEngine and the physical database permissions?

The global permissions provided by SphereEx-DBPlusEngine control access for users who connect to the distributed database at a logical level, restricting their access to logical databases, tables, and columns.

The physical database permissions limit the actions that users can perform on the physical connections to the database, and some object names like table and column names are rewritten by DBPlusEngine, which may differ from their names in business SQL (such as shard table names and encrypted column names).

Additionally, because DBPlusEngine is a distributed database, it may connect to multiple physical databases, each with its own users and permissions. However, DBPlusEngine provides unified authentication and permission verification for users to achieve a better user experience.

2. What kind of authorization should be provided by the physical database?

Users need to operate business data through DBPlusEngine, so when adding storage resources, a connecting user with sufficient permissions should be used.

3. How is the permission configuration of DBPlusEngine backed up?

The user and authorization information of DBPlusEngine is stored in the governance center and can be backed up and restored through the persistence method of the governance center. For future works, DBPlusEngine will also provide DistSQL statements for targeted export and import, making it more convenient for users to operate.