mysql 权限设置

背景

在了解动态权限之前，我们先回顾下 MySQL 的权限列表。

权限列表大体分为服务级别和表级别，列级别以及大而广的角色（也是MySQL 8.0 新增）存储程序等权限。我们看到有一个特殊的 SUPER 权限，可以做好多个 *** 作。比如 SET 变量，在从机重新指定相关主机信息以及清理二进制日志等。那这里可以看到，SUPER 有点太过强大，导致了仅仅想实现子权限变得十分困难，比如用户只能 SET 变量，其他的都不想要。那么 MySQL 8.0 之前没法实现，权限的细分不够明确，容易让非法用户钻空子。

那么 MySQL 8.0 把权限细分为静态权限和动态权限，下面我画了两张详细的区分图，图 1 为静态权限，图 2 为动态权限。

图 1- MySQL 静态权限的权限管理图

图 2-动态权限图

那我们看到其实动态权限就是对 SUPER 权限的细分。 SUPER 权限在未来将会被废弃掉。

我们来看个简单的例子，

比如， 用户 'ytt2@localhost', 有 SUPER 权限。

mysql>show grants for ytt2@'localhost'+---------------------------------------------------------------------------------+| Grants for ytt2@localhost                                                       |+---------------------------------------------------------------------------------+| GRANT INSERT, UPDATE, DELETE, CREATE, ALTER, SUPER ON *.* TO ytt2@localhost |+---------------------------------------------------------------------------------+1 row in set (0.00 sec)

但是现在我只想这个用户有 SUPER 的子集，设置变量的权限。那么单独给这个用户赋予两个能设置系统变量的动态权限，完了把 SUPER 给拿掉。

mysql>grant session_variables_admin,system_variables_admin on *.* to ytt2@'localhost'Query OK, 0 rows affected (0.03 sec)mysql>revoke super on *.* from ytt2@'localhost'Query OK, 0 rows affected, 1 warning (0.02 sec)

我们看到这个 WARNINGS 提示 SUPER 已经废弃了。

mysql>show warnings

+---------+------+----------------------------------------------+

| Level   | Code | Message                                      |

+---------+------+----------------------------------------------+

| Warning | 1287 | The SUPER privilege identifier is deprecated |

+---------+------+----------------------------------------------+

1 row in set (0.00 sec)`

mysql>show grants for ytt2@'localhost'

+-----------------------------------------------------------------------------------+

| Grants for ytt2@localhost                                                         |

+-----------------------------------------------------------------------------------+

| GRANT INSERT, UPDATE, DELETE, CREATE, ALTER ON *.* TO ytt2@localhost          |

| GRANT SESSION_VARIABLES_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO ytt2@localhost |

+-----------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

当然图 2 上还有其它的动态权限，这里就不做特别说明了。

hi 楼主，在数据库中创建包含很多，视图，索引，临时表的创建权限都能分开赋予，你可以执行 show privileges 来查看权限参数，我这边就以创建表为例，只包含查询表功能，其他修改，删除，备份没有权限；以下是步骤：

1，create user 'tom'@'%' identified by '123456'---创建用户，无权限；

2， grant create,select on wangxh2.* to tom-----把wangxh2库的所有表的创建和查询赋予tom

3，flush privileges-----刷新权限表才能起效

接下来是测试：

mysql>show databases

+--------------------+

| Database |

+--------------------+

| information_schema |

| test |

| wangxh2|

+--------------------+

3 rows in set (0.06 sec)

mysql>use wangxh2

Database changed

mysql>show tables

+-------------------+

| Tables_in_wangxh2 |

+-------------------+

| test |

+-------------------+

1 row in set (0.00 sec)

mysql>drop test

ERROR 1064 (42000): You have an error in your SQL syntaxcheck the manual that corresponds to your MySQL server version for the right syntax to use near 'test' at line 1

mysql>drop table test

ERROR 1142 (42000): DROP command denied to user 'tom'@'localhost' for table 'test'

mysql>select count(*) from test

+----------+

| count(*) |

+----------+

| 33554432 |

+----------+

1 row in set (0.01 sec)

mysql>insert into test values(1)

ERROR 1142 (42000): INSERT command denied to user 'tom'@'localhost' for table 'test'

mysql>delete from test

ERROR 1142 (42000): DELETE command denied to user 'tom'@'localhost' for table 'test'

mysql>update test set id=1

ERROR 1142 (42000): UPDATE command denied to user 'tom'@'localhost' for table 'test'

mysql>create table test1 (id int)

Query OK, 0 rows affected (0.02 sec)

mysql>insert into test1 values(1)

ERROR 1142 (42000): INSERT command denied to user 'tom'@'localhost' for table 'test1'

[mysql@localhost ~]$ mysqldump -u tom -paidengshan wangxh2 >/home/mysql/aa.sql

mysqldump: Got error: 1044: Access denied for user 'tom'@'%' to database 'wangxh2' when using LOCK TABLES

[mysql@localhost ~]$

-----------------------------------------------------------------------------------------

以上测试发现，tom对wangxh2有建表，查询表的权限，但是修改，删除，新增，备份都没有权限，达到你的需求了

创建用户

创建一个名为mysqlusr的用户，设置一个随机密码，密码的长度最好

不要少于20位。

2．设置用户的身份

将mysqlusr用户加入Guests组，并去掉其它任何的组。

3．设置磁盘权限

假设MySQL安装在如下目录中

D:hostingsystemmysql

假设MySQL的数据库存放在如下目录中

D:hostingMySQLDB

假设MySQL的服务运行者修改为mysqlusr

目录权限设置如下

D:hostingsystemmysql

mysqlusr

读取和运行

列出文件夹目录

读取

D:hostingsystemmysqltmpdir

mysqlusr

修改

读取和运行

列出文件夹目录

读取

写入

D:hostingMySQLDB

mysqlusr

修改

读取和运行

列出文件夹目录

读取

写入

4．修改MySQL的相应配置

修改MySQL目录下的my.ini

在其中增加一行，内容如下

tmpdir=D:/hosting/system/MySQL/tmpdir

---