Security Management #

User Management #

SphereEx-DBPlusEngine provides multiple users for different platforms and scenarios.

Role Management #

SphereEx-DBPlusEngine provides multiple roles for use in different platforms and scenarios.

Authentication Login #

The following instructions refer specifically to SphereEx-DBPlusEngine users.

Basic Concept #

SphereEx-DBPlusEngine has a strict login authentication mechanism, allowing only authenticated users to establish successful connections. To secure user data and distributed configuration information, SphereEx-DBPlusEngine provides user authentication, which cannot be turned off. Otherwise, unauthenticated client connections will be rejected.

«««< HEAD Currently, SphereEx-DBPlusEngine supports multiple user authentication protocols, including MySQL Client Side (mysql_native_password, mysql_clear_password), PostgreSQL Client Side (MD5, password), and openGauss Client Side (scram-sha-256,MD5). Additionally, to facilitate unified identity management for enterprise users, SphereEx-DBPlusEngine provides LDAP (Lightweight Directory Access Protocol) authentication. LDAP authentication is already supported for MySQL and PostgreSQL clients. #

Currently, SphereEx-DBPlusEngine supports multiple user authentication protocols, including

• MySQL Client Side (mysql_native_password, mysql_clear_password),

• PostgreSQL Client Side (MD5, password),

• openGauss Client Side (scram-sha-256).

Additionally, to facilitate unified identity management for enterprise users, SphereEx-DBPlusEngine provides LDAP (Lightweight Directory Access Protocol) authentication. LDAP authentication is already supported for MySQL and PostgreSQL clients.

cb3ec38120a6dc2f9b44d334cb5437be1a063ebb

When using SphereEx-DBPlusEngine, users don’t need to worry about choosing the protocol as the protocol negotiation process is done automatically between SphereEx-DBPlusEngine and the client. By default, when using MySQL clients, mysql_native_password is used, and SphereEx-DBPlusEngine will only ask the client to switch to mysql_clear_password protocol for communication when the user needs LDAP authentication. Similarly, when using the PostgreSQL client, MD5 is used by default, and SphereEx-DBPlusEngine will only ask the client to switch to the password protocol for communication when the user needs LDAP authentication.

Password Authentication #

SphereEx-DBPlusEngine uses password authentication by default, requiring logged-in users to provide the correct username and password.

As Proxy supports multiple database protocols (such as MySQL, PostgreSQL, etc.), it can automatically adapt the password communication protocol to provide a consistent security experience for users in complex scenarios where users apply different database clients.

Host Restrictions #

In addition to providing password-authenticated login, you can also restrict the login host address for SphereEx-DBPlusEngine users to increase the security level. For example:

‑ !AUTHORITY
users:
  ‑ user: root@127.0.0.1
    password: root

The above configuration specifies that the root user can only access the Proxy from the address 127.0.0.1. Login from other addresses will be denied even if the password is correct.

LDAP Authentication #

To facilitate unified authentication management for enterprise users, SphereEx-DBPlusEngine also provides LDAP (Lightweight Directory Access Protocol) authentication method, and LDAP authentication is now supported by MySQL and PostgreSQL clients. At the same time, SphereEx-DBPlusEngine allows users to access LDAP in a very flexible way, for example:

• The possibility to configure to use LDAP by default, so that all users are authenticated via LDAP.
• Support for configuring the auth attribute for users, specifying that they use password or LDAP authentication, with individual users being able to use different methods.
• Each user can use a different LDAP authenticator, i.e., dock to a different LDAP service.
• Support for specifying DN templates for users to meet the needs of complex scenarios.
• Support LDAPS protocol, which can further enhance the security level.

Permission Management #

Definition #

SphereEx-DBPlusEngine provides distributed collaboration capabilities for databases, while abstracting some of the database features to the upper layer for unified management. Permission control is one such capability. Leaving permission control to SphereEx-DBPlusEngine for unified management has several benefits:

