CREATE USER

Introduced or updated: v1.2.703

Creates a SQL user for connecting to Databend. Users must be granted appropriate privileges to access databases and perform operations.

See also:

• GRANT
• ALTER USER
• DROP USER

Syntax

CREATE [ OR REPLACE ] USER <name> IDENTIFIED [ WITH <auth_type> ] BY '<password>' 
[ WITH MUST_CHANGE_PASSWORD = true | false ]
[ WITH SET PASSWORD POLICY = '<policy_name>' ]
[ WITH SET NETWORK POLICY = '<policy_name>' ]
[ WITH DEFAULT_ROLE = '<role_name>' ]
[ WITH DISABLED = true | false ]

Parameters:

• <name>: Username (cannot contain single quotes, double quotes, backspace, or form feed characters)
• <auth_type>: Authentication type - double_sha1_password (default), sha256_password, or no_password
• MUST_CHANGE_PASSWORD: When true, user must change password at first login
• DEFAULT_ROLE: Sets default role (role must be explicitly granted to take effect)
• DISABLED: When true, user is created in disabled state and cannot log in

Examples

Example 1: Create User and Grant Database Privileges

Create a user and grant database privileges:

-- Create a new user
CREATE USER data_analyst IDENTIFIED BY 'secure_password123';

-- Grant database privileges to the user
GRANT SELECT, INSERT ON default.* TO data_analyst;

Verify the user and permissions:

SHOW GRANTS FOR data_analyst;
+---------------------------------------------------+
| Grants                                            |
+---------------------------------------------------+
| GRANT SELECT,INSERT ON 'default'.* TO 'data_analyst'@'%' |
+---------------------------------------------------+

Example 2: Create User and Grant Role

Create a user and assign a role with specific privileges:

-- Create a role with specific privileges
CREATE ROLE analyst_role;
GRANT SELECT ON *.* TO ROLE analyst_role;
GRANT INSERT ON default.* TO ROLE analyst_role;

-- Create user and grant the role
CREATE USER john_analyst IDENTIFIED BY 'secure_pass456';
GRANT ROLE analyst_role TO john_analyst;

Verify the role assignment:

SHOW GRANTS FOR john_analyst;
+------------------------------------------+
| Grants                                   |
+------------------------------------------+
| GRANT SELECT ON *.* TO 'analyst_role'    |
| GRANT INSERT ON 'default'.* TO 'analyst_role' |
+------------------------------------------+

Example 3: Create Users with Different Authentication Types

-- Create user with default authentication
CREATE USER user1 IDENTIFIED BY 'abc123';

-- Create user with SHA256 authentication
CREATE USER user2 IDENTIFIED WITH sha256_password BY 'abc123';

Example 4: Create Users with Special Configurations

-- Create user with password change requirement
CREATE USER new_employee IDENTIFIED BY 'temp123' WITH MUST_CHANGE_PASSWORD = true;

-- Create user in disabled state
CREATE USER temp_user IDENTIFIED BY 'abc123' WITH DISABLED = true;

-- Create user with default role (role must be granted separately)
CREATE USER manager IDENTIFIED BY 'abc123' WITH DEFAULT_ROLE = 'admin';
GRANT ROLE admin TO manager;