权限和安全

MySQL访问权限系统

MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的各种权限，一般生产环境中的程序账号只需要SELECT、INSERT、UPDATE和DELETE权限即可

MySQL将验证用户的3项 信息：用户名、密码、主机来源。

权限可以分为两类：系统权限和对象选项。 系统权限允许执行一些特定的功能，如关闭数据库、终止进程、显示数据库列表、查看当前执行的查询等。对象权限是指 对一些特殊的对象（表、列、视图、数据库）的访问权限，例如是否允许访问某张表，是否允许在某个库中创建表。

一般不允许直接更改MySQL的权限表，而是通过GRANT和REVOKE语句进行权限的赋予和收回，这也是更安全可靠的办法

GRANT和REVOKE语句允许系统管理员创建MySQL用户账户、授予权限和撤销权限。授予的权限可以分为多个级别：服务器级别（全局）、数据库级别、表级别、列级别、子程序级别。撤销权限即回收已经存在的权限。

GRANT [privileges] ON [objects] TO [user] 
GRANT [privileges] ON [objects] TO [user] IDENTIFIED BY [password] 
REVOKE [privileges] ON [objects] FROM [user]

MySQL为有SUPER权限的用户专门保留了一个额外的连接，因此即使是所有的普通连接都被占用，MySQLroot用户仍可以登录并检查服务器的活动。

如果想要限制单个账号允许的连接数量，可以通过设置max_user_connections变量来完成。

当在GRANT语句中指定数据库名称时，允许使用“_”和“%”通配符。这意味着，如果想要使用“_”字符作为一个数据库名称的一部分，则应该在GRANT语句中指定它为“\_”，例如，“GRANT…ON‘foo\_bar’.*TO…。”

SHOW TABLES命令不会显示用户没有权限访问的表。

常用的权限 

SHOW PRIVILEGES命令可以显示MySQL所支持的权限，如下是一些常用的权限。

SELECT、INSERT、UPDATE和DELETE权限允许用户在一个数据库现有的表上实施读取、插入、更新和删除记录的操作。这也是一般程序账号所需要的权限。 

SHOW VIEW权限允许用户查看已经创建了的视图。 

ALTER权限允许用户使用ALTERTABLE命令来修改现有数据表的结构。 

CREATE和DROP权限允许用户创建新的数据库和表，或者删除现存的数据库和表。生产环境中一般不赋予程序账号DROP的权限。·GRANT权限允许用户把自己拥有的权限授予其他的用户。 ·FILE权限允许被授予该权限的用户都能读或写MySQL服务器能读写的任何文件。

SHUTDOWN权限允许用户使用SHUTDOWN命令关掉服务器。可以创建一个用户专门用来关闭服务器。 

PROCESS权限允许用户使用PROCESSLIST命令显示在服务器内执行的进程的信息；使用KILL命令终止服务器进程。用户总是能显示或终止自己的进程，但是，显示或终止其他用户启动的进程则需要PROCESS权限。一些监控工具需要PROCESS权 限查看正在执行的命令。

显示某个用户的权限的命令如下
SHOW GRANTS FOR username@'ip_range';

如

show grants for root@localhost;

show grants for zbp@127.0.0.1

show grants 只能查看一个用户的权限

如果想查看所有用户可以
use mysql 
select host,user from user;

不过这个只能看到哪个用户的那个ip可以连接该台服务器,看不到权限

赋予某个用户对库db1进行SELECT、INSERT、UPDATE和DELETE的权限的命令如下。
GRANT SELECT,INSERT,UPDATE,DELETE ON db1.* TO username@'10.%' IDENTIFIED BY 'your_password';

又如 
grant all privileges on art1.* to zbp@"127.0.0.1" identified by "123456";    # 赋予zbp用户在本机上对art1所有表的所有权限; on *.* 表示所有库的所有表； 如果允许zbp在所有的ip上远程连接该数据库则 zbp@"%"

PS 在mysql中127.0.0.1和localhost不同,mysql -uroot -p 默认的主机是 localhost ; 所以如果想用刚刚的zbp登陆就要 mysql -h127.0.0.1 -uzbp -p 才可以

如果相对赋予权限的用户修改密码,只需重新赋予一次权限即可。

如需撤销权限可以这样:
revoke all privileges on art1.* from zbp@127.0.0.1;

一定要制定好要撤销的权限,库表,用户和主机名,缺一不可,最好赋予权限的时候怎么写,撤销的时候原封不动的复制。如果忘记了可以用 show grants 命令查看

当撤消了zbp用户的权限之后,在查看zbp的权限
show grants for zbp@127.0.0.1
+-----------------------------------------+
| Grants for zbp@127.0.0.1                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'zbp'@'127.0.0.1' |
+-----------------------------------------+

发现还有一个USAGE权限,这是zbp的登陆权限还留着。

revoke usage on *.* from zbp@127.0.0.1 ;

发现还是没法删除这个权限。其实删掉这个用户即可

drop user zbp@127.0.0.1;    # 删除用户除了要指定用户名还要指定ip,而且ip在赋予权限的时候怎么设,删的时候就要怎么写

下面是具体的mysql远程连接的例子:
两台服务器: 192.0.0.10     192.0.0.11
现在192.0.0.11要远程连接到192.0.0.10

在 192.0.0.10设置权限:
grant all privileges on *.* to zbp@"192.0.0.11" identified by "123456";   # 可以执行flush privileges 刷新一下权限

然后在192.0.0.11就可以连接 192.0.0.10了:
mysql -h 192.0.0.10 -u zbp -p 123456

** 那么接下来就是说一下一系列无法远程连接的状况:

Can't connect to MySQL server on 'xxx' (111) 

这种情况的话需要在被连接的服务器 192.0.0.10 的数据库配置文件 /etc/my.cnf 找到 bind-address = 127.0.0.1 在他后面加一个ip 192.0.0.11允许这台服务器连接

bind-address = 127.0.0.1 192.0.0.11

=============================================
PS:如果不知道自己的mysql配置文件在哪里可以这样:                     
which mysqld     #得到mysqld命令的绝对路径,我这里是/usr/sbin/mysqld 
/usr/sbin/mysqld --verbose --help |grep -A 1 'Default options'       
就会显示mysql的配置文件位置,会显示多个,要一个个找                   
=============================================

如果在/etc/my.cnf找不到这句,可能在my.cnf所引入的其他配置文件中里,例如 my.cnf中有一句!includedir /etc/my.cnf.d ,意思是引入这个目录下所有的配置文件,所以可以在这个目录下的配置文件下找找看。如果找不到，那么可以直接自己在my.cnf加一句bind-address

Can't connect to MySQL server on 'xxx' (110)

110的情况是防火墙的原因,此时在 192.0.0.11 和 192.0.0.10 都关掉防火墙,然后重启一下mysql服务

systemctl stop firewalld

如果还是不行,那么很可能是因为你的服务器是云服务器,此时要登陆云服务器后台开启3306端口对外开放才行。

最后如果被连接的服务器撤消了zbp@192.0.0.11的权限之后,192.0.0.11依旧可以通过zbp用户连接到192.0.0.10的mysql服务器,但是权限已经没了,无法查看或者操作远程服务器的表和数据。可以在192.0.0.10删掉zbp用户,这样.11就无法连接到.10了

更多内容请关注微信公众号