MySQL用户和权限管理(二)

在前面的文章中MySQL用户和权限管理(一)介绍了MYSQL用户和权限体系以及访问控制的两个阶段；

在本篇博文中接着上一篇的内容介绍MYSQL账户以及密码管理，会涉及到账户的创建、删除、授权等问题；

一、MySQL账户及密码管理

MySQL提供许多语句用来管理用户账号，这些语句可以用来管理包括登陆和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。MySQL数据库的安全性，需要通过账户管理来保证。下面介绍四种用来管理账号密码的方式：

1.1 添加账户

您可以通过两种方式创建MySQL帐户：

（1）通过使用用于创建帐户和建立其权限的帐户管理语句，例如 CREATE USER和 GRANT。这些语句使服务器对基础授权表进行适当的修改。

（2）通过DML语句操作mysql权限表，如操作MySQL授权表INSERT， UPDATE或 DELETE（不推荐这样操作）。

首选方法是使用帐户管理语句，因为它们比直接操作授权表更简洁，更不容易出错。

代码语言：javascript复制

root@localhost [(none)]>CREATE USER 'wjq'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)
 
root@localhost [(none)]>GRANT ALL PRIVILEGES ON *.* TO 'wjq'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
 
root@localhost [(none)]>CREATE USER 'seiang'@'localhost' IDENTIFIED BY 'password';      
Query OK, 0 rows affected (0.01 sec)
 
root@localhost [(none)]>GRANT select,insert ON *.* TO 'seiang'@'localhost';
Query OK, 0 rows affected (0.00 sec)

更改用户名

代码语言：javascript复制

root@localhost [(none)]>rename user 'seiang'@'localhost' to 'seiang1'@'localhost';
Query OK, 0 rows affected (0.01 sec)

通过GRANT指令（只能用于添加新用户）

代码语言：javascript复制

# 创建mytest用户并对此库下的所有表赋予所有权限;
root@localhost [mysql]>grant all on *.* to 'mytest'@'%' identified by 'seiang';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
root@localhost [mysql]>show warningsG;
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation.
1 row in set (0.00 sec)

同样，通过grant创建用户在MySQL 5.7中使用会被警告，是一个即将被移除的特性。推荐使用CREATE USER语句。虽然介绍了好几种方法创建用户，但真正在使用中，最好按照规范使用CREATE USER创建用户，GRANT设置权限，ALTER USER更改密码，而不要直接将用户信息插入user表中，因为user表中存储了全局级别的权限以及其他的账户信息，如果意外破坏了user表中的记录，则可能会对MySQL服务器造成很大的影响。

要查看帐户的权限，请使用 SHOW GRANTS：

代码语言：javascript复制

root@localhost [(none)]>show grants for 'wjq'@'%';
 ------------------------------------------------------------ 
| Grants for wjq@%                                           |
 ------------------------------------------------------------ 
| GRANT ALL PRIVILEGES ON *.* TO 'wjq'@'%' WITH GRANT OPTION |
 ------------------------------------------------------------ 
1 row in set (0.00 sec)

要查看帐户的非特权属性，请使用 SHOW CREATE USER：

代码语言：javascript复制

root@localhost [(none)]>show create user 'wjq'@'%'G;
*************************** 1. row ***************************
CREATE USER for wjq@%: CREATE USER 'wjq'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
1 row in set (0.00 sec)

1.2 删除账户

代码语言：javascript复制

root@localhost [(none)]>drop user 'wjq'@'%';      
Query OK, 0 rows affected (0.00 sec)

1.3 预留用户账户

代码语言：javascript复制

root@localhost [(none)]>select user,host from mysql.user;
 --------------- --------------- 
| user          | host          |
 --------------- --------------- 
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
 --------------- --------------- 
3 rows in set (0.01 sec)

MySQL安装过程需要首先初始化。在初始化期间，MySQL会创建应被视为保留的用户帐户：

