MySQL进阶01 — 用户管理
陸風睿
|
|
|
39
|
0
|
文档

1692 字
|
21 分钟

概述

本章，您将学习 MySQL 中的用户管理，用户管理涉及到用户的创建、权限的分配、修改和删除等操作。

在前面 MySQL基础03—排错与后续操作 文章中，我们创建了一个用户：

# 创建用户并设置密码
create user 'jimcat8'@'%' identified by 'RockyStar8.10!';

# 赋予权限（privileges 关键字可以被省略）
grant all privileges on *.* to 'jimcat8'@'%' with grant option;

用户管理

用户类型与存储位置

MySQL 中，用户主要分为：

  • 超级管理员用户 root - 拥有 MySQL 数据库系统的‌所有权限‌，包括创建用户、删除用户、修改用户密码、管理数据库结构等‌管理权限‌‌
  • 普通用户 - 仅拥有‌被授予的特定权限‌，超级管理员用户或具有相应权限的用户可对普通用户授予或分配特定的权限

所有用户的信息都保存在 mysql.user 这个表中。查看当前 MySQL 实例中的所有用户：

select host,user from mysql.user;

创建用户

创建用户需要使用 create user 语句，其完整的语法如下：

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
    [COMMENT 'comment_string' | ATTRIBUTE 'json_object']

user:
    (see Section 8.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED BY RANDOM PASSWORD [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin AS 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin [initial_auth_option]
}

2fa_auth_option: {
    IDENTIFIED BY 'auth_string' [AND 3fa_auth_option]
  | IDENTIFIED BY RANDOM PASSWORD [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY 'auth_string' [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin AS 'auth_string' [AND 3fa_auth_option]
}

3fa_auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

initial_auth_option: {
    INITIAL AUTHENTICATION IDENTIFIED BY {RANDOM PASSWORD | 'auth_string'}
  | INITIAL AUTHENTICATION IDENTIFIED WITH auth_plugin AS 'auth_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
  | FAILED_LOGIN_ATTEMPTS N
  | PASSWORD_LOCK_TIME {N | UNBOUNDED}
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

说明：

  • 在最上面的例子中，没有输入 DEFAULT ROLE role [, role ]，则表示创建的用户没有默认角色

  • 语法中，user 表示您需要输入的账户名称，账户名称的规则如下：

    • 账户名称由用户名与主机名组成，语法为 'user_name'@'host_name'，如 'jimcat8'@'%'
    • 语法中的 @'host_name' 这部分可以被省略，即 'user_name' 等同于 'user_name'@'%'，如 'me' 等同于 'me'@'%'
    • % 表示主机名的通配符
    • localhost 表示本地主机；127.0.0.1 表示 IPv4 的环回接口地址；::1 表示 IPv6 的环回接口地址
    • 可在主机名或 IP 地址中使用 %，例如 'me'@'198.51.100.%' 或 'me'@'%.mysql.com'。由于这种表示形式会在未来的版本中被删除，因此并不推荐这样使用
    • IPv4 地址可以指定子网掩码，例如 'me'@'198.51.100.44/24' 或 'me'@'198.51.100.44/255.255.255.0'
    • IPv6 地址不可以指定子网掩码

  • 默认情况下，新创建的用户无任何角色或权限。需要注意的是，在 MySQL 8.x 版本中，创建用户与权限分配是分开的两步操作。

修改已存在用户的密码

以下三种方式都可以修改已存在用户的密码：

# 使用 root 用户修改其他用户的密码
set password for '用户名'@'主机名'='新密码的字符串';

# 修改当前已经登录 MySQL 的用户密码
set password='新密码的字符串';

# 使用 root 用户修改其他用户的密码（推荐方式）
alter user '用户名'@'主机名' IDENTIFIED BY '新密码的字符串';

删除用户

语法为：

drop user '用户名'@'主机名','用户名2'@'主机名'...;

权限管理

权限分类

MySQL 8.x 版本中有两大类权限：

  • 静态权限 - 静态权限是 MySQL 服务器在‌启动时‌就已经内置的权限，这些权限始终存在，不依赖于任何特定的服务器组件或插件是否启用‌。适用于常规的数据库操作，如查询、修改数据等‌。单个用户可以拥有一个或多个静态权限：

    • ALL - ALL 等同于 ALL PRIVILEGES
    • ALTER
    • ALTER ROUTINE
    • CREATE
    • CREATE ROLE
    • CREATE
    • CREATE ROUTINE
    • CREATE TABLESPACE
    • CREATE TEMPORARY TABLES
    • CREATE USER
    • CREATE VIEW
    • DELETE
    • DROP
    • DROP ROLE
    • EVENT
    • EXECUTE
    • FILE
    • GRANT OPTION
    • INDEX
    • INSERT
    • LOCK TABLES
    • PROCESS
    • PROXY
    • REFERENCES
    • RELOAD
    • REPLICATION CLIENT
    • REPLICATION SLAVE
    • SELECT
    • SHOW DATABASES
    • SHOW VIEW
    • SHUTDOWN
    • SUPER
    • TRIGGER
    • UPDATE
    • USAGE

  • 动态权限 - 动态权限是在服务器‌运行时‌定义的权限，其存在与否取决于相关组件或插件是否被激活‌。适用于需要特定管理功能的场景，如审计日志配置、系统变量管理等‌。

有关静态权限与动态权限的详细说明，请参阅 这里。

分配权限（授予权限）

授予权限需要通过 grant 语句来执行，其完整语法为：

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]
    [AS user
        [WITH ROLE
            DEFAULT
          | NONE
          | ALL
          | ALL EXCEPT role [, role ] ...
          | role [, role ] ...
        ]
    ]
}

GRANT PROXY ON user_or_role
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]

GRANT role [, role] ...
    TO user_or_role [, user_or_role] ...
    [WITH ADMIN OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user_or_role: {
    user (see Section 8.2.4, “Specifying Account Names”)
  | role (see Section 8.2.5, “Specifying Role Names”)
}

授予权限的示例语句如下：

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

# 这里是为用户分配角色，角色后面会介绍
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';

# 这里是为角色绑定权限，后面会介绍
GRANT SELECT ON world.* TO 'role3';

查看权限

语法为：

show grants for '用户名1'@'主机名','用户名2'@'主机名'...;

撤销用户的权限

语法为：

revoke 权限1, 权限2, ... on 库名.表名 from '用户名1'@'主机名', '用户名2'@'主机名', ...;

角色管理

角色（Role）：MySQL 8 版本中的新功能，指的是相同权限的集合。单个或多个 MySQL 用户可以分配一个或多个角色，也可以从单个或多个用户中撤销一个或多个角色。

角色与用户之间的对应关系可通过查询 mysql.role_edges 表得到。

创建角色

要创建角色，请使用 create role 语句，其语法为：

create role '角色名1', '角色名2', ...;

例如：

create role 'app_developer', 'app_read', 'app_write';

为角色绑定权限

要为角色绑定特定权限，需要使用 grant 语句，语法为：

grant 权限1, 权限2, ... on 库名.表名 to '角色名1', '角色名2', ...;

例如：

GRANT ALL ON app_db.* TO 'app_developer';

GRANT SELECT ON app_db.* TO 'app_read';

GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

查看角色绑定的权限

语法为：

show grants for '角色名';

撤销角色绑定的权限

语法为：

revoke 权限1, 权限2, ... on 库名.表名 from '角色名1', '角色名2', ...;

为用户分配角色

假设您需要一名开发者的用户、两个只读权限的用户和一个读写权限的用户：

create user 'dev1'@'localhost' identified by 'Dev1#Pass';

create user 'read_user1'@'localhost' identified by 'Read_user1#Pass';

create user 'read_user2'@'localhost' identified by 'Read_user2#Pass';

create user 'rw_user1'@'localhost' identified by 'Rw_user2#Pass';

为这些用户分配角色：

# 为一名用户分配单个角色
grant 'app_developer' to 'dev1'@'localhost';

# 为两名用户分配单个角色
grant 'app_read' to 'read_user1'@'localhost', 'read_user2'@'localhost';

# 为单个用户分配两个角色
grant 'app_write','app_write' to 'rw_user1'@'localhost';

虽然角色分配给了用户，但是对应的用户可以选择性地激活或停用。。

激活用户分配的角色

语法为：

set default role 角色名1, 角色名2 ... to '用户名1'@'主机名','用户名2'@'主机名', ...;

激活成功后，可以使用下面的函数查询当前登录的用户拥有哪些角色：

select current_role();

若相应的用户不再需要角色，则可以执行以下的语句：

set role none;

以上操作是在 MySQL 实例不重启的情况下临时性生效，若需要让角色在用户登录时立刻激活，请在配置文件 /etc/my.cnf 中配置 activate_all_roles_on_login 并重启 MySQL:

[mysqld]
...
activate_all_roles_on_login=ON
...

切换角色

单个用户可以被赋予多个角色，因此可以在这些角色中自由切换：

set role '角色名';

删除角色

语法为：

drop role '角色名1', '角色名2', ...;

示例

来看下面的这样的实际例子：

# 创建新库
create database erp;

use erp;

# 创建产品信息表
create table product_info(
  id int primary key,
  name varchar(20) not null,
  description tinytext,
  unit varchar(5) default '个' not null,
  sale_price decimal(8,2) not null,
  cose_price decimal(8,2) not null,
  supplier_name varchar(20)
);

# 插入两行数据
insert into product_info values(25273, 'hz 610耳机', '白色有线的半入耳式耳机，3.5mm 接口，耳麦一体', '个' , 100, '80', '东莞xxx工厂'),
(30612, 'rt500 ARGB风冷散热器','黑色的TC版本，五热管，120 mm x 70mm x 152mm，扣具适用于 AMD/Intel', '个' ,'94.83', '70', '深圳市超频三科技股份有限公司');

# 创建用户
create user 'erp_jack'@'%' identified by 'ErpLog#>!8888';
create user 'erp_guest'@'%' identified by 'EveryLog<#@>.';

# 创建角色
create role 'erp_r','erp_rw';

# 为角色绑定权限
grant select on erp.* to 'erp_r';
grant select, insert, delete, update on erp.* to 'erp_rw';

# 为用户分配角色
grant 'erp_r' to 'erp_guest'@'%';
grant 'erp_rw' to 'erp_jack'@'%';

# 查看角色绑定的权限信息
show grants for 'erp_r';
+----------------------------------------+
| Grants for erp_r@%                     |
+----------------------------------------+
| GRANT USAGE ON *.* TO `erp_r`@`%`      |
| GRANT SELECT ON `erp`.* TO `erp_r`@`%` |
+----------------------------------------+
2 rows in set (0.00 sec)

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

# 激活用户分配的角色
set default role 'erp_rw' to 'erp_jack'@'%';
Query OK, 0 rows affected (0.00 sec)

set default role 'erp_r' to 'erp_guest'@'%';
Query OK, 0 rows affected (0.00 sec)

# 查询用户与角色之间的对应关系
select * from mysql.role_edges;
+-----------+-----------+---------+-----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER   | WITH_ADMIN_OPTION |
+-----------+-----------+---------+-----------+-------------------+
| %         | erp_r     | %       | erp_guest | N                 |
| %         | erp_rw    | %       | erp_jack  | N                 |
+-----------+-----------+---------+-----------+-------------------+
2 rows in set (0.00 sec)

用这两名用户登录并执行一些的行数据操作：

Shell > /usr/local/mysql8/bin/mysql -u erp_jack --password='ErpLog#>!8888'

MySQL > select user();
+--------------------+
| user()             |
+--------------------+
| erp_jack@localhost |
+--------------------+
1 row in set (0.00 sec)

MySQL > select current_role();
+----------------+
| current_role() |
+----------------+
| `erp_rw`@`%`   |
+----------------+
1 row in set (0.00 sec)

# 正常输出
MySQL > select * from erp.product_info;

MySQL > quit;

Shell > /usr/local/mysql8/bin/mysql -u erp_guest --password='EveryLog<#@>.'

MySQL > select user();
+---------------------+
| user()              |
+---------------------+
| erp_guest@localhost |
+---------------------+
1 row in set (0.00 sec)

MySQL > select current_role();
+----------------+
| current_role() |
+----------------+
| `erp_r`@`%`    |
+----------------+
1 row in set (0.00 sec)

# 正常输出
MySQL > select * from erp.product_info;

# 无法删除
delete from erp.product_info where id = 25273;
ERROR 1142 (42000): DELETE command denied to user 'erp_guest'@'localhost' for table 'product_info'

将示例的数据库对象删除

删除过程（使用 root 登录）：

# 删除角色
drop role 'erp_r','erp_rw';
Query OK, 0 rows affected (0.01 sec)

# 删除用户
drop user if exists 'erp_jack'@'%','erp_guest';
Query OK, 0 rows affected (0.01 sec)

# 删除库
drop database erp;
Avatar photo

关于 陸風睿

GNU/Linux 从业者、开源爱好者、技术钻研者，撰写文档既是兴趣也是工作内容之一。Q - "281957576"；WeChat - "jiulongxiaotianci"，Github - https://github.com/jimcat8
查看 陸風睿 所有文章 | 网站
版权声明:「自由转载-保持署名-非商业性使用-禁止演绎 3.0 国际」（CC BY-NC-ND 3.0）
用一杯咖啡支持我们，我们的每一篇[文档]都经过实际操作和精心打磨，而不是简单地从网上复制粘贴。期间投入了大量心血，只为能够真正帮助到您。
MySQL
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																

							
							
						

																

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
		

	






上一篇

                    

                    
			        推荐文章
		            

		            

							
MySQL基础22 — 触发器

							
							

							
MySQL基础21 — 事件调度器

							
							

							
Kafka 容错和持久性：Zookeeper VS KRaft 模式（翻译）

							
							

							
MySQL基础20 — 游标

							
							

							
公有云安全审计工具 ScoutSuite

							
							

							
MySQL基础19 — 流程控制

							
							

							
MySQL基础18 — 存储过程与存储函数

							
							

							
MySQL基础17 — 变量

							
							

							
MySQL基础16 — 视图

							
							

							
MySQL基础15 — TCL