Authority Control #

Authority Classification #

Global Authority #

Global authority refers to that the authorization obtained by the user does not distinguish the target object, and the user can perform corresponding operations on any logical database and logical table.

For example, ，if the following instruction grants the global INSERT, SELECT, UPDATE and DELETE authorities to user ‘sharding’@'%', the user can perform DML operation on tables in any logical database.

-- The following two statements are equivalent.
GRANT DIST INSERT,SELECT,UPDATE,DELETE TO 'sharding'@'%';
GRANT DIST INSERT,SELECT,UPDATE,DELETE TO sharding;

Note: The global authority includes two special authorities: CREATE_USER and SUPER.

Get CREATE_USER authorization user can perform the following operations:

SUPER represents the highest authority of the database system. By default, the initial user configured before ShardingSphere starts has super authorization.

Object Authority #

Object Authority means that the granted authority is limited by the scope of action, and users cannot perform corresponding operations outside the scope of authorization.

The scope of object authority can be all logical databases, or you can specify single or multiple logical databases and tables and columns in the databases.

For example, the following instructions sets all authorities of t_order in logical database sharding_db to user ‘sharding’@'%'. After that the user can operate on table sharding_db.t_order. However the user cannot operate other tables of sharding_db without additional authorization.

GRANT DIST ALL ON sharding_db.t_order TO sharding;

DistSQL List #

The complete authority management list of DistSQL is as follows:

Note:

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

Configuration of Authority #

It is used to configure the initial user logging in to the computing node and the data authorization of the storage node.

Example #

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

User Management #

Initial User Management #

The initial user is specified by the administrator before the DBPlusEngine starts.

Ordinary User Management #

Create User #

The CREATE DIST USER statement is used to create a new user with the specified password.

In DBPlusEngine, a user is a combination of a user name and the host to which the user name is connected.

Example:

• Create a user named ‘sharding’, with a password of ‘123456’, ‘%’ indicates that login to the host is not restricted.

CREATE DIST USER 'sharding'@'%' IDENTIFIED BY '123456';

• Create a user who can only log in at 127.0.0.1.

CREATE DIST USER 'sharding'@'127.0.0.1' IDENTIFIED BY '123456';

Modify User #

The ALTER DIST USER statement is used to modify existing users. Currently, it only supports password modification.

Example:

• Change the password of the user of ‘sharding’ @ ‘%’ to ‘sharding’.

ALTER DIST USER 'sharding'@'%' IDENTIFIED BY 'sharding';

Delete User #

The DROP DIST USER statement is used to delete a user.

Example:

• Remove the user of ‘sharding’@'%’ from the system.

DROP DIST USER 'sharding'@'%';

View User List #

The SHOW DIST USERS statement is used to view the user list.

Example:

• View all users, only the authorized host and user name are queried, and the user password is not displayed.

SHOW DIST USERS;

Role Management #

Create Role #

The CREATE DIST ROLE statement is used to create the role.

Example:

• Create a role named ‘dml_only’.

CREATE DIST ROLE dml_only;

Delete Role #

The DROP DIST ROLE statement is used to delete a role.

Example:

• Remove user ‘dml_only’ from the system.

DROP DIST ROLE dml_only;

View Role List #

The SHOW DIST ROLES statement is used to view the role list.

Example:

• View all role names that have been created.

SHOW DIST ROLES;

Authority Management #

Authorize #

The GRANT DIST privileges statement is used to authorize a user or role.

Example:

• Grant global SELECT authority to user ‘sharding’@'%'.

GRANT DIST SELECT TO 'sharding'@'%';

• Grant SELECT and INSERT authority of logical databse sharding_db to user ‘sharding’@'%'.

GRANT DIST SELECT, INSERT ON sharding_db.* TO 'sharding'@'%';

• Grant SELECT and INSERT authority of logical table sharding_db.t_order to user ‘sharding’@'%'.

GRANT DIST SELECT, INSERT ON sharding_db.t_order TO 'sharding'@'%';

• Grant the specified SELECT authority in logical table sharding_db.t_order to user ‘sharding’@'%'.

GRANT DIST SELECT (order_id), SELECT (user_id, status) ON sharding_db.t_order TO 'sharding'@'%';

• Assign Global SELECT, INSERT, UPDATE and DELETE authority to role dml_only.

GRANT DIST INSERT,SELECT,UPDATE,DELETE TO dml_only;

• Grant role dml_only to user ‘sharding’@'%'.

GRANT DIST dml_only TO 'sharding'@'%';

Revocation of Authorization #

The REVOKE DIST privileges statement is used to revoke authorization of a user or role.

Example:

• Revoke Global Select authorization for user ‘sharding’ @ ‘%'.

REVOKE DIST SELECT FROM 'sharding'@'%';

• Revoke SELECT and INSERT authority of user ‘sharding’@'%’ in logical database sharding_db.

REVOKE DIST SELECT, INSERT ON sharding_db.* FROM 'sharding'@'%';

• Revoke SELECT and INSERT authority of user ‘sharding’@'%’ in logical table sharding_db.

REVOKE DIST SELECT, INSERT ON sharding_db.t_order FROM 'sharding'@'%';

• Revoke SELECT authority of user ‘sharding’@'%’ in logical sharding_db.t_order.

REVOKE DIST SELECT (order_id), SELECT (user_id, status) ON sharding_db.t_order FROM 'sharding'@'%';

• Revoke SELECT, INSERT, UPDATE and DELETE authority of role dml_only.

REVOKE DIST INSERT,SELECT,UPDATE,DELETE FROM dml_only;

• Revoke the role dml_only of user ‘sharding’@'%'.

REVOKE DIST dml_only FROM 'sharding'@'%';

Related Reference #

Authority Control