Operations with users

CREATE USER — Create a new user
ALTER USER — Change user attributes
DROP USER — Reset user
DESCRIBE USER — Outputs information about the user
SHOW USERS — Outputs a list of all users
GRANT ROLE — Assign roles to users
REVOKE ROLE — Revoke roles from users

Create a new user

CREATE [OR REPLACE] USER [IF NOT EXISTS] <user_name>
    DEFAULT WORKER POOL <pool_id>
    IDENTIFIED BY (TRUST <ip_address> | PASSWORD <user_password>);

Creates a new user with the specified name.

Parameter `SET DEFAULT WORKER POOL`

The obligatory parameter SET DEFAULT WORKER POOL <pool_id> sets the default compute pool <pool_id> for the specified user.

Parameter `IDENTIFIED BY TRUST`

The obligatory parameter IDENTIFIED BY TRUST <ip_address> sets the authentication of the specified user through the IP address <ip_address>.

Modifier `OR REPLACE`

If the optional OR REPLACE modifier is specified, this action is equivalent to deleting the current user and creating a new user with the same username. the current user and creating a new user with the same username. The newly created user does not inherit any roles or attributes.

Modifier `IF NOT EXISTS`

If the optional IF NOT EXISTS modifier is specified, the query will only be executed if the user named <user_name> does not exist, otherwise nothing will happen.

The OR REPLACE and IF NOT EXISTS modifiers are mutually exclusive. Specifying them both will result in an error.

See examples

CREATE USER tengri_user IDENTIFIED BY TRUST '127.0.0.1';

ALTER USER tengri_user IDENTIFIED BY TRUST '127.0.0.1';

Parameter `IDENTIFIED BY PASSWORD`

The optional parameter IDENTIFIED BY PASSWORD <user_password> sets the authentication of the specified user through the password <user_password> using the SCRAM-SHA-256 algorithm.

See examples

CREATE USER tengri_user IDENTIFIED BY PASSWORD 'QWERTY123456';

ALTER USER tengri_user IDENTIFIED BY PASSWORD 'QWERTY123456';

Change user attributes

ALTER USER [IF EXISTS] <user_name>
    RENAME TO <new_name>;

ALTER USER [IF EXISTS] <user_name>
    SET DEFAULT WORKER POOL <pool_id>;

ALTER USER [IF EXISTS] <user_name>
    IDENTIFIED BY (TRUST <ip_address> | PASSWORD <user_password>);

Modifies the user’s attributes.

RENAME TO <new_name> is an atomic operation that renames the user to a new name. No other properties of this user (roles granted, privileges, etc.) are changed.
SET DEFAULT WORKER POOL <pool_id> — see Parameter SET DEFAULT WORKER POOL.
IDENTIFIED BY TRUST <ip_address> — see Parameter IDENTIFIED BY TRUST.
IDENTIFIED BY PASSWORD <user_password> — see Parameter IDENTIFIED BY PASSWORD.

The optional IF EXISTS modifier restricts the query to only those cases in which the specified object exists.

Reset user

DROP USER [IF EXISTS] <user_name>;

Completely removes the user from the system.

Nothing is saved, including granted roles and access rights.

It is possible that some objects may lose access rights.

The optional IF EXISTS modifier restricts the query to only those cases in which the specified object exists.

Outputs information about the user

DESC[RIBE] USER <user_name>;

Outputs all information about the user, including their attributes and default values.

Outputs a list of all users

SHOW USERS;

Outputs a list of all users.

Output format:

+----------+---------------------+-------------+--------+--------+---------------+------------+
| username | default_worker_pool | auth_method | active | system | query_timeout | spill_size |
+----------+---------------------+-------------+--------+--------+---------------+------------+
| ...      | ...                 | ...         | ...    | ...    | ...           | ...        |
+----------+---------------------+-------------+--------+--------+---------------+------------+

Assign roles to users

GRANT ROLE <role_name> TO <user_name>;

Assigns the specified role to the specified user.

Revoke roles from users

REVOKE ROLE <role_name> FROM <user_name>;

Revokes the specified role from the specified user.