本站文章除注明转载/出处外,均为本站原创或翻译,转载前请务必署名原文链接~~~

MySQL的用户及权限管理

微信搜索 zze_coding 或扫描 👉 二维码关注我的微信公众号获取更多资源推送:


用户管理

MySQL 用户的定义格式:

<用户名>@<白名单地址>

白名单地址指的是允许登录数据库的主机地址,看如下定义示例:

  • zze@'%':允许所有网络地址以 zze 用户登录数据库;
  • zze@'10.0.1.%':允许 10.0.1.0/24 网段以 zze 用户登录数据库;
  • zze@'10.0.1.5%':允许 10.0.1.50-10.0.1.59 段的地址以 zze 用户登录数据库;
  • zze@'10.0.1.0/255.255.255.0':同 zze@'10.0.1.%'
  • zze@'localhost':仅允许本机地址以 zze 用户登录数据库;
  • zze@'db01':允许主机名为 db01 的主机以 zze 用户登录数据库;
  • zze@'zze.xyz':允许对应域名为 zze.xyz 的 IP 以 zze 用户登录数据库;

查询用户

用户的信息保存在 MySQL 中的 mysql 库下的 user 表中,其中我们需要重点关注的列如下:

mysql> select user,host,authentication_string,plugin from mysql.user;
+---------------+-----------+-------------------------------------------+-----------------------+
| user          | host      | authentication_string                     | plugin                |
+---------------+-----------+-------------------------------------------+-----------------------+
| root          | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF | mysql_native_password |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
+---------------+-----------+-------------------------------------------+-----------------------+
3 rows in set (0.00 sec)

上述几列的描述如下:

  • user:用户名;
  • host:允许登录的主机,即白名单地址;
  • autoentication_string:加密后的密码;
  • plugin:密码加密使用的加密插件;

创建用户

创建一个用户,用户名为 zze,允许通过 10.0.1.0/24 网段的主机登录,并授权给其所有权限:

mysql> create user zze@'10.0.1.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to zze@'10.0.1.%';

MySQL 中关键字不区分大小写,字面量是区分大小写的,在上述 SQL 中 create useridentified by 是关键字,zze@10.0.1.%123 是字面量。
在 MySQL 5.7 中也可通过如下单条命令同时完成上面的用户创建及授权的操作:

mysql> grant all on *.* to zabbix@'%' identified by '123';

但是不推荐这样使用,因为在 MySQL 8.0+ 中已经不支持 identified by 字句了。
MySQL 8.0+ 的版本采用了全新的密码加密插件 caching_sha_password,在主从、MHA、以及使用部分客户端的场景下,需要将加密插件替换为 5.7 版本支持 mysql_native_password

下面是在 MySQL 8.0+ 中指定插件创建用户的一个示例:

mysql> create user zze@% identified with mysql_native_password by '123'`;

修改用户

以修改用户密码为 123456 为例:

mysql> alter user zze@'10.0.1.%' identified by '123456'

在 MySQL 8.0+ 可同时修改密码插件:

mysql> alter user zze@'10.0.1.%' identified with mysql_native_password by '123';

让密码过期禁止用户登录:

mysql> alter user zze@'10.0.1.%' password expire;

锁定与解锁用户:

-- 锁定
mysql> alter user zze@'10.0.1.%' account lock;
-- 解锁
mysql> alter user zze@'10.0.1.%' account unlock;

删除用户

以删除 zze@'10.0.1.%' 用户为例:

mysql> drop user zze@'10.0.1.%';

权限管理

MySQL 中权限管理的作用是用来约束、控制用户能对 MySQL 中的对象(库、表)做什么操作。

  • 在 MySQL 8.0 以前,MySQL 仅支持基于命令的授权,即授权的最小粒度为命令。
  • 在 MySQL 8.0 以后,MySQL 加入了 role(角色)的授权。

MySQL 中的用户权限是基于表进行存储的,有如下几个常用的授权表:

  • mysql.user:保存了所有用户的基本信息和和全库级别的权限信息;
  • mysql.db:保存了用户在单库级别的权限信息;
  • mysql.tables_priv:保存了用户在单表级别的权限信息;
  • mysql.columns_priv:保存了用户在单列级别的权限信息;

查看可授权的权限

可通过如下 SQL 查看可授权的权限列表:

mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                | To create new views                                   |
| Create user             | Server Admin                          | To create new users                                   |
| Delete                  | Tables                                | To delete existing rows                               |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| Event                   | Server Admin                          | To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  | To execute stored routines                            |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                   | Tables                                | To create or drop indexes                             |
| Insert                  | Tables                                | To insert data into tables                            |
| Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          | To make proxy user possible                           |
| References              | Databases,Tables                      | To have references on tables                          |
| Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          | To read binary log events from the master             |
| Select                  | Tables                                | To retrieve rows from table                           |
| Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view               | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          | To shut down the server                               |
| Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                | To use triggers                                       |
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.01 sec)

其中 Usage 权限其实就是新创建用户未做任何授权的状态,仅允许登录数据库。
除上面列表中显示的权限之外,在授权时还可使用 all 关键字,它是一个权限的集合,包含了除 Grant option 的所有权限。

授权

以授权 zze@'10.0.1.%' 用户为超级管理员为例,即授予其所有权限,执行下面 SQL 即可:

mysql> grant all on *.* to zze@'10.0.1.%' identified by '123' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

上述 on *.* 指的是一个授权范围,其格式为 on <库>.<表>* 表示所有,所以 on *.* 的含义就是授权所有库的所有表。

以授权一个普通用户 test@'10.0.1.%' 为例,让其拥有 test 库所有表的 selectupdatedeleteinsert 权限,执行下面 SQL 即可:

mysql> grant select,update,delete,insert on test.* to test@'10.0.1.%' identified by '123';

多个权限之间用 , 隔开。

查看权限

以查看 test@'10.0.1.%' 拥有的权限为例,执行下面 SQL 即可:

mysql> show grants for test@'10.0.1.%';    
+-----------------------------------------------------------------------+
| Grants for test@10.0.1.%                                              |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'10.0.1.%'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'test'@'10.0.1.%' |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

撤销授权

以撤销 test@'10.0.1.%' 用户对 test 库中所有表的删除权限为例,执行下面 SQL 即可:

mysql> revoke delete on test.* from test@'10.0.1.%';

角色

在 MySQL 8.0+ 中新增了角色的功能,角色就是权限的集合,创建角色后可以通过将用户设定为指定的角色,从而间接的赋予对应角色拥有的所有权限给用户。

创建角色:

mysql> CREATE ROLE 'app_developer', 'app_read', 'app_write';

角色授权:

mysql> GRANT ALL ON app_db.* TO 'app_developer';
mysql> GRANT SELECT ON app_db.* TO 'app_read';
mysql> GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

创建用户:

mysql> CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
mysql> CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
mysql> CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
mysql> CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';

授权用户角色:

mysql> GRANT 'app_developer' TO 'dev1'@'localhost';
mysql> GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
mysql> GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

扩展

查看表定义

可通过 desc <TABLE> 的方式查看指定表的定义信息:

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
...
45 rows in set (0.00 sec)

查看帮助

通过 help <关键字> 查看对应关键字的用法,以查看 create user 的用法为例:

mysql> help create user;
Name: 'CREATE USER'
Description:
Syntax:
CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
...

开启日志

以将所有执行过的 SQL 及其结果记录到 mysqlop.log 中为例:

mysql> tee mysqlop.log
Logging to file 'mysqlop.log'

要注意的是,此种方式仅会保存执行 tee 之后的执行的 SQL 及其结果。

# MySQL  

如果这篇文章对您有帮助,可点击下方链接分享给你的朋友们😋,如果遇到问题欢迎评论、留言~~~😇

评论

公众号:zze_coding

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×