• Users can access heterogeneous resources without worrying about which dialect to use for management, avoiding confusion.
• Logical databases and logical tables are used for authorization management, isolated from the real library tables in the lower layer, making it easier for users to understand.
• Avoid inconsistent authorization information caused by changes in database resources, and no consumption of information synchronization.
• A new permission control system has been created by the SphereEx-DBPlusEngine team to make permission control easier to use.

Concept #

• Users

A user is a user of SphereEx-DBPlusEngine.

• Initial Users

The initial user is the user that is set through the configuration file before SphereEx-DBPlusEngine starts.

• General Users

As opposed to the initial user, the normal user is a user that is created dynamically during the SphereEx-DBPlusEngine runtime.

• Roles

A role is a collection of named and a certain number of permissions. Role-based privilege control simplifies the process of managing privileges for users.

• Privilege

Privilege is the authority of a user to perform an action on a specific target.

• DistSQL

DistSQL (Distributed SQL) is a DBPlusEngine-specific manipulation language that provides a proprietary DistSQL syntax after DBPlusEngine has abstracted and unified the privilege control capabilities to facilitate administrator management and maintenance of users and privileges.

• DML

Data manipulation language, including INSERT, SELECT, UPDATE, and DELETE statements.

• DDL

Data definition language, including CREATE, ALTER, DROP, and TRUNCATE statements.

Impact on the system #

• Fine-grained permission management

Precise control over the privileges granted to each user at the library level, table level, and column level.

• Unified Interaction Language

Use SphereEx-DBPlusEngine’s proprietary DistSQL for user and permission management, allowing indiscriminate permission control regardless of whether the storage node is MySQL, PostgreSQL, OpenGauss or Oracle.

• Real-time permission control

Changes to users or permissions take effect in real time without the need to restart SphereEx-DBPlusEngine.

• Automatic synchronization of authorization information in the cluster

When user and authorization information is changed, other compute nodes in the cluster can receive the change in real time and complete user authorization updates, so administrators don’t need to repeat the operation in multiple nodes, which is convenient for cluster management.

Principle #

• Permissions Storage

In the architecture of SphereEx-DBPlusEngine, the compute node (DBPlusEngine-Proxy) is stateless and does not provide data storage capability, so user accounts and authorization information will be stored in the Governance Center. At the same time, with the ability of Governance Center, it is able to distribute information to multiple compute nodes in the cluster in real time, which will greatly reduce the maintenance cost of users when using the cluster and provide management efficiency. On the other hand, due to the unified permission management mechanism provided, SphereEx-DBPlusEngine no longer forwards the received native DCL statements to the lower storage nodes, and will give an indication that they are not supported. Users are required to use DistSQL provided by SphereEx-DBPlusEngine for account and authorization management.

• Permissions Provider

SphereEx-DBPlusEngine uses pluggable architecture for the organization and extension of functionality. Among other things, the permission engine provides users with a variety of different permission providers

Description: Permission providers are specified by the administrator before SphereEx-DBPlusEngine is started.

• Authentication Process

In SphereEx-DBPlusEngine, permissions are verified in top-down order. When a user has upper-level permissions, no further checks are performed downward to ensure authentication efficiency. Example.

• If a user has global SELECT privileges, there is no need to check whether the user has SELECT authorization for the target library table when performing SELECT operations.
• If a user has INSERT privileges at the library level, then when performing an INSERT operation, it is not necessary to check whether the user has INSERT authorization for the target table. And so on.

e.g.

• If a user has global SELECT privileges, it is not necessary to check whether the user has SELECT authorization for the target library table when performing a SELECT operation.
• If a user has INSERT privileges at the library level, then when performing an INSERT operation, it is not necessary to check whether the user has INSERT authorization for the target table and so on.

Permission Grading #

Global Permission #

Global permission means that the authorization obtained by the user does not distinguish the target object, and the user can perform corresponding operations on any logical library or logical table. For example, the following command gives the global INSERT, SELECT, UPDATE and DELETE privileges to user ‘sharding’@’%’, which enables the user to perform DML operations on any table in any logical library. The following two statements are equivalent.

