DistSQL Authority Control #

Authority Classification #

DistSQL authority classification is similar to standard database authority, they also have global authority, database level object authority and table level object authority. At present, DistSQL does not have column level operations, so there are no column level authority in the authority classification.

DistSQL authority has more types of authorization items than standard database authority. DistSQL authorization item is composed of operation + operation object, and its combination method is as follows.

	CREATE	ALTER	DROP	SHOW
RESOURCE	✔️	✔️	✔️	✔️
SHARDING	✔️	✔️	✔️	✔️
READWRITE_SPLITTING	✔️	✔️	✔️	✔️
ENCRYPT	✔️	✔️	✔️	✔️
DB_DISCOVERY	✔️	✔️	✔️	✔️
SHADOW	✔️	✔️	✔️	✔️
SINGLE_TABLE	✔️	✔️	✔️	✔️

In addition to the authorization items shown in the above table, RDL, RQL and RAL syntax types are also provided as authorization items.

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, the following instruction grants the global SHOW SHARDING authority to user ‘sharding’@'%', and the user can perform SHOW operation on sharding rules in any logical database.

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

In addition, because there are many DistSQL authorization items, when granting multiple authorization items at a time, you can use the syntax type as the authorization item. For example, the following instruction grants SHOW of all operations object global to user ‘sharding’@'%', the user can perform the SHOW operation on any operation object in any logical database.

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

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 in databases.

For example, the following instruction grants sharding rule t_order creation and modification in logical database sharding_db to user ‘sharding’@'%'. After that, the user can create and modify rule sharding_db.t_order. However, the user cannot operate any other rules in sharding_db.

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

DistSQL List #

Description	Syntax	Sample
Create user	CREATE DIST USER ‘user’@‘host’ IDENTIFIED BY ‘password’;	CREATE DIST USER ‘sharding’@'%’ IDENTIFIED BY ‘123456’;
Modify user Password	ALTER DIST USER ‘user’@‘host’ IDENTIFIED BY ‘password’;	ALTER DIST USER ‘sharding’@'%’ IDENTIFIED BY ‘sharding’;
Delete user	DROP DIST USER ‘user’@‘host’;	DROP DIST USER ‘sharding’@'%';
Create role	CREATE DIST ROLE role;	CREATE DIST ROLE admin;
Delete role	DROP DIST ROLE role;	DROP DIST ROLE admin;
Grant role to user	GRANT DIST role TO ‘user’@‘host’;	GRANT DIST admin TO ‘sharding’@'%';
Cancel user role	REVOKE DIST role FROM ‘user’@‘host’;	REVOKE DIST admin FROM ‘sharding’@'%';
Grant global authority	GRANT DIST privileges TO ‘user’@‘host’;	GRANT DIST CREATE SHARDING TO ‘sharding’@'%';
Grant authority (all objects)	GRANT DIST privileges ON . to ‘user’@‘host’;	GRANT DIST CREATE SHARDING, SHOW SHARDING ON . TO ‘sharding’@'%';
Grant authority (database level)	GRANT DIST privileges ON schema.* to ‘user’@‘host’;	GRANT DIST CREATE SHARDING, SHOW SHARDING ON sharding_db.* TO ‘sharding’@'%';
Grant authority (table level)	GRANT DIST privileges ON schema.table to ‘user’@‘host’;	GRANT DIST CREATE SHARDING, SHOW SHARDING ON sharding_db.t_order TO ‘sharding’@'%';
Cancel global authority	REVOKE DIST privileges FROM ‘user’@‘host’;	REVOKE DIST SHOW SHARDING FROM ‘sharding’@'%';
Cancel authority (all objects)	REVOKE DIST privileges ON . FROM ‘user’@‘host’;	REVOKE DIST SHOW SHARDING ON . FROM ‘sharding’@'%';
Cancel authority (database level)	REVOKE DIST privileges ON schema.* FROM ‘user’@‘host’;	REVOKE DIST CREATE SHARDING ON sharding_db.* FROM ‘sharding’@'%';
Cancel authority (table level)	REVOKE DIST privileges ON schema.table FROM ‘user’@‘host’;	REVOKE DIST CREATE SHARDING ON sharding_db.t_order FROM ‘sharding’@'%';
View user authority	SHOW DIST GRANTS FOR ‘user’@‘host’;	SHOW DIST GRANTS FOR ‘sharding’@'%';
Refresh authorization	FLUSH DIST PRIVILEGES;	FLUSH DIST PRIVILEGES;

The list of DistSQL using syntax type as authorization item is as follows:

Description	Syntax	Sample
Grant global authority	GRANT DIST privileges TO ‘user’@‘host’;	GRANT DIST RAL, RDL TO ‘sharding’@'%';
Cancel global authority	REVOKE DIST privileges FROM ‘user’@‘host’;	REVOKE DIST RAL, RDL FROM ‘sharding’@'%';

Note:

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