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

俊达2年前技术文章822

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



相关文章

MySQL运维实战之备份和恢复(8.9)xtrabackup备份指定表

备份部分表如果实例设置了参数innodb_file_per_table,xtrabackup可以备份部分表。通过--tables,--tables-file,--databases,--databas...

 MySQL运维实战(1.3)安装部署:源码编译安装

MySQL运维实战(1.3)安装部署:源码编译安装

源码编译安装通常不需要自己编译mysql源码,编译的mysql和二进制包的内容基本一致。当然有些时候可能会需要采用源码编译的方式安装,安装一些非标准版本的mysql安装一些社区的patch、bugfi...

MySQL运维实战之ProxySQL(9.1)ProxySQL介绍

MySQL运维实战之ProxySQL(9.1)ProxySQL介绍

mysql通过复制技术实现了数据库高层面的可用,但是对于应用来说,当后端MySQL发生高可用切换时,应该怎么处理?我们考虑几种方案:1、使用域名绑定。应用通过dns连接后端实例,当后端发生切换后,将d...

MySQL运维实战(6)用户认证插件caching_sha2_password

MySQL用户认证可以使用几种不同的方式,创建用户时可以制定认证方式:create user 'username'@'%' identif...

MySQL优化器特性(三)表关联之BKA(Batched Key Access)优化

MySQL优化器特性(三)表关联之BKA(Batched Key Access)优化

单表range查询时,可以使用MRR优化,先对rowid进行排序,然后再回表查询数据。在表关联的时候,也可以使用类似的优化方法,先根据关联条件取出被关联表的rowid,将rowid缓存在join bu...

MySQL运维实战(7.1) 开启GTID复制

MySQL从5.6版本开始支持GTID复制。开启GTID之后,主库上执行的每一个事务都有一个全局唯一的ID。GTID由两部分组成:server_uuid和事务序列号。初始化数据库时,会生成一个全局唯一...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。