GRANT DIST INSERT,SELECT,UPDATE,DELETE TO 'sharding'@'%';
GRANT DIST INSERT,SELECT,UPDATE,DELETE TO sharding;

Note that the global privileges include two special privileges: CREATE_USER and SUPER.

In particular, users who have been granted the CREATE_USER authorization can perform the following actions：

SUPER represents the highest privilege of the database system, and by default, the initial user configured before SphereEx-DBPlusEngine starts has the SUPER authorization.

Object privileges #

Object privilege means that the privilege granted is restricted in scope and the user cannot perform the corresponding operation outside the scope of the authorization.

The scope of object privileges can be all logical libraries, or it can specify single or multiple logical libraries and the tables and columns in the libraries.

For example, the following command grants all permissions on the t_order table in the logical database sharding_db to the user 'sharding'@'%', after which the user can perform operations on the sharding_db.t_order table. However, this user can not operate on other tables in sharding_db without additional authorization.

sql> GRANT DIST ALL ON sharding_db.t_order TO sharding;

DistSQL Checklist (Permissions Related) #

The complete list of DistSQL for Permissions Management is as follows.

Instructions:

• The syntax for “authorize/revoke authorization” for users and roles is the same, just replace ‘user’@‘host’ with role.
• REVOKE DIST ALL will revoke all the roles given to the user at the same time.

Use Case: Permission Configuration Initialization #

• Applicable scenarios

The permission engine performs system initialization based on the permission rules configured in server.yaml.

• Data Planning
  • users is used to specify the initial user, e.g. here root@% is set as the initial user.
  • The type in privilege is used to specify the selected service provider, such as SPHERE_EX_PRIVILEGES_PERMITTED, which is assigned as the enterprise privilege provider.
• Cautions
  • The initial user has SUPER privileges by default.
  • If non- SUPER privileges are granted to the initial user display via DistSQL, the initial user will lose their SUPER privileges.
  • To grant the SUPER authorization again, the GRANT DIST SUPER TO user statement is required.
• Operation Steps

The configuration format is as follows.

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

Use Case: Not Using Role Management #

• Application Scenario

An application provides different levels of SphereEx-DBPlusEngine accounts for development and operations staff, where developers can only execute DML commands and operations staff can execute DML + DDL commands, with a root user as the top administrator.

• Data Planning

The full account requirements are as follows.

The root user is the initial user.

• Operation steps

1. Create each development and operation and maintenance user in turn, and set the password according to the actual situation.

– No restriction on login host, omit host configuration

sql> CREATE DIST USER zhangsan IDENTIFIED BY '123456';
sql> CREATE DIST USER wangwu IDENTIFIED BY '123456';
sql> CREATE DIST USER develop_test IDENTIFIED BY '123456';
sql> CREATE DIST USER operator_1 IDENTIFIED BY '123456';
sql> CREATE DIST USER operator_2 IDENTIFIED BY '123456';

2. Authorization for development users.

sql> GRANT DIST INSERT,SELECT,UPDATE,DELETE TO zhangsan;
sql> GRANT DIST INSERT,SELECT,UPDATE,DELETE TO wangwu;
sql> GRANT DIST INSERT,SELECT,UPDATE,DELETE TO develop_test;

3. Authorization for O&M users.

sql> GRANT DIST INSERT,SELECT,UPDATE,DELETE,CREATE,ALTER,DROP,TRUNCATE TO operator_1;
sql> GRANT DIST INSERT,SELECT,UPDATE,DELETE,CREATE,ALTER,DROP,TRUNCATE TO operator_2;

4. If required to add a new development user, repeat the following two steps.

• New User

sql> CREATE DIST USER new_developer IDENTIFIED BY '123456';

• Authorization

sql> GRANT DIST INSERT,SELECT,UPDATE,DELETE TO new_developer;

5. If required to add a new O&M user, repeat the following two steps

• New users

sql> CREATE DIST USER new_operator IDENTIFIED BY '123456';

• Authorization

sql> GRANT DIST INSERT,SELECT,UPDATE,DELETE,CREATE,ALTER,DROP,TRUNCATE TO new_operator;

