Create a user and grant permissions to the user - E-MapReduce

The permission management system of StarRocks supports fine-grained table-level permission control, role-based access control, and a whitelist mechanism.

Background information

Some operations that you can perform vary based on the version of StarRocks. For more information, see StarRocks 2.5 and StarRocks 3.2.

Create a user

Important

Only users who have the ADMIN permission or users who have the GRANT permission at any level can create a user.

Syntax

CREATE USER user_identity [auth_option] [DEFAULT ROLE 'role_name'];

Parameters:

user_identity: the identity of the user. Set the value in the format of username@'userhost' or username@['domain'].
[auth_option]: the authentication method. Valid values:
- IDENTIFIED BY 'auth_string'
- IDENTIFIED WITH auth_plugin
- IDENTIFIED WITH auth_plugin BY 'auth_string'
- IDENTIFIED WITH auth_plugin AS 'auth_string'
DEFAULT ROLE: the default role that is assigned to the user.

Examples

Create a user without a password and do not specify a host.
```
CREATE USER 'jack';
```
Create a user with a plaintext password and allow the user to log on from 172.10.**.**.
```
CREATE USER 'jack'@'172.10.**.**' IDENTIFIED WITH mysql_native_password BY '123456';
```
Create a user with a ciphertext password and allow the user to log on from 172.10.**.**.
```
CREATE USER 'jack'@'172.10.**.**' IDENTIFIED BY PASSWORD '6BB4837EB74329105EE4568DDA7DC67ED2CA****';
```
Note
You can obtain the ciphertext of a password by using PASSWORD(). Example: SELECT PASSWORD('123456');.
Create a user that can log on from the 192.168 subnet and assign the example_role role to the user.
```
CREATE USER 'jack'@'192.168.%' DEFAULT ROLE 'example_role';
```

Create a user that can log on from the example_domain domain.

CREATE USER 'jack'@['example_domain'] IDENTIFIED BY '12345';

Change the password of a user

Important

Users who have the ADMIN permission or users who have the GRANT permission at the global level can set the passwords of any users.
Regular users can set the password for their own user identity. The user identity can be obtained by running the SELECT CURRENT_USER(); command.
Users who have the GRANT permission at a non-global level cannot set the password of an existing user. These users can specify the password only when they create a user.
Only the root user can reset the password of the root user.

Syntax

SET PASSWORD [FOR user_identity] = [PASSWORD('plain password')]|['hashed password'];

Examples

Change the password of the current user.

SET PASSWORD = PASSWORD('123456');
SET PASSWORD = '6BB4837EB74329105EE4568DDA7DC67ED2CA****';

Change the password of a specific user.

SET PASSWORD FOR 'jack'@'192.%' = PASSWORD('123456');
SET PASSWORD FOR 'jack'@['domain'] = '6BB4837EB74329105EE4568DDA7DC67ED2CA****';

Note

You can obtain the ciphertext of a password by using PASSWORD().

Delete a user

Important

Users who have the ADMIN permission can delete any users.

DROP USER 'user_identity';

Grant permissions

Important

Users who have the ADMIN permission or users who have the GRANT permission at the global level can grant permissions to any users.
Users who have the GRANT permission at the database level can grant permissions on a specified database to any users.
Users who have the GRANT permission at the table level can grant permissions on a specified table in a specified database to any users.
The ADMIN_PRIV permission can be granted to or revoked from a user only at the global level.
The GRANT_PRIV permission at the global level is equivalent to the ADMIN_PRIV permission. This is because users who have the GRANT_PRIV permission at the global level can grant any permissions. Exercise caution when you grant the GRANT_PRIV permission at the global level to a user.

StarRocks 3.X

Grant the read permissions on all databases and all tables in the databases to a specified user.
```
GRANT SELECT ON *.* TO 'jack'@'%';
```
Grant the import permissions on the database db1 and all tables in the database to a specified role.
```
GRANT INSERT ON db1.* TO ROLE '<role_name>';
```
Grant usage permissions on all resources to a specified user.
```
GRANT USAGE ON RESOURCE * TO 'jack'@'%';
```

StarRocks 2.X

Grant permissions on a specified database or table to a specified user.

GRANT privilege_list ON db_name[.tbl_name] TO user_identity [ROLE role_name];

Grant permissions on a specified resource to a specified user.

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

Parameters:

privilege_list: the permissions to be granted. Separate multiple permissions with commas (,). The following permissions are supported:
- NODE_PRIV: the permission to modify node configurations. Users who have this permission can add, remove, and unpublish frontend (FE) nodes, backend (BE) nodes, and brokers. This permission can be granted only to the root user.
- GRANT_PRIV: the permission to change permissions. Users who have this permission can grant permissions to and revoke permissions from other users, and create, delete, and modify users or roles.
- SELECT_PRIV: the read-only permission on databases and tables.
- LOAD_PRIV: the write permission on databases and tables. Users who have this permission can perform operations such as LOAD, INSERT, and DELETE.
- ALTER_PRIV: the permission to modify databases and tables. Users who have this permission can rename databases and tables, add, remove, and change columns, and create and delete partitions.
- CREATE_PRIV: the permission to create databases, tables, and views.
- DROP_PRIV: the permission to drop databases, tables, and views.
- USAGE_PRIV: the permission to use resources.
db_name: the name of the database.
tbl_name: the name of the table.
user_identity: the identity of the user. Set the value in the format of username@'userhost' or username@['domain'].
ROLE: the role to which the permissions are granted. If the specified role does not exist, a role is automatically created.

Revoke permissions

Important

Users who have the ADMIN permission or users who have the GRANT permission at the global level can revoke permissions from any users.
Users who have the GRANT permission at the database level can revoke permissions on a specified database from any users.
Users who have the GRANT permission at the table level can revoke permissions on a specified table in a specified database from any users.

StarRocks 3.X

Revoke the SELECT permission on a specified table from a specified user.
```
REVOKE SELECT ON TABLE sr_member FROM USER 'jack'@'172.10.**.**';
```
Revoke the USAGE permission on a specified resource from a specified role.
```
REVOKE USAGE ON RESOURCE '<resource_name>' FROM ROLE '<role_name>';
```

StarRocks 2.X

Revoke permissions on a specified database or table from a specified user.

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

Revoke permissions on a specified resource from a specified user.

REVOKE privilege_list ON RESOURCE resource_name FROM user_identity [ROLE role_name];

Create a role

Important

Only users who have the ADMIN permission can create a role.

You can grant permissions to a created role. The user to whom the role is assigned has the permissions that are granted to the role.

CREATE ROLE <role_name>;

View roles

SHOW ROLES;

Delete a role

Important

Users who have the GRANT_PRIV or ADMIN_PRIV permission can delete a role.

DROP ROLE <role_name>;

View user properties

SHOW PROPERTY [FOR user] [LIKE key];

Parameters:

user: the username.
key: the keyword of the properties that you want to view.

Examples:

View the properties of a specified user.
```
SHOW PROPERTY FOR 'jack';
```
View the properties related to load_cluster of a specified user.
```
SHOW PROPERTY FOR 'jack' LIKE '%load_cluster%';
```