14.用户管理

和密码。其中 User和 Host 为 User 表的联合主键。当用户与服务器之间建立连接时，输入的账户信息中的用户名称、主机名和密码必须匹配 User 表中对应的字段，只有3 个值都匹配的时候，才允许连接建立。这3个字段的值就是创建账户时保存的账户信息。修改用户密码时，实际就是修改 user表的authentication_string字段的值。

查看当前用户信息：

select host,user,authentication_string from user;

2.权限列

权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操

作。

包括查询权限、修改权限等普通权限，还包括了关闭服务器、超级权限和加载用户等

高级权限。

普通权限用于操作数据库;高级权限用于数据库管理。

user 表中对应的权限是针对所有用户数据库的。这些字段值的类型为 ENUM，可以取

的值

只能为Y和N，Y表示该用户有对应的权限:N 表示用户没有对应的权限。

查看 user 表的结构可以看到,这些字段的值默认都是N。如果要修改权限,可以使用

GRANT 语或UPDATE语句更改user表的这些字段来修改用户对应的权限。

3.安全列

安全列只有6个字段，其中两个是 ssl相关的，两个是509 相关的，另外两个是授权插

件相关的。

ssl用于加密;x509 标准可用于标识用户;Plugin 字段标识可以用于验证用户身份的插

件，

如果该字段为空，服务器使用内建授权验证机制验证用户身份。

可以通过 SHOW VARIABLES LIKE 'have openssl'; 语来查询服务器是否支持ssl功能.

如果显示DISABLED则表明MySQL尚不支持OpenSSL。

4.资源控制列

资源控制列的字段用来限制用户使用的资源，包含 4个字段，分别为:

(1)max_questions

用户每小时允许执行的查询操作次数。

(2)max_updates

用户每小时允许执行的更新操作次数

(3)max_connections

用户每小时允许执行的连接操作次数

(4)max_user_connections

用户允许同时建立的连接次数

一个小时内用户查询或者连接数量超过资源控制限制，用户将被锁定，

直到下一个小时才可以在此执行对应的操作。可以使用 GRANT 语句更新这些字段的

值。

2、db表和host 表

db表和 host表是 MySQL 数据库中非常重要的权限表。db表中存储了用户对某个数据

库的操作权限，

决定用户能从哪个主机存取哪个数据库。host 表中存储了某个主机对数据库的操作权限，

配合 db 权限表对给定主机上数据库级操作权限做更细致的控制。这个权限表不受

GRANT和

REVOKE语句的影响。db表比较常用，host表一般很少使用。db表和 host 表结构相

似，

字段大致可以分为两类:用户列和权限列。db 表和 host 表的结构分别如下所示。

1.用户列

db 表用户列有3个字段，分别是 Host、User、Db，标识从某个主机连接某个用户对某

个数据库

的操作权限，这3 个字段的组合构成了 db 表的主键。host 表不存储用户名称，用户列只有2个字段，分别是 Host 和 Db，表示从某个主机连接的用户对某个数据库的操作权限，其主键包括 Host和 Db两个字段host 很少用到，一般情况下 db 表就可以满足权限控制需求了。

2.权限列

db表和host表的权限列大致相同，表中create_routine_priv和alter_routine_priv这两个

字段表明

用户是否有创建和修改存储过程的权限。

user 表中的权限是针对所有数据库的，如果希望用户只对某个数据库有操作权限，那

么需要将

user 表中对应的权限设置为 N，然后在 db 表中设置对应数据库的操作权限。

例如，有一个名称为 Zhangting 的用户分别从名称为 largedomain.com和

smalldomaincom的两个

主机连接到数据库，并需要操作 books数据库。这时，可以将用户名称 Zhangting 添

加到db表中，

而db 表中的 host字段值为空，然后将两个主机地址分别作为两条记录的 host 字段值

添加到 host

表中，并将两个表的数据库字段设置为相同的值 books。当有用户连接到 MySQL 服务

器时db 表中

没有用户登录的主机名称，则 MySQL 会从 host 表中查找相匹配的值，并根据查询的

结果决定用户

的操作是否被允许。

3. tables_priv 表和 columns _priv 表

tables_priv 表用来对表设置操作权限，columns_priv 表用来对表的某一列设置权限

tables_priv表和columns_priv表的结构分别如下：

4/6tables_priv 表有8个字段，分别是 Host、Db、User、Table_name、Grantor、

Timestamp、

Table_priv和Column_priv，各个字段说明如下:

(1)Host、Db、User 和 Table name 4 个字段分表示主机名、数据库名、用户名和表

名。

(2)Grantor 表示修改该记录的用户。

(3)Timestamp字段表示修改该记录的时间。

(4)Table_priv 表示对表的操作权限，

包括 Select、Insert、Update、Delete、Create、Drop、Grant、References、Index

和Alter 等。

