DistSQL Authority Control #

Authority Configuration #

Scenarios

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

Data Planning

• users is 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

• The initial user has SUPER authority by default.
• If the initial user is given non SUPER authorization through DistSQL, the initial user will lose SUPER authorization.
• 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 DBPlusEngine accounts with different authorities for different personnel.

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 zhaoliu IDENTIFIED BY '123456';

2. Authorize users.

GRANT DIST CREATE SHARDING, ALTER SHARDING, ALTER SHARDING, SHOW SHARDING TO zhangsan;
GRANT DIST RQL SHOW TO wangwu;
GRANT DIST RAL TO zhaoliu;

Using Role Management #

Scenarios

An application system provides DBPlusEngine accounts with different authorities for different users.

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';

2. Create two roles: develop_sharding and operate_show.

CREATE DIST ROLE develop_sharding;
CREATE DIST ROLE operate_show;

3. Authorize roles.

GRANT DIST CREATE SHARDING, ALTER SHARDING, ALTER SHARDING, SHOW SHARDING TO develop_sharding;
GRANT DIST RQL SHOW TO operate_show;

4. Grant roles to users, and the user will have the authorities that the role have.

GRANT DIST develop_sharding TO zhangsan;
GRANT DIST operate_show TO wangwu;

5. Grant users to operation and maintenance roles.

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