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
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 ofusername@'userhost'
orusername@['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****';
NoteYou 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
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****';
You can obtain the ciphertext of a password by using PASSWORD()
.
Delete a user
Users who have the ADMIN permission can delete any users.
DROP USER 'user_identity';
Grant permissions
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 ofusername@'userhost'
orusername@['domain']
.ROLE
: the role to which the permissions are granted. If the specified role does not exist, a role is automatically created.
Revoke permissions
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
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
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%';