Configuring Doris User Permissions

Notes

• The deletion operation is irreversible. Even if a role with the same name is added immediately after the deletion, the permission may be different from that of the deleted one. Ensure that the role is not in use before deleting it.
• Before deleting a user, ensure that it is no longer needed. The deletion operation is irreversible. Even if an account with the same name is added immediately after the deletion, the permission may be different from that of the deleted one.
• The password of an existing account cannot be viewed. If you forget the password, you can reset the password.
• The operation permissions of an account on the database can be viewed.
• The username and role name are case sensitive.

Prerequisites

• A Doris cluster has been created and is running properly.
• The MySQL client has been installed.

Step 1: Create a Doris Role

1. Log in to the CloudTable console.
2. Choose Cluster Management. In the right pane, select the target cluster. Choose Role Management on the page that is displayed.
3. Click Create Role. On the Create Role page, set parameters.

   Table 1 Role permissions

   Parameter	Description
   Rolename	The role name must start with a letter and contain 1 to 64 characters.
   Global Permission	Global permissions refer to the permissions on all databases and tables, including the SELECT, LOAD, ALTER, CREATE, and DROP permissions.
   Each Level Permission	Database And Table refers to the databases and tables created in the cluster. A role has high-risk permissions for databases but only common permissions for tables. Permission types High-risk permissions: CREATE TABLE and DROP TABLE Common permissions: SELECT, LOAD, and ALTER

4. Click OK.

Step 2: Create a Doris User

1. After a role is created, choose Account Management.
2. Click Create Account. On the Create Account page, set the username and password.

   Table 2 Account parameters

   Parameter	Description
   Username	The username must start with a letter and contain 1 to 64 characters.
   Password	Enter a password for the user. NOTE: The password must meet the following requirements: Contain 8 to 16 characters. Contain at least four types of the following characters: uppercase letters, lowercase letters, digits, and special characters ($@!%*?&) Cannot be the same as the username or the username spelled backwards.
   Confirm Password	Enter the password again.

3. Click OK.

Step 3: Bind a Role to a Doris User and Access the Cluster

For example, role A has the permission to query, insert, modify, create, and delete data. After being bound to role A, the user has the permissions of role A.

1. After a role and user are created, choose Account Management, locate the target account, click More, and select Assign Role in the Operation column.
2. In the Assign Role dialog box, select a role. Click OK.
3. Click Permission in the Operation column and check that the user has the role permissions.
4. Connect to the cluster as the created user.
   For details about how to connect to a non-security cluster, see Using the MySQL Client to Connect to a Doris Normal Cluster.

   ./mysql -uadmin -pPassword -h Internal IP address of the cluster -P9030

   For details about how to connect to a security cluster, see Using the MySQL Client to Connect to a Doris Security Cluster.

   ./mysql -uadmin -h Internal IP address of the cluster -P9030 --ssl-ca={path}/certificate.crt --ssl-mode=VERIFY_CA -pPassword

5. Execute the query, insert, change, create, and delete commands in the CLI.
   • If these commands can be executed, the role is bound successfully.
   • If these commands cannot be executed, check whether the role has been configured with permissions and whether it has been bound to the user. If the fault persists, contact technical support.

Managing User Permissions

• Managing roles
  • Deleting a role: Click Delete in the Operation column. On the displayed page, enter DELETE in the text box or click Auto Enter, and click OK.
  • Modifying role permissions: Click Edit in the Operation column. On the displayed page, select permissions as needed and click OK.
  • Viewing role permissions: Click Permission in the Operation column. On the displayed page, view the role's permissions on databases and tables.
• Managing users
  • Viewing user permissions: Click Permission in the Operation column. On the displayed page, view the user's permissions on databases and tables.
  • Deleting a user: Click Delete in the Operation column. In the displayed dialog box, click Yes.
  • Changing the user password: Click More and select Update Password in the Operation column. On the displayed page, change the password and click OK.

Common Commands for User Permissions

1. Creating a role

   CREATE Role role_name;

   role_name: name of the role to be created

2. Assigning permissions to a role
   • Grant the permissions on a specified database table.

     GRANT LOAD_PRIV ON ctl1.db1.* TO ROLE 'my_role';

   • Grant the permission to use specified resources.

     GRANT USAGE_PRIV ON RESOURCE 'spark_resource' TO ROLE 'my_role';

3. Deleting a role

   DROP ROLE role1;

4. Creating a user
   • Use the CREATE USER command to create a Doris user (without a role).

     CREATE USER 'Jack' IDENTIFIED BY 'password';

     Table 3 Parameters

     Parameter	Description
     Jack	Username
     password	Password of the created user

     

     You can run the command to specify an IP address.

     CREATE USER 'jack02' @'192.168.%'  identified by '123456'  DEFAULT ROLE 'default_role';

   • Use the CREATE USER command to create a Doris user (with the default role assigned).

     CREATE USER 'jeo' IDENTIFIED BY 'password' DEFAULT ROLE 'default_role';

5. Changing a user password
   Use the ALTER USER command to change the password of a user.

   ALTER USER 'Jack' IDENTIFIED BY "password";

6. Assigning a role to the user
   • Assign a role to the user.

     GRANT 'role1','role2' TO 'jack'@'%';

     Table 4 Parameters

     Parameter	Description
     role	Created role
     jack	Created username

   • Revoke the role assigned to the user.

     REVOKE 'role1' FROM 'candy';

7. Deleting a user

   DROP USER'Jack';

8. Querying the permissions and roles of a user

   SHOW GRANTS;