Use Case: Using Role Management #

• Application Scenario

An application provides different levels of SphereEx-DBPlusEngine accounts for development and operations staff, where developers can only execute DML commands and operations staff can execute DML + DDL commands, with a root user as the top administrator.

• Data Planning

The full account requirements are as follows.

• Operation steps

1. Create each development and operation and maintenance user in turn, and set the password according to the actual situation.

Do not limit the login host, omit the host configuration

sql> CREATE DIST USER zhangsan IDENTIFIED BY '123456';
sql> CREATE DIST USER wangwu IDENTIFIED BY '123456';
sql> CREATE DIST USER develop_test IDENTIFIED BY '123456';
sql> CREATE DIST USER operator_1 IDENTIFIED BY '123456';
sql> CREATE DIST USER operator_2 IDENTIFIED BY '123456';

2. Create two roles, develop_dml and operate_ddl.

sql> CREATE DIST ROLE develop_dml;
sql> CREATE DIST ROLE operate_ddl;

3. Authorization for roles.

sql> GRANT DIST INSERT,SELECT,UPDATE,DELETE TO develop_dml;
sql> GRANT DIST INSERT,SELECT,UPDATE,DELETE,CREATE,ALTER,DROP,TRUNCATE TO operate_ddl;

4. Assign the development role to the user so that the user has the privileges that the role has.

sql> GRANT DIST develop_dml TO zhangsan;
sql> GRANT DIST develop_dml TO wangwu;
sql> GRANT DIST develop_dml TO develop_test;

5. Assign users to O&M roles.

sql> GRANT DIST operate_ddl TO operator_1;
sql> GRANT DIST operate_ddl TO operator_2;

6. If required to add a new development user, repeat the following two steps.

– New User

sql> CREATE DIST USER new_developer IDENTIFIED BY '123456';

‑‑ Authorization

sql> GRANT DIST develop_dml TO new_developer;

7. If required to add a new O&M user, repeat the following two steps.

‑‑ New User

sql> CREATE DIST USER new_operator IDENTIFIED BY '123456';

‑‑ Authorization

sql> GRANT DIST operate_ddl TO new_operator;

DistSQL Authority Control #

DistSQL, as an extended SQL supported by SphereEx-DBPlusEngine, SphereEx-DBPlusEngine also provides the corresponding permission control.

Authority Level #

DistSQL privilege hierarchy is similar to the standard database privilege, which also has global privilege, library-level object privilege, and table-level object privilege. DistSQL permissions are more diverse than the standard database permissions, and DistSQL permissions consist of operation + operation object, which are combined as follows.

In addition to the licenses shown in the table above, RDL, RQL, RAL and RUL syntax types are also available as licenses.

Global Privilege #

The global authority means that the authorization obtained by the user does not distinguish the target object, and the user can execute the corresponding operation on any logical library or logical table.

For example, the following command gives the global SHOW SHARDING privilege to user 'sharding'@'%', then the user can perform SHOW operations on any sharding rules in any logical library.

sql> GRANT DIST SHOW SHARDING TO 'sharding'@'%';

Also, because DistSQL has a large number of authorization items, for granting multiple authorization items at once, you can use syntax types as authorization items. For example, if the following command grants global SHOW privileges for all operation objects to user 'sharding'@'%', the user will be able to perform SHOW operations on any operation object in any logical database.

sql> GRANT DIST RQL SHARDING TO 'sharding'@'%';

Object privileges #

Object privilege means that the privilege granted is limited by the scope of action and the user can not perform the corresponding operation outside the scope of the authorization. The scope of object privileges can be all logical databases, or it can specify single or multiple logical libraries and the tables in the libraries. For example, the following command grants create and modify permissions for the sharding rule t_order in the logical library sharding_db to user ‘sharding’@’%’, after which the user can perform create and modify operations on the sharding_db.t _order rule. However, this user can not manipulate other rules in sharding_db without additional authorization.

