MySQL中主要包括两种用户:root用户和普通用户,其中前者为超级管理员,拥有MySQL提供的一切权限；而普通用户则只能拥有创建用户时赋予它的权限。

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

1.MySQL所提供的权限

在系统数据库mysql下存储的都是权限的表,其中最重要的表是mysql.user,mysql.db,mysql.host。

1.1 系统表mysql.user

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

用户字段:用户字段包含三个字段,主要用来判断用户是否能够登录成功。

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

安全字段:安全字段主要包括4个字段,包含ssl字段的字段主要用来实现加密,包含x509的字段主要用来标识用户。

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

1.2 系统表mysql.db和mysql.host

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

2. MySQL所提供的用户机制

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

2.1 登录和退出MySQL

2.1.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.1.2 退出MySQL

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

EXIT | QUIT

2.2 创建普通用户账号

2.2.1 执行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';

2.2.2 执行INSERT语句来创建用户

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

INSERT INTO USER(HOST,USER,PASSWORD) VALUES('hostname','username',PASSWORD('password');

2.2.3 执行GRANT语句来创建用户

GRANT priv_type ONdatabasename.tablenameTO username[IDENTIFIED BY [PASSWORD] 'password'][,username[IDENTIFIED BY [PASSWORD] 'password']]

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

示例如下：

GRANT SELECT ONcompany.t_deptTO 'cjogng2'@'localhost' IDENTIFIED BY '123456';

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

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

2.3.1 通过mysqladmin命令修改root用户密码

mysqladmin -u username -p PASSWORD "new_password"

示例如下:

mysqladmin -u root -p PASSWORD "123456"

2.3.2 通过SET命令修改root用户密码

SET PASSWORD=PASSWORD("new_password")

示例如下:

SET PASSWORD=PASSWORD("123456")

2.3.3 更新系统表mysql.user数据记录修改root用户密码

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

示例如下:

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

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

2.4.1 通过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'

2.4.2 通过SET命令修改普通用户密码

语法如下:

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

示例如下:

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

2.4.3 更新系统表mysql.user数据记录修改root用户密码

语法如下:

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

示例如下:

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

2.4.4 通过SET命令修改普通用户密码(2)

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

语法如下:

SET PASSWORD=PASSWORD("new_password");

示例如下:

SET PASSWORD=PASSWORD("123456");

2.5 删除普通用户账号

2.5.1 通过DROP USER语句删除普通用户

语法如下:

Drop user user1 [,user2]...

示例如下:

DROP USER 'cjgong'@'localhost';

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

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

示例如下:

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

3. 权限管理

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

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

3.1 对用户进行授权

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

GRANT priv_type [(colunn_list)] ONdatabasename.tablenameTO 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;

3.2 查看用户所拥有权限

语法如下:

SHOW GRANTS FOR user

示例如下:

SHOW GRANTS FOR 'cjgong'@'localhost'

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

3.3 收回用户所拥有的 权限

语法如下：

REVOKE priv_type [(colunn_list)] ONdatabasename.tablenameTO 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,GRANTOPTIONSFROM 'cjgong'@'localhost'

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