TDSQL 用户创建与删除

用户创建

1. 基本语法

创建用户

sql

-- 基本语法
CREATE USER [IF NOT EXISTS] 'username'@'host' [IDENTIFIED BY 'password'] [OPTIONS];

-- 示例1：创建本地用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'App@123456';

-- 示例2：创建允许远程访问的用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'App@123456';

-- 示例3：创建只读用户
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'Read@123456';

-- 示例4：创建带过期时间的用户
CREATE USER 'temp_user'@'%' IDENTIFIED BY 'Temp@123456' PASSWORD EXPIRE INTERVAL 30 DAY;

-- 示例5：创建密码永不过期的用户
CREATE USER 'perm_user'@'%' IDENTIFIED BY 'Perm@123456' PASSWORD EXPIRE NEVER;

密码策略

sql

-- 查看密码策略
SHOW VARIABLES LIKE 'validate_password%';

-- 设置密码策略
SET GLOBAL validate_password_policy = 'MEDIUM';
SET GLOBAL validate_password_length = 8;
SET GLOBAL validate_password_number_count = 1;
SET GLOBAL validate_password_special_char_count = 1;
SET GLOBAL validate_password_mixed_case_count = 1;

2. 用户创建最佳实践

1. 使用强密码

密码长度至少8位
包含大小写字母、数字和特殊字符
避免使用常见密码和弱密码
定期更换密码

2. 限制用户访问范围

sql

-- 限制只能从特定IP访问
CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'App@123456';

-- 限制只能从特定网段访问
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'App@123456';

3. 遵循最小权限原则

只授予用户必要的权限
避免授予不必要的管理员权限
定期审查用户权限

4. 使用角色管理

sql

-- 创建角色
CREATE ROLE 'app_role';

-- 授予角色权限
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_role';

-- 创建用户并授予角色
CREATE USER 'app_user'@'%' IDENTIFIED BY 'App@123456';
GRANT 'app_role' TO 'app_user'@'%';

-- 激活角色
SET DEFAULT ROLE 'app_role' FOR 'app_user'@'%';

5. 启用密码过期策略

sql

-- 设置默认密码过期时间
SET GLOBAL default_password_lifetime = 90;

-- 为现有用户设置密码过期时间
ALTER USER 'app_user'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

3. 批量创建用户

使用脚本批量创建

sql