‘root’@’localhost：用于管理目的。此帐户具有所有权限，可以执行任何操作。严格地说，此帐户名称不是保留的，因为某些安装会将root帐户重命名为其他名称，以避免使用众所周知的名称公开具有高权限的帐户。
‘mysql.sys’@’localhost’：作为 DEFINER对 sys架构对象。使用该 mysql.sys帐户可避免DBA重命名或删除root 帐户时出现的问题。此帐户已锁定，因此无法用于客户端连接。
‘mysql.session’@’localhost’：由插件内部使用以访问服务器。此帐户已锁定，因此无法用于客户端连接

1.4 设置账户资源限制

限制客户端使用MySQL服务器资源的一种方法是将全局 max_user_connections系统变量设置为非零值。这限制了任何给定帐户可以同时进行的连接数，但对连接后客户端可以执行的操作没有限制。此外，设置 max_user_connections无法管理个人帐户。MySQL管理员都对这两种控件都很感兴趣。为了解决这些问题，MySQL允许使用这些服务器资源限制个人帐户：帐户每小时可以发出的查询数帐户每小时可以发布的更新次数帐户每小时可以连接到服务器的次数帐户与服务器同时连接的数量客户端可以发出的任何语句都会针对查询限制进行计数，除非其结果是从查询缓存中提供的。只有修改数据库或表的语句才会计入更新限制。

要在帐户创建时为帐户建立资源限制，请使用该CREATE USER 语句。要修改现有帐户的限制，请使用 ALTER USER。提供一个 WITH子句，命名每个资源是有限的。每个限制的默认值为零（无限制）。例如，要创建可以访问customer数据库但只能以有限方式访问数据库的新帐户，请发出以下语句：

代码语言：javascript复制

root@localhost [(none)]>CREATE USER 'wjq'@'%' IDENTIFIED BY 'password'
    -> WITH MAX_QUERIES_PER_HOUR 20
    ->      MAX_UPDATES_PER_HOUR 10
    ->      MAX_CONNECTIONS_PER_HOUR 5
    ->      MAX_USER_CONNECTIONS 2;
Query OK, 0 rows affected (0.00 sec)

限制类型不必全部在 WITH子句中命名，但命名的那些可以按任何顺序存在。每个每小时限制的值应该是一个表示每小时计数的整数。对于 MAX_USER_CONNECTIONS，限制是一个整数，表示帐户的最大同时连接数。如果此限制设置为零，则全局 max_user_connections系统变量值确定同时连接的数量。如果max_user_connections也为零，则帐户没有限制。

要修改现有帐户的限制，请使用 ALTER USER语句。以下语句将用户seiang查询限制更改为100：

代码语言：javascript复制

root@localhost [(none)]>ALTER USER 'seiang'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;       
Query OK, 0 rows affected (0.00 sec)

要删除限制，请将其值设置为零。例如，要删除每小时可连接的次数限制，请使用以下语句：

代码语言：javascript复制

root@localhost [(none)]>ALTER USER 'seiang'@'localhost' WITH MAX_QUERIES_PER_HOUR 0;
Query OK, 0 rows affected (0.00 sec)

如前所述，帐户的同时连接限制由 MAX_USER_CONNECTIONS限制和 max_user_connections系统变量确定。假设全局max_user_connections值为10，并且三个帐户具有指定的个别资源限制，如下所示：

代码语言：javascript复制

ALTER USER 'user1'@'localhost' WITH MAX_USER_CONNECTIONS 0;
ALTER USER 'user2'@'localhost' WITH MAX_USER_CONNECTIONS 5;
ALTER USER 'user3'@'localhost' WITH MAX_USER_CONNECTIONS 20;

user1连接限制为10（全局 max_user_connections值），因为它的MAX_USER_CONNECTIONS限制为零。user2并且分别user3具有5和20的连接限制，因为它们具有非零MAX_USER_CONNECTIONS限制

针对于数据库在user表与该帐户对应的表行中存储帐户的资源限制的列为max_questions，max_updates和 max_connections列存储每小时限制和max_user_connections列存储MAX_USER_CONNECTIONS限制。

