MySQL运维实战（2.3）MySQL的权限体系
作者：俊达
MySQL权限划分
MySQL权限按授权范围可以分为三大类：全局权限、数据库权限和对象权限。
- 全局权限主要用于管理系统模块，这些权限涵盖了对MySQL服务器整体的操作和管理，与具体的数据库或对象无关，因此在授权时需要指定为.。
- 数据库权限是用于管理数据库，这些权限针对特定数据库，允许用户执行与该数据库相关的操作，例如创建、修改、删除数据库等。在授权时需指定database_name.*。
- 对象权限是用于管理数据库对象，涉及对具体数据库对象（如表、字段）的权限管理，允许用户对特定对象执行特定操作，例如对表进行查询、更新或删除操作。授权时需指定为database_name.table_name或database_name.table_name.column_name。
这种分类使得MySQL的权限管理更加灵活，可以根据实际需求精细地控制不同级别的权限，保障数据库安全性和管理的有效性。
MySQL各权限说明
| 权限名称 | 授权范围 | 描述 |
|---|---|---|
| ALL | 全局权限 | 实例所有权限 |
| CREATE ROLE | 全局权限 | 创建数据库角色 |
| CREATE TABLESPACE | 全局权限 | 创建表空间 |
| CREATE USER | 全局权限 | 创建数据库用户 |
| DROP ROLE | 全局权限 | 删除数据库角色 |
| PROCESS | 全局权限 | 查看实例中的所有session(show processlist) |
| 默认只能看登录用户的session | ||
| PROXY | 全局权限 | |
| RELOAD | 全局权限 | 执行下面的操作需要reload权限 |
| flush privileges | ||
| flush logs | ||
| REPLICATION CLIENT | 全局权限 | 查看复制信息的权限(show slave status) |
| REOLICATION SLAVE | 全局权限 | 复制权限（从主库复制数据） |
| SHUTDOWN | 全局权限 | 关闭实例 |
| SUPER | 全局权限 | 超级权限 |
| kill任何用户的session | ||
| 修改参数 | ||
| 管理复制(start slave,change master等） | ||
| USAGE | 全局权限 | 无任何权限 |
| SHOW DATABASES | 全局权限 | 查看数据库列表 |
| show databases | ||
| CREATE | 数据库权限 | 创建数据库、表 |
| INDEX | 对象权限 | 创建索引（create index) |
| CREATE VIEW | 对象权限 | 创建视图 |
| CREATE ROUTINE | 数据库权限 | 创建存储过程、函数 |
| DROP | 数据库权限 | 删除表、视图、存储过程、触发器、定时任务 |
| ALTER | 对象权限 | 修改表结构和表的索引 |
| 即使没有index权限，也可以使用alter语句添加或删除索引 | ||
| 修改表名称时，同时需要drop权限 | ||
| ALTER ROUTINE | 对象权限 | 修改存储过程、函数 |
| EVENT | 全局权限 | 创建、删除调度任务 |
| TRIGGER | 对象权限 | 创建触发器的权限 |
| SELECT | 对象权限 | 查询数据 |
| INSERT | 对象权限 | 插入数据 |
| UPDATE | 对象权限 | 更新数据 |
| DELETE | 对象权限 | 删除数据 |
| EXECUTE | 对象权限 | 执行存储过程的权限 |
| REFERENCE | 数据库权限 | 外键引用权限 |
| LOCK TABLES | 数据库权限 | 执行lock tables权限，需要同时对表有select权限 |
| SHOW VIEW | 对象权限 | 查看视图定义（show create view) |
一个例子：授权库不一致导致访问报错
1、创建账号并授权
授予账号(cc@%) select和create view的权限。
mysql> create database hello_db_x;
Query OK, 1 row affected (0.01 sec)
mysql> grant select on `hello\_db\_x`.* to 'cc'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> grant create view, show view on `hello_db_x`.* to 'cc'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> use hello_db_x;
Database changed
mysql> create table tx(a int);
Query OK, 0 rows affected (0.14 sec)
2、使用新账号登陆，创建视图
报没有create view的权限
root@box1 ~]# mysql -ucc -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hello_db_x |
+--------------------+
2 rows in set (0.00 sec)
mysql> use hello_db_x;
mysql> show tables;
+----------------------+
| Tables_in_hello_db_x |
+----------------------+
| tx |
+----------------------+
1 row in set (0.01 sec)
mysql> select * from tx;
Empty set (0.01 sec)
mysql> create or replace view v_xx as select * from tx;
ERROR 1142 (42000): CREATE VIEW command denied to user 'cc'@'localhost' for table 'v_xx'
mysql> show grants;
+------------------------------------------------------------+
| Grants for cc@% |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cc'@'%' |
| GRANT SELECT ON `hello\_db\_x`.* TO 'cc'@'%' |
| GRANT CREATE VIEW, SHOW VIEW ON `hello_db_x`.* TO 'cc'@'%' |
+------------------------------------------------------------+
创建视图失败，但是通过show grants，可以看到账号有create view的权限。
3、分析原因
hello_db_x库存在2条授权记录，一条有select权限，但是没有create view权限，一条有create view权限，但是无select权限。
mysql使用没有create view权限的那条授权记录，导致create view失败。
mysql> select host, db, user, select_priv, create_view_priv, show_view_priv from mysql.db where user='cc';
+------+--------------+------+-------------+------------------+----------------+
| host | db | user | select_priv | create_view_priv | show_view_priv |
+------+--------------+------+-------------+------------------+----------------+
| % | hello_db_x | cc | N | Y | Y |
| % | hello\_db\_x | cc | Y | N | N |
+------+--------------+------+-------------+------------------+----------------+
4、解决: 重新授权，库名保持一致（包括转义符）
grant create view, show view on `hello\_db\_x`.* to 'cc'@'%';
select host, db, user, select_priv, create_view_priv, show_view_priv from mysql.db where user='cc';
+------+--------------+------+-------------+------------------+----------------+
| host | db | user | select_priv | create_view_priv | show_view_priv |
+------+--------------+------+-------------+------------------+----------------+
| % | hello_db_x | cc | N | Y | Y |
| % | hello\_db\_x | cc | Y | Y | Y |
账号和权限管理实践
以下是一些关于MySQL账号和权限管理的建议：
- 避免无密码和弱密码账号：
确保不使用没有密码或者弱密码的账号，可以利用validate_password插件来评估密码的强度，从而保证只有强密码被使用。 - 删除匿名用户： 删除所有用户名为空的匿名用户，以防止未经授权的访问。
- 最小权限原则： 按需分配权限，给予用户所需的最小权限。例如，对于只需要进行查询的账号，仅授予"select"权限。
- 区分业务账号权限：对于业务相关的账号，限制权限仅限于数据操作语言（DML）操作，如"insert"、"update"、"delete"、"select"、"lock tables"。通常不建议使用业务账号执行数据定义语言（DDL）操作（如"create"、"alter"、"drop"），以防止对数据库结构的意外更改。
- 创建专用的DBA账号： 为数据库管理员（DBA）创建单独的账号，赋予其DDL权限（如"create"、"drop"、"alter"等）。这种职责分离确保了管理任务与常规业务操作分离，增强了对数据库修改的安全性和控制。
这些措施可以有助于更好地保护MySQL数据库安全，并且有条不紊地管理账号和权限，减少潜在的安全风险。
