数据库进阶玩法

1. 创建用户并限制登录来源

语句：

1
CREATE USER 'UserZood'@'192.168.20.%' IDENTIFIED BY 'secure_password';

详细说明：

  • **CREATE USER**：用于创建一个新的 MySQL 用户。

  • 'UserZood'@'192.168.20.%'

    ：

    • 'UserZood' 是用户名。
    • '192.168.20.%' 表示此用户只能从内网的 192.168.20.0/24 段登录（% 是通配符，匹配任何主机）。

  • **IDENTIFIED BY**：为用户设置初始密码。

注意事项：

  • 如果不加限制，默认允许从任何 IP 地址登录，表示为 'UserZood'@'%'。

  • 创建用户时，若主机部分有误，可以通过以下语句删除用户：

    1
    DROP USER 'UserZood'@'192.168.20.%';

2. 设置密码过期时间

语句：

1
ALTER USER 'UserZood'@'192.168.20.%' PASSWORD EXPIRE INTERVAL 7 DAY;

详细说明：

  • **ALTER USER**：修改现有用户的属性。

  • PASSWORD EXPIRE INTERVAL 7 DAY

    ：指定密码有效期为 7 天。

    • 用户在 7 天后需要更改密码，否则无法登录。

可选的密码策略（MySQL 8.0 及更高版本）：

  • 强制用户在下次登录时更改密码

    ：

    1
    ALTER USER 'UserZood'@'192.168.20.%' PASSWORD EXPIRE;

  • 启用密码历史记录，防止用户重复使用旧密码

    ：

    1
    SET GLOBAL password_history = 3; -- 记录 3 个旧密码

  • 设置密码复杂性要求

    ：

    1
    2
    SET GLOBAL validate_password.policy = 'MEDIUM';
    SET GLOBAL validate_password.length = 12; -- 最少 12 个字符

3. 限制更新操作次数

语句：

1
GRANT USAGE ON *.* TO 'UserZood'@'192.168.20.%' WITH MAX_UPDATES_PER_HOUR 10;

详细说明：

  • **GRANT USAGE**：授予用户使用数据库的基础权限，不包括数据操作权限（如 SELECT、INSERT 等）。

  • WITH MAX_UPDATES_PER_HOUR

    ：限制用户每小时能执行的 

    1
    UPDATE

    操作次数。

    • 这里设置为 10，即用户每小时最多更新 10 条记录。

实际场景：

  • 需要确保用户有 

    1
    UPDATE

    权限，具体表的权限需要单独授予，例如：

    1
    GRANT UPDATE ON my_database.my_table TO 'UserZood'@'192.168.20.%';

4. 限制查询操作次数

语句：

1
GRANT USAGE ON *.* TO 'UserZood'@'192.168.20.%' WITH MAX_QUERIES_PER_HOUR 30;

详细说明：

  • WITH MAX_QUERIES_PER_HOUR

    ：限制用户每小时最多执行的查询语句数量。

    • 这里设置为 30，即用户每小时最多执行 30 次查询操作。

组合限制：

  • WITH

    1
    2
    3
    4
    5
    6
    7
    8

     子句支持组合多个限制：

    ```sql
    GRANT USAGE ON *.* TO 'UserZood'@'192.168.20.%'
    WITH MAX_QUERIES_PER_HOUR 30
         MAX_UPDATES_PER_HOUR 10
         MAX_CONNECTIONS_PER_HOUR 5;
    

- `MAX_CONNECTIONS_PER_HOUR`：限制用户每小时的连接次数。

5. 查询资源限制配置

语句：

1
2
3
SELECT User, Host, max_updates_per_hour, max_queries_per_hour, max_connections_per_hour
FROM mysql.user
WHERE User = 'UserZood';

说明：

  • 查询用户的资源限制配置，以确认设置是否生效。
  • 表 mysql.user 存储了用户的权限和资源限制。

6. 刷新权限

语句：

1
FLUSH PRIVILEGES;

说明：

  • 修改用户权限或资源限制后，使用此命令使更改立即生效。
  • 大多数情况下，CREATE USER 和 GRANT 语句会自动刷新权限，但手动刷新能确保一致性。

7. 扩展：使用触发器实现更灵活的限制

如果你的 MySQL 版本不支持上述资源限制，可以使用触发器实现自定义限制。例如：

  • 限制每小时的更新操作：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    DELIMITER //
    CREATE TRIGGER limit_update_count
    BEFORE UPDATE ON my_database.my_table
    FOR EACH ROW
    BEGIN
        DECLARE cnt INT;
        SET @current_hour = HOUR(NOW());
        SELECT COUNT(*) INTO cnt
        FROM update_log
        WHERE user = USER() AND hour = @current_hour;
        IF cnt >= 10 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Update limit reached for this hour';
        END IF;
    END;
    //
    DELIMITER ;

总结

  1. 使用 GRANT USAGE 和 WITH 语句简单直观，但需要确保 MySQL 版本支持这些资源限制功能。
  2. 可以结合 ALTER USER 和全局变量实现密码策略。
  3. 使用触发器或审计日志可以实现更灵活的业务规则限制，但复杂度较高，适用于更定制化的需求。