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

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.
Sample commands:
  • 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 obtain the ciphertext of a password by using PASSWORD().
  • 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 password 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 permissions 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'];
Sample commands:
  • Change the password of the current user.
    SET PASSWORD = PASSWORD('123456')
    SET PASSWORD = '6BB4837EB74329105EE4568DDA7DC67ED2CA****'
  • Change the password of the specified user.
    SET PASSWORD FOR 'jack'@'192.%' = PASSWORD('123456')
    SET PASSWORD FOR 'jack'@['domain'] = '6BB4837EB74329105EE4568DDA7DC67ED2CA****'
Note You 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 the specified database to any users.
  • Users who have the GRANT permission at the table level can grant permissions on the specified table in the 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 have the permissions to grant any permissions. Exercise caution when you grant the GRANT_PRIV permission at the global level to a user.
  • Syntax for granting permissions at the database or table level to the specified user:
    GRANT privilege_list ON db_name[.tbl_name] TO user_identity [ROLE role_name];
  • Syntax for granting permissions on the specified resource to the 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 change a node. Users who have this permission can add, delete, and unpublish frontends (FEs), backends (BEs), 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 users, create, delete, and change users, and add, remove, and change roles.
    • SELECT_PRIV: the read-only permissions on databases and tables.
    • LOAD_PRIV: the write permissions on databases and tables, 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 delete 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 the specified database from any users.
  • Users who have the GRANT permission at the table level can revoke permissions on the specified table in the specified database from any users.
  • Syntax for revoking permissions at the database or table level from the specified user:
    REVOKE privilege_list ON db_name[.tbl_name] FROM user_identity [ROLE role_name];
  • Syntax for revoking permissions on the specified resources from the 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.
Sample commands:
  • View the properties of the specified user.
    SHOW PROPERTY FOR 'jack';
  • View the properties related to load_cluster of the specified user.
    SHOW PROPERTY FOR 'jack' LIKE '%load_cluster%';