(5)Column_priv 字段表示对表中的列的操作权限，包括 Select、Insert、Update 和

References。

columns_priv 表只有7个字段，

分别是 Host、Db、User、Table_name、Column_name、Timestamp、Column_priv。

其中，Column_name用来指定对哪些数据列具有操作权限

4.procs_priv 表

procs_priv表可以对存储过程和存储函数设置操作权限。procs priv的表结构如表下：

procs_priv 表包含8个字段分别是

Host 、Db、User、 Routine_name、 Routine_type、Grantor_Procpriv和

Timestamp，

各个字段的说明如下:

(1)Host、Db和 User 字段分别表示主机名、数据库名和用户名。

Routine_name 表示存储过程或函数的名称。

(2)Routine_type 表示存储过程或函数的类型。Routine_type 字段有两个值，

分别是FUNCTION和PROCEDURE。FUNCTION 表示这是一个函数;

PROCEDURE 表示这是一个存储过程。

(3)Grantor 是插入或修改该记录的用户

(4)Proc_priv表示拥有的权限，包括Execute、Alter Routine、Grant3种

(5)Timestamp表示记录更新时间。

注意：mysql 新设置用户或更改密码后需用 flush privileges; 刷新MySQL的系统权限相关表

2、账户管理

MySQL提供许多语句用来管理用户账号,这些语句可以用来管理包括登录和退出MySQL服务器、

创建用户、删除用户、密码管理和权限管理等内容。MySQL 数据库的安全性，需要通过账户管理来保证。

1. 登录和退出MySQL服务器

大家已经知道登录 MySQL 时，使用 MySQL 命令并在后面指定登录主机以及用户名和密码。

通过MySQL-help 命令可以查看 MySQL命令帮助信息。MySQL命令的常用参数如下:

(1)-h主机名，可以使用该参数指定主机名或ip，如果不指定，默认是 localhost。

(2)-u用户名，可以使用该参数指定用户名。

(3)-p密码，可以使用该参数指定登录密码。如果该参数后面有一段字段，则该段字符串将作为用户的密码直接登录。如果后面没有内容，则登录的时候会提示输入密码。

注意:该参数后面的字符串和-p 之间不能有空格。

(4)-P端口号，该参数后面接MySOL 服务器的端口号，默认为3306。

(5)数据库名，可以在命令的最后指定数据库名。

(6)-e 执行 SQL语句。如果指定了该参数，将在登录后执行-e 后面的命令或SQL语句并退出。

举例：使用root用户登录到本地mysql服务器的test库中

mysql -uroot -p -hlocalhost test

执行命令时，会提示 Enter password:，如果没有设置密码，可以直接按 Enter 键。

密码正确就可以直接登录到服务器下面的 test 数据库中了。

举例2：使用root用户登录到本地mysql服务器的test库中，执行一条查询语句

mysql -uroot -p -hlocalhost test -e "DESC a;"

按照提示输入密码，命令执行完成后查询出 a 表的结构，查询返回之后会自动退出MySQL。

2、创建普通用户：

创建新用户，必须有相应的权限来执行创建操作。在 MySQL 数据库中，有两种方式创建新用户:

一种是使用CREATE USER或GRANT语句;另一种是直接操作 MySQL 授权表。

最好的方法是使用 GRANT 语句，因为这样更精确，错误少。下面分别介绍创建普通用户的方法；

1.使用CREATE USER语创建新用户

执行CREATE USER或GRANT语句时，服务器会修改相应的用户授权表，添加或者修改

用户及其权限。

CREATE USER语句的基本语法格式如下:

CREATE USER user_specification [, user_specification ] ....

user specification:

user@host

[

IDENTIFIED BY [PASSWORD] ‘password’

| IDENTIFIED WITH auth_plugin [AS 'auth string']

]

user 表示创建的用户的名称;host 表示允许登录的用户主机名称;IDENTIFIED BY表示用来设置用户的密码;

[PASSWORD]表示使用哈希值设置密码，该参数可选;“password’表示用户登录时使用的普通明文密码;

IDENTIFIED WITH语为用户指定一个身份验证插件:auth_plugin 是插件的名称，插件的名称可以是一个带单引号的字符串，或者带引号的字符串:auth string 是可选的字符串参数该参数将传递给身份验证插件由该插件解释该参数的意义.

CREATE USER语句会添加一个新的MySQL账户。使用CREATE USER语句的用户，必须有全局的CREATE USER权限或MySQL数据库的INSERT权限每添加一个用户 CREATE USER语句会在MySQL 的user 表中添加一条新记录，但是新创建的账户没有任何权限。如果添加的账户已经存在，CREATE USER语句会返回一个错误。

举例：

使用CREATE USER创建一个用户，用户名是cqb，密码是1111，主机名是localhost

create user 'cqb'@'localhost' identified by '1111';

