MySQL远程连接及相关权限管理

MySQL默认不允许远程登录，需要我们进行相关授权操作，在网上查了一些资料，在此简单整理

如有描述不准确或理解有误之处，还请各位大神轻拍砖，多指正！

1.实现MySQL远程连接

网上普遍存在两种方法，即“改表法”和“授权法”

（1）改表法

默认情况下，用户只允许localhost登录，需要通过update语句，将localhost改为%，表示任何主机均可登录

update mysql.user set host = '%' where user = 'root';

此处%表示允许任何主机登录，root表示MySQL用户名

flush privileges;//更新授权操作，使修改生效

（2）授权法

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;//授权

此处root为MySQL用户名，password为密码，原理相当于从新插入一条记录（即新建一个MySQL用户），并授予全部权限

flush privileges;//更新授权操作，使修改生效

注：这是范围最大的授权操作，出于安全考虑，生产环境通常不会这样设置。GRANT授权语句的参数详解将在下文一一介绍

至此，MySQL的远程登录设置已经全部完毕，Windows下开启防火墙3306端口后，已经可以从任意主机登录MySQL。

下文主要介绍由“远程登录”所引出的相关权限管理问题。

2.查看权限

MySQL将用户统一存放在mysql.user表中

通过

select * from mysql.user;select host, user, password from mysql.user;//host为主机地址，可以是IP也可以是主机名，%表示任何主机；user为用户名；password为经过加密处理后的登录密码

即可查看相关信息

通过

show grants;//查看当前登录用户的权限

show grants for 'root'@'%';//查看指定用户的权限

可以查看相关用户权限，MySQL的具体权限种类比较多，将在下文以表格形式呈现，其中on privileges表示全部权限

3. 授权操作（GRANT命令）

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

ALL PRIVILEGES：表示全部权限，也可以使用insert，update，delete，select等

*.*：表示授权操作针对哪些数据库和哪些表，第一个*为数据库，第二个*为该数据库下的表

'root'@'%'：表示授权操作针对哪个用户和哪些主机限制，root为用户名，可以是存在的用户，也可以是不存在的用户

如果是存在的用户，则增加新的授权，如果不存在，则新建用户并进行指定的授权操作

%表示对登录主机无限制，也可以是ip，ip段，域名，表示只有指定ip，ip段内的主机，域名才拥有被授予权限

password：指定用户登录密码

WITH GRANT OPTION：表示允许用户进行授权操作，如果不写，表示被授权用户不能进行授权操作

注意，可以重复进行授权操作，权限叠加，即第一次授权select，第二次授权insert，select，则用户同时现select和insert两种权限

4.收回授权（revoke）

revoke delete on *.* from 'root'@'%';

revoke语法及参数和grant十分相似，只是把to改为from即可，此处只给出简单示例，不再重复说明

5. 修改用户密码

update mysql.user set password = password('password');

<pre name="code" class="sql">update mysql.user set password = password('password') where host = '%';

user表中的password字段经过加密处理，需要以password（’你的密码‘）的形式进行更新

注意：如果update语句中没有where条件，或有where条件，但一次可能更新多条记录时（即where条件中没有指定id），可能会报如下错误

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode...

解决办法：

SET SQL_SAFE_UPDATES=0;//缺省值为1

通常不建议此类操作，update语句还是添加where条件比较好

6.MySQL权限列表

权限分布

可设置的权限

表权限

'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'

列权限

'Select', 'Insert', 'Update', 'References'

过程权限

'Execute', 'Alter Routine', 'Grant'

权限

权限级别

权限说明

CREATE

数据库、表或索引

创建数据库、表或索引权限

DROP

数据库或表

删除数据库或表权限

GRANT OPTION

数据库、表或保存的程序

赋予权限选项

REFERENCES

数据库或表

 

ALTER

表

更改表，比如添加字段、索引等

DELETE

表

删除数据权限

INDEX

表

索引权限

INSERT

表

插入权限

SELECT

表

查询权限

UPDATE

表

更新权限

CREATE VIEW

视图

创建视图权限

SHOW VIEW

视图

查看视图权限

ALTER ROUTINE

存储过程

更改存储过程权限

CREATE ROUTINE

存储过程

创建存储过程权限

EXECUTE

存储过程

执行存储过程权限

FILE

服务器主机上的文件访问

文件访问权限

CREATE TEMPORARY TABLES

服务器管理

创建临时表权限

LOCK TABLES

服务器管理

锁表权限

CREATE USER

服务器管理

创建用户权限

PROCESS

服务器管理

查看进程权限

RELOAD

 

 

服务器管理

执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限

REPLICATION CLIENT

服务器管理

复制权限

REPLICATION SLAVE

服务器管理

复制权限

SHOW DATABASES

服务器管理

查看数据库权限

SHUTDOWN

服务器管理

关闭数据库权限

SUPER

服务器管理

执行kill线程权限