MySQL安全性机制

一、学习任务1：MySQL软件所提供的权限

在MySQL软件中存在许多系统数据库，其中一个名为mysql的数据库，在该数据库下存储的都是关于权限的表。其中最重要的表是mysql.user、mysql.db，本节将详细介绍这些涉及权限的系统表。

1.1 系统表mysql.user

在系统数据库mysql，存在一张非常重要的名为user的权限表，通过SQL语句DESC关键字查看表结构，可以发现该表拥有43个字段。这些字段大致可以分为4类，分别为用户字段、权限字段、安全字段和资源控制字段。

1.用户字段

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

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

用户字段名	含义
Host	主机名
User	用户名
Password	密码

2.权限字段

系统表mysql.user中拥有一系列以“_priv”字符串结尾的字段，这些字段决定用户了权限。以“_priv”字符串结尾的字段含义如下表所示。

这些字段的值只有Y和N。Y表示该权限可以用到所有数据库上；N表示该权限不能用到所有数据库上；通常，可以使用GRANT语句给用户赋予一些权限，也可以通过Update语句更新user表的方式来设置权限；不过，修改user表之后，一定要执行一下FLUSH PRIVILEGES，否则可能会出现如下错误：　　Error (1133): Can’t find any matching row in the user table

表权限字段

字段	说明
Select_priv	确定用户是否可以通过SELECT命令选择数据
Insert_priv	确定用户是否可以通过INSERT命令插入数据
Update_priv	确定用户是否可以通过UPDATE命令修改现有数据
Delete_priv	确定用户是否可以通过DELETE命令删除现有数据
Create_priv	确定用户是否可以创建新的数据库和表
Drop_priv	确定用户是否可以删除现有数据库和表
Reload_priv	确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令，包括日志、权限、主机、查询和表
Shutdown_priv	确定用户是否可以关闭MySQL服务器。在将此权限提供给root账户之外的任何用户时，都应当非常谨慎
Process_priv	确定用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程
File_priv	确定用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令
Grant_priv	确定用户是否可以将已经授予给该用户自己的权限再授予其他用户
References_priv	目前只是某些未来功能的占位符；现在没有作用
Index_priv	确定用户是否可以创建和删除表索引
Alter_priv	确定用户是否可以重命名和修改表结构
Show_db_priv	确定用户是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库
Super_priv	确定用户是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程，使用SET GLOBAL修改全局MySQL变量，执行关于复制和日志的各种命令
Create_tmp_table_priv	确定用户是否可以创建临时表
Lock_tables_priv	确定用户是否可以使用LOCK TABLES命令阻止对表的访问/修改
Execute_priv	确定用户是否可以执行存储过程
Repl_slave_priv	确定用户是否可以读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中，有利于主机和客户机之间的通信
Repl_client_priv	确定用户是否可以确定复制从服务器和主服务器的位置
Create_view_priv	确定用户是否可以创建视图
Show_view_priv	确定用户是否可以查看视图或了解视图如何执行
Create_routine_priv	确定用户是否可以更改或放弃存储过程和函数
Alter_routine_priv	确定用户是否可以修改或删除存储函数及函数
Create_user_priv	确定用户是否可以执行CREATE USER命令，这个命令用于创建新的MySQL账户
Event_priv	确定用户能否创建、修改和删除事件
Trigger_priv	确定用户能否创建和删除触发器

3.安全列

user表的安全列有4个字段：ssl_type；ssl_cipher；x509_issuer；x509_subject；　

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

4.资源控制列

user表的4个资源控制列是：默认值为0，表示无限制。

用户字段名	含义
max_questions	每小时允许执行多少次查询
max_updates	每小时允许执行多少次更新
max_connections	每小时可以建立多少次连接
max_user_connections	单个用户可以同时具有的连接数

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

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

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

1.2 系统表mysql.db

在系统数据库mysql中，权限表除了表user外，还有表db。这张表中都存储了某个用户对相关数据库的权限，结构大致相同。这张表所拥有的字段大致可以分为用户字段和权限字段两类。

1.用户字段

系统表mysql.db中的用户字段包含3个字段，各个字段的含义如表所示。

用户字段名	含义
Host	主机名
User	用户名
Db	数据库名

系统表mysql.host是系统表mysql.db的扩展，包含两个字段，各个字段的含义如表所示。

用户字段名	含义
Host	主机名
Db	数据库名

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

2.权限字段

査看系统表mysql.db的表结构，可以发现这张表的权限字段与user表几乎相同。

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

1.3其他权限表

在系统数据库mysql中，除了上述所介绍的权限表外，还有表table_priv和columns_priv。其中表tables_priv用来实现单个表的权限设置，表columns_priv用来实现单个字段列的权限设置。

1.系统表mysql.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中的任意一项。

2.系统表columns_priv的表结构

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

3.系统表procs_priv的表结构

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

二、学习任务2：MySQL软件所提供的用户机制

用户应该对所需的数据具有适当的访问权限，即用户不能对过多的数据库对象具有过多的访问权，这是MySQL软件的安全基础。为了实现数据的安全性和完整性，MySQL软件专门提供了一整套用户管理机制。用户管理机制包括登录和退出MySQL服务器、创建用户、删除用户、修改用户密码和为用户赋予权限等内容。

2.1登录和退出MySQL软件的完整命令

在DOS窗口中通过DOS命令来连接数据库。

mysql -h hostname|hostIP -pport -u usename -p DatabaseName -e "SQL语句"

上述命令中各参数的含义如下。

参数-h：用来指定所连接MySQL服务器的地址，可以用两种方式来表示。参数hostname表示主机名；参数hostIP表示主机IP地址。
参数-p：用来指定所连接MySQL服务器的端口号。由于MySQL软件在安装过程中，MySQL服务的端口号为默认为3306，因此如果没有指定该参数时，默认通过端口3306连接MySQL 服务器。
参数-u：用来指定哪个用户要连接MySQL服务器„
参数-p：表示将提示输入密码。
参数databasename：用来指定连接到MySQL服务器后，登录到哪一个数据库中。
参数-e：用来指定所执行的SQL语句。

如果出现如上的错误，建议按照下列步骤解决：

1、打开MySQL目录下的my.ini文件，在文件的最后添加一行“skip-grant-tables”，保存并关闭文件。

2、重启MySQL服务。

3、在dos窗口命令行中输入“mysql -uroot -p”(不输入密码)，回车即可进入数据库。

4、执行，“use mysql;”使用mysql数据库。

5、执行，“update user set password=PASSWORD("rootadmin") where user='root';”（修改root的密码）

6、打开MySQL目录下的my.ini文件，删除最后一行的“skip-grant-tables”，保存并关闭文件。 7、重启MySQL服务。

8、在命令行中输入“mysql [–h localhost] –u root –prootadmin”，如果输入mysql –h 127.0.0.1 –u root –prootadmin还会报错。

【实例16-1】在DOS窗口中，通过用户账户root登录到MySQL服务器的数据库company中，具体命令内容句如下：

mysql -h localhost -u root -p company

【代码说明】在上述命令中，通过值localhost指定所连接MySQL服务器的地址，参数-u指定了登录MySQL服务器的用户账户，参数-p表示会出现输入密码提示信息，最后值“company”指定了所连接的数据库。

如果想在具体连接中直接设置密码，而不是在输入密码提示中进行设置，可以通过如下命令来实现：

mysql -h localhost -u root -proot company

【代码说明】上述命令中，参数-p指定了用户账户密码。

【运行效果】执行上面的命令，其结果如图所示，可以发现将不会提示输入密码。

注意：在具体连接MySQL服务器时，可以直接设置用户账户密码，不过该密码需要直接加在参数-p的后面，中间绝对不能有空格。

【实例16-2】在DOS窗口中，通过用户账户root登录到MySQL服务器的数据库company中，同时执行査询表t_dept中所有数据记录的SQL语句，具体命令如下：

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

【代码说明】上述命令中，通过参数-e指定了所执行的SQL语句。

【运行效果】执行上面的命令，其结果如图所示。

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

EXIT|QUIT

在上述命令中,使用命令Exit或Quit都可以退出MySQL服务器,最后Quit的缩写形式\q、也可以实现退出MySQL服务器。

2.2创建普通用户账户

通过前面的知识可以知道，在安装MySQL软件时，默认为创建一个名为root的用户账户，由于该用户账户拥有超级权限，因此可以对整个服务器具有完全的控制。如果每次都通过用户名root 登录MySQL服务器进行各种数据库操作，是不是不太合适，因为该用户的权限太大了。

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

在具体开发应用中，不应该使用root用户账户，而应该创建一系列的用户账户，分别为专门用于管理的用户账户、专门供开发人员使用的用户账户等。在MySQL软件中，可以通过以下3种方式来创建普通用户（具有普通权限的用户）。

1.执行CREATE USER语句来创建用户账户

在MySQL数据库管理系统中创建用户账户通过SQL语句CREATE USER 来实现，其语法形式如下：

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

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

[,username[IDENTIFIEDBY [PASSWORD] 'password']

在上述语句中，关键字USER用来设置用户账号的名字，关键字IDENTIFIED BY用来设置用户账号的密码。值username表示所设置的用户账号名，由用户名和主机名构成。值password表示所设置的用户账户密码，如果其是一个普通的字符串，则不需要关键字PASSWORD,该关键字主要用来实现对密码进行加密。

【实例16-3】执行SQL语句CREATE USER,创建名为cjgong密码为123456的用户账户。

（1）通过用户root，连接到MySQL数据库软件，具体语句如下：

mysql -h localhost -u root -proot

（2）执行CREATE USER，实现创建用户账号，具体语句如下:

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

【运行效果】执行上面的语句，其结果如图所示。

执行结果显示，己经为MySQL软件创建了一个名为cjgong密码为123456的用户账户。

2.执行INSERT语句来创建用户

根据前面的知识可以知道，系统权限表mysql.user中存储了关于用户账户的信息，所以可以通过向该表插入数据记录来实现创建用户账号。当向系统表mysql.user中插入数据记录时，一般只需插入Host、User和Password这3个字段的值即可，具体语法形式如下：

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

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

【实例16-4】执行SQL语句CREATE USER,创建名为cjgong1密码为123456的用户账户。

（11）通过用户root，连接到MySQL数据库软件，具体SQL语句如下：

mysql -h localhost -u root -proot

（2）执行SQL语句INSERT INTO，实现创建用户账号，具体SQL语句如下：

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

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

【运行效果】执行上面的语句，其结果如图所示。

执行结果显示，已经为MySQL软件创建了一个名为cjgong1密码为123456的用户账户。这时如果在DOS窗口通过该用户账号登录MySQL服务器，不会登录成功，具体执行过程如图所示。

之所以不会登录成功，是由于名为cjgong1用户账户还没有生效。

（3）执行命令FLUSH,使用户账号cjgong1生效，具体命令内容如下:

flush privileges;

这时如果通过该用户账号重新登录，就不会登录失败：

3.执行GRANT语句来创建用户

虽然CREATE USER语句和INSERT INTO语句都可以创建普通用户，但这两种方式不方便为用户赋权限，这时可以使用GRANT。

在MySQL数库管理系统中创建用户账户通过SQL语句GRANT 来实现，其语法形式如下：

GRANT priv_type on databasename.tablename （*.*）

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

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

……

[,username[IDENTIFIEDBY [PASSWORD] 'password']

在上述语句中，参数priv_type表示用户实现设置所创建用户账号的权限；参数databasename.tablename表示所创建用户账号的权限范围，即只能在指定的数据库和表上使用这些权限；其他部分与CREATE USER语句一致。

【实例16-5】执行SQL语句GRANT，创建名为cjgong2密码为123456的用户账户，同时设置其只具有select权限，具体步骤如下：

（1）执行SQL语句 GRANT，实现创建用户账号，具体SQL语句如下:

GRANT SELECT ON company.t_dept

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

【运行效果】执行上面的SQL语句，其结果如图所示。

执行结果显示，已经创建了一个名为cjgong2密码为123456的用户账户，该用户账号只对表company.t_dept具有査询权限。

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

用户管理机制包括登录和退出MySQL服务器、创建用户、修改用户密码、删除用户和为用户赋予权限等内容。本节将详细介绍如何修改用户账号，即修改用户账号密码。在MySQL软件中可以通过两种方式来修改用户账号，分别为通过超级权限用户root修改用户账户密码和通过普通权限用户修改用户账号密码。

本节将详细介绍如何利用用户账户root修改用户账户密码，按照所修改用户账户的权限分为修改root用户密码和修改普通用户密码两种类型。

在MySQL数据库管理系统中，修改超级权限用户root的密码可以通过三种方式来实现，分别为通过mysqladmin命令修改密码、通过SET语句修改密码和更新系统表 mysql.user数据记录。

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

在MySQL数据库管理系统中修改root用户密码通过命令来实现，其语法形式如下：

mysqladmin -u username -p password "new_password"

在上述命令中参数u表示用户名，参数p表示密码，password为关键字。参数new_password必须用双引号（“”）括起来。

【实例16-6】执行命令mysqladmin，修改用户root的密码为rootadmin，具体步骤如下:

（1）在DOS窗口里，执行命令mysqladmin，具有内容如下：

mysqladmin [-h localhost] -u root -p password "rootadmin"

【代码说明】上述命令中，实现修改超级用户root的密码为rootadmin。

【运行效果】执行上面的命令语句，其结果如图所示。

（2）在执行命令mysqladmin的过程中，只有输入正确的旧密码就可以成功修改密码。修改密码后，就可以通过修改后的密码进行登录，具体内容如下：

mysql -h localhost -u root -prootadmin

【运行效果】执行上面的语句，其结果如图所示。

2.通过set命令修改root用户密码

当通过root用户账户登录到MySQL服务器后，可以通过SET命令修改root用户账户密码。其语法形式如下：

SET PASSWORD=PASSWORD(''new_password");

在上述命令中，需要通过函数PASSWORD（）来加密新密码new_password。

【实例15-7】执行命令set修改用户root的密码为root（再修改回来），具体步骤如下：

（1）执行命令SET,修改用户账户root的密码为root，具体内容如下：

SET PASSWORD=PASSWORD("root");

【运行效果】执行上面的SQL语句，其结果如图所示。

（2）在执行命令set后，为了校验修改密码是否成功，可以通过修改后的密码root重新登录MySQL服务器，具体内容如下：

mysql -h localhost -u root –proot

【代码说明】上述命令中，通过修改后的密码登录MySQL服务器。

【运行效果】执行上面的语句，其结果如图所示。

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

当通过root用户账户登录到MySQL服务器后，可以通过更新系统表mysql.user的数据记录来修改root账户用户密码。具体语法形式如下：

UPDATE user SET password=PASSWORD("new_password")WHERE user="root" AND host="localhost";

上述SQL语句中，通过更新语句更新表user中字段password的值，条件为user="root" AND host="locaIhost" ;

【实例15-8】执行SQL语句update，更新系统表user中的信息，具体步骤如下：

（1）执行SQL语句UPDATE，修改用户账户root的密码为rootadmin,具体内容如下:

UPDATE USER SET PASSWORD=PASSWORD ("rootadmin")

WHERE USER='ROOT' AND HOST='LOCALHOST';

修改成功后要执行刷新操作flush privileges

【运行效果】执行上面的SQL语句，其结果如图所示。

（2）为了校验修改密码是否成功，可以通过修改后的密码rootadmin重新登录MySQL服务器，具体内容如下：

mysql -h localhost -u root –prootadmin

可以登录成功。

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

通过超级权限用户root修改普通用户账户也有三种方式，分别为通过GRANT 命令修改密码、通过SET命令修改密码和更新系统表mysql.user数据记录。为了便于讲解，创建的名为cjgong，密码为cjgong的用户账户，具体语法如下：

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

WITH GRANT OPTION;

【代码说明】上述命令中，创建普通用户cjgong,同时设置该用户的密码为cjgong。

1.通过GRANT命令修改cjgong用户密码

在MySQL数据库管理系统中修改cjgong用户密码通过GRANT命令来实现，其语法形式如下：

GRANT priv_type ON database.table

TO user[IDENTIFIED BY[PASSWORD] 'new_password']

在上述语句中，参数priv_type用来设置普通用户的权限，参数database.table用来设置用户的权限范围，参数user表示新用户账户，由用户名和主机名构成。值new_password表示为用户设置的新密码。

注意：参数new_password必须用双引号（“”）括起来.

【实例15-9】利用具有超级用户权限的用户root,修改普通用户cjgong的密码为123456，具体步骤如下：

（1）通过用户root，连接到MySQL数据库软件，具体SQL语句如下：

mysql -h localhost -u root -proot

（2）执行GRANT，修改普通用户账号cjgong的密码。具体SQL语句如下：

GRANT SELECT,CREATE,DROP ON *.*

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

【运行效果】执行上面的语句，其结果如图所示。

（3）为了校验修改密码是否成功，首先通过命令exit退出root用户登录，然后再通用户cjgong

利用密码123456重新登录MySQL服务器，具体内容如下：

EXIT

mysql -h localhost -u cjgong –p123456

【代码说明】在上述语句中，首先通过命令EXIT退出用户账户root登录，然后通过用户账户cjgong重新登录MySQL软件。

【运行效果】执行上面的语句，其结果如图所示。

2.root账户通过set命令修改密码修改cjgong用户密码

当通过root用户账户登录到MySQL服务器后，可以通过SET命令修改普通用户账户cjgong 的密码。其语法形式如下：

SET PASSWORD FOR

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

在上述命令中，参数usemame用来表示普通用户的用户名，参数new_password用来表示所要设置的新密码。

【实例15-10】执行命令SET,修改用户cjgong的密码为cjgong，具体步骤如下：

（1）通过用户root,连接到MySQL数据库软件，具体语句如下：

mysql -h localhost -u root -proot

（2）执行上面的SQL语句，其结果如图所示。

SET PASSWORD FOR

'cjgong'@'localhost'=PASSWORD("cjgong");

【运行效果】执行上面的语句，其结果如图所示。

（3）为了校验修改密码是否成功，首先通过命令EXIT退出root用户登录，然后再通用户cjgong 利用密码cjgong重新登录MySQL服务器，具体内容如下：

EXIT

mysql –h localhost -u cjgong –pcjgong

【代码说明】在上述语句中，首先通过命令EXIT退出用户账户root登录，然后通过用户账户 cjgong重新登录MySQL软件。

根据执行结果，可以发现已经修改用户账户qjgong的密码为cjgong。

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

当通过root用户账户登录到MySQL服务器后，可以通过更新系统表mysql.user的数据记录来修改cjgong账户用户密码。具体语法形式如下：

UPDATE user SET password=PASSWORD("new_password")

WHERE user="cjgong" AND host="localhost";

【实例15-11】执行SQL语句UPDATE，更新系统表user中的信息，具体步骤如下：

（1）通过用户root,连接到MySQL数据库软件；

（2）执行SQL语句use，选择数据库mysql；

（3）执行SQL语句UPDATE，修改用户账户cjgong的密码为123456，

UPDATE USER SET password=PASSWORD('123456')

WHERE USER='cjgong' AND host='localhost';

【运行效果】执行上面的SQL语句，其结果如图所示。

（4）为了校验修改密码是否成功，首先通过命令EXIT退出root用户登录，然后再通用户cjgong 利用密码123456重新登录MySQL服务器，具体内容如下：

EXIT

mysql -h localhost -u cjgong –p123456

【代码说明】在上述语句中，首先通过命令EXIT退出用户账户root登录，然后通过用户账户 cjgong重新登录MySQL软件。

根据执行结果，可以发现己经修改用户账户cjgong的密码为123456。

4.普通账户通过SET命令修改密码修改cjgong用户密码

当通过普通用户账户登录到MySQL服务器后，可以通过SET命令修改自己的密码。其语法形式如下：

SET PASSWORD=PASSWORD("new_password");

【实例15-12】执行命令set，修改用户cjgong的密码为cjgong,具体步骤如下:

（1）通过用户cjgong，连接到MySQL数据库软件，具体SQL语句如下：

mysql -h localhost -u cjgong –p123456

（2）执行set命令，修改cjgong账户密码为cjgong，

SET PASSWORD=PASSWORD("cjgong");

（3）为了校验修改密码是否成功，首先通过命令EXIT退出root用户登录，然后再通用户cjgong 利用密码cjgong重新登录MySQL服务器，具体内容如下：

EXIT

mysql -h localhost -u root –pcjgong

【代码说明】在上述语句中，首先通过命令EXTT退出用户账户root登录，然后通过用户账户 cjgong重新登录MySQL软件。

2.5 删除普通用户账户

用户账号的操作包括创建用户账号和删除用户账号。本节将详细介绍如何删除用户账号。在MySQL软件中可以通过两种方式来删除用户账号,分别为通过drop user语句和通过删除系统表mysql.user里相应数据记录实现删除用户账号。

1.通过drop user语句删除普通用户

在MySQL软件中删除普通用户通过SQL语句DROP USER来实现，其语法形式如下：

DROP USER user1[,user2]...

在上述语句中，参数user表示所要删除的用户，在MySQL软件中，用户由用户名（user）和主机名（host）组成。

2.删除系统表mysql.user数据记录实现删除cjgong用户账号

当通过root用户账号登录到MySQL服务器后，可以通过更新系统表mysql.user的数据记录来

修改root账户用户密码。具体语法形式如下：

DELETE FROM user

WHERE user='cjgong" AND host="localhost";

三、学习任务3：权限管理

为了进行权限管理，MySQL软件在数据库mysql的user表中存储了各种类型权限。权限管理，是指登录到MySQL数据库服务器的用户需要进行权限验证，只有拥有了权限，才能进行该权限相对应的操作。合理的权限管理能够保证数据库系统的安全，不合理的权限管理会给数据库服务器带来非常可怕的安全隐患。

3.1 对用户进行授权

权限管理包含授权、查看权限和收回权限。本节将详细介绍如何对用户进行授权。授权是指为用户赋予相应的权限。合理的授权能够保证数据库的安全，不合理的授权会给数据库带有安全隐患。在进行授权操作之前，首先需要用户具有GRANT权限。

root用户和普通用户，其中前者为超级管理员，拥有MySQL软件提供的一切权限；而普通用户则只能拥有创建用户时赋予它的权限。

在MySQL软件对用户授权通过SQL语句GRANT来实现，其语法形式如下：

GRANT priv_type [(column_list)] ON database.table TO user [IDENTIFIED BY[PASSWORD] 'password'] [,user [IDENTIFIED BY[PASSWORD] 'password']]

…

[WITH with-option]

在上述语句中，参数priv_type表示权限的类型，参数column_list表示权限所作用于的字段，当省略该参数时表示作用于整个表；参数database.table表示数据库中的某个表，参数user表示用户，由用户名和主机名构成，关键字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个连接。

【实例15-13】执行SQL语句GRANT,为用户账号cjgong授权相应权限。

（1）由于root用户为超级管理员，拥有MySQL软件提供的一切权限，所以可以通过该用户连接到MySQL数据库服务器进行授权操作，具体SQL语句如下：

mysql -h localhost -u root -proot

（2）执行SQL语句GRANT，创建一个名为cjgong，密码为cjgong的用户账号，同时设置该用户对所有数据库里的数据具有select、create、drop以及grant权限，具体语法如下：

GRANT SELECT,CREATE,DROP ON *.*

TO 'cjgong' @'localhost' IDENTIFIED BY 'cjgong' WITH GRANT OPTION;

（3）为了校验修改授权是否成功，查询系统表user中关于用户cjgong的数据记录，具体内容如下：

select * from user where user='cjgong'\G

（4）创建cjgong1密码为cjgong1的普通账户，

CREATE USER 'cjgong1'@'localhost' IDENTIFIED BY 'cjgong1';

（5）通过命令GRANT退出EXIT用户登录，然后再通过普通用户cjgong登录MySQL服务器进行授权操作，具体内容如下：

EXIT

mysql -h localhost -u cjgong –pcjgong

（6）执行SQL语句GRANT，通过用户cjgong为用户cjgong1授权，具体语法如下：

GRANT SELECT,CREATE,DROP ON *.* TO 'cjgong1'@'localhost';

【代码说明】在上述命令中，为用户cjgong1授予了 SELECT、CREATE、DROP权限。

【运行效果】执行上面的命令语句，其结果如图所示。

（7）为了校验修改授权是否成功，査询系统表mysql.user中关于用户cjgong1的数据记录，具体内容如下：

select * from user where user='cjgong1'\G

（8）执行SQL语句GRANT,通过用户cjgong为用户cjgong2赋予UPDATE等权限，具体语法如下：

GRANT SELECT,CREATE,update ON *.* TO 'cjgong2'@'localhost';

【代码说明】在上述命令中，为用户cjgong2授予了 UPDATE、SELECT、CREATE、DROP权限.

在为用户cjgong授权时，with_option选项的值为WITH GRANT OPTION,表示被授权的用户cjgong可以将这些权限（SELECT、CREATE、DROP、GRANT 权限）赋予给其他用户（cjgong1）。但是由于用户cjgong将权限update赋予给了用户cjgong2,超出了cjgong用户所拥有的权限，会报错。

3.2 查看用户所拥有权限

权限管理包含授权、查看权限和收回权限。本节将详细介绍如何查看用户所拥有的权限。可以通过査看系统表mysql.user中的数据记录来查看相应用户的权限。除了该种方式外，MySQL软件还专门提供了SQL语句SHOW GRANTS实现査看权限。

在MySQL软件中查看用户权限通过SQL语句SHOW GRANT来实现，其语法形式如下：

SHOW GRANTS FOR user

3.3 收回用户所拥有权限

权限管理包含授权、查看权限和收回权限，既然可以给用户进行授权，那么就应该能够回收赋予用户的权限。在上面章节己经详细介绍关于授权和查看授权操作，本节将详细介绍如何收回权限。

收回权限是指取消用户的某些权限。收回用户不应该拥有的权限，可以很好地消除数据库的安全

隐患。

在MySQL软件收回权限通过SQL语句REVOKE来实现，其语法形式如下：

REVOKE priv_type[(column_list)] ON database. table

FROM user1[IDENTIFIED BY[PASSWORD]) 'password']

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

由于回收权限语法与授权语法非常相似，所以就不详细介绍各个参数。

除了上述语法形式外，MySQL软件还专门提供了一种回收全部权限的SQL语句，具体语法形式如下：

REVOKE ALL PRIVILEGES,GRANT OPTION

FRCM user1[IDENTIFIED BY[PASSWORD] 'password']

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

上述语句主要用来实现回收用户所拥有的全部权限。

四、学习任务4：数据库备份还原

所谓数据库维护，主要包含备份数据、还原数据和数据库迁移。通过数据备份和还原可以保证MySQL服务器的数据安全。

4.1 通过复制数据文件实现数据备份

备份数据是数据库维护中最常用的操作，通过备份后的数据文件可以在数据库发生故障后还原和恢复数据。可能造成数据损失的原因很多，包含如下几个方面。

存储介质故障：保存数据库文件的磁盘设备损坏，用户没有数据库备份导致数据彻底丢失。
用户的错误操作：如误删除了某些重要数据，甚至整个数据库。
服务器的彻底瘫痪：如数据库服务器彻瘫痪，系统需要重建。

由于MySQL服务器中的数据文件是基于磁盘的文本文件，所以最简单、最直接的备份操作，就是数据文件直接复制出来。由于MySQL服务器的数据文件在服务器运行时期，总是处于打开和使用状态，因此文本文件副本备份不一定总是有效。为了解决该问题，在具体复制数据文件时，需要先停止MySQL数据库服务器。

注意：为了保证所备份数据的完整性，在停止MySQL数据库服务器之前，需要先执行FLUSH TABLES语句将所有数据写入到数据文件的文本文件里。

虽然停止MySQL数据库服务器，可以解决复制数据文件实现数据备份的问题，但是这种方法不是最好的备份方法。这是因为实际情况下，MySQL数据库服务器不允许被停止，同时该种方式对InnoDB存储引擎的表也不适合。

注意：在通过复制数据文件方式实现数据备份时，只适合存储引擎为MyISAM的表。

在Windows操作系统下，MySQL数据库服务器的数据文件经常存放在如下3个路径之一。

C:\mysql\date 目录。
C:\Documents and Settings\ALL Users\Application Data\MySQL\MySQL Server 5.6\data 目录。
C:\Program Files\MySQL\MySQL Server 5.6\data 目录（本书 MySQL 数据库服务器所安装的目录）。

如果需要进行备份操作，可以直接复制上述相关目录里的数据文件。

4.2 通过命令mysqldump实现数据备份

除了可以通过复制数据文件实现数据备份外，还可以通过其他方式来实现。在MySQL软件中经常通过命令mysqldump实现数据备份，即该命令会将包含数据的表结构和数据内容保存在相应的文本文件。具体执行时，首先会检査所需要备份数据的表结构，在相应的文本文件中生成CREATE语句。然后检査数据内容，在相应的文本文件中生成INSERT INTO语句。将来如果需要进行还原数据，只需执行文本文件中的CREATE语句和INSERT INTO语句。在具体使用命令mysqldump时，经常分为以下3种形式。

备份一个数据库。
备份多个数据库。
备份所有数据库。

下面将详细介绍命令mysqldump的使用方式。

1.备份一个数据库

在MySQL软件中备份一个数据库通过命令mysqldump来实现，其命令形式如下：

mysqldump -u username -p dbname table1 table2 … tablen

>backupname.sql

在上述语句中，usemame参数表示用户名;参数dbname表示数据库；参数table表示所要备份的表，如果没有参数table,则表示备份整个数据库；参数backupname表示所生成的备份文件。

注意：备份文件一般以.sql为扩展名，也可以使用其他扩展名，不过.sql扩展名的文件给人的感觉就是与数据库的文件有关。

【实例17-1】通过超级用户root登录到MySQL服务器，然后备份company数据库下的t_employee 表，具体步骤如下：

（1）于DOS窗口中执行命令mysqldump，备份数据库company下的表t_employee，具体内容如下：mysqldump -u root -p company t_dept> D:\t_dept_back.sql

【代码说明】在上述语句中，通过用户root对数据库company里的表t_employee进行备份，同时设置备份文件为D磁盘下的文件t_employee_ back.sql

【运行效果】执行上面的命令语句，其结果如图所示。

打开磁盘D，将创建一个名为t_employee_back.sql的新文件，具体内容如下：

【代码说明】上述语句中存在两种注释，以“--”开头都是关于SQL语言的注释，以“/*!40014” 开头都是关于与MySQL 5.6.14服务器相关的注释。整个文档开始部分记录了 MySQL服务器的版本、主机名和数据库名，分别为Server version 5.6.14、localhost和company。文档中存在DROP语句、 CREATE语句和“INSERT INTO”语句，它们分别为将来还原数据库时使用。其中“DROP TABLE ff EXISTS't_employee';"语句用来判断数据库中是否还有名为t_employee的表；如果存在，则刪除掉这个表；其中“CREATE TABLE”语句用来实现创建表；“INSERT INTO”语句用来实现还原表里的数据。

注意：在具体还原数据时，如果MySQL服务器的版本比5.5.21高，“/*!40000”和“*/”间的代码将被当作SQL命令来执行；如果MySQL服务器的版本比5.5.21低，“/*!40000”和“*”之间的代码将被当作注释.

2.备份多个数据库

在MySQL软件中备份多个数据库通过命令mysqldump来实现，其语法形式如下：

mysqldump -u username -p --databases dbname1 dbname2 … dbnamen

> backupname.sql

上述语句中多出一个名为databases选项，该选项用来设置所备份的数据库。

【实例17-2】通过超级用户root登录到MySQL服务器，然后备份company和company1数据库，具体步骤如下。

（1）在DOS窗口中执行命令mysqldump,备份数据库company和company1,具体内容如下：

mysqldump -u root -p --databases company company1> D:\database_company_back.sql

【代码说明】上述语句中，通过选项设置备份数据库company和company1。

【运行效果】执行上面的命令语句，其结果如图所示。

通过查看文件database_company_back.sql，可以发现不仅对数据库company进行了备份，而且还对数据库company1进行了备份。

3.备份所有数据库

在MySQL软件中备份所有数据库通过命令mysqldump来实现，其语法形式如下：

mysqldump -u username -p --all –databases > backupname.sql

上述语句中多出一个名为all的选项，用来实现备份所有数据库。

mysqldump -u root -p --all-databases> D:\all_databases_back.sql

【代码说明】在上述语句中，通过“--all-databases”选项设置备份所有数据库。

4.3 通过复制数据实现数据还原

还原数据是数据库维护中最常用的操作，利用备份文件可以将MySQL数据库服务器还原到备份时状态，这样就可以将管理员的非常操作和计算机的故障造成的相关损失降到最小。

前面章节介绍了通过复制数据文件实现数据备份，可以通过复制该种方式所生成的备份文件来实现还原操作。在通过复制数据文件这种方式实现数据还原时，必须保证两个MySQL数据库的主版本号一致，因为只有MySQL数据库主版本号相同时，才能保证两个MySQL数据库的文件类型是相同的。由于通过复制数据文件实现数据备份时，对存储引擎类型为InnoDB的表不可用，仅对存储引擎为MyISAM类型的表有效。因此通过复制数据文件实现数据还原时，也只对存储类型为 MyISAM类型的表有效。

注意：关于MySQL数据库服务器的版本号，第一个数字表示主版本号，只有主版本号一致的 MySQL数据文件，其文件类型才会相同。例如MySQL5.5.2和MySQL5.5.0这两个版本的主版本号都是5，这两个数据库的数据文件拥有相同类型的数据文件。

4.4 通过命令mysql实现数据还原

除了可以通过复制数据文件实现数据还原外，还可以通过其他方式来实现。在MySQL软件中经常通过命令mysql实现数据还原。在MySQL软件中还原数据库通过命令mysql来实现，其语法形式如下：

mysql -u username -p [dbname]< backupname. sql

在上述语句中，usemame参数表示用户名；参数backupname表示所用来还原的备份文件；参数dbname用来指定数据库的名称，可以指定也可以不指定，指定数据库时表示还原该数据库下的表，不指定数据库时表示还原备份文件中的所有数据库。

在具体执行命令mysql时，将执行备份文件中的create和insert into语句，即通过执行CREATE语句创建数据库，通过执行INSERT INTO语句插入所备份的表中数据。

通过超级用户root登录到MySQL服务器，然后利用上面章节所生成的各种备份文件还原MySQL数据库服务器。

【实例17-3】利用备份文件还原数据库下的表，即通过备份文件t_dept_back.sql还原数据库 company下的表t_dept,具体步骤如下：

执行命令mysql，删除company中的表t_dept，具体命令内容如下图：

（2）执行命令mysql，还原company中的表t_dept，具体命令内容如下

mysql -u root -p company <D:\t_dept_back.sql

【代码说明】在上述语句中，通过用户root利用D磁盘下的文件t_dept_back.sql,对数据库company里的表t_dept进行还原。

【运行效果】执行上面的命令语句，其结果如图所示。

（3）为了校验还原操作是否成功，执行下面语句查看表t_dept的数据内容，具体SQL语句如下:

Show tables；