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账号和权限管理的建议：

1. 避免无密码和弱密码账号：
   确保不使用没有密码或者弱密码的账号，可以利用validate_password插件来评估密码的强度，从而保证只有强密码被使用。
2. 删除匿名用户： 删除所有用户名为空的匿名用户，以防止未经授权的访问。
3. 最小权限原则： 按需分配权限，给予用户所需的最小权限。例如，对于只需要进行查询的账号，仅授予"select"权限。
4. 区分业务账号权限：对于业务相关的账号，限制权限仅限于数据操作语言（DML）操作，如"insert"、“update”、“delete”、“select”、“lock tables”。通常不建议使用业务账号执行数据定义语言（DDL）操作（如"create"、“alter”、“drop”），以防止对数据库结构的意外更改。
5. 创建专用的DBA账号： 为数据库管理员（DBA）创建单独的账号，赋予其DDL权限（如"create"、“drop”、"alter"等）。这种职责分离确保了管理任务与常规业务操作分离，增强了对数据库修改的安全性和控制。

这些措施可以有助于更好地保护MySQL数据库安全，并且有条不紊地管理账号和权限，减少潜在的安全风险。

更多技术信息请查看云掣官网https://yunche.pro/?t=yrgw