当任何帐户对其使用任何资源具有非零限制时，将进行资源使用计数。在服务器运行时，它会计算每个帐户使用资源的次数。如果帐户在过去一小时内达到其连接数限制，则服务器将拒绝该帐户的其他连接，直到该小时为止。同样，如果帐户达到其查询或更新数量的限制，服务器将拒绝进一步的查询或更新，直到小时结束。在所有这些情况下，服务器都会发出相应的错误消息。资源计数发生在每个帐户。可以为所有帐户全局重置当前每小时资源使用计数，也可以针对给定帐户单独重置当前每小时资源使用计数：要将所有帐户的当前计数重置为零，请发出 FLUSH USER_RESOURCES声明。还可以通过重新加载授权表来重置计数（例如，使用FLUSH PRIVILEGES语句或mysqladmin reload命令）。通过再次设置其任何限制，可以将个人帐户的计数重置为零。指定等于当前分配给帐户的值的限制值。每小时计数器重置不会影响 MAX_USER_CONNECTIONS限制。服务器启动时，所有计数从零开始。计数不会通过服务器重启而延续。

1.5 通过mysqladmin工具（只能改密码）

代码语言：javascript复制

# 给root@localhost用户登录mysql设置密码为"seiang";
$ mysqladmin -u root -h localhost password "seiang" 

# 修改root@localhost用户登录mysql数据库的密码;
$ mysqladmin -u root -h localhost password "new passwd" -p "old passwd"

1.6 通过直接修改mysql.user表的用户记录

代码语言：javascript复制

# MySQL 5.6
mysql> update mysql.user set password=PASSWORD('redhat') where user='root'; 
 
# MySQL 5.7
mysql> update mysql.user set authentication_string=PASSWORD('redhat') where user='root';
或
mysql> set password for 'root'@'localhost'=PASSWORD('redhat');

改完记得刷新内存中现有的表，另外这种形式在MySQL 5.7中使用会被警告，会告诉你这是一个即将被移除的特性。MySQL 5.7提供了新的更改密码的方式：ALTER USER语句。

代码语言：javascript复制

mysql> use mysql
mysql> alter user root@'localhost' identified by '123456';

二、MySQL管理员密码找回

1、关闭MySQL $ service mysqld stop 2、在配置文件中[mysqld]字段添加skip-grant-tables指令，跳过授权表 $ cat /etc/my.cnf [mysqld]skip-grant-tables 3、给root用户登录mysql设置密码为redhat并以加密方式 mysql> use mysql; mysql> update user set password=PASSWORD(‘redhat’) where user=’root’; MySQL5.7修改密码 mysql> update mysql.user set authentication_string=PASSWORD(‘redhat’) where user=’root’;

三、MySQL权限管理

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

1）MySQL权限说明

账户权限信息被存储在MySQL数据库的几张权限表中，在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。其中GRANT和REVOKE语句所涉及的常用权限大致如下这些：CREATE、DROP、SELECT、INSERT、UPDATE、DELETE、INDEX、ALTER、CREATE、ROUTINE、FILE等，还有一个特殊的proxy权限，是用来赋予某个用户具有给他人赋予权限的权限。

2）MySQL用户授权

授权就是为某个用户授予权限，合理的授权可以保证数据库的安全，MySQL中可以使用GRANT语句为用户授予权限。授权可以分为多个层次：

全局层级：全局权限适用于一个给定服务器中的所有数据库，这些权限存储在mysql.user表中。

数据库层级：数据库权限适用于一个给定数据库中的所有目标，这些权限存储在mysql.db表中。

表层级：表权限适用于一个给定表中的所有列，这些权限存储在mysql.tables_priv表中。

列层级：列权限使用于一个给定表中的单一列，这些权限存储在mysql.columns_priv表中。

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

PS：MySQL中必须拥有GRANT权限的用户才可以执行GRANT语句。

3.1 GRANT赋予用户权限

代码语言：javascript复制

# 定义对已经存在的用户可以操作此库下的所有表及所有权限；
Mysql> grant all privileges on DB_NAME.* to 'USERNAME'@'HOST'; 
 
