CREATE USER
引入或更新于:v1.2.703
创建用于连接 Databend 的 SQL 用户。用户必须被授予适当权限,才能访问数据库并执行操作。
另请参阅:
语法
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 ]
参数:
<name>
:用户名(不能包含单引号、双引号、退格符或换页符)<auth_type>
:认证类型 -double_sha1_password
(默认)、sha256_password
或no_password
MUST_CHANGE_PASSWORD
:为true
时,用户首次登录必须修改密码DEFAULT_ROLE
:设置默认角色(必须先显式授予该角色才能生效)DISABLED
:为true
时,用户创建后即被禁用,无法登录
示例
示例 1:创建用户并授予数据库权限
创建用户并授予数据库权限:
-- 创建新用户
CREATE USER data_analyst IDENTIFIED BY 'secure_password123';
-- 授予数据库权限
GRANT SELECT, INSERT ON default.* TO data_analyst;
验证用户及权限:
SHOW GRANTS FOR data_analyst;
+---------------------------------------------------+
| Grants |
+---------------------------------------------------+
| GRANT SELECT,INSERT ON 'default'.* TO 'data_analyst'@'%' |
+---------------------------------------------------+
示例 2:创建用户并授予角色
创建用户并分配具有特定权限的角色:
-- 创建角色并授予权限
CREATE ROLE analyst_role;
GRANT SELECT ON *.* TO ROLE analyst_role;
GRANT INSERT ON default.* TO ROLE analyst_role;
-- 创建用户并授予角色
CREATE USER john_analyst IDENTIFIED BY 'secure_pass456';
GRANT ROLE analyst_role TO john_analyst;
验证角色分配:
SHOW GRANTS FOR john_analyst;
+------------------------------------------+
| Grants |
+------------------------------------------+
| GRANT SELECT ON *.* TO 'analyst_role' |
| GRANT INSERT ON 'default'.* TO 'analyst_role' |
+------------------------------------------+
示例 3:创建不同认证类型的用户
-- 使用默认认证创建用户
CREATE USER user1 IDENTIFIED BY 'abc123';
-- 使用 SHA256 认证创建用户
CREATE USER user2 IDENTIFIED WITH sha256_password BY 'abc123';
示例 4:创建具有特殊配置的用户
-- 创建需修改密码的用户
CREATE USER new_employee IDENTIFIED BY 'temp123' WITH MUST_CHANGE_PASSWORD = true;
-- 创建禁用状态的用户
CREATE USER temp_user IDENTIFIED BY 'abc123' WITH DISABLED = true;
-- 创建带默认角色的用户(需单独授予角色)
CREATE USER manager IDENTIFIED BY 'abc123' WITH DEFAULT_ROLE = 'admin';
GRANT ROLE admin TO manager;