数据库之权限与安全

一、MySQL权限表

1.MySQL权限系统的工作原理

MySQL的存储控制过程是：首先服务器检查用户是否允许连接，假定用户能连接，服务器检查用户发出的每个请求，看其是否有足够的权限实施它。

服务器在存储控制过程中使用MySQL数据库中的权限表进行权限判断。MySQL5.7中存在4个控制权限的表，分别为user表，db表，tables_priv表和columns_priv表。这些表位于系统数据库mysql中。

MySQL权限表的验证过程为：

（1）先通过user表中的host和user两个字段判断连接的IP和用户名是否存在，存在则通过验证。对于身份认证，MySQL是通过IP地址和用户名联合进行确认。同一个用户，如果来自不同的IP地址，则MySQL将其视为不同的用户。

（2）通过身份认证后，进行权限分配，按照user，db，tables_priv，columns_priv的顺序进行验证。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db，tables_priv和columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中为Y的权限，依此类推。

2.权限表

如果要查看user表中的字段，可以像查看欧通表那样使用SELECT语句：

SELECT * FROM mysql.user WHERE user=’root’ AND host=’localhost’ \G

user表是MySQL中最重要的权限表，用于记录允许连接到服务器的账号信息。其中的权限是全局级的，如果用户在该表中被授予了DELETE权限，则该用户可以删除MySQL服务器上所有数据库中的任何记录。

user表中的列可以分为4部分：用户列、权限列、安全列和资源控制列，通常使用最多的是用户列和权限列。

1.用户列

用户列包括host和user，表示主机名和用户名，并且这两个字段是表的联合主键。在用户与服务器建立连接时，输入的主机名和用户名必须匹配user表中对应的字段，只有2个值都匹配才允许建立连接。

2.权限列

权限列的字段决定了用户的权限，描述了在全局范围内允许对数据库进行的操作。包括查询和修改等用于数据库操作的普通权限，也包括关闭服务器和加载用户等管理权限。这些字段值的类型为ENUM，可取值为Y和N，Y表示用户有对应的权限；N表示用户没有对应的权限。权限列中所有字段的值默认都为N，如果要修改权限，可以使用GRANT语句或UPDATE语句修改user表中相应字段的值。

3.安全列

安全列有6个字段，其中两个是ss1相关的，两个是x509相关的。ss1用于加密，x509标准用于标注用户，plugin字段标识用于验证用户身份。如果该字段为空，服务器使用内建授权验验证机制验证用户身份。authentication_string字段为5.7版本中新增的用于代替password，标识密码的字段。

4.资源控制列

资源控制列的字段用于限制用户使用的字段，包括以max开头的4个字段。如果1小时内用户查询或连接数量超过字段控制限制，该用户将被锁定，直到1小时后才可以再次执行相应操作。

注：MySQL5.7还新增了4个字段，分别用于控制密码失效、最后一次i修改时间、失效时间，及账户锁定。

二、账号管理

1.创建账号

常用的创建账号的方式有两种：

一种是使用GRANT语句；

另一种是使用CREATE USER语句。

一般情况下使用GRANT语句，因为操作简单，出错几率少。

1.使用GRANT语句创建新用户

使用GRANT语句不仅可以创建新用户，还可以在创建的同时对用户授权。另外，使用GRANT语句还可以指定账户的其他特点，如使用安全连接、限制使用服务器资源等。

需要注意的是，使用GRANT语句创建新用户时必须有GRANT权限。

GRANT语句的基本语法格式如下：

GRANT priv_type [,priv_type...] ON db.table

TO user@host [IDENTIFIED BY ‘password’] [,user [IDENTIFIED BY ‘password’]]

[WITH with_option [with_option]...];

其中，priv_type表示赋予用户的权限类型，比如SELECT，UPDATE等，all privileges为所有权限；db.table表示用户的权限所作用的数据库或数据库中的表，*.*表示所有数据库；user表示用户名；host表示主机名，其中%匹配任何主机名；IDENTIFIED BY关键字用于设置密码；password表示用户密码；[WITH with_option [with_option]...]为可选参数，表示对新创建的用户赋予GRANT权限，即该用户可以对其他用户赋予权限。

注：如果只指定用户名部分user，不指定主机名，则主机名部分默认为%，表示对所有主机开放权限；如果指定用户登录不需要密码，可以省略IDENTIFIED BY部分。User表中的user和host字段区分大小写。

2.使用CREATE USER语句创建新用户

要使用CREATE USER语句创建用户，必须有全局的CREATE USER权限，或MySQL数据库的INSERT权限。每添加一个用户，CREATE USER语句会在mysql.user表中添加一条新纪录。CREATE USER语句的基本语法格式如下：

CREATE USER user@host [IDENTIFIED BY ‘password’];

使用CREATE USER语句创建的用户没有任何权限，还需要使用GRANT语句赋予其权限。

2.删除账号

在MySQL中，可以使用DROP USER语句删除用户。其基本语法格式如下：

DROP USER ‘user’@’host’ [,’user’@’host’];

DROP USER语句可以一次删除一个或多个用户。要使用该语句，必须拥有MySQL数据库的全局CREATE USER权限或DELETE权限。

3.root用户修改自身密码

1.修改MySQL数据库的user表

由于所有账号信息都保存在user表中，因此可以通过修改user表中的密码字段值来改变root用户的密码。使用root用户登录MySQL服务器后，可以执行以下UPDATE语句修改其登录密码：

UPDATE mysql.user SET authentication_string=PASSWORD(‘newpwd’) WHERE user=’root’ AND host=’localhost’;

然后使用FLUSH PRIVILGES;语句，重新加载用户权限。

2.使用mysqladmin命令修改密码

使用mysqladmin命令修改root用户密码的基本语法形式如下：

mysqladmin -u username -h localhost -p password “newpwd”

username为要修改密码的用户名，此处指定为root用户；参数-h指定要修改的服务器地址，可以不屑，默认为localhost；-p表示输入当前密码；password为关键字，后面双引号中的内容newpwd为要设置的新密码。

3.使用SET语句修改root用户密码

使用SET语句可以重新设置自身或其他用户的登录密码。修改自身登录密码的语法结构如下：

SET PASSWORD=PASSWORD(“newpwd”);

新密码必须使用PASSWORD()函数加密。

4.root用户修改普通用户密码

1.使用SET语句修改普通用户密码

使用SET语句修改普通用户密码的语法格式如下：

SET PASSWORD FOR ‘user’@’host’ = PASSWORD(‘newpwd’);

如果是普通用户修改自身密码，则可以省略FOR子句:

SET PASSWORD = PASSWORD(‘newpwd’);

2.使用UPDATE语句修改普通用户密码

在使用root用户登录MySQL服务器后，可以通过之心UPDATE语句修改mysql数据库中user表的authentication_string字段值，来修改普通用户的密码。使用UPDATE语句修改普通用户密码的基本语法格式如下：

UPDATE mysql.user SET authentication_string=PASSWORD(“newpwd”)

WHERE user=”username” AND host=”hostname”;

PASSWORD()函数用户加密用户数据。执行该语句后需要执行FLUSH PRIVILEGES;语句刷新权限，重新加载权限表。

3.使用GRANT语句修改普通用户密码

只有拥有GRANT权限，才能使用GRANT语句修改密码。使用GRANT语句修改普通用户密码的基本语法格式如下：

GRANT USAGE ON . TO ‘username’@’hostname’ IDENTIFIED BY ‘newpwd’;

注：在使用GRANT语句或mysqladimn命令修改用户密码时，密码均会自动加密，不需要使用PASSWORD()函数。

5.root用户密码丢失的解决方法

第一步：打开管理员模式下的命令提示符窗口，执行net stop mysql命令，停止MySQL服务器（如果服务名称不为mysql，则输入自己版名的服务名，或者打开服务管理器查看）；

第二步：在命令窗口中执行mysqld --defaults-file=”安装路径\my.ini” --skip-grant-tables

第三步：打开命令提示符窗口，执行mysql -u root -p命令，在提示输入密码时直接回车，不用输入密码。

第四步：成功登录MySQL后，执行SQL语句选择数据库mysql。

第五步：执行UPDATE语句，修改user表中root用户对用的authentication_string字段值。

第六步：执行FLUSH PRIVILEGES;语句，刷新权限表。

第七步：退出MySQL后，使用新密码重新登陆。

三、MySQL权限管理

1.查看账号权限

创建好账号后，可以使用SHOW GRANTS语句查看账号的权限信息，其基本语法格式如下：

SHOW GRANTS FOR ‘user’@’host’;

其中user表示登录用户名，host表示登录的主机名或IP地址。在使用该语句时，指定的用户名和主机名都要用单引号引起来，并且在两个名字中间使用@符号连接。

GRANT可以显示全局级和非全局级权限的详细信息，如果表或列层级的权限被授予用户，它们也能在结果中显示。

除上述方法外，也可以使用SELECT语句查看权限表中各权限字段值来确定用户的权限信息，其基本语法格式如下：

SELECT privileges_list FROM mysql.user WHERE user=’username’ AND host=’hostname’;

其中privileges_list为想要查看的权限字段，可以为select_priv,insert_priv等，各字段之间使用逗号隔开。

2.给账号授权

给账户授权就是将某个权限授予某个用户。合理的权限可以保证数据库的安全。在MySQL中使用GRANT语句为账户授权，其基本语法格式如下：

GRANT priv_type [,priv_type] ON db.table TO user@host

[WITH with_option [with_option]...];

同创建账号时一样，priv_type表示赋予用户的权限类型，比如SELECT，UPDATE等；db.table表示用户的权限所作用的数据库中的表，*.*表示所有数据库的所有表；user表示用户名；host表示主机名；[WITH with_option [with_option]...]为可选参数，除了可以对新创建的用户赋予GRANT权限外，其可取值还有4个，用户账号资源限制，各值及其意义分别如下：

MAX_QUERIES_PER_HOUR count：设置每小时可以执行count次查询。

MAX_UPDATES_PER_HOUR count：设置每小时可以执行count次更新。

MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立count个连接。

MAX_USER_CONNECTIONS count：设置单个用户可以同时建立count个连接。

3.收回权限

收回权限就是取消用户已有的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全。MySQL中使用REVOKE语句取消用户权限。取消用户权限后，用户账号的记录将从db，tables_priv和columns_priv表中删除，但是用户账号记录依然保存在user表中（可以使用DROP USER语句删除user表中的账号记录）。

REVOKE语句的基本语法格式如下：

REVOKE priv_type [,priv_type] ...ON db.table

FROM ‘user’@’host’ [,’user’@’host’...]

该语句收回指定的权限，其中priv_type参数表示权限类型；db.table表示从哪个数据库哪个表上收回权限；‘user’@’host’表示用户账户，由用户名和主机名构成。

一般将在用户从user表中彻底删除之前，应该收回其所有权限，包括全局层级、数据库层级、表层级和列层级的权限。使用REVOKE语句收回用户所有权限的基本语法格式如下：

REVOKE ALL PRIVILEGES,GRANT OPTION

FROM ‘user’@’host’ [,’user’@’host’...]

使用REVOKE语句，必须拥有MySQL数据库的全局CREATE权限或UPDATE权限。