Authority Control #

Authority Configuration #

Scenarios

The authority engine performs system initialization according to the authority rules configured in the server.yaml.

Data Planning

• users are used to specify the initial user. For example, set root@% as the initial user.
• The type in the privilege is used to specify the selected service provider. For example, the enterprise authority provider SphereEx:PERMITTED is configured here.

Notes

1. The initial user has SUPER authority by default.
2. If the initial user is given non SUPER authorization through DistSQL, the initial user will lose SUPER authorization.
3. To grant SUPER authorization again, you need to use GRANT DIST SUPER TO user statement.

Procedure

The configuration format is as follows:

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

Do not Use Role Management #

Scenario

An application system provides different levels of DBPlusEngine accounts for developers and operation and maintenance personnel. Among them, developers can only execute DML instructions, operation and maintenance personnel can execute DML + DDL instructions, and another root user is the top manager.

Data Planning

All account requirements are as follows:

The root user is the initial user.

Procedure

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

-- The login host is not limited, and the host configuration is omitted.
CREATE DIST USER zhangsan IDENTIFIED BY '123456';
CREATE DIST USER wangwu IDENTIFIED BY '123456';
CREATE DIST USER develop_test IDENTIFIED BY '123456';
CREATE DIST USER operator_1 IDENTIFIED BY '123456';
CREATE DIST USER operator_2 IDENTIFIED BY '123456';

2. Authorize development users.

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

3. Authorize operation and maintenance users.

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

4. If you need to add new development users, repeat the following two steps.

-- Create new user.
CREATE DIST USER new_developer IDENTIFIED BY '123456';
-- Authorize
GRANT DIST INSERT,SELECT,UPDATE,DELETE TO new_developer;

5. If you need to add new operation and maintenance users, repeat the following two steps.

-- Create new user.
CREATE DIST USER new_operator IDENTIFIED BY '123456';
-- Authorize
GRANT DIST INSERT,SELECT,UPDATE,DELETE,CREATE,ALTER,DROP,TRUNCATE TO new_operator;

Using Role Management #

Scenarios

An application system provides different levels of DBPlusEngine accounts for developers and operation and maintenance personnel. Among them, developers can only execute DML instructions, operation and maintenance personnel can execute DML + DDL instructions, and another root user is the top manager.

Data Planning

All account requirements are as follows:

Procedure

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

-- The login host is not limited, and the host configuration is omitted.
CREATE DIST USER zhangsan IDENTIFIED BY '123456';
CREATE DIST USER wangwu IDENTIFIED BY '123456';
CREATE DIST USER develop_test IDENTIFIED BY '123456';
CREATE DIST USER operator_1 IDENTIFIED BY '123456';
CREATE DIST USER operator_2 IDENTIFIED BY '123456';

2. Create two roles: develop_dml and operate_ddl.

CREATE DIST ROLE develop_dml;
CREATE DIST ROLE operate_ddl;

3. Authorize roles.

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

4. Assign the developer to the user, so that the user has the authorizations owned by the role.

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

5. Assign users to the operation and maintenance role.

GRANT DIST operate_ddl TO operator_1;
GRANT DIST operate_ddl TO operator_2;

6. If you need to add new developmers, repeat the following two steps.

-- Create new user.
CREATE DIST USER new_developer IDENTIFIED BY '123456';
-- Authorize
GRANT DIST develop_dml TO new_developer;

7. If you need to add new operation and maintenance users, repeat the following two steps.

-- Create new user.
CREATE DIST USER new_operator IDENTIFIED BY '123456';
-- Authorize
GRANT DIST operate_ddl TO new_operator;