MySQL用户权限与管理

用户权限与管理

1.用户管理

1.1登录

启动MySQL服务后，可以通过mysql命令登录服务器，命令如下：

mysql -h hostname|hostIP -P port -u username -p DatabaseName -e 'sql语句'

下面详细介绍命令中的参数:

• -h参数后面接主机名或者主机IP，hostname为主机,hostlP为主机IP。
• -P参数后面接MysQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306,不使用该参数时自动连接到3306端口，port为连接的端口号。
• -u参数后面接用户名，username为用户名。
• -p参数会提示输入密码。
• DatabaseName参数指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MysQL数据库中，然后可以使用USE命令来选择数据库。
• -e参数后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个sQL语句，然后退出MySQL服务器。

1.2创建用户

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

CREATE USER 用户名[IDENTIFIED BY '密码'];

• 用户名参数表示新建用户的账户，由用户(User)和主机名(Host)构成;
• “[]"表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用IDENTIFIED BY指定明文密码值。
• CREATE USER语句可以同时创建多个用户。

举例：

mysql> CREATE USER doudou IDENTIFIED BY 'doudou';	#默认host是%
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'zhangsan';
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | doudou           |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | zhangsan         |
+-----------+------------------+
6 rows in set (0.00 sec)

1.3修改用户名

UPDATE mysql.user SET USER='lisi' WHERE USER='zhangsan';

FLUSH PRIVILEGES;

1.4删除用户

方式1：使用DROP方式删除（推荐）

使用DROP USER语句来删除用户时，必须拥有DROP USER权限。DROP USER语句的基本语法形式如下：

DROP USER user[,user]...;

举例：

DROP USER doudou;			#默认删除host为%的用户

DROP USER 'lisi'@'localhost';

方式2：使用DELETE方式删除

DELETE FROM mysql.user WHERE HOST='hostname' AND USER='username';

执行完DELETE命令后要使用FLUSH命令来使用户生效，命令如下：

FLUSH PRIVILEGES;

1.5设置当前用户密码

方式1：使用ALTER USER命令，基本语法如下：

ALTER USER USER() IDENTIFIED BY 'new_password';

方式2：使用SET语句，基本语法如下：

SET PASSWORD='new_password';

1.6修改其他用户密码

1. 使用ALTER语句修改普通用户的密码，基本语法形式如下：

   ALTER USER user [IDENTIFIED BY '新密码'];
   

2. 使用SET命令修改普通用户密码，使用root用户登录，可以使用SET语句修改普通用户密码：

   SET PASSWORD FOR 'username'@'hostname'='new_password';
   

3. 使用UPDATE命令修改普通用户密码（不推荐）

   UPDATE MYSQL.user SET authentication_string=PASSWORD("new_password")
   WHERE USER='username' AND HOST='hostname';
   

2.权限

2.1权限列表

查看MySQL中所有权限

show privileges;

常用权限

权限	说明
CREATE、DROP	可以创建新的数据库和表，或删除(移掉）已有的数据库和表
SELECT、INSERT、UPDATE和DELETE	允许在一个数据库现有的表上实施操作
SELECT	SELECT权限只有在它们真正从一个表中检索行时才被用到
INDEX	允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义
ALTER	可以使用ALTER TABLE来更改表的结构和重新命名表
CREATE ROUTINE	用来创建保存的程序（函数和程序)
ALTER ROUTINE	用来更改和删除保存的程序
EXECUTE	用来执行保存的程序
GRANT	允许授权给其他用户，可用于数据库、表和保存的程序
FILE	使用户可以使用LOAD DATAINFILE和SELECT …INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MysQL服务器上的任何文件(说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件)

2.2授予权限的原则

权限控制主要是出于安全因素，因此需要遵循以下几个经验原则︰

1、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
2、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。
3、为每个用户设置满足密码复杂度的密码。
4、定期清理不需要的用户，回收权限或者删除用户。

2.3授予权限

给用户授权的方式有2种，分别是通过把角色赋予用户给用户授权和直接给用户授权。

授权命令：

GRANT 权限2,权限2... ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY '密码'];

PS：该权限如果发现没有该用户，则会直接新建一个用户。

举例：

#给zhangsan用户授予study.user表SELECT,INSERT,UPDATE权限
GRANT SELECT,INSERT,UPDATE ON studydb.user TO zhangsan@localhost;

#给dou用户授予所有库所有表的全部权限，注意这里不包括grant权限
GRANT ALL PRIVILEGES ON *.* TO dou@'%';

2.4查看权限

• 查看当前用户权限

  SHOW GRANTS;
  或
  SHOW GRANTS FOR CURRENT_USER;
  或
  SHOW GRANTS FOR CURRENT_USER();
  

• 查看某用户的全局权限

  SHOW GRANTS FOR 'username'@'hostname';
  

2.5收回权限

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

注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限；

收回权限命令：

REVOKE 权限1,权限2... ON 数据库名称.表名称 FROM 用户名@用户地址;

举例：

#收回zhangsan用户studydb.user表INSERT,UPDATE权限
REVOKE INSERT,UPDATE ON studydb.user FROM zhangsan@localhost;

#收回dou用户全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM dou@'%';