# 创建tom用户并赋予select权限对此库下的所有表；
mysql> grant select on DB_NAME.* to 'tom'@'localhost' identified by '1234'; 
 
# 定义tom用户赋予insert权限对db库下的xsb表； 
mysql> grant insert on db.xsb to 'tom'@'localhost';  
 
# 定义tom用户赋予update权限对db库下的xsb表；                      
mysql> grant update on db.xsb to 'tom'@'localhost';  
 
# 定义tom用于赋予update权限对db库下的xsb表中的AGE字段；
mysql> grant update(AGE) on db.xsb to 'tom'@'localhost'; 
 
# 定义tom用于赋予super权限在*.*上(super权限可以对全局变量更改)；
mysql> grant super on *.* to 'tom'@'%'; 
 
# 通过GRANT语句中的USAGE权限，你可以创建账户而不授予任何权限；它可以将所有全局权限设为'N'，假定你将在以后将具体权限授予该账户；
mysql> grant usage on *.* to 'tom'@'%';

all表示赋予用户全部权限（包含存储过程、存储函数等创建和执行）。当数据库名称.表名称被*.*代替，表示赋予用户操作服务器上所有数据库所有表的权限。用户地址可以是localhost，也可以是ip地址、机器名字、域名。也可以用’%’表示从任何地址连接。而’连接口令’不能为空，否则创建失败。

3.2 REVOKE移除用户权限

代码语言：javascript复制

# 移除tom用户对于db.xsb的权限;
mysql> revoke all on db.xsb from 'tom'@'localhost'; 
 
# 刷新授权表;
mysql> flush privileges;
 
# SHOW查看用户的权限
mysql> show grants for 'USERNAME'@'HOST';

PS：使用REVOKE收回权限之后，用户帐户的记录将从db、host、tables_priv、columns_priv表中删除，但是用户帐号记录依然在user表中保存。

3.3 PROXY特殊权限

如果想让某个用户具有给他人赋予权限的能力，那么就需要proxy权限了。当你给一个用户赋予all权限之后，你查看mysql.user表会发现Grant_priv字段还是为N，表示其没有给他人赋予权限的权限。我们可以查看一下系统默认的超级管理员权限：

代码语言：javascript复制

mysql> show grants for 'root'@'localhost';
 --------------------------------------------------------------------- 
| Grants for root@localhost                              |
 --------------------------------------------------------------------- 
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION     |
 --------------------------------------------------------------------- 
2 rows in set (0.00 sec)

可以看到其本身有PROXY权限，并且这个语句跟一般授权语句还不太一样。所以如果想让一个远程用户有给他人赋予权限的能力，就需要给此用户PROXY权限，如下：

代码语言：javascript复制

mysql> grant all on *.* to 'test'@'%' identified by '123456';
mysql> GRANT PROXY ON ''@'' TO 'test'@'%' WITH GRANT OPTION;

3.4 数据库开发人员，创建表、索引、视图、存储过程、函数等权限授权

代码语言：javascript复制

# grant创建、修改、删除MySQL数据表结构权限
grant create on testdb.* to developer@'192.168.0.%';
grant alter on testdb.* to developer@'192.168.0.%';
grant drop on testdb.* to developer@'192.168.0.%';
 
# grant操作MySQL外键权限
grant references on testdb.* to developer@'192.168.0.%';
 
# grant操作MySQL临时表权限。
grant create temporary tables on testdb.* to developer@'192.168.0.%';
 
# grant操作MySQL索引权限
grant index on testdb.* to developer@'192.168.0.%';
 
# grant操作MySQL视图、查看视图源代码 权限
grant create view on testdb.* to developer@'192.168.0.%';grant show view on testdb.* to developer@'192.168.0.%';
 
# grant操作MySQL存储过程、存储函数权限
grant create routine on testdb.* to developer@'192.168.0.%';
grant alter routine on testdb.* to developer@'192.168.0.%';
grant execute on testdb.* to developer@'192.168.0.%';

<参考>

https://dev.mysql.com/doc/refman/5.7/en/security.html

云推荐引擎数据库云数据库SQLServer sql

0 人点赞