Skip to main content

ALTER USER

Changes ClickHouse user accounts.

Syntax:

ALTER USER [IF EXISTS] name1 [ON CLUSTER cluster_name1] [RENAME TO new_name1]
        [, name2 [ON CLUSTER cluster_name2] [RENAME TO new_name2] ...]
    [NOT IDENTIFIED | IDENTIFIED {[WITH {no_password | plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}] BY {'password' | 'hash'}} | {WITH ldap SERVER 'server_name'} | {WITH kerberos [REALM 'realm']} | {WITH ssl_certificate CN 'common_name' | SAN 'TYPE:subject_alt_name'}]
    [[ADD | DROP] HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [VALID UNTIL datetime]
    [DEFAULT ROLE role [,...] | ALL | ALL EXCEPT role [,...] ]
    [GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY | WRITABLE] | PROFILE 'profile_name'] [,...]

To use ALTER USER you must have the ALTER USER privilege.

GRANTEES Clause​

Specifies users or roles which are allowed to receive privileges from this user on the condition this user has also all required access granted with GRANT OPTION. Options of the GRANTEES clause:

  • user — Specifies a user this user can grant privileges to.
  • role — Specifies a role this user can grant privileges to.
  • ANY — This user can grant privileges to anyone. It's the default setting.
  • NONE — This user can grant privileges to none.

You can exclude any user or role by using the EXCEPT expression. For example, ALTER USER user1 GRANTEES ANY EXCEPT user2. It means if user1 has some privileges granted with GRANT OPTION it will be able to grant those privileges to anyone except user2.

Examples​

Set assigned roles as default:

ALTER USER user DEFAULT ROLE role1, role2

If roles aren’t previously assigned to a user, ClickHouse throws an exception.

Set all the assigned roles to default:

ALTER USER user DEFAULT ROLE ALL

If a role is assigned to a user in the future, it will become default automatically.

Set all the assigned roles to default, excepting role1 and role2:

ALTER USER user DEFAULT ROLE ALL EXCEPT role1, role2

Allows the user with john account to grant his privileges to the user with jack account:

ALTER USER john GRANTEES jack;