ApsaraDB for SelectDB:Manage the basic permissions of users

Permissions

Permission types

Permission levels

The following table describes the levels of the permissions on databases and tables that are divided based on the scope of permissions.

The following table describes the two levels of the permissions on resources.

ADMIN_PRIV and GRANT_PRIV permissions

Both the ADMIN_PRIV and GRANT_PRIV permissions allow you to grant permissions. This section describes the usage notes on the ADMIN_PRIV and GRANT_PRIV permissions.

• CREATE USER

  • Users who have the ADMIN_PRIV permission or the GRANT_PRIV permission at the global and database levels can create users.

• DROP USER

  • Users who have the ADMIN_PRIV permission or the GRANT_PRIV permission at the global level can delete users.

• CREATE/DROP ROLE

  • Users who have the ADMIN_PRIV permission or the GRANT_PRIV permission at the global level can create roles.

• GRANT/REVOKE

  • Users who have the ADMIN_PRIV permission or the GRANT_PRIV permission at the global level can grant permissions to and revoke permissions from users.

  • Users who have the GRANT_PRIV permission at the catalog level can grant the permissions on the specified catalog to or revoke the permissions from users.

  • Users who have the GRANT_PRIV permission at the database level can grant the permissions on the specified database to or revoke the permissions from users.

  • Users who have the GRANT_PRIV permission at the table level can grant the permissions on the specified table in the specified database to or revoke the permissions from users.

• SET PASSWORD

  • Users who have the ADMIN_PRIV permission or the GRANT_PRIV permission at the global level can set a password for all users.

  • Regular users can set the password that corresponds to their own user identity. You can execute the SELECT CURRENT_USER(); statement to query your user identity.

  • Users who have the GRANT_PRIV permission that is not at the global level cannot set a password for existing users. Such users can set a password only when they create a user.

User properties

The following table describes the properties that are related to users.

Operations allowed by permissions

CREATE USER [IF EXISTS] user_identity [IDENTIFIED BY 'password']
[DEFAULT ROLE 'role_name']
[password_policy]

 DROP USER 'user_identity'
 
 `user_identity`:
     user@'host'
     user@['domain']

GRANT privilege_list ON priv_level TO user_identity [ROLE role_name]
 
GRANT privilege_list ON RESOURCE resource_name TO user_identity [ROLE role_name]

REVOKE privilege_list ON db_name[.tbl_name] FROM user_identity [ROLE role_name]
 
REVOKE privilege_list ON RESOURCE resource_name FROM user_identity [ROLE role_name]

 CREATE ROLE rol_name;

 DROP ROLE rol_name;

SHOW [ALL] GRANTS [FOR user_identity];

SHOW ROLES

SHOW PROPERTY [FOR user] [LIKE key]

SET PROPERTY [FOR 'user'] 'key' = 'value' [, 'key' = 'value']

Parameter description

Operation examples

• Example 1: Create a user named test_user. The password of the user is 123456 and the user can send logon requests from the IP address 172.10.XX.XX.

  CREATE USER test_user@'172.10.XX.XX' IDENTIFIED BY '123456';

• Example 2: Delete a user whose user identity is test_user@'172.10.XX.XX'.

  DROP USER 'test_user'@'172.10.XX.XX'

• Example 3: Grant a user whose user identity is test_user@'172.10.XX.XX' the permissions to read data from, modify data in, and import data to the test_ctl.test_db.test_table table.

  GRANT SELECT_PRIV,ALTER_PRIV,LOAD_PRIV ON test_ctl.test_db.test_table TO 'test_user'@'172.10.XX.XX';

• Example 4: Revoke the read permissions on the test_db database from the user named test_user.

  REVOKE SELECT_PRIV ON test_db.* FROM 'test_user'@'172.10.XX.XX';

• Example 5: Create a role named test_role.

  CREATE ROLE test_role;

• Example 6: Delete a role named test_role.

  DROP ROLE test_role;

• Example 7: Grant a role named test_role the permissions to import data to all tables of the test_ctl.test_db database.

  GRANT LOAD_PRIV ON test_ctl.test_db.* TO ROLE 'test_role';

• Example 8: Change the role assigned to the user whose user identity is test_user @'172.10.XX.XX' to test_role.

  GRANT "test_role" TO test_user@'172.10.XX.XX';

