Configure StarRocks User Permissions and Access Control in EMR - E-MapReduce

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

Some SQL syntax and behavior differ between StarRocks 2.x and 3.x. Where applicable, each section provides version-specific syntax. For a full command reference, see the StarRocks 2.5 command reference or the StarRocks 3.2 command reference.

Who can perform each operation

Before making changes, confirm that your account has the required permission for the operation.

Operation	Required permission
Create a user	ADMIN permission, or GRANT permission at any level
Change another user's password	ADMIN permission, or GRANT permission at the global level
Change your own password	Any authenticated user
Reset the root user's password	Root user only
Delete a user	ADMIN permission
Grant permissions	ADMIN permission, or GRANT permission at the matching scope (global / database / table)
Revoke permissions	ADMIN permission, or GRANT permission at the matching scope (global / database / table)
Create a role	ADMIN permission
Delete a role	GRANT_PRIV or ADMIN_PRIV permission

Important

The GRANT permission at the global level is equivalent to ADMIN permission, because it allows granting any permission to any user. Grant it with caution.

Important

The ADMIN_PRIV permission can be granted to or revoked from a user only at the global level.

Manage users

Create a user

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

Parameters

Parameter	Description
`user_identity`	The user identity. Use the format `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 assigned to the user at login.

Examples

Create a user without a password:

CREATE USER 'jack';

Create a user with a plaintext password, restricted to logins from 172.10..:

CREATE USER 'jack'@'172.10.**.**' IDENTIFIED WITH mysql_native_password BY '123456';

Create a user with a ciphertext password, restricted to logins from 172.10..:

CREATE USER 'jack'@'172.10.**.**' IDENTIFIED BY PASSWORD '6BB4837EB74329105EE4568DDA7DC67ED2CA****';

Get the ciphertext for a password with PASSWORD(). For example: SELECT PASSWORD('123456');

Create a user restricted to the 192.168 subnet and assign the example_role role:

CREATE USER 'jack'@'192.168.%' DEFAULT ROLE 'example_role';

Create a user restricted to logins from the example_domain domain:

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

Change a user's password

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

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****';

Get the current user's identity with SELECT CURRENT_USER();. Get the ciphertext for a password with PASSWORD().

Delete a user

DROP USER 'user_identity';

View user properties

SHOW PROPERTY [FOR user] [LIKE key];

Parameter	Description
`user`	The username.
`key`	A keyword to filter the properties.

View all properties for a specific user:

SHOW PROPERTY FOR 'jack';

View properties related to load_cluster for a specific user:

SHOW PROPERTY FOR 'jack' LIKE '%load_cluster%';

Grant and revoke permissions

Grant permissions

StarRocks 3.x

Grant the SELECT privilege on all databases and tables to a user:

GRANT SELECT ON *.* TO 'jack'@'%';

Grant the INSERT privilege on all tables in db1 to a role:

GRANT INSERT ON db1.* TO ROLE '<role_name>';

Grant the USAGE privilege on all resources to a user:

GRANT USAGE ON RESOURCE * TO 'jack'@'%';

StarRocks 2.x

Grant privileges on a database or table to a user or role:

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

Grant privileges on a resource to a user or role:

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

If the role specified in ROLE role_name does not exist, it is created automatically.

Privilege reference (StarRocks 2.x)

Privilege	Description
`NODE_PRIV`	Modify node configurations: add, remove, and unpublish frontend (FE) nodes, backend (BE) nodes, and brokers. Can only be granted to the root user.
`GRANT_PRIV`	Change permissions: grant or revoke permissions, and create, delete, or modify users and roles.
`SELECT_PRIV`	Read-only access to databases and tables.
`LOAD_PRIV`	Write access to databases and tables: LOAD, INSERT, and DELETE operations.
`ALTER_PRIV`	Modify structure of databases and tables: rename databases and tables, add, remove, or change columns, and create or delete partitions.
`CREATE_PRIV`	Create databases, tables, and views.
`DROP_PRIV`	Drop databases, tables, and views.
`USAGE_PRIV`	Use resources.

Revoke permissions

StarRocks 3.x

Revoke the SELECT privilege on a table from a user:

REVOKE SELECT ON TABLE sr_member FROM USER 'jack'@'172.10.**.**';

Revoke the USAGE privilege on a resource from a role:

REVOKE USAGE ON RESOURCE '<resource_name>' FROM ROLE '<role_name>';

StarRocks 2.x

Revoke privileges on a database or table from a user or role:

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

Revoke privileges on a resource from a user or role:

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

Manage roles

Roles let you group a set of privileges and assign them to multiple users. A user inherits all privileges granted to their assigned roles.

Create a role and assign it to a user

Use the following workflow to create a role, grant it privileges, and assign it to a user:

Create the role.
```
CREATE ROLE <role_name>;
```

Grant the required privileges to the role.

-- StarRocks 3.x: grant INSERT on all tables in db1 to the role
GRANT INSERT ON db1.* TO ROLE '<role_name>';

-- StarRocks 2.x: grant privileges on a database or table to the role
GRANT privilege_list ON db_name[.tbl_name] TO ROLE role_name;

Assign the role to the user when creating the account, or update an existing user's default role.
```
-- Assign role at user creation
CREATE USER 'jack'@'192.168.%' DEFAULT ROLE 'example_role';
```

View roles

SHOW ROLES;

Delete a role

DROP ROLE <role_name>;