注意：用户重新登录后才能生效！

3.权限表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中。MysQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表、db表。除此之外，还有table_priv表、column_priv表和proc_priv表等。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。

3.1user表

user表时MySQL中最重要的一个权限表：记录用户账号和权限信息。

Host、User这两个字段分别表示主机名、用户名，这2个字段组合构成了user表的主键，表示某个用户从某个主机是否具有链接数据库的权限，同时其他字段进行安全验证及权限控制等；

3.2db表

db表用来对库设置操作权限；

Host、User、Db。这3个字段分别表示主机名、用户名和数据库名。这3个字段的组合构成了db表的主键。表示从某个主机连接某个用户对某个数据库的操作权限，

3.3tables_priv表

tables_priv表用来对表设置操作权限；

Host 、 Db 、 User和Table_name 四个字段分别表示主机名、数据库名、用户名和表名。这4个字段的组合构成了tables_priv表的主键，表示从某个主机链接某个用户对某个数据库的某个表的操作权限；

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

3.4columns_priv表

columns_priv表用来对列设置操作权限；

Host 、 Db 、 User、Table_name和Column_name 五个字段分别表示主机名、数据库名、用户名、表名和字段名。这5个字段的组合构成了columns_priv表的主键，表示从某个主机链接某个用户对某个数据库的某个表的某个字段的操作权限；

4.角色管理

4.1角色的理解

角色是在MysQL 8.0 中引入的新功能。在MySQL中，角色是权限的集合，可以为角色添加或移除权限。用户可以被赋予角色，同时也被授予角色包含的权限。对角色进行操作需要较高的权限。并且像用户账户一样，角色可以拥有授予和撤消的权限。
引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

4.2创建角色

创建角色使用CREATE ROLE语句，语法如下：

CREATE ROLE 'role_name'[@'host_name'];

角色名称的命名规则和用户名类似，如果host_name省略，默认为%，role_name不可省略，不可为空；

练习：

#创建user角色，可以从任何主机登录
CREATE ROLE 'user';

#创建manager角色，只可本地登录
CREATE ROLE 'manager'@'localhost';

4.3给角色赋予权限

创建角色后，默认这个角色是没有任何权限的，我们需要给角色授权，语法结构是：

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

上述语句中privileges代表权限的名称，多个权限以逗号隔开；

举例：

#将studydb.user表SELECT权限赋予user角色
GRANT SELECT ON studydb.user TO 'user';

4.4查看角色权限

授予角色权限之后，我们可以通过SHOW GRANTS语句，来查看权限是否创建成功；

mysql> SHOW GRANTS FOR 'user';
+------------------------------------------------+
| Grants for user@%                              |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `user`@`%`               |
| GRANT SELECT ON `studydb`.`user` TO `user`@`%` |
+------------------------------------------------+
2 rows in set (0.00 sec)

创建一个角色，系统会自动赋予’USAGE’权限：连接登录数据库的权限；

4.5回收角色的权限

角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色授权相同。撤销角色或角色权限使用REVOKE语句。

修改了角色的权限，会影响拥有该角色的账户的权限。

撤销角色权限的SQL语法如下:

REVOKE privileges ON tablename FROM 'role_name';

举例：

#收回user用户在studydb.user表中SELECT权限
REVOKE SELECT ON studydb.user FROM 'user';

4.6删除角色权限

语法结构：

DROP ROLE role_name;

注意：如果删除了角色，那么用户也就失去了通过这个角色所获得的的所有权限；

4.7给用户赋予角色

角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。

给用户添加角色语法格式：

GRANT role TO USER user;

举例：

#给zhangsan赋予user角色
GRANT user TO 'zhangsan'@'localhost';
#查询zhagnsan权限
mysql> show grants;
+------------------------------------------------------------+
| Grants for zhangsan@localhost                              |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `zhangsan`@`localhost`               |
| GRANT SELECT ON `studydb`.`user` TO `zhangsan`@`localhost` |
| GRANT `user`@`%` TO `zhangsan`@`localhost`                 |
+------------------------------------------------------------+
3 rows in set (0.00 sec)

登录zhangsan用户，查询当前角色，如果角色未激活，结果将显示NONE（此时相关权限是不可用的）

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.01 sec)

4.8激活角色

方式1：使用set default role命令激活用户

#给zhangsan用户默认激活所有已拥有的角色
SET DEFAULT RELE ALL TO 'zhangsan'@'localhost';

方式2：将activate_all_roles_on_login设置为ON

• 默认情况

  mysql> show variables like 'activate_all_roles_on_login';
  +-----------------------------+-------+
  | Variable_name               | Value |
  +-----------------------------+-------+
  | activate_all_roles_on_login | OFF   |
  +-----------------------------+-------+
  1 row in set (0.02 sec)
  

• 修改设置

  SET GLOBAL activate_all_roles_on_login=ON;
  

  这条SQL语句的意思是，对所有角色永久激活，执行这条语句后，用户才真正拥有的赋予角色的所有权限；

4.9撤销用户的角色

语法格式：

REVOKE role FROM user;

示例：

#收回zhangsan用户的user角色
REVOKE user FROM 'zhangsan'@'localhost';