User Management #

1. Create user #

The initial user is specified by the administrator before DBPlusEngine starts. Configured in the server.yaml file is used to configure the initial user who logs in to the computing node and the data authorization of the storage node.

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

CREATE DIST USER statement used to create new user with specified password.

CREATE DIST USER 'user'@'host' IDENTIFIED BY 'password';

Parameter Description

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

Example

Create a user named ‘sharding’ with a password of ‘123456’, ‘%’ means unlimited login hosts.

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

Create a user that can only log in at 127.0.0.1.

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

2. Modify user password #

The ALTER DIST USER statement is used to modify existing users, and currently only supports changing passwords.

ALTER DIST USER 'user'@'host' IDENTIFIED BY 'password';

Example

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

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

3. Delete user #

DROP DIST USER statement used to delete user.

DROP DIST USER 'user'@'host';

Example

Remove the ‘sharding’@’%’ user from the system.

4. View user #

SHOW DIST USERS statement used to view user list.

SHOW DIST USERS

Parameter Description

None

Example

View all users, only query authorized hosts and user names, and do not display user passwords.

SHOW DIST USERS;

Role Management #

1. Create Role Example #

CREATE DIST ROLE statement used to create role.

CREATE DIST ROLE roleName;

Example

Create a role named ‘dml_only’.

CREATE DIST ROLE dml_only;

2. Delete Role Example #

DROP DIST ROLE statement used to delete role.

DROP DIST ROLE roleName;

Example

Remove the ‘dml_only’ user from the system.

DROP DIST ROLE dml_only;

3. Grant User Role #

GRANT DIST role TO 'user'@'host';

Example

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

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

Grant global SELECT, INSERT, UPDATE, and DELETE authorities to the role dml_only.

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

4. Revoke User Role #

REVOKE DIST role FROM 'user'@'host';

Example

Revoke dml_only role granted to user ‘sharding’@’%’.

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

5. View Role List #

SHOW DIST ROLES statement used to view role list

SHOW DIST ROLES;

Parameter Description

无

Example

View all created role names.

SHOW DIST ROLES;

Grant And Revoke Authority #

1. Grant Global Authority #

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

GRANT DIST SELECT to 'user'@'host';

Example

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

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

2. Grant Authority (all objects) Example #

GRANT DIST privileges ON *.* to 'user'@'host';

Example

Grant all object SELECT and INSERT authority to user ‘sharding’@’%’.

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

3. Grant Authority (database level) #

GRANT DIST privileges ON schema.* to 'user'@'host';

Example

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

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

4. Grant Authority (table level) #

GRANT DIST privileges ON schema.table to 'user'@'host';

Example

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

5. Grant Authority (column level) #

GRANT DIST privileges (column name) ON schema.table to 'user'@'host';

Example

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

6. Revoke All Authorities #

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

REVOKE DIST ALL FROM 'user'@'host';

Example

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

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

7. Revoke Global Authority #

REVOKE DIST privileges FROM 'user'@'host';

Example

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

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

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

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

8. Revoke Authority (all object) #

REVOKE DIST privileges ON *.* FROM 'user'@'host';

Example

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

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

9. Revoke Authority (database level) #

REVOKE DIST privileges ON schema.* FROM 'user'@'host';

Example

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

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

10. Revoke Authority (table level) #

REVOKE DIST privileges ON schema.table FROM 'user'@'host';

Example

Revoke the SELECT and INSERT authorizations of user ‘sharding’@’%’ on logical table sharding_db.t_order.

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

11. Revoke Authority (column level) #

REVOKE DIST privileges (column name) ON schema.table FROM 'user'@'host';

Example

Revoke SELECT grant for user ‘sharding’@’%’ on specified column in logical table sharding_db.t_order.

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

12. View User Authority #

SHOW DIST GRANTS FOR 'user'@'host';

Example

View authority of user ‘sharding’@’%’

SHOW DIST GRANTS FOR 'sharding'@'%' ;