sql> GRANT DIST CREATE SHARDING, ALTER SHARDING ON sharding_db.t_order TO 'sharding'@'%';

Managing Security #

In SphereEx-DBPlusEngine, users are able to perform a variety of dimensional management operations through DistSQL, including but not limited to

• Proxy configuration management, such as transaction types, log switches, etc.
• Logical library management.
• Storage resource management.
• Data partitioning rule management.
• Read-write separation rule management.
• Encryption and decryption rule management.
• Database discovery rule management.
• Shadow library rule management.
• Metadata viewing, etc.

Due to the powerful features of DistSQL, database administrators can assign different DistSQL permissions to different users for SphereEx-DBPlusEngine management security. e.g.

sql> GRANT DIST SHOW SHARDING ON sharding_db.* TO 'sharding'@'%';

The above authorization statement grants ‘sharding@%’ permission to “view sharding rules” in the logical library sharding_db, so the user can execute SHOW SHARDING TABLE RULES, SHOW SHARDING BINDING TABLE RULES and other SHARDING related RQLs in sharding_db, but can not execute other unauthorized DistSQL.

For example, if user ‘sharding@%’ executes CREATE SHARDING TABLE RULE statement at this time, he will get an exception.

Access denied for operation [CREATE] of subject sharding_db.table_name:SHARDING.]

To grant all DistSQL privileges to the ‘sharding@%’ user, you can do the following.

sql> GRANT DIST RDL,RQL,RAL ON sharding_db.* TO 'sharding'@'%';

Access Security #

Data access security is one of the essential capabilities of an enterprise-class database, and SphereEx-DBPlusEngine, as the gateway to a distributed database cluster, provides users with comprehensive access control capabilities. Unlike traditional centralized databases or single-protocol databases, SphereEx-DBPlusEngine has the ability to manage multiple types of underlying databases and interface to multi-protocol clients, which presents a number of challenges when access control is performed:

• Different database types have different logical concepts.
• Different database types use different dialects.
• Different databases provide different storage structures.

To provide users with a consistent security experience, SphereEx-DBPlusEngine shields the underlying database differences and provides a unified and easy-to-use security system with the following features:

• Fine-grained privilege management: support for library-level, table-level, and column-level access control.
• Unified interaction language: use DistSQL for user and permission management, applicable to different database protocols.
• Storage independence: information about permissions is stored in the governance center of DBPlusEngine and is not dependent on the underlying database.
• Real-time effect: control over users and permissions takes effect in real time, without the need for restarts or manual refreshes.

For example, if an administrator grants query and write permissions to ‘sharding@%’ for the t_order table in sharding_db, you can execute DistSQL like this.

sql> GRANT DIST SELECT, INSERT ON sharding_db.t_order TO ‘sharding’@'%';

After the operation is completed, the ‘sharding@%’ user is immediately granted the appropriate authorization. If the user performs an unauthorized operation, such as a DELETE, he or she will receive a rejection message:

sql> DELETE FROM sharding_db.t_order WHERE id = 1;
Access denied for operation [DELETE] of subject sharding_db.t_order]

• SSL Description

Currently, SphereEx-DBPlusEngine does not support SSL access. If SSL access is enabled for the back-end database, you can configure the back-end to connect using SSL in SphereEx-DBPlusEngine.

Storage Security #

In recent years, data security and privacy protection have become increasingly important. Data encryption is one of the means to effectively address these issues.

• Disk Encryption

Disk encryption is supported by block-level encryption technologies such as AWS’s EBS, AliCloud’s ECS, and others. The biggest benefit of this encryption is that it is transparent to the operating system. Performance degrades after encryption compared to before encryption, and performance degradation varies depending on the upper layer application.

• File Encryption

Provides transparent, dynamic, efficient and secure encryption for applications by stacking on top of other file systems (e.g. Ext2, Ext3, ReiserFS, JFS, etc.). Typically used to encrypt a specified directory. The concern is that this type of encryption may incur a large performance loss.

• Database Encryption - TDE

Transparent Data Encryption TDE, an encryption technique provided by databases, performs real-time I/O encryption and decryption of data files. TDE does not increase the size of the data file, and developers do not need to change any application. Its corresponding key management is also implemented by an API or component provided by the database and is transparent to the application. In some scenarios where the disk or system is not open to users (such as cloud environment), this approach is more suitable.

• Database encryption - three-party reinforcement

Database encryption is also a way to use the database for three-party reinforcement, that is, the products of third-party professional database encryption vendors built into the database to provide transparent data encryption capabilities. The so-called transparent means that the user application system that does not need to do transformation can be used, and the user with permission to see the plaintext data, completely senseless. In addition, the security capability of the original database can be enhanced, such as providing separation of powers, desensitized display, etc.

• Application layer encryption

Application layer encryption, which can be said to be an ultimate solution, ensure that data encryption has been done before the data reaches the database, and can protect users’ sensitive data in real time. The key need here is to provide application transparency to ensure that the application needs no modification or only a small amount of modification. This approach is fully controlled by the users themselves, without the need to trust any three-party vendor to provide data security, getting full freedom and flexibility. For example, unified security encryption policies can be provided across multiple databases, etc.

The data encryption capability provided by SphereEx-DBPlusEngine belongs to application layer encryption. However, unlike traditional application encryption, SphereEx-DBPlusEngine provides a senseless encryption capability. For front-end users, they only need to consider access to the database and do not need to pay attention to the encryption details. Refer to Data Encryption for details.

Safety in Use #

Desensitized for use #

With the promulgation and implementation of the Cybersecurity Law, the protection of personal privacy data has risen to the legal level. Traditional application systems generally lack measures to protect personal privacy data. Data desensitization can be used to specifically encrypt, mask, and replace the data returned by the production database according to user-defined desensitization rules without making any changes to the data in the production database to ensure the sensitive data of the production environment can be protected. In real business scenarios, relevant business development teams often need to implement and maintain a set of desensitization functions based on desensitization requirements. The desensitization functions are often coupled in various business logic, making it difficult to reuse different business systems. When the desensitization scenario changes, self-maintained desensitization functions often face the risk of refactoring or modification. Based on the industry’s demand for desensitization and business transformation pain points, it provides a complete, secure, transparent, and low transformation cost integrated data desensitization solution, which is the main design goal of the SphereEx-DBPlusEngine data desensitization module. Implementation principle: By parsing the SQL queried by the user and decorating the SQL execution results according to the desensitization rules provided by the user, the original data is desensitized. For details, see Data Desensitization

Security Audit #

Full audit log, which means that when this function is enabled, the system will record all executed SQL statements and include the database, user, access address, access time consumption and other information corresponding to the statements, which is convenient for enterprises to conduct audit operations.

Parameter explanation #

general_query_log: There is only one parameter for general audit log. If the value is true, enable the general log, and if the value is false, disable the function.

Prerequisite #

Similar to the “Slow Query Log” feature, the implementation of the full audit log is also based on the Agent, so this feature is only applicable to SphereEx-DBPlusEngine with the Agent enabled scenario.

Configuration Example #

The Full Audit Log is an Agent-based feature, so the following configuration is located in agent.yaml.

plugins:
 Logging:
  props:
   # Whether to enable general query log.
   general_query_log: false
   # Whether to enable slow query log.
   #slow_query_log: true
   # Long query threshold, in 

Where general_query_log is configured to false, indicating that full logging is not enabled. When full volume logging needs to be enabled, configure general_query_log to true and restart SphereEx-DBPlusEngine.

Full audit log format #

db: {database} user: {user} host: {host} query_time: {query time} {sql}

• db: the name of the database.
• user: the name of the user used in the current connection.
• host: the client access address.
• query_time: the time taken for SQL execution in ms.
• sql: the SQL statement sent by the client.

Example of a full audit log #

[INFO ] 2022-07-01 00:00:00.000 [ShardingSphere-Command-0] GENERAL-QUERY - db: sharding_db user: root host: 127.0.0.1 query_time: 145
CREATE TABLE `t_order` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
)