如果出现：Your password does not satisfy the current policy requirements

查看系统默认策略

show variables like 'validate_password%';

查看安全插件：

select PLUGIN_NAME, PLUGIN_STATUS
from INFORMATION_SCHEMA.PLUGINS
where PLUGIN_NAME = 'validate_password';

如果没有需要安装插件

INSTALL PLUGIN validate_password SONAME 'validate_password.so';

则修改策略：

set global validate_password_policy=0; 
set global validate_password_length=4; 
show variables like 'validate_password%';

再次创建则成功

如果只指定用户名部分“cqb’，主机名部分则默认为“%“(即对所有的主机开放权限）。

user_specification告诉 MySQL 服务器当用户登录时怎么验证用户的登录授权。

如果指定用户登录不需要密码，可以省略IDENTIFIED BY部分:

create user 'cqb2'@'localhost';

此种情况，MySQL服务端使用内建的身份验证机制，用户登录时不能指定密码，

注意：如果创建失败，报错：Your password does not satisfy the current policy requirements。

是由于 Mysql 5.7 版本默认安装了 validate_password 插件。

查看：

show variables like 'vali%';

然后关闭插件，并重启服务

# vim /etc/my.cnf 
[mysqld] 
validate_password=off

重启服务：systemctl restart mysqld

systemctl restart mysqld

service mysqld  restart #重启mysql服务
service mysqld  status #查看mysql状态
service mysqld  stop  #停止mysql服务

接下来就可以创建空密码账户了。

create user 'cqb2'@'localhost';

如果要创建指定密码的用户，需要IDENTIFIED BY指定明文密码值:

create user 'cqb3'@'localhost' identified by '1111';

此种情况，MySQL服务端使用内建的身份验证机制，用户登录时必须指定密码。

为了避免指定明文密码，如果知道密码的散列值，可以通过PASSWORD关键字使用密码的哈希值设置密码。

密码的哈希值可以使用password0函数获取，如:

select password('mypass') ；

create user 'cqb5'@'localhost' identified by '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';

用户cqb的密码将被设定为mypass。

对于使用插件认证连接的用户，服务器调用指定名称的插件，客户端需要提供验证方法所需要的凭据。

如果创建用户时或者连接服务器时，服务器找不到对应的插件，将返回一个错误，

IDENTIFIED WITH语法格式如下:

create user  'cqb'@'localhost' identified with my_auth_plugin;

MySOL的某些版本中会引入授权表的结构变化，添加新的特权或功能。

每当更新 MySQl到一个新的版本时，应该更新授权表，以确保它们有最新的结构，

确认可以使用任何新功能。5.7后的版本修改密码的代码如下：

ALTER USER 'cqb2'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

IDENTIFIED WITH只能在 MySQL 5.5.7 及以上版本中使用。

IDENTIFIED BY和IDENTIFIED WITH是互斥的，所以对于一个账户来说只能使用一个验证方法。

CREATE USER语句的操作会被记录到服务器日志文件或者操作历史文件中如~/MySOL history。

这意味着对这些文件有读取权限的人，都可以读取新添加用户的明文密码。

2.使用GRANT 语创建新用户

CREATE USER语可以用来创建账户，通过该语句可以在 user 表中添加一条新的记录但是CREATE USER语句创建的新用户没有任何权限，还需要使用GRANT 语句赋予用户权限。而GRANT语句不仅可以创建新用户，还可以在创建的同时对用户授权。GRANT还可以指定账户的其他特点，如使用安全连接、限制使用服务器资源等。使用GRANT 语句创建新用户时必须有GRANT权限。GRANT 语句是添加新用户并授权他们访问 MySOL对象的首选方法，GRANT语句的基本语法格式如下:

GRANT privileges ON db.table

TO user@host [ IDENTIFIED BY 'password'] [，user [ IDENTIFIED BY 'password']]

[WITH GRANT OPTION]

其中，privileges表示赋予用户的权限类型; db.table 表示用户的权限所作用的数据库中的表;

IDENTIFIED BY关键字用来设置密码;password表示用户密码;WITH GRANT OPTION为可选参数，

表示对新建立的用户赋予 GRANT 权限，即该用户可以对其他用户赋予权限。

举例：使用GRANT语句创建一个新的用户testUser，密码为testpwd。用户 testUser对所有的数据有查询和更新权限，并授于对所有数据表的SELECT和UPDATE权限

/*创建账户并授予权限*/

grant  select,update on  *.*  to 'testUser'@'localhost' identified by 'testpwd';

/*查看账户权限信息*/

select host,user,Select_priv,Update_priv from mysql.user where user='testUser';

查询结果显示用户testUser 被创建成功，其SELECT和UPDATE权限字段值均为Y’

注意：User 表中的 user 和host 字段区分大小写，在查询的时候要指定正确的用户名称或者主机名。

3.直接操作MySQL用户表

通过前面的介绍，不管是CREATE USER 或者GRANT，在创建新用户时，实际上都是在user 表中添加一条新的记录。因此，可以使用INSERT 语向 user 表中直接插入一条记录来创建一个新的用户。

使用INSERT 语句，必须拥有对MySOL user 表的INSERT 权限。

使用INSERT语句创建新用户的基本语法格式如下:

INSERT INTO MySQL.user(Host，User， Password,[privilegelist])

VALUES('host，iusername'，PASSWORD('password')，privilegevaluelist)；

其中 Host、User、Password 分别为 user 表中的主机、用户名称和密码字段;

privilegelist 表示用户的权限，可以有多个权限:PASSWORDO函数为密码加密函数;

privilegevaluelist 为对应的权限的值，只能取“Y’或者“N’

举例：

使用INSERT创建一个新账户，其用户名称为customer1，主机名称为localhost，密码为customer1：

insert into user 
(host,user,authentication_string,select_priv,insert_priv,update_priv,ssl_cipher,x509_issuer,x509_subject) 
values('localhost','guest',MD5('guest123'),'Y','Y','Y','0x','0x','0x'); 
flush privileges;

3、删除普通用户：

在MySQL 数据库中，可以使用 DROP USER 语句删除用户，也可以直接通过 DELETE

语句从

MySQL.user 表中删除对应的记录来删除用户。

1.使用DROPUSER语删除用户

DROP USER语句语法如下:

DROP USER user [，userl;

DROP USER语句用于删除一个或多个 MySQL 账户。要使用 DROP USER，必须拥有

MySOL数据库的全局

CREATE USER权限或DELETE 权限。使用与GRANT或REVOKE相同的格式为每个账户命名;

例如，cqb@localhost账户名称的用户和主机部分与用户表记录的User和Host 列值相对应。

使用DROP USER，可以删除一个账户和其权限，操作如下:

DROP USER 'cqb'@'localhost';

举例：使用DROP USER删除用户'cqb'@'localhost'

drop user 'cqb'@'localhost';

执行完毕后查看效果：

select host,user,authentication_string from user;

user 表中已经没有名称为cqb，主机名为 localhost 的账户，即cqb@localhost的用户账

号已经被删除。

2.使用DELETE语删除用户

DELETE语句基本语法格式如下

DELETE FROM MySQL.user WHERE host='hostname' and user='username'

host和user为user表中的两个字段，两个字段的组合确定所要删除的账户记录。

举例：

使用DELETE删除用户'customer1'@'localhost'

delete from mysql.user where host='localhost' and user='cqb2';

4.root 用户修改自己的密码

root用户的安全对于保证MySQL的安全非常重要，因为root用户拥有很高的权限。

修改root用户密码的方式有多种，本小节将介绍几种常用的修改root 用户密码的方法。

1.使用MySQLadmin 命令在命令行指定新密码

MySQLadmin命令的基本语法格式如下:

mysqladmin -u username -h localhost -p password "newpwd"

username为要修改密码的用户名称，在这里指定为root 用户;

参数-h 指需要修改的、对应哪个主机用户的密码，该参数可以不写，默认是localhost;

-p表示输入当前密码;password为关键字，后面双引号内的内容“newpwd”为新设置的密码。

执行完上面的语句，root 用户的密码将被修改为newpwd。

举例：使用mysqladmin将root用户的密码修改为“rootpwd”

mysqladmin -u root -p password "rootpwd"

按照要求输入 root用户原来的密码，执行完毕后，新的密码将被设定。root 用户登录时将使用新的密码。

2.修改MySQL数据库的user表

因为所有账户信息都保存在user 表中，因此可以通过直接修改user 表来改变root 用户的密码。

root用户登录到MySQL服务器后，使用UPDATE语修改MySQL数据库的user 表的password字段，

从而修改用户的密码。使用UPDATA语句修改root用户密码的语句如下:

UPDATE mysql.user set authentication_string=PASSWORD('rootpwd')

WHERE User='root' and Host='localhost';

PASSWORD()函数用来加密用户密码。执行 UPDATE 语句后，

需要执行 FLUSH PRIVILEGES 语句重新加载用户权限。

举例：

使用UPDATE语句将root用户的密码修改为“rootpwd2”：

update mysql.user 
set authentication_string=password("rootpwd2") 
where user="root" and host="localhost";

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

SET PASSWORD 语句可以用来重新设置其他用户的登录密码或者自己使用的账户的密

使用SET语句修改自身密码的语法结构如下:

SET PASSWORD

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

举例：使用SET语句将root用户的密码修改为“rootpwd3”：

set password=password("rootpwd3");

SET语句执行成功，root用户的密码被成功设置为 rootpwd3。为了使更改生效，

需要重新启动MySQL或者使用FLUSH PRIVILEGES;语句刷新权限，重新加载权限表。

5、root 用户修改普通用户的密码

root 用户拥有很高的权限，不仅可以修改自己的密码，还可以修改其他用户的密码。

root用户登录MySQL服务器后，可以通过SET语句修改MySQL user表，以及GRANT语

句修改用户的密码。

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

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

SET PASSWORD FOR 'user'@'host' = PASSWORD('somepassword');

只有root 可以通过更新 MySQL数据库的用户来更改其他用户的密码。如果使用

普通用户修改，可省略FOR子句更改自己的密码:

SET PASSWORD = PASSWORD('somepassword');

举例：使用SET语句将testUser用户的密码修改为“newpwd”：

set password for 'testUser'@'localhost'=password("newpwd");

SET语句执行成功，testUser用户的密码被成功设置为newpwd。

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

使用root用户登录到 MySQL服务器后，可以使用UPDATE语修改MySQL数据库的

user表password字段，从而修改普通用户的密码。使用UPDATA 语句修改用户密码的语法。

如下:

UPDATE MySQL.user SET Password=PASSWORD("pwd")

WHERE User="username AND Host="hostnamer“;

PASSWORD()函数用来加密用户密码。

执行UPDATE 语句后，需要执行 FLUSH PRIVILEGES语重新加载用户权限。

举例：使用UPDATE语句将testUser用户的密码修改为“newpwd2”：

update mysql.user set authentication_string=password("newpwd2")
where user="testUser" and host="localhost";

执行完UPDATE语句后testUser 的密码被修改成了newpwd2 使用

FLUSHPRIVILEGES重新加载权限，

就可以使用新的密码登录testUser用户了。

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

除了前面介绍的方法，还可以在全局级别使用GRANT USAGE 语(*.*)指定某个账户的密码而不影响账户当前的权限，使用GRANT 语句修改密码，必须拥有GRANT 权限。一般情况下最好使用该方法来指定或修改密码:

GRANT USAGE ON *,* TO ’someuser'@'%' IDENTIFIED BY 'somepassword';

举例：使用GRANT语句将testUser用户的密码修改为“newpwd3”：

grant usage on*.*to 'testUser'@'localhost' identified by 'newpwd3';

执行完GRANT语句后，testUser 的密码被修改成了 newpwd3。可以使用新密码

登录MySQL服务器。

如果使用GRANT...IDENTIFIED BY语或MySQLadmin password 命令设置密码，它

们均会加密密码。

在这种情况下，不需要使用PASSWORD()函数。

6、普通用户登录MySQL服务器后，通过SET语设置自己的密码。

SET语句修改自己密码的基本语法如下:

SET PASSWORD = PASSWORD(''newpassword'');

其中，PASSWORDO函数对密码进行加密，“newpassword”是设置的新密码。

举例：testUser用户使用SET语句将自身的密码修改为“newpwd4”：

SET PASSWORD = PASSWORD("newpwd4");

set password = password("newpwd4");

SET 语句执行成功，testUser 用户的密码被成功设置为 newpwd4。可以使用新密码登

录MySQL服务器。

7、root用户密码丢失的解决办法

对于root 用户密码丢失这种特殊情况，MySQL实现了对应的处理机制。

可以通过特殊方法登录到MySOL服务器，然后在root 用户下重新设置密码。执行步骤

如下:

1、使用--skip-grant-tables 选项启动MySQL服务

以skip-grant-tables选项启动时，MySQL服务器将不加载权限判断，任何用户都能访问数据库。

在Windows操作系统中，可以使用MySQLd或MySQLd-nt来启动MySQL服务进程

在Linux系统中，可以使用 systemctl restart mysqld 重启服务进程

如果MySQL的目录已经添加到环境变量中，可以直接使用 MySQLd、MySOLd-nt

命令启动MySQL服务。

否则需要先在命令行下切换到MySQL的bin目录。

MySOLd命令如下:

mysqld --skip-grant-tables

MySOLd-nt命令如下:

mysgld-nt --skip-grant-tables

在Linux操作系统中mysql5.7 以前，使用 MySQLd safe来启动MySQL 服务。

也可以使用/etc/init.d/MySQL命令来启动MySQL服务。

MySQLd-safe命令如下:

mysqld_safe --skip-grant-tables user=mysql

/etc/init.d/MySOL命令如下:

/etc/init.d/mysql start-mysqld --skip-grant-tables

启动MySOL服务后，就可以使用root用户登录了。

如果是5.7 以后的系统：

1、修改配置文件：

vim /etc/my.cnf

2、重启服务

sudo service mysqld restart

3、无密码登录

mysql -uroot

2、使用root用户登录，重新设置密码在这里使用的平台为Windows7，

操作步骤如下

1、使用net stop MySQL 命令停止MySQL服务进程。

2、在命令行输入MySQLd --skip-grant-tables 选项启动MySQL服务。

3、打开另外一个命令行窗口，输入不加密码的登录命令

4、登录成功以后，可以使用UPDATE语句或者使用MySQLadmin 命令重新设置

root密码设置密码

5、 UPDATE mysql.user SET Password=PASSWORD('newpwd')

WHERE User='root' and Host='localhost';

3、加载权限表

修改密码完成后，必须使用FLUSH PRIVILEGES语句加载权限表。加载权限表

后，新的密码才会生效，

同时MySQL服务器开始权限验证。输入语句如下:mysql> FLUSH PRIVILEGES；

修改密码完成后，将输入MySQLd --skip-grant-tables 命令的命令行窗口关闭，

接下来就可以使用新设置的密码登录MySQL了。

3、权限管理

权限管理主要是对登录到MySQL的用户进行权限验证所有用户的权限都存储在MySQL的权限表中，不合理的权限规划会给 MvSQL 服务器带来安全隐患。数据库管理员要对所有用户的权限进行合理规划管理。MySQL权限系统的主要功能是证实连接到一台给定主机的用户并且赋予该用户在数据库上的 SELECT、INSERT、UPDATE 和DELETE 权限。

1、MySQL 的各种权限

MySQL的各种权限账户权限信息被存储在MySQL 数据库的 user、 db、 hosttables_priv、columns priv和procs_priv表中。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。GRANT和REVOKE语句所涉及的权限的名称如下，还有在授权表中每个权限的表列名称和每个权限有关的操作对象等。

(1)CREATE和DROP权限，可以创建新数据库和表或删除(移掉)已有数据库和表。如果将MySQL数据库中的DROP权限授予某用户用户可以删掉MySQL访问权限保存的数据库。

(2)SELECT、INSERT、UPDATE 和 DELETE 权限允许在一个数据库现有的表上实施操作。

(3)SELECT权限只有在它们真正从一个表中检索行时才被用到。

(4)INDEX权限允许创建或删除索引INDEX适用已有表。如果具有某个表的CREATE权限，可以在CREATE TABLE语中包括索引定义。

(5)ALTER权限，可以使用ALTER TABLE来更改表的结构和重新命名表。

(6)CREATE ROUTINE权限来创建保存的程序(函数和程序)，ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE权限用来执行保存的程序。

(7)GRANT权限允许授权给其他用户。可用于数据库、表和保存的程序。

(8)FILE权限给予用户使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语读或写服务器上的文件任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件(说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件)。FILE 权限允许用户在MySQL服务器具有写权限的目录下创建新文件，但不能覆盖已有文件。

其余的权限用于管理性操作，它使用MySQL admin程序或SQL语实施。

表13.8显示每个权限允许执行的MySQLadmin命令。

(1) reload命令告诉服务器将授权表重新读入内存;

flush-privileges是reload 的同义词;

refresh命令清空所有表并关闭/打开记录文件;

其他flush-xxx 命令执行类似refresh 的功能

(2)shutdown命令关掉服务器。只能从MySQLadmin发出命令。

(3)processlist 命令显示在服务器内执行的线程的信息(即其他账户相关的客户端执行的语句)。kill 命令杀死服务器线程。用户总是能显示或杀死自己的线程，但是需要 PROCESS权限来显示或杀死其他用户和SUPER权限启动的线程。

(4)kill命令能用来终止其他用户或更改服务器的操作方式。

总的来说，只授予权限给需要他们的那些用户。

2、授权

授权就是为某个用户授予权限。合理的授权可以保证数据库的安全。

MySQL 中可以使用GRANT语句为用户授予权限。授予的权限可以分为多个层级:

1.全局层级

全局权限适用于一个给定服务器中的所有数据库。这些权限存储在 MySQL.user 表中GRANT ALL ON *.* 和 REVOKE ALL ON *.* 只授予和撤销全局权限。

2、数据库层级

数据库权限适用于一个给定数据库中的所有目标。这些权限存储在 MySQLdb 和MySQL.host表中。GRANT ALL ON db_name和 REVOKE ALL ON db_name *.* 只授予和撤销数据库权限

3.表层级

表权限适用于一个给定表中的所有列。这些权限存储在MySQL.talbes_priv表中。

GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限.

4.列层级

列权限适用于一个给定表中的单一列。这些权限存储在MySQL.columns priv表中。

当使用REVOKE时，必须指定与被授权列相同的列。

5.子程序层级

CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT 权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外这些权限可以被授予子程序层级，并存储在MySQL.procs_priv表中。

在MySQL中，必须是拥有GRANT权限的用户才可以执行GRANT语句。要使用GRANT或REVOKE，必须拥有GRANT OPTION权限，并且必须用于正在授予或撤销的权限。

GRANT的语法如下:

GRANT priv_type [(columns)][，priv type[(columns)]] ..

ON [object type] tablel，table2,..， tablen

To user [ IDENTIFIED BY [PASSWORD] password']

[. user [ IDENTIFIED BY [PASSWORD] password] ...

WITH GRANT OPTION]

object_type = TABLE | FUNCTION | PROCEDURE

其中，priv_type参数表示权限类型;columns 参数表示权限作用于哪些列上，不指定该参数，表示作用于整个表;tablel,table2,...,tablen 表示授予权限的列所在的表;

object type 指定授权作用的对象类型包括TABLE(表)、FUNCTION(函数)和PROCEDURE(存储过程)当从旧版本的MySQL升级时，要使用object tpye 子，必须升级授权表;

user 参数表示用户账户，由用户名和主机名构成，形式是“username@hostame”;

IDENTIFIEDBY参数用于设置密码。

WITH关键字后可以跟一个或多个GRANT OPTION。GRANTOPTION的取值有5个意义如下:

(1) GRANTOPTION:被授权的用户可以将这些权限赋予别的用户

(2) MAX QUERIES PER HOUR count:设置每个小时可以执行count 次查询

(3) MAX UPDATES PER HOUR count:设置每小时可以执行count 次更新。

(4) MAXCONNECTIONS PER HOURcount:设置每小时可以建立count个连接

(5) MAX USERCONNECTIONS count:设置单个用户可以同时建立count个连接

举例：使用GRANT语句创建一个新的用户grantUser，密码为“grantpwd”。

用户grantUser对所有的数据有查询、插入权限，并授于GRANT权限。

GRANT语句及其执行结果如下：

grant select,insert on *.* to 'grantUser'@'localhost'
identified by 'grantpwd' with grant option;

结果显示执行成功，使用SELECT语句查询用户testUser2的权限：

select host,user,Select_priv,Insert_priv, Grant_priv from mysql.user where user='grantUser';

查询结果显示用户 grantUser 被创建成功，并被赋予SELECT、INSERT和GRANT权限其相应字段值均为“Y’被授予GRANT 权限的用户可以登录 MySQL 并创建其他用户账户，在这里为名称是grantUser 的用户。

3、收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySOL中使用REVOKE语句取消用户的某些权限。使用 REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv 表中删除，但是用户账号记录仍然在user表中保存(删除user 表中的账户记录，使用DROP USER语)。

在将用户账户从user 表删除之前，应该收回相应用户的所有权限，REVOKE语句有两种语法格式，

第一种语法是收回所有用户的所有权限，此语法用于取消对于已命名的用户的所有全局层级数据库层级、表层级和列层级的权限，其语法如下:

REVOKE ALL PRIVILEGES.GRANT OPTION

FROM 'user'@'host' [，ruser'@'host' ...]

REVOKE语句必须和FROM语句一起使用，FROM语指明需要收回权限的账户另一种为长格式的

REVOKE语句，基本语法如下:

REVOKE priv_type [(columns)][，privtype[(columns)]] ...

ON tablel，table2,, tablen

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

该语法收回指定的权限。其中，priv_type 参数表示权限类型;columns参数表示权限作用于哪些列上，

如果不指定该参数，表示作用于整个表;tablel,table2,...,tablen 表示从哪个表中收回权限;

user@host参数表示用户账户，由用户名和主机名构成。要使用REVOKE语句，必须拥有MySOL数据库的全局CREATE USER权限或UPDATE权限。

举例：

使用REVOKE语句取消用户testUser的更新权限。REVOKE语句及其执行结果如下：

revoke update on *.* from 'testUser'@'localhost';

执行结果显示执行成功，使用SELECT语句查询用户test的权限：

select host,user,Select_priv,Insert_priv, Grant_priv from mysql.user where user='testUser';

查询结果显示用户 testUser的Update_priv 字段值为“N”，UPDATE权限已经被收回。

4、查看权限

SHOW GRANTS语句可以显示指定用户的权限信息，使用SHOW GRANTS查看账户信息的基本语法格式

如下: SHOW GRANTS FOR 'user'@'host'；

其中，user 表示登录用户的名称，host 表示登录的主机名称或者IP 地址。在使用该语句时，要确保指定的用户名和主机名都要用单引号括起来，并使用@符号，将两个名字分隔开。

举例：使用SHOW GRANTS语句查询用户testUser的权限信息。

SHOW GRANTS语句及其执行结果如下：

show grants for 'testUser'@'localhost';

在前面创建用户时，查看新建的账户时使用 SELECT 语，也可以通过SELECT 语句查看user表中的

各个权限字段以确定用户的权限信息，其基本语法格式如下:

SELECT privileges_list FROM user WHERE user ='username' ,host='hostname';

其中，privileges_list 为想要查看的权限字段，可以为 Select_priv、Insert_priv 等。

使用样例：

GRANT SELECT,UPDATE ON *.* TO 'test2'@'localhost' IDENTIFIED BY 'Test2@##';

GRANT ALL ON *.* TO 'test3'@'localhost' IDENTIFIED BY 'Test2@##';

GRANT ALL ON test2.* TO 'test4'@'localhost' IDENTIFIED BY 'Test2@##';

GRANT ALL ON test2.student TO 'test5'@'localhost' IDENTIFIED BY 'Test2@##';

FLUSH PRIVILEGES;

REVOKE ALL ON test2.student from 'test5'@'localhost' ;

4、访问控制

正常情况下，并不希望每个用户都可以执行所有的数据库操作。当MySQL 允许一个用户执行各种操作时，它将首先核实该用户向 MySQL 服务器发送的连接请求，然后确认用户的操作请求是否被允许。MySQL 的访问控制分为两个阶段:连接核实阶段和请求核实阶段。

1、连接核实阶段

当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码身份

验证来接受或拒绝连接。即客户端用户连接请求中会提供用户名称、主机地址名和密码，MySQL使用user 表中的3个字段 (Host、User 和 authentication_string)执行身份检查，服务器只有在 user 表记录的Host和User 字段匹配客户端主机名和用户名，并且提供正确的密码时才接受连接。如果连接核实没有通过，服务器完全拒绝访问: 否则，服务器接受连接，然后进入阶段2等待用户请求。

2、请求核实阶段

建立连接之后，服务器进入访问控制的阶段2。对在此连接上的每个请求，服务器检查

用户要执行的操作，然后检查是否有足够的权限来执行它。这正是在授权表中的权限列发挥作用的地方。这些权限可以来自user、db、host、tables_priv或columns_priv表。确认权限时，MySQL首先检查user表如果指定的权限没有在user表中被授权:MySQL将检查 db表，db表是下一安全层级其中的权限限定于数据库层级，在该层级的 SELECT权限允许用户查看指定数据库的所有表中的数据:如果在该层级没有找到限定的权限,则 MySQL继续检查 tables_priv表以及columns_priv表如果所有权限表都查完但还是没有找到允许的权限操作，MySQL将返回错误信息，用

户请求的操作不能执行，操作失败。

MySQL通过向下层级的顺序检查权限表(从user 表到columns_priv表)，但并不是所有

的权限都要执行该过程。例如，一个用户登录到MySQL服务器之后只执行对MySQL的管理操作，此时，只涉及管理权限，因此MySQL只检查 user 表。另外，如果请求的权限操作不被允许， MySQL也不会继续检查下一层级的表。

set global validate_password_policy=0;

set global validate_password_length=4;

UPDATE mysql.user set authentication_string=PASSWORD('654321')

WHERE User='test' and Host='%';

FLUSH PRIVILEGES;

5、练习题

问题：

1、已经将一个账户的信息从数据库中完全删除，为什么该用户还能登录数据库?

出现这种情况的原因可能有多种，最有可能的是在 user 数据表中存在匿名账户。

在 user表中匿名账户的 User 字段值为空字符串，这会允许任何人连接到数据库，

检测是否存在匿名登录用户的方法是，输入以下语句:

SELECT *FROM user WHERE User='';

如果有记录返回，则说明存在匿名用户，需要删除该记录，以保证数据库的访问安全

删除语句为:

DELETE FROM user WHERE user='';

这样一来，该账户肯定不能登录MySQL服务器了

2:应该使用哪种方法创建用户?

本章介绍了创建用户的几种方法:GRANT 语CREATE USER 语和直接操作 user表。一般

情况，

最好使用GRANT 或者CREATE USER语句，而不要直接将用户信息插入 user表，因为

user 表

中存储了全局级别的权限以及其他的账户信息，如果意外破坏了 user 表中的记录，

则可能会对MySQL服务器造成很大影响。

练习题：

创建数据库Team，定义数据表player，语句如下

CREATE DATABASE Team;

user Team;

CREATE TABLE player

{

playid INT PRIMARY KEY,

playname VARCHAR(30) NOT NULL,

teamnum INT NOT NULL UNIQUE,

info VARCHAR(50)

};

执行以下操作:

(1)创建一个新账户，用户名为 account1，该用户通过本地主机连接数据库，密码为

oldpwd1。授权该用户对 Team 数据库中 player 表的SELECT和INSERT权限，并且授权该用

户对player表的info字段的UPDATE权限。

(2)创建SQL语句，更改account1用户的密码为newpwd2。

(3)创建SQL语句，使用FLUSHPRIVILEGES重新加载权限表。

(4)创建SOL语，查看授权给account1用户的权限。

(5)创建SOL语句，收回account1用户的权限。

(6)创建SQL语句，将account1用户的账号信息从系统中删除。

文章来源:https://blog.csdn.net/m2282475145/article/details/135303473
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：chenni525@qq.com进行投诉反馈，一经查实，立即删除！