• Example 9: Query the permissions of the user whose user identity is test_user@'%'.

  SHOW GRANTS FOR test_user@'%';

• Example 10: Query the properties of the user named test_user.

  # Query all properties of the user.
  SHOW PROPERTY FOR 'test_user';
  
  # Use a LIKE expression to query a specific property of the user.
  SHOW PROPERTY FOR 'test_user' LIKE '%max_user_connections%';

• Example 11: Configure the properties of the user named test_user.

  SET PROPERTY FOR 'test_user' 'max_user_connections' = '1000';

Best practices

The permission management system is commonly used in the following scenarios:

• Scenario 1

  The users of a cluster are classified into administrators, R&D engineers, and regular users. Administrators have full permissions on the cluster and are responsible for cluster management, such as building the cluster and managing nodes. R&D engineers are responsible for service modeling, such as creating databases and tables, and importing and modifying data. Regular users access data in different databases and tables.

  In this scenario, you can grant the ADMIN_PRIV or GRANT_PRIV permission to administrators. You can grant R&D engineers the CREATE, DROP, ALTER, LOAD, and SELECT permissions on one or more databases and tables. You can grant regular users the SELECT permission on one or more databases and tables. You can also create different roles to grant permissions to multiple users in an easier way.

• Scenario 2

  A cluster provides different services. Each service may use one or more data records. You need to manage the permissions of users of each service. In this scenario, an administrator can create a user that has the GRANT_PRIV permission at the database level for each database. This user can only grant the permissions on the specified database to users.

• Scenario 3

  ApsaraDB for SelectDB does not support blacklists, but supports only whitelists. However, you can use specific methods to simulate a blacklist. For example, you create a user whose user identity is test_user1@'192.%'. The user can send logon requests from the IP address 192.*. In this case, if you want to reject logon requests from 192.168.XX.XX, you can create another user whose user identify is test_user2@'192.168.XX.XX' and set a new password for the user. The priority of 192.168.XX.XX is higher than that of 192.%. Therefore, the user from 192.168.XX.XX can no longer use the old password to log on to ApsaraDB for SelectDB instances.

Usage notes on permissions

• When an ApsaraDB for SelectDB instance is initialized, the following roles and users are automatically created:

  • operator: This role has the NODE_PRIV and ADMIN_PRIV permissions on ApsaraDB for SelectDB and allows you to log on to the ApsaraDB for SelectDB instance from any node. This role is assigned only to the built-in instance user whose user identity is root@'%'.

  • admin: This role is a management role and has the ADMIN_PRIV permission. This role has all permissions except the permissions to change nodes, and allows you to log on to the ApsaraDB for SelectDB instance from a node. When the ApsaraDB for SelectDB instance is initialized, the system automatically creates a user whose user identity is admin@'%' and assigns the admin role to the user. You can create multiple users and assign the admin role to the users.

• You cannot revoke or change the permissions of roles or users that are automatically created.

• If you forget the password of the user to which the admin role is assigned and cannot log on to an ApsaraDB for SelectDB instance, you can reset the password of the user in the ApsaraDB for SelectDB console. For more information, see Reset the password of an admin user for an instance.

• The ADMIN_PRIV permission can be granted or revoked only at the global level.

• The GRANT_PRIV permission at the global level is equivalent to the ADMIN_PRIV permission because the former has the permissions to grant all permissions. Proceed with caution when you use the GRANT_PRIV permission at the global level.

• You can execute the SELECT current_user(); statement to query current_user and the SELECT user(); statement to query user. current_user indicates the user identity that is used by the current user to pass identity authentication, and user indicates the actual user identity of the user. All permissions are granted to the user identity that is used by the current user to pass identity authentication and the user has the permissions of the user identity.

  For example, you create a user whose user identity is user1@'192.%' and a user named user1 from the IP address 192.168.XX.XX logged on to an ApsaraDB for SelectDB instance. In this case, the user identity that is used by the current user to pass identity authentication is user1@'192.%' and the actual user identity of the user is user1@'192.168.XX.XX.

FAQ

Q: What do I do if a domain name conflicts with an IP address or IP address conflicts occur when I create a user and grant permissions to the user?

A: Execute the DROP USER statement to delete the user and create another one.