-- 创建用户的存储过程
DELIMITER //
CREATE PROCEDURE create_multiple_users()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE username VARCHAR(50);
    DECLARE password VARCHAR(50);
    
    WHILE i <= 10 DO
        SET username = CONCAT('user_', i);
        SET password = CONCAT('Pass@', LPAD(i, 6, '0'));
        SET @sql = CONCAT('CREATE USER ''', username, '''@''%'' IDENTIFIED BY ''', password, '''');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 调用存储过程
CALL create_multiple_users();

从文件导入用户

bash

# 创建用户文件 users.sql
cat > users.sql << EOF
CREATE USER 'user1'@'%' IDENTIFIED BY 'Pass@000001';
CREATE USER 'user2'@'%' IDENTIFIED BY 'Pass@000002';
CREATE USER 'user3'@'%' IDENTIFIED BY 'Pass@000003';
EOF

# 执行SQL文件
mysql -u root -p < users.sql

用户删除

1. 基本语法

删除用户

sql

-- 基本语法
DROP USER [IF EXISTS] 'username'@'host' [, 'username'@'host'] ...;

-- 示例1：删除本地用户
DROP USER 'app_user'@'localhost';

-- 示例2：删除远程用户
DROP USER 'app_user'@'%';

-- 示例3：批量删除用户
DROP USER 'user1'@'%', 'user2'@'%', 'user3'@'%';

-- 示例4：删除不存在的用户（使用IF EXISTS避免错误）
DROP USER IF EXISTS 'non_existent_user'@'%';

查看当前用户

sql

-- 查看当前登录用户
SELECT USER();

-- 查看当前用户信息
SELECT CURRENT_USER();

-- 查看所有用户
SELECT user, host FROM mysql.user;

2. 用户删除最佳实践

1. 先回收权限再删除用户

sql

-- 回收用户权限
REVOKE ALL PRIVILEGES ON *.* FROM 'app_user'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

-- 删除用户
DROP USER 'app_user'@'%';

2. 检查用户依赖关系

sql

-- 检查用户是否有未完成的会话
SELECT * FROM information_schema.processlist WHERE user = 'app_user';

-- 检查用户是否有正在执行的查询
SHOW PROCESSLIST LIKE 'app_user';

-- 检查用户是否有事件或触发器
SELECT * FROM information_schema.events WHERE definer LIKE 'app_user%';
SELECT * FROM information_schema.triggers WHERE definer LIKE 'app_user%';

3. 终止用户会话

sql

-- 查找用户会话ID
SELECT id FROM information_schema.processlist WHERE user = 'app_user';

-- 终止用户会话
KILL 123; -- 替换为实际会话ID

-- 批量终止用户会话
DELIMITER //
CREATE PROCEDURE kill_user_sessions(IN username VARCHAR(50))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE sess_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM information_schema.processlist WHERE user = username;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO sess_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('KILL ', sess_id);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    
    CLOSE cur;
END //
DELIMITER ;

-- 调用存储过程终止用户会话
CALL kill_user_sessions('app_user');

4. 记录删除操作

sql

-- 启用审计日志
SET GLOBAL audit_log = ON;

-- 记录删除操作到日志表
INSERT INTO audit_log (action, username, host, timestamp) 
VALUES ('DROP USER', 'app_user', '%', NOW());

5. 定期清理无用用户

sql

-- 查找长期未使用的用户（例如：90天未登录）
SELECT user, host, last_login FROM mysql.user WHERE last_login < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- 删除长期未使用的用户
DROP USER 'unused_user'@'%';

用户密码管理

1. 修改用户密码

基本语法

sql

-- 语法1：使用ALTER USER
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

-- 语法2：使用SET PASSWORD
SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

-- 语法3：使用UPDATE语句（不推荐）
UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE user = 'username' AND host = 'host';
FLUSH PRIVILEGES;

-- 示例：修改用户密码
ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewPass@123456';

强制用户修改密码

sql

-- 创建用户并强制第一次登录修改密码
CREATE USER 'temp_user'@'%' IDENTIFIED BY 'Temp@123456' PASSWORD EXPIRE;

-- 强制现有用户修改密码
ALTER USER 'app_user'@'%' PASSWORD EXPIRE;

2. 密码重置

重置root密码

sql

-- 方法1：使用mysqladmin
mysqladmin -u root -p'old_password' password 'new_password';

-- 方法2：使用ALTER USER
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewRoot@123456';

-- 方法3：跳过权限检查重置（适用于忘记密码情况）
# 1. 停止MySQL服务
# 2. 以跳过权限检查方式启动
mysqld_safe --skip-grant-tables --skip-networking &
# 3. 登录并修改密码
mysql -u root
UPDATE mysql.user SET authentication_string = PASSWORD('NewRoot@123456') WHERE user = 'root' AND host = 'localhost';
FLUSH PRIVILEGES;
# 4. 重启MySQL服务

3. 密码强度验证

启用密码验证插件

sql

-- 安装密码验证插件
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- 查看插件状态
SHOW PLUGINS LIKE 'validate_password';

-- 配置密码验证规则
SET GLOBAL validate_password_policy = 'STRONG';
SET GLOBAL validate_password_length = 10;
SET GLOBAL validate_password_number_count = 2;
SET GLOBAL validate_password_special_char_count = 2;
SET GLOBAL validate_password_mixed_case_count = 2;

自定义密码验证函数

sql

-- 创建密码强度验证函数
DELIMITER //
CREATE FUNCTION validate_password_strength(password VARCHAR(255)) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE strength INT DEFAULT 0;
    
    -- 检查密码长度
    IF LENGTH(password) >= 8 THEN
        SET strength = strength + 1;
    END IF;
    
    -- 检查是否包含数字
    IF password REGEXP '[0-9]' THEN
        SET strength = strength + 1;
    END IF;
    
    -- 检查是否包含小写字母
    IF password REGEXP '[a-z]' THEN
        SET strength = strength + 1;
    END IF;
    
    -- 检查是否包含大写字母
    IF password REGEXP '[A-Z]' THEN
        SET strength = strength + 1;
    END IF;
    
    -- 检查是否包含特殊字符
    IF password REGEXP '[^a-zA-Z0-9]' THEN
        SET strength = strength + 1;
    END IF;
    
    RETURN strength;
END //
DELIMITER ;

-- 使用自定义函数验证密码
SELECT validate_password_strength('WeakPass123'); -- 返回3
SELECT validate_password_strength('Strong@Pass123'); -- 返回5

用户权限管理

1. 授予权限

基本语法

sql

-- 基本语法
GRANT privileges ON database.table TO 'username'@'host' [WITH GRANT OPTION];

-- 示例1：授予数据库所有权限
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'%';

-- 示例2：授予表的特定权限
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.users TO 'app_user'@'%';

-- 示例3：授予存储过程执行权限
GRANT EXECUTE ON PROCEDURE app_db.sp_get_users TO 'app_user'@'%';

-- 示例4：授予带有GRANT OPTION的权限
GRANT SELECT ON app_db.* TO 'app_user'@'%' WITH GRANT OPTION;

常用权限类型

权限类型	描述
ALL PRIVILEGES	所有权限
SELECT	查询权限
INSERT	插入权限
UPDATE	更新权限
DELETE	删除权限
CREATE	创建权限
DROP	删除权限
ALTER	修改权限
INDEX	索引权限
GRANT OPTION	授权权限
PROCESS	进程权限
FILE	文件权限
SUPER	超级权限

2. 回收权限

基本语法

sql

-- 基本语法
REVOKE privileges ON database.table FROM 'username'@'host';

-- 示例1：回收数据库所有权限
REVOKE ALL PRIVILEGES ON app_db.* FROM 'app_user'@'%';

-- 示例2：回收表的特定权限
REVOKE DELETE ON app_db.users FROM 'app_user'@'%';

-- 示例3：回收GRANT OPTION
REVOKE GRANT OPTION ON *.* FROM 'app_user'@'%';

3. 查看用户权限

基本语法

sql

-- 方法1：使用SHOW GRANTS
SHOW GRANTS FOR 'app_user'@'%';

-- 方法2：使用information_schema
SELECT * FROM information_schema.user_privileges WHERE grantee = '''app_user''@''%''';
SELECT * FROM information_schema.schema_privileges WHERE grantee = '''app_user''@''%''';
SELECT * FROM information_schema.table_privileges WHERE grantee = '''app_user''@''%''';

-- 示例：查看用户的所有权限
SHOW GRANTS FOR 'app_user'@'%';

用户管理最佳实践

1. 命名规范

使用有意义的用户名，便于识别
结合业务系统名称和角色
示例：app_erp_readonly、report_user

2. 权限管理

遵循最小权限原则
定期审查用户权限
及时回收不再需要的权限
使用角色管理权限

3. 安全管理

使用强密码策略
启用密码过期机制
限制用户访问IP
定期更换密码
监控用户活动

4. 审计和监控

启用审计日志
监控用户登录和操作
记录权限变更
定期生成用户权限报告

5. 备份和恢复

定期备份mysql.user表
记录用户创建和删除操作
制定用户恢复计划

常见问题及解决方案

Q1: 如何创建允许所有IP访问的用户？

A1: 解决方案：

使用通配符%表示所有IP

示例：

sql

CREATE USER 'app_user'@'%' IDENTIFIED BY 'App@123456';

Q2: 如何限制用户只能从特定IP访问？

A2: 解决方案：

在host部分指定具体IP

示例：

sql

CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'App@123456';

Q3: 如何查看用户的登录历史？

A3: 解决方案：

启用审计日志
或者使用第三方工具

示例：

sql

-- 启用审计日志
SET GLOBAL audit_log = ON;

-- 查看审计日志
SELECT * FROM mysql.audit_log WHERE action = 'login' AND username = 'app_user';

Q4: 如何删除正在使用的用户？

A4: 解决方案：

先终止用户会话
再删除用户

示例：

sql

-- 终止用户会话
CALL kill_user_sessions('app_user');

-- 删除用户
DROP USER 'app_user'@'%';

Q5: 如何重置忘记的用户密码？

A5: 解决方案：

使用root用户重置
或者跳过权限检查重置

示例：

sql

ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewPass@123456';

Q6: 如何批量删除用户？

A6: 解决方案：

使用脚本批量删除

示例：

sql

-- 批量删除以user_开头的用户
DELIMITER //
CREATE PROCEDURE drop_user_prefix(IN prefix VARCHAR(50))

BEGIN DECLARE done INT DEFAULT FALSE; DECLARE username VARCHAR(50); DECLARE host VARCHAR(50); DECLARE cur CURSOR FOR SELECT user, host FROM mysql.user WHERE user LIKE CONCAT(prefix, '%'); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP
    FETCH cur INTO username, host;
    IF done THEN
        LEAVE read_loop;
    END IF;
    SET @sql = CONCAT('DROP USER ''', username, '''@''', host, '''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END LOOP;

CLOSE cur;

END // DELIMITER ;

-- 调用存储过程 CALL drop_user_prefix('user_');


## 用户管理工具

### 1. 命令行工具

#### mysql
- 用于用户登录和管理
- 示例：
```bash
mysql -u root -p
CREATE USER 'app_user'@'%' IDENTIFIED BY 'App@123456';

mysqladmin

用于管理MySQL服务器

示例：

bash

mysqladmin -u root -p password 'NewRoot@123456';

mysqldump

用于备份用户表

示例：

bash

mysqldump -u root -p mysql user > users_backup.sql;

2. 图形化工具

MySQL Workbench

官方图形化管理工具
支持用户创建、删除和权限管理
提供可视化界面

phpMyAdmin

基于Web的管理工具
支持多用户管理
适合远程管理

Navicat

商业图形化管理工具
支持多种数据库类型
提供丰富的管理功能

3. 自动化工具

Ansible

自动化配置管理工具
用于批量管理用户

示例Playbook：

yaml

- name: Create TDSQL user
  hosts: db_servers
  tasks:
    - name: Create app user
      mysql_user:
        name: app_user
        password: App@123456
        priv: 'app_db.*:ALL'
        host: '%'
        state: present

Puppet

配置管理工具
用于自动化用户管理

示例Manifest：

puppet

mysql_user {
  'app_user@%':
    ensure => present,
    password_hash => mysql_password('App@123456'),
    privileges => ['app_db.*:ALL'],
}

常见问题（FAQ）

Q1: 如何创建允许所有IP访问的用户？

A1: 使用通配符%表示所有IP地址：

sql

CREATE USER 'app_user'@'%' IDENTIFIED BY 'App@123456';

Q2: 如何修改用户密码？

A2: 使用ALTER USER语句修改用户密码：

sql

ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewPass@123456';

Q3: 如何删除正在使用的用户？

A3: 先终止用户会话，再删除用户：

sql

-- 查找用户会话ID
SELECT id FROM information_schema.processlist WHERE user = 'app_user';
-- 终止会话
KILL 123; -- 替换为实际会话ID
-- 删除用户
DROP USER 'app_user'@'%';

Q4: 如何查看用户的权限？

A4: 使用SHOW GRANTS语句查看用户权限：

sql

SHOW GRANTS FOR 'app_user'@'%';

Q5: 如何限制用户只能执行SELECT操作？

A5: 只授予用户SELECT权限：

sql

GRANT SELECT ON app_db.* TO 'readonly_user'@'%';

Q6: 如何创建密码永不过期的用户？

A6: 创建用户时指定PASSWORD EXPIRE NEVER：

sql

CREATE USER 'perm_user'@'%' IDENTIFIED BY 'Perm@123456' PASSWORD EXPIRE NEVER;

TDSQL 用户创建与删除 ​

用户创建 ​

1. 基本语法 ​

创建用户 ​

密码策略 ​

2. 用户创建最佳实践 ​

1. 使用强密码 ​

2. 限制用户访问范围 ​

3. 遵循最小权限原则 ​

4. 使用角色管理 ​

5. 启用密码过期策略 ​

3. 批量创建用户 ​

使用脚本批量创建 ​

从文件导入用户 ​

用户删除 ​

1. 基本语法 ​

删除用户 ​

查看当前用户 ​

2. 用户删除最佳实践 ​

1. 先回收权限再删除用户 ​

2. 检查用户依赖关系 ​

3. 终止用户会话 ​

4. 记录删除操作 ​

5. 定期清理无用用户 ​

用户密码管理 ​

1. 修改用户密码 ​

基本语法 ​

强制用户修改密码 ​

2. 密码重置 ​

重置root密码 ​

3. 密码强度验证 ​

启用密码验证插件 ​

自定义密码验证函数 ​

用户权限管理 ​

1. 授予权限 ​

基本语法 ​

常用权限类型 ​

2. 回收权限 ​

基本语法 ​

3. 查看用户权限 ​

基本语法 ​

用户管理最佳实践 ​

1. 命名规范 ​

2. 权限管理 ​

3. 安全管理 ​

4. 审计和监控 ​

5. 备份和恢复 ​

常见问题及解决方案 ​

Q1: 如何创建允许所有IP访问的用户？ ​

Q2: 如何限制用户只能从特定IP访问？ ​

Q3: 如何查看用户的登录历史？ ​

Q4: 如何删除正在使用的用户？ ​

Q5: 如何重置忘记的用户密码？ ​

Q6: 如何批量删除用户？ ​

mysqladmin ​

mysqldump ​

2. 图形化工具 ​

MySQL Workbench ​

phpMyAdmin ​

Navicat ​

3. 自动化工具 ​

Ansible ​

Puppet ​

常见问题（FAQ） ​

Q1: 如何创建允许所有IP访问的用户？ ​

Q2: 如何修改用户密码？ ​

Q3: 如何删除正在使用的用户？ ​

Q4: 如何查看用户的权限？ ​

Q5: 如何限制用户只能执行SELECT操作？ ​

Q6: 如何创建密码永不过期的用户？ ​

TDSQL 用户创建与删除

用户创建

1. 基本语法

创建用户

密码策略

2. 用户创建最佳实践

1. 使用强密码

2. 限制用户访问范围

3. 遵循最小权限原则

4. 使用角色管理

5. 启用密码过期策略

3. 批量创建用户

使用脚本批量创建

从文件导入用户

用户删除

1. 基本语法

删除用户

查看当前用户

2. 用户删除最佳实践

1. 先回收权限再删除用户

2. 检查用户依赖关系

3. 终止用户会话

4. 记录删除操作

5. 定期清理无用用户

用户密码管理

1. 修改用户密码

基本语法

强制用户修改密码

2. 密码重置

重置root密码

3. 密码强度验证

启用密码验证插件

自定义密码验证函数

用户权限管理

1. 授予权限

基本语法

常用权限类型

2. 回收权限

基本语法

3. 查看用户权限

基本语法

用户管理最佳实践

1. 命名规范

2. 权限管理

3. 安全管理

4. 审计和监控

5. 备份和恢复

常见问题及解决方案

Q1: 如何创建允许所有IP访问的用户？

Q2: 如何限制用户只能从特定IP访问？

Q3: 如何查看用户的登录历史？

Q4: 如何删除正在使用的用户？

Q5: 如何重置忘记的用户密码？

Q6: 如何批量删除用户？

mysqladmin

mysqldump

2. 图形化工具

MySQL Workbench

phpMyAdmin

Navicat

3. 自动化工具

Ansible

Puppet

常见问题（FAQ）

Q1: 如何创建允许所有IP访问的用户？

Q2: 如何修改用户密码？

Q3: 如何删除正在使用的用户？

Q4: 如何查看用户的权限？

Q5: 如何限制用户只能执行SELECT操作？

Q6: 如何创建密码永不过期的用户？