MySQL运维实战（2.3）MySQL的权限体系和一个例子

俊达2年前 (2023-02-24)技术文章682

mysql权限按授权范围分为3大类

全局权限。

全局权限是用于管理系统模块的权限。跟具体的数据库或对象无关。授权时需要指定为*.*

数据库权限

对象权限

对于具体的数据库对象的权限，如表、字段级别的权限。

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)
REPLICATION 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              |

账号和权限管理实践

不使用无密码的账号。
不使用弱密码账号。可以使用validate_password插件验证密码强度。
删除匿名用户（用户名为空的用户）
使用最小权限原则。只给用户赋予所需使用的最小权限。
对只需要查询的账号（如数据抽取场景），只授权select权限。
对业务账号，只授权DML权限（insert, update, delete, select, lock tables)。通常不建议使用业务账号进行DDL操作（create, alter, drop)。
给DBA管理员创建DDL权限的账号（create , drop, alter等）