ApsaraDB for SelectDB:Manage the basic permissions

Key concepts

User properties belong to the username, not to a specific user identity. Multiple identities sharing the same username share one set of user properties.

Built-in roles

When a SelectDB instance is initialized, two roles and their corresponding users are created automatically.

You cannot revoke or modify the permissions of automatically created roles or users.

If you forget the password of the admin user and cannot log in to the instance, reset it from the SelectDB console. For more information, see Reset the password of an admin user for an instance.

Permission types

The following table describes the available permission types and the levels at which each can be applied.

Permission levels

Permissions apply to data objects at four levels of scope.

Resource permissions use a separate scope (RESOURCE resource_name) and do not map to data levels.

SQL syntax reference

ADMIN_PRIV and GRANT_PRIV permissions

Both ADMIN_PRIV and GRANT_PRIV allow granting permissions to other users. Their scope differs.

• ADMIN_PRIV: Can only be granted or revoked at the global level. GRANT_PRIV at the global level is equivalent to ADMIN_PRIV—use both with caution.

• GRANT_PRIV: Can be scoped to global, catalog, database, or table level. A user with GRANT_PRIV at a given level can only grant permissions within that scope.

The table below shows which permission is required for each administrative operation.

Users with GRANT_PRIV at a non-global level cannot change the password of an existing user. They can set a password only when creating a new user.

To query your current authenticated user identity, run:

SELECT current_user();

To query your actual login identity, run:

SELECT user();

All permissions are enforced against current_user, the identity that passed authentication.

For example, if you create a user whose user identity is user1@'192.%' and user1 logs on from the CIDR block 192.168.0.0/16, then current_user is user1@'192.%' and user is user1@'192.168.%'. All permissions apply to the current_user identity.

User properties

User properties control resource limits and behavior for a given username. Set them with SET PROPERTY and query them with SHOW PROPERTY.

When the same setting exists at multiple levels, the system resolves it in this order:

session variable > user property > global variable > default value

Examples

-- Create the user, allowing login from the 172.10.0.0/16 CIDR block
CREATE USER test_user@'172.10.%' IDENTIFIED BY '123456';

-- Grant read, modify, and import permissions on the table
GRANT SELECT_PRIV, ALTER_PRIV, LOAD_PRIV ON test_db.test_table TO 'test_user'@'172.10.%';

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

-- Create the role
CREATE ROLE test_role;

-- Grant import permissions on all tables in test_db to the role
GRANT LOAD_PRIV ON test_db.* TO ROLE 'test_role';

-- Assign the role to a user
GRANT "test_role" TO test_user@'172.10.%';

-- Query all properties of a user
SHOW PROPERTY FOR 'test_user';

-- Filter by property name
SHOW PROPERTY FOR 'test_user' LIKE '%max_user_connections%';

-- Set a property
SET PROPERTY FOR 'test_user' 'max_user_connections' = '1000';

DROP USER 'test_user'@'172.10.%';
DROP ROLE test_role;

SHOW GRANTS FOR test_user@'%';

Best practices

Usage notes

• ADMIN_PRIV can only be granted and revoked at the global level.

• GRANT_PRIV at the global level is equivalent to ADMIN_PRIV because it allows granting all permissions. Apply it with caution.

• All permissions are enforced against current_user (the authenticated identity), not the actual login identity (user).

FAQ

What do I do if a domain name conflicts with an IP address when I create a user?

Delete the conflicting user with DROP USER, then recreate with the correct identity.

For a domain-vs-IP conflict, create the user with the domain identity first, then grant the domain-level permissions:

CREATE USER test_user@['domain'];
GRANT SELECT_PRIV ON *.* TO test_user@['domain'];

If DNS resolves the domain to IP1 and IP2, and you later grant a different permission to test_user@'IP1', that identity gets its own permission set—changing test_user@['domain'] does not affect test_user@'IP1'.

What do I do if two user identities with overlapping IP ranges conflict?

Create the more specific identity. The more specific host pattern takes priority.

CREATE USER test_user@'%' IDENTIFIED BY "12345";
CREATE USER test_user@'192.%' IDENTIFIED BY "abcde";

A login attempt from the 192.168.0.0/16 range matches 192.% (higher priority). Using the password 12345 from that range is rejected; use abcde instead.

What's next

• Grant a user the permissions to access clusters

• Reset the password of an admin user for an instance