MySQL 权限管理
|
MySQL
|
9

1. MySQL 数据库授权表

MySQL 数据库管理软件将权限信息都存放于 mysql 库下，该库下有一系列授权 表，权限信息都存放于这一系列表中，下面介绍几个常用的授权表：

  • mysal.user
    • 存储内容：用户账户和全局权限
    • 权限范围：
      • 服务器级别的全局权限
      • 影响所有数据库的权限
  • msyql.db
    • 存储内容：数据库级别的权限
    • 权限范围：特定数据库的操作权限
  • tables_priv
    • 存储内容：表级别的权限
    • 权限范围：特定表的操作权限
  • columns_priv
    • 存储内容：列级别的权限
    • 权限范围：表中特定列的操作权限
  • procs_priv
    • 存储内容：存储过程和函数的权限
    • 权限范围：存储过程和函数的执行、修改权限

注意：在 MySQL 中，一个用户是通过 User + Host 的组合唯一标识的。相同的用户名允许登录的来源不同的时，可以看做是不同的用户，可以分别授予不同的权限。例如：

SELECT User, Host FROM mysql.user;

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | localhost |
| alice            | 192.168.% |
| alice            | %         |
+------------------+-----------+
# 这里 alice@192.168.% 和 alice@% 是两个不同的用户账户（即使用户名相同，但允许的登录来源不同）。
Bash

2. 权限相关操作

2.1. 创建用户

create user '用户名'@'主机' identified by '密码';

# 示例：
create user 'alice'@'1.1.1.1' identified by '123';
create user 'alice'@'192.168.1.%' identified by '123';
create user 'alice'@'%' identified by '123';
Bash

2.2. 删除用户

mysql> drop user 'alice'@'1.1.1.1';
Bash

2.3. 授权

GRANT 命令基本语法：

GRANT 权限类型 
    ON 数据库对象
    TO 用户
    
    
    
# 查看 GRANT 命令详细用法
mysql> help GRANT
Bash

常用权限类型：

  • 数据库操作权限
    • ALL PRIVILEGES: 所有权限
    • CREATE: 创建数据库/表
    • ALTER: 修改表结构
    • DROP: 删除数据库/表
    • SHOW VIEW: 查看视图定义
  • 数据操作权限
    • SELECT: 查询数据
    • INSERT: 插入数据
    • UPDATE: 更新数据
    • DELETE: 删除数据
    • EXECUTE: 执行存储过程
  • 管理权限
    • GRANT OPTION: 允许用户授权他人
    • PROXY: 允许代理用户

示例：

全局权限：

# 授予用户 'admin'@'localhost' 对所有数据库和所有表的完全权限，并允许该用户将这些权限授予其他用户。
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' 
WITH GRANT OPTION;

# 授予用户 'alice'@'localhost' 对所有数据库和所有表的只读查询权限。
GRANT SELECT ON *.* TO 'alice'@'localhost';
Bash

数据库级别权限：

# 授予用户 'user'@'%' 对数据库 mydb 中所有表的查询(SELECT)、插入(INSERT)和更新(UPDATE)权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user'@'%';
Bash

表级别权限：

# 授予用户 'user'@'192.168.1.%' 对数据库 mydb 中表 mytable 的查询(SELECT)和插入(INSERT)权限
GRANT SELECT, INSERT ON mydb.mytable TO 'user'@'192.168.1.%';
Bash

列级别权限：

# 授予用户 'user'@'localhost' 对 mydb.mytable 表的列级精细权限，col1 和 col2 两列有查询(SELECT)权限，col1 列有更新(UPDATE)权限。
GRANT SELECT (col1, col2), UPDATE (col1) ON mydb.mytable TO 'user'@'localhost';
Bash

存储过程和函数权限：

# 授予用户 'user'@'localhost' 执行存储过程 mydb.myprocedure 的权限
GRANT EXECUTE ON PROCEDURE mydb.myprocedure TO 'user'@'localhost';
Bash

2.4. 查看权限

# 查看当前用户（自己）权限：
mysql> show grants;
 
# 查看其他 MySQL 用户权限：
mysql> show grants for root@'%';
Bash

2.5. 撤销权限

撤销权限命令 REVOKE 语法与 GRANT 相似，只需将关键字 TO 更换为 FROM。

grant  all on *.* to   dba@localhost;
revoke all on *.* from dba@localhost;
Bash

2.6. 用户资源限制

max_queries_per_hour  # 一个用户每小时可发出的查询数量
max_updates_per_hour  # 一个用户每小时可发出的更新数量
max_connections_per_hour  # 一个用户每小时可连接到服务器的次数
max_user_connections  #  允许同时连接数量

# 示例：
# 创建用户时直接设置 
CREATE USER 'user1'@'localhost' 
IDENTIFIED BY 'password'
WITH 
    MAX_QUERIES_PER_HOUR 1000
    MAX_UPDATES_PER_HOUR 200
    MAX_CONNECTIONS_PER_HOUR 50
    MAX_USER_CONNECTIONS 10;
    
# 修改已有用户的限制
ALTER USER 'user1'@'localhost' 
WITH 
    MAX_QUERIES_PER_HOUR 500
    MAX_USER_CONNECTIONS 5;
    
# 需结合 GRANT 语句精细权限控制
GRANT SELECT ON db1.* TO 'report_user'@'%' 
WITH MAX_QUERIES_PER_HOUR 1000;
Bash

2.7. 针对不同角色的权限分配

MySQL 的角色权限系统（MySQL 8.0+）提供了一种高效的方式来管理用户权限。

角色权限分配：

  • 创建角色
CREATE ROLE 'role_name';
Bash
  • 授予角色权限
GRANT privilege_type ON database.object TO 'role_name';
Bash
  • 将角色分配给用户
GRANT 'role_name' TO 'user'@'host';
Bash
  • 激活角色
-- 激活并设置默认角色
SET DEFAULT ROLE 'role_name' TO 'user'@'host';

-- 或当前会话激活，每个新会话开始时需要重新激活（除非设置了默认角色）
SET ROLE 'role_name';
Bash

常见角色权限方案：

  • 数据库管理员 (DBA)
CREATE ROLE 'db_admin';
GRANT ALL PRIVILEGES ON *.* TO 'db_admin' WITH GRANT OPTION;
Bash
  • 应用开发者
CREATE ROLE 'app_developer';
GRANT CREATE, ALTER, DROP, INDEX, SELECT, INSERT, UPDATE, DELETE 
ON app_db.* TO 'app_developer';
GRANT EXECUTE ON PROCEDURE app_db.* TO 'app_developer';
Bash
  • 数据分析师
CREATE ROLE 'data_analyst';
GRANT SELECT ON analytics.* TO 'data_analyst';
GRANT SHOW VIEW ON analytics.* TO 'data_analyst';
Bash
  • 只读用户
CREATE ROLE 'read_only';
GRANT SELECT ON *.* TO 'read_only';
REVOKE SELECT ON mysql.* FROM 'read_only';  -- 排除系统库
Bash
  • 备份管理员
CREATE ROLE 'backup_admin';
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, PROCESS 
ON *.* TO 'backup_admin';
Bash

完整示例工作流示例：

-- 1. 创建角色
CREATE ROLE 'finance_ro', 'finance_rw', 'finance_admin';

-- 2. 分配权限
GRANT SELECT ON finance.* TO 'finance_ro';
GRANT SELECT, INSERT, UPDATE ON finance.* TO 'finance_rw';
GRANT ALL ON finance.* TO 'finance_admin' WITH GRANT OPTION;

-- 3. 创建用户并分配角色
CREATE USER 'john'@'%' IDENTIFIED BY 'secure_pass';
GRANT 'finance_ro' TO 'john'@'%';

CREATE USER 'jane'@'10.0.%' IDENTIFIED BY 'strong_pass';
GRANT 'finance_rw', 'finance_ro' TO 'jane'@'10.0.%';

-- 4. 设置默认角色
SET DEFAULT ROLE 'finance_rw' TO 'jane'@'10.0.%';
SET DEFAULT ROLE 'finance_ro' TO 'john'@'%';

-- 5. 验证权限
SHOW GRANTS FOR 'john'@'%' USING 'finance_ro';
Bash

3. 破解密码

3.1. Linux 平台

方法一：启动时跳过授权表

# 修改配置文件
vim /etc/my.cnf
[mysqld]
skip-grant-table  # 以跳过权限表方式启动

# 重启数据库 
systemctl restart mysqld
 
# 连接到MySQL（无需密码）
mysql -u root

# 在MySQL中执行（5.7.6+版本）
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

# 或旧版本
UPDATE mysql.user SET authentication_string=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;
 
# 打开 /etc/my.cnf 去掉 skip-grant-table，然后重启
systemctl restart mysql
Bash

方法二：使用 init 文件重置

# 停止MySQL服务
sudo systemctl stop mysqld

# 创建包含密码修改命令的文件
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';" > /root/reset.sql

# 配置MySQL在启动时执行脚本
sudo sed -i '/\[mysqld\]/a init-file=/root/reset.sql' /etc/my.cnf

# 启动MySQL服务
sudo systemctl start mysqld

# 确认服务运行后，立即清理
sudo rm -f /root/reset.sql
sudo sed -i '/init-file=\/root\/reset.sql/d' /etc/my.cnf
Bash

3.2. Windows 平台

# 停止 MySQL 服务

# 创建包含密码重置命令的文本文件

# 使用 --init-file 参数启动 mysqld
Bash
上一篇
下一篇