MySQL 第八章MySQL用户权限与维护

    为了方便，前面都是用root用户来登录MySQL访问数据库数据，本章介绍如何添加用户并给用户授权。例如：可以允许一个用户创建新的表，另一个用户被授权更新现有的表，第三个用户只能查询表。

    可以使用标准的sql语句——grant和revoke语句来修改控制客户访问的授权表

8.1 用户管理

8.1.1 添加、删除用户

1. 添加用户

    用户可以使用create user语法添加一个或多个用户，并设置相应的密码：

create user 用户 [identified by [password] '密码' ]
[,...]

用户的格式为：

'用户名' @ '主机名'

• 大多数SQL产品中，用户名和密码只由字母和数字组成
• identified by 为账户给定一个密码。特别是要在纯文本中指定密码，需忽略password关键字，如果不想以明文发送密码，而且知道password()函数返回给密码的混编值，则可以指定该混编值，但要加关键字password。
• create user 用户创建新的MySQL账户，此后会在系统本身的mysql数据库的user表中添加一个新纪录
• 要使用该命令，必须拥有mysql数据库的全局create user权限或insert权限，如果用户账户已经存在则会出现错误

例如：添加两个新用户，king的密码为queen，palo的密码为530415

 create user
 'king'@'localhost' identified by 'queen',
 'palo'@'localhost' identified by '530415';

完成后可切换到mysql数据库，从user表中查到刚刚添加的两个用户记录

use mysql
show tables;
select * from user;

• 如果一个用户名和主机名中包含特殊符号如”_“，或通配符如”%“，则需要用单引号将其括起来。”%“表示一组主机
• 如果两个用户具有相同的用户名但是主机名不一致，MySQL将其视为不同用户，允许为这两个用户分配不同的权限合集
• 如果没有输入密码，那么MySQL允许相关的用户不适用密码登录，但是从安全的角度不推荐这种做法
• 刚创建的用户还没有很多权限，他们可以登录到MySQL，但是他们不能使用use语句在让用户已经创建的任何数据库称为当前数据库，因此无法访问那些数据库的表，只允许进行不需要权限的操作，例如用一条show语句查询所有存储引擎和字符集的列表

2. 删除用户

    删除用户的语法格式如下：

drop user 用户 [, 用户]....

    要使用drop user 必须拥有mysql数据库的全局create user权限或delete权限
例如：删除用户palo

drop user palo@localhost;

    如果被删除的用户已创建了表，索引或其他数据库对象，他们将继续保留，因为mysql并没有记录是由谁创建了这些对象。

8.1.2 修改用户名，密码

1. 修改用户名

rename user 老用户 to 新用户
[,..]

    要使用该命令，必须拥有mysql数据库的全局create user权限或mysql数据库update权限，如果旧帐户不存在或者新账户已存在则会出现错误。

例如：将用户king的名字修改为ken

rename user
'king'@'localhost' to 'ken'@'localhost';

2. 修改某个用户的密码

set password [for 用户]='新密码';

    如果不加for用户则为修改当前用户密码，加了for 用户则修改特定用户密码

例如：将用户ken的密码修改为qen

set password for 'ken'@'localhost' ='qen';

8.2 权限控制

8.2.1 授予权限

    新的SQL用户不允许访问属于其他sql用户的表，也不能立即创建自己的表，它必须被授权。可以授权的权限有以下几组。

1. 列权限：和表中的一个具体列相关。例如，使用update语句更新表xs学号列的值的权限
2. 表权限：和一个具体表中的所有数据相关，例如，使用select语句查询表xs的所有数据的权限。
3. 数据库权限：和一个具体的数据库中所有表相关，例如，在已有的xscj数据库中创建新表的权限
4. 用户权限：和MySQL所有数据库相关，例如，删除已有的数据库或者创建一个新的数据库的权限

     给某位用户授权可以使用grant语句，使用show grants语句可以查看当前账户拥有什么权限

grant语法格式如下：

grant priv_type [(列名)] ...
on [object_type] {表名或视图名 | * | *.* |数据库名.*}
to 用户 [identified by [password] '密码']...
[with with_option ...]

object_type格式为：

table
| fuction
| procedure

with_option格式为：

grant option
| max_queries_per_hour count
| max_updates_per_hour count
| max_connections_per_hour count
| max_user_connections count

备注：priv_type为权限的名字，如select、update等，给不同的对象授予权限priv_type的值也不同。to子句用来设定用户的密码，on关键字后面给出的是要授予权限的数据库或表名

1. 授予表权限和列权限

（1）授予表权限

     授予表权限时，priv_type可以是以下值

1. select；使用select语句访问特定的表的权力。用户也可以在一个视图公式中包含表，然而，用户必须对试图公式中指定的每个表（视图）都有权限
2. insert：使用insert语句向一个特定表中添加行的权力。
3. delete：使用delete语句向一个特定表中删除行的权力
4. update：使用update语句修改特定表中值的权力
5. references：创建一个外键来参照特定的表的权力
6. create：使用特定的名字创建一个表的权力
7. alter：使用alter table语句修改表的权利
8. index：在表上定义索引的权利
9. drop：删除表的权力
10. all或all privileges：表示所有权限名

例如：授予用户ken在xs表上的select权限

 use test
 grant select 
 on xs 
 to 'ken'@'localhost';

     备注：如果在to子句中给存在的用户指定密码，则新密码会覆盖旧密码。如果权限授予了一个不存在的用户，MySQL会自动执行一条create user语句来创建这个用户，前提是必须给用户指定密码。

例如：授予用户ken在xs表上的select和update权限。用户liu和用户zhang不存在

 grant select, update
 on xs
 to liu@localhost identified by 'lpwd',
 zhang@localhost identified by 'zpwd';

（1）授予列权限

     对于列权限，priv_type的值只能去select、insert和update。权限的后面需要加上列名column_list.

例如：授予ken在xs表上的学号列和姓名列的update权限。

use test
grant update(学号,姓名)
on xs 
to ken@localhost;

2. 授予数据库权限

     表权限适用于一个特定的表，MySQL还支持针对整个数据库的权限。例如，在一个特定的数据库中创建表和视图的权限。
授予数据库权限时，priv_type可以是以下的值：

1. select；使用select语句访问特定的数据库中所有表和视图的权力
2. insert：使用insert语句向一个特定数据库中所有表添加行的权力。
3. delete：使用delete语句删除特定数据库中所有表的行的权力
4. update：使用update语句更新特定数据库中所有表的权力
5. references：创建指向特定数据库中的表外键的权力
6. create：使用特定的名字创建一个表的权力
7. alter：使用alter table语句修改特定数据库中所有表的权力
8. index：在特定数据库中所有表示定义和删除索引的权力
9. drop：删除特定数据库中所有表和视图的权力
10. create temporary tables:在特定数据库中创建临时表的权力
11. create view:在特定数据库中创建新视图的权力
12. show view：查看特定数据库中已有视图的视图定义的权力
13. create routine：为特定数据库创建存储过程和存储函数的权力
14. alter routine：更新和删除特定数据库已有的存储过程和存储函数的权力
15. exeute routine：调用特定数据库的存储过程和存储函数的权力
16. lock tables：锁定特定数据库的已有表的权力
17. all或all privileges：表示以上所有权限名。

     在grant语法格式中，授予数据库权限

 on关键字后面跟‘*’和‘数据库.*’.'*'表示当前数据库中的所有表，数据库.*表示某个数据库中的所有表

    这个权限适用于所有已有的表，以及此后添加到xscj数据库中的任何表

例如：授予ken在xscj数据库中所有数据库权限

grant all on *
to ken@localhost;

    如果用户被授予可以创建新表和视图的权限，但是不能访问他们，要访问他们，还得需要单独的授予select权限或更多权限。

3. 授予用户权限

    最有效率的权限就是用户权限，对于需要授权的数据库权限的所有语句，也可以定义在用户权限上，例如，在用户级别上授予某人create权限，这个用户可以创建一个新的数据库，也可以在所有（而不是特定）数据库中创建新表

    MySQL授予用户权限时priv_type还可以是以下值

1. create user：给予用户创建和删除新用户的权力
2. show databases：基于用户使用show databases 语句查看所有已有的数据库的定义的权力
3. 在grant语法格式中埋首于用户权限时on子句中使用”*.*“，表示所有数据库的所有表

例如：授予peter对所有数据库中的所有表create、alter 和drop的权力

 grant create,alter,drop
 on *.*
 to peter@localhost identified by 'ppwd';

8.2.2 权限转移和限制

    grant语句最后可以使用with子句。如果指定为with grant option，则表示to子句中指定的所有用户都有把自己所拥有的权限授予其他用户的权力，而不管其他用户是否拥有该权限。

例如：授予caddy在xs表上select权限，并允许其将权限授予其他用户
首先授予caddy权限

grant select 
on xs
to caddy@localhost identified by '19830925'

接着以caddy身份登录mysql

（1）打开命令行，进入MySQL安装目录下的bin

cd C:\Program Files \MySQL\MySQL Server 8.0\bin

（2）登录，输入命令

mysql -hlocalhost -ucaddy -p19830925

    其中 -h后是主机名，-u后是用户名，-p后是密码

    登陆后，caddy只具备查询xscj数据库中xs表的权力，它可以把这个权力传递给其他用户，这里假设用户Jim已经创建

grant select
on xs
to jim@localhost;

with_option可以为一个用户授予使用限制：

grant option
| max_queries_per_hour count
| max_updates_per_hour count
| max_connections_per_hour count
| max_user_connections count

• max_queries_per_hour count：表示每小时可以查询数据库的次数
• max_updates_per_hour count：每小时可以修改数据库的次数
• max_connections_per_hour count：每小时可以连接数据库的次数
• max_user_connections count：表示同时连接MySQL的最大用户数

例如：授予jim每小时只能处理一条select语句

 grant select
 on xs
 to jim@localhost
 with max_queries_per_hour 1;

8.2.3 权限回收

    要从一个用户回收权限，但不从user表中删除该用户，可以使用revoke语句，这条语句和grant语句格式相似，但具有相反的效果。要使用revoke，用户必须具备mysql数据库全局create user权限或update权限，其语法格式如下：

revoke priv_type[(列)]...
on {表名或视图名| * |*.* |数据库名.*}
from 用户..

或者

revoke all privileges, grant option
from 用户...

第一种格式用来回收某些特定的权限，第二种格式回收所有该用户的权限。

例如：回收用户caddy在xs表上的select权限。

use xscj
revoke select
on xs
from caddy@localhost；

    由于caddy用户对xs表的select权限被回收了，那么包括直接或间接地依赖于它的所有权限也回收了，在这个例子中，jim也失去了对xs表的select权限。但以上语句执行之后with grant option 还保留，当再次授予caddy对于同一个表的权限时，他会立刻把这个权限传给jim

8.3 表维护语句

    MySQL支持几条与维护和管理数据库相关的sql语句，统称为表维护语句

8.3.1 索引列可压缩性语句：analyze table

    在一个定义了索引的列上不同值的数目被称为该索引的可压缩性，可以使用”show index from 表名“语句来显示它，一个索引列的可压缩性不是自动更新的，创建索引后，其可压缩性不会立即计算出来，需要用命令来更新它

analyze [local | no write_to_binlog]
table 表名...

    在MySQL上执行的所有更新都将被写入到一个二进制日志文件中，如果使用该语句，结果数据也会写入日志文件中，如果使用no write_to_binlog则不会写入到日志文件中。这样该语句也将会更快完成

例如：更新xs表的索引可压缩性，并随后显示

analyze table xs;
show index from xs;

8.3.2 检查表是否有错语句：check table

check table 表名…[option]…
option的格式为：

quick | fast | medium | extended | changed

• quick：不扫描行，不检查错误的连接，这是最快的方法
• fast：检查表是否已经正确关闭
• changed 检查上次检查后被更改的表，以及没有正确关闭的表
• medium 扫描行，以验证被删除的连接是有效的，也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点
• extended 对每行的所有关键字进行全面的关键字查找。这可以确保表是完整一致的，但是花的时间长

     如果没有设定 QUICK, MEDIUM 或 EXTENDED 选项，对于动态格式 GsSYS 表缺省的检查类型是 MEDIUM。对于静态格式 GsSYS 表，如果没有设定 CHANGED 或 FAST，缺省检查类型也是 MEDIUM ，否则缺省为 QUICK。 对于 CHANGED 和 FAST ，会跳过行扫描，因为行通常很少被损坏

例如：检查xs表是否正确

check table xs;

+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.xs | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (0.00 sec)

    返回的是一个状态表，table为表的名称；op为进行的动作，此处是check；msg_type是状态、错误、信息或错误之一；Msg_text是返回的消息，这里为ok，说明表是正确的

8.3.3 获得表校验和语句：checksum table

    对于数据库中的每一个表，用户都可以使用checksum table语句获得一个校验和

checksum table 表名...[quick | extended]

备注：如果表是myisam表，指定了quick，则报告校验和，否则报告null。指定extended则表示无论表是否是myisam表，都只计算校验和。

例如：获得表xs的校验和均值

checksum table xs;

+---------+------------+
| Table   | Checksum   |
+---------+------------+
| test.xs | 1839611978 |
+---------+------------+
1 row in set (0.00 sec)

8.3.4 优化表语句：optimize table

    如果用户不断使用delete、insert和update语句更新一个表，那么表的内部结构就会出现很多碎片和未利用的空间。这时可以使用optimize table语句冲淋利用未使用的空间，并整理数据文件的碎片。optimize table语句只对myisam、bdb和innodb表起作用。

语法格式：

optimize [local | no_write_to_binlog] table 表名..

例如：优化xs表

optimize table xs；

8.3.5 修复表语句：repair table

    如果一个表或索引已经损坏，可以使用repair table语句尝试修复它。repair table只对myisam和archive起作用。

repair [local | no_write_to_binlog] table 表名....
[quick] [extended] [use_frm]

• quick:如果指定了该选项，则repair table会尝试只修复索引树
• extended：使用了该选项，则mysql会一行一行创建索引行，代替使用分类一次创建一个索引
• use_frm:如果myi索引文件确实或标题被破坏，则必须使用此选项

     另外还有两个表维护语句：

 backup table 表名...to 'path/to/backup/directory'

    可以对一个或多个myisam表备份

restore table 表名...from 'path/to/backup/directory'

    创建一个或多个表的本分，将数据读取到数据库中

    这两条语句不是很理想，不推荐使用