Mysql 的安全性机制

MySQL中主要包括两种用户:

root用户：为超级管理员,拥有MySQL提供的一切权限；

普通用户：只能拥有创建用户时赋予它的权限。

MySQL的安全性机制主要包括权限机制,用户机制和对用户进行权限管理。

MySQL所提供的权限表

在MySQL的系统数据库mysql中存储着权限表，最主要有mysql.user、mysql.db、mysql.host、table_priv、columns_priv、procs_priv表。

系统表mysql.user

该表有43个字段,这些字段大致可以分为4类,分别为用户字段,权限字段,安全字段和资源控制字段。

1.用户字段:

系统表mysql.user中的用户字段包含3个字段，主要用来判断用户是否能够登录成功，各个字段的含义如下表所示。

当用户登录时，首先会到系统表mysql.user中判断用户字段，如果这3个字段能够同时匹配，则会被允许登录。当创建新用户时，实际上会设置用户字段中所包含的3个字段。当修改用户密码时，实际上会修改用户字段中的Password字段。

2.权限字段:

user表中拥有一系列以"_priv"字符串结尾的字段,这些字段决定了用户权限。

3.安全字段:

安全字段主要包括4个字段:ssl_type；ssl_cipher；x509_issuer；x509_subject；

包含ssl字段的字段主要用来实现加密,包含x509的字段主要用来标识用户。

普通的发行版都没有加密功能。可以使用SHOW VARIABLES LIKE 'have_openssl'语句来查看是否具有ssl功能。如果取值为DISABLED，那么则没有ssl加密功能。 

4.资源控制字段：

资源控制字段主要包括4个字段,主要用来控制资源,所有资源控制字段的默认值为0,表示没有任何限制。

 plugin：5.5.7开始,mysql引入plugins以进行用户连接时的密码验证,plugin创建外部/代理用户。  

authentication_string：通过authentication_string可以控制两者的映射关系,(PAM plugin等,PAM可以支持多个服务名)尤其是在使用代理用户时，并须声明这一点。

password_expired：密码过期 Y,说明该用户密码已过期 N相反。

系统表mysql.db和mysql.host

在系统数据库mysql中,权限除了表user外,还有表db和表host。这两张表中都存储了某个用户对相关数据库的权限,结构大致相同。

当查找某个用户的权限，首先会从系统表mysql.db中査找，如果找不到Host字段的值，则会到系统表mysql.user去查找。

系统表mysql.tables_priv

表tables_priv用来实现单个表的权限设置。

执行带有DESC关键字段的SQL语句，查询到表tables_priv的结构，通过执行结果可以发现，表tables_priV包含8个字段，其中前4个分别表示主机名、数据库名、 用户名和表名。字段Grantor表示权限是由谁设置的。字段Timestamp表示存储更新的时间。字段Table_priv表示对表进行操作的权限，其值可以是Select、Insert、Update、Delete、Create、Drop、Grant、References、Index、Alter、Create View、Show view和Trigger中的任意一项。字段Column_priv表示对表中字段列进行操作的权限，其值可以是Select、Insert、Update和References中的任意一项。

系统表columns_priv

表columns_priv用来实现单个字段列的权限设置。

表columns_priv包含7个字段，与系统表mysql.tables_priv相比，该表中多出了Column_name字段，该字段表示可以对哪些字段列进行操作。

系统表procs_priv

表procs_priv包含8个字段，前3个字段分别表示主机名、数据库名和用户名。字段Routine_name表示存储过程或函数的名称。字段Routine_type表示数据库对象类型，其值只能是procedure (存储过程）和function(函数）之一。字段Grantor表示存储权限是谁设置的。字段Proc_priv表示拥有的权限，其值可以是Execute、Alter Routine和Grant字段。

MySQL所提供的用户机制

为了数据库的安全性和完整性,MySQL提供了一整套用户管理机制。用户管理机制包括登录和退出MySQL服务器,创建用户,删除用户,修改用户密码和为用户赋权限等内容。

登录和退出MySQL

1.登录MySQL:

连接MySQL服务器的完整DOS命令如下所示:

mysql -h hostname | hostIp -P port -u username -p DataBaseName -e "SQL语句"

参数说明:

-h:用来指定所连接的MySQL服务器的地址,可以用两种方式来表示。参数hostname表示主机名,参数hostIp表示主机IP地址。

-P:用来指定所连接的MySQL服务器的端口号。

-u:用来指定哪个用户要连接MySQL服务器。

-p:表示将提示输入密码。

DataBaseName:用来指定连接到MySQL服务器后,登录到哪一个数据库中。如果没有指定,默认为系统数据库mysql。

-e:用来指定所执行的SQL语句。

示例:

通过用户账户root登录到MySQL服务器的数据库cmpany中,命令如下：

mysql -h 127.0.0.1-u root -p company

mysql -h 127.0.0.1-u root -p company -e "SELECT  * FROM t_dept";

如果想在具体连接中直接设置密码,而不是在输入密码提示中进行设置,可以通过下面命令来实现,不过该密码需要直接加在参数-p后面,中间绝对不能有空格。

mysql -h 127.0.0.1-u root -p123456 company

2.退出MySQL

退出MySQL服务器的DOS命令如下:

EXIT| QUIT

创建普通用户账号

注意：在具体操作MySQL软件中的数据库对象时，应该严格杜绝使用root用户账户登录MySQL 服务器。仅在绝对需要时使用，而不应该在日常MySQL操作中使用该用户账户.

方法一：执行CREATE USER语句来创建用户账户

CREATE USER username[IDENTIFIED BY [PASSWORD]'password']

[,username[IDENTIFIED BY [PASSWORD]'password']]

……

[,username[IDENTIFIED BY [PASSWORD]'password']]

示例如下:

CREATE USER'cjgong'@'localhost' IDENTIFIED BY '123456';

方法二：执行INSERT语句来创建用户

当向系统表mysql.user中插入数据记录时,一般只需插入Host,User,和Password这三个字段即可。语法如下:

INSERT INTO user(Host,User,Password) VALUES('hostname', 'username', PASSWORD ('password'));

注意：在具体实现创建用户账号时，由于表mysql.user中字段ssl_cipher，x509_issuer，x509_subject 没有默认值，所以还需要设置这些字段的值。对于字段Password的值，一定要使用PASSWORD（）函数进行加密。

示例如下：

INSERT INTO user (Host,User, Password, ssl_cipher, x509_issuer,x509_subject)

VALUES('localhost','cjgong1',PASSWORD('123456'), '', '', '');

flush privileges;

方法三：执行GRANT语句来创建用户（推荐方法）

GRANT priv_type ON databasename.tablename

TO username[IDENTIFIED BY [PASSWORD]'password']

[,username[IDENTIFIED BY [PASSWORD]'password']]

……

[,username[IDENTIFIED BY [PASSWORD]'password']]]

示例如下：

GRANT SELECT ON company.t_dept

TO 'cjogng2'@'localhost' IDENTIFIED BY'123456';

该语句不仅可以创建用户账户,还可以对其进行赋权限。

修改用户口令

利用拥有超级权限用户root修改账户密码

方法一：通过mysqladmin命令修改root用户密码

mysqladmin -u username -p PASSWORD "new_password"

示例如下:

mysqladmin -u root -p PASSWORD "123456"

方法二：通过SET命令修改root用户密码

SET PASSWORD=PASSWORD("new_password")

或

set password for 'root'@'localhost' = password('new_password');

示例如下:

SET PASSWORD=PASSWORD("123456")

方法三：更新系统表mysql.user数据记录修改root用户密码

--5.6版本

UPDATE USER SET PASSWORD=PASSWORD("new_password") WHERE USER="root" AND HOST="localhost";

示例如下:

use mysql;

UPDATE USER SET PASSWORD=PASSWORD("123456") WHERE USER="root" AND HOST="localhost";

flush privileges;

--5.7版本

update user set authentication_string = password('password') where user='username' and host='host';

示例如下:

use mysql;

UPDATE USER SET authentication_string=PASSWORD("123456") WHERE USER="root" AND HOST="localhost";

flush privileges;

注意：旧版本中密码字段是password，而不是authentication_string，必须刷新权限！！！

利用拥有超级权限用户root修改普通用户账户密码

方法一：通过GRANT命令修改普通用户密码

语法如下所示:

GRANT priv_type ON database.table

TO user[IDENTIFIED BY [PASSWORD] ‘new_password’]

示例如下:

GRANT SELECT,CREATE,DROP ON *.*

TO 'cjgong'@'localhost' IDENTIFIED BY'123456'

方法二：通过SET命令修改普通用户密码

语法如下:

SET PASSWORD FOR 'username'@'hostname'=PASSWORD("new_password");

示例如下:

SET PASSWORD FOR 'cjgong'@'localhost'=PASSWORD("123456");

方法三：更新系统表mysql.user数据记录修改root用户密码

-- 5.6版本

UPDATE USER SET PASSWORD=PASSWORD("new_password") WHERE USER="user" AND HOST="localhost";

示例如下:

use mysql;

UPDATE USER SET PASSWORD=PASSWORD("new_password") WHERE USER="cjgong" AND HOST="localhost";

flush privileges;

-- 5.7版本

update user set authentication_string = password('password') where user='username' and host='host';

示例如下:

use mysql;

UPDATE USER SET authentication_string=PASSWORD("123456") WHERE USER="cjgong" AND HOST="localhost";

flush privileges;

注意：旧版本中密码字段是password，而不是authentication_string，必须刷新权限！！！

方法四：普通用户通过SET命令修改自己的账户密码

使用普通用户登录MySQL服务器,可以通过SET命令修改自己的密码。

语法如下:

SET PASSWORD=PASSWORD("new_password");

或

set password for 'username'@'hostname' = password('new_password');

示例如下:

SET PASSWORD=PASSWORD("123456");

删除用户账号

方法一：通过DROP USER语句删除普通用户

语法如下:

Drop user user1 [,user2]...

示例如下:

DROP USER 'cjgong'@'localhost';

方法二：删除系统表mysql.user数据记录实现删除普通用户账号

DELETE FROM USER WHERE USER="user" AND HOST="localhost"

示例如下:

DELETE FROM USER WHERE USER="cjgong" AND HOST="localhost"

flush privileges;

root口令忘记，如何重置口令？

1.首先更改my.cnf的配置文件，并重启mysql

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

skip-grant-tables

[root@localhost ~]# systemctl restart mysqld

2.登录MySQL,此时不需要输入密码，直接回车即可

[root@localhost ~]# mysql -uroot -p

3.切换到mysql数据库，查询user表的结构，修改mysql的root密码并退出mysql

在MySQL5.7版本中mysql数据库下已经没有password这个字段了，password字段改成了authentication_string字段。

mysql> use mysql;

mysql> desc user;

mysql> update user set authentication_string=password('ABCabc123!') where user='root' and host='localhost';

4.再次修改my.cnf配置文件，将第一步添加的语句注释或删除,然后重启mysql。

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

#skip-grant-tables

[root@localhost ~]# systemctl restart mysqld

5.用新密码登录mysql

注意：如果不行的话，你可能还需要执行下面这一步。我用的 MySQL5.7 要执行下面一步。

mysql> alter user root@localhost identified by ''ABCabc123!';

权限管理

权限管理，是指登录到MySQL数据库服务器的的用户需要进行权限验证,只有拥有了权限,才能进行该权限相对应的操作。

权限管理包括授权,查看权限和收回权限。

对用户进行授权

通过GRANT来对普通用户授权,语法如下:

GRANT priv_type [(colunn_list)]ON databasename.tablename

TO username[IDENTIFIED BY [PASSWORD]'password']

[,username[IDENTIFIED BY [PASSWORD]'password']]

……

[,WITH WITH-OPTION[WITH-OPTION]...]

priv_type表示权限的类型;

参数column_list表示权限所作用于的字段;

databasename.tablename表示数据库中的某个表;

参数username表示用户,由用户名和主机名构成；

关键字IDENTIFIED BY用来实现设置密码;

参数with_option的值只能是下面5个值中的一个:

• GRANT OPTION:被授权的用户可以将权限授予其他用户。
• MAX_QUERIES_PER_HOUR count:设置每小时可以执行count次查询。
• MAX_UPDATES_PER_HOUR count:设置每小时可以执行count次更新。
• MAX_CONNECTIONS_PER_HOUR count:设置每小时可以建立count次连接。
• MAX_USER_CONNECTIONS count:设置单个用户可以同时具有count个连接。

示例如下所示:

GRANT SELECT,CREATE,DROP ON *.* TO 'cjgong'@'localhost' IDENTIFIED BY 'cjgong' WITH GRANT OPTION;

查看用户所拥有权限

语法如下:

SHOW GRANTS FOR user;

示例如下:

SHOW GRANTS FOR 'cjgong'@'localhost';

根据执行结果可以显示相应用户的授权语句,从而能够实现查询用户所拥有的权限功能。

收回用户所拥有的权限

语法如下：

REVOKE priv_type [(colunn_list)]ON databasename.tablename

FROM username[IDENTIFIED BY [PASSWORD]'password']

[,username[IDENTIFIED BY [PASSWORD]'password']]

……

[,WITH WITH-OPTION[WITH-OPTION]...]

示例如下：

REVOKE SELECT ON *.* FROM 'cjgong'@'localhost';

回收全部权限的SQL语法如下：

REVOKE ALL PRIVILEGES,GRANT OPTION

FROM user1 [IDENTIFIED BY[PASSWORD] ‘password’]

…

[,user2 [IDENTIFIED BY[PASSWORD] ‘password’]]

示例如下:

REVOKE ALL PRIVILEGES,GRANT OPTIONS FROM'cjgong'@'localhost';

上述实现回收用户cjgong所拥有的全部权限。