此学习文是基于MySQL 8.0写的
得益于大神朋友的悉心指导解决不少坑，才写出此文，向大神奉上膝盖
# 前言
| 参考文章 | 备注 |
|---|---|
| Linux - MySQL 8.0基本操作：远程连接（Yum、Apt） | 前置开胃菜 |
不应该在访问MySQL时使用root用户，除非是localhost的管理任务。你应该创建用户、限制访问、限制资源使用，等等。为了创建新用户，需要拥有CREATE USER权限。 在初始设置过程中，你可以使用root用户创建其他用户。 |
- 提前说：每次操作完用户、角色、权限后，一定要操作
更新权限
本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
查看当前登录用户
mysql> select user();
一、创建用户
1. 语法介绍
如果不指定主机地址，则将采用%（任意主机）
create user '{用户名}'@'{主机地址}' identified with {身份验证插件类型} by '{密码}';
mysql> CREATE USER IF NOT EXISTS 'employees'@'%'
-> IDENTIFIED WITH mysql_native_password BY 'Employees@123456'
-> WITH MAX_QUERIES_PER_HOUR 500
-> MAX_UPDATES_PER_HOUR 100;
Query OK, 0 rows affected (0.14 sec)
mysql> select host, user, plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | employees | mysql_native_password |
| % | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.02 sec)
上述声明将为用户创建以下内容
- 用户名：
employees - 可以从任意主机访问，也可以限制对IP范围的访问，例如
10.148.%.% - 密码：
Employees@123456 - 使用
mysql_native_password身份验证插件，还可以指定任何可选的身份验证，例如sha256_password、LDAP或Kerberos - 用户可以在一小时内执行的最大查询数为
500 - 用户可以在一小时内执行的最大更新次数为
100
当客户端连接到MySQL服务器时，它会经历两个访问控制阶段：
- 连接验证
- 请求验证
在连接验证过程中，服务器通过用户名和连接的主机名来识别连接，服务器会调用用户认证插件并验证密码，服务器还会检查用户是否被锁定。
在请求验证阶段，服务器会检查用户是否有足够的权限执行每项操作。
2. 创建dbadmin用户
参考： Linux - MySQL 8.0(二)基本操作：远程连接（Yum、Apt） - 6. 创建dbadmin用户
# 仅做了解
在前面的语句中，必须以明文形式输入密码，这些密码可以记录在命令历史记录文件$ HOME/.mysql_history中。为了避免这种情况（以明文形式输入密码），你可以在本地服务器上计算hash值并直接指定hash字符串。 语法与之前几乎相同，除了需要把mysql_native_password BY 'Employees@123456'更改为mysql_native_password AS '{hashed_string}'
create user '{用户名}'@'{主机地址}' identified with {身份验证插件类型} as '{hashed_string}';
# MySQL 8.0执行报错，兼容性问题
mysql> select password('Employees@123456');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('Employees@123456')' at line 1
# 查了资料，8.0需要指定加密类型，例如：md5
mysql> select md5('Employees@123456');
+----------------------------------+
| md5('Employees@123456') |
+----------------------------------+
| ddcbd1760a56f3d339507bb51a6c07e6 |
+----------------------------------+
1 row in set (0.00 sec)
更多有关的创建用户请参阅 https://dev.mysql.com/doc/refman/8.0/en/create-user.html
二、授予和撤销用户的访问权限
1. 授予权限
grant {权限类型} on {schema}.{table} to '{用户名}'@'{主机地址}';
MySQL新版已经弃用授予权限方式直接创建新用户
so，下面示例中如涉及此种方式，仅当语法参考
- 授予
read（select）权限
mysql> grant select on employees.* to 'employees'@'%';
Query OK, 0 rows affected (0.13 sec)
星号（*）表示数据库内的所有表
- 限制
查询指定表
# 举个栗子
mysql> grant select on employees.employees to 'employees_read'@'%';
- 可以进一步将访问权限限制
仅能查询指定列
grant {权限类型}(col1, col2,……) on {schema}.{table} to '{用户名}'@'{主机地址}';
# 举个栗子
mysql> grant select(first_name, last_name) on employees.employees to 'employees_ro'@'%';
# employees_ro用户
mysql> select * from employees.employees limit 2;
ERROR 1143 (42000): SELECT command denied to user 'employees_ro'@'localhost' for column 'emp_no' in table 'employees'
mysql> select first_name,last_name from employees.employees limit 2;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Georgi | Facello |
| Bezalel | Simmel |
+------------+-----------+
2 rows in set (0.00 sec)
# 查看拥有权限查询的列
mysql> desc employees.employees;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
- 授予
write（insert）权限
mysql> grant insert on employees.* to 'employees'@'%';
Query OK, 0 rows affected (0.01 sec)
- 授予
write（insert、delete、update）权限
mysql> grant insert, delete, update on employees.* to 'employees'@'%';
Query OK, 0 rows affected (0.01 sec)
- 创建
super用户并授予ALL权限
需要一个管理员账户来管理服务器，ALL表示除GRANT权限之外的所有权限
mysql> create user 'dbadmin'@'%' identified with mysql_native_password by 'Dbadmin@123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all privileges on *.* to 'dbadmin'@'%';
Query OK, 0 rows affected (0.02 sec)
- 授予
GRANT特权（等同上面授权语句加上后缀with grant option）
用户拥有GRANT OPTION权限才能授予其他用户权限，可以将GRANT特权扩展到dbadmin超级用户
grant grant option on *.* to '{用户名}'@'{主机地址}';
mysql> grant grant option on *.* to 'dbadmin'@'%';
Query OK, 0 rows affected (0.10 sec)
mysql> select host, user, plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | dbadmin | mysql_native_password |
| % | employees | mysql_native_password |
| % | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
6 rows in set (0.00 sec)
更多有关的权限类型请参阅 https://dev.mysql.com/doc/refman/8.0/en/grant.html
2. 检查授权
show grants for '{用户名}'@'{主机地址}'\G;
# 简单写法
mysql> show grants for dbadmin;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for dbadmin@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `dbadmin`@`%` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `dbadmin`@`%` WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
3. 撤销权限
撤销权限与创建权限的语法相同，向用户授予权限用TO，撤销用户的权限用FROM。
revoke {权限类型} on {schema}.{table} from '{用户名}'@'{主机地址}';
默认是级联撤销，即：会将该用户的该权限以及该用户授予给其他用户的该权限全部回收。使用restrict防止级联回收。
revoke {权限类型} on {schema}.{table} from '{用户名}'@'{主机地址}' restrict;
# 查一下撤销前的权限
mysql> show grants for employees;
+----------------------------------------------------------------------------------------+
| Grants for employees@% |
+----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `employees`@`%` |
| GRANT SELECT, INSERT ON `employees`.`dept_emp` TO `employees`@`%` |
| GRANT SELECT (`first_name`, `last_name`) ON `employees`.`employees` TO `employees`@`%` |
+----------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
# employees用户
# 无访问权限的列会报错
mysql> select * from employees.employees limit 2;
ERROR 1143 (42000): SELECT command denied to user 'employees'@'localhost' for column 'emp_no' in table 'employees'
# 仅能查询有访问权限的列
mysql> select first_name,last_name from employees.employees limit 2;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Georgi | Facello |
| Bezalel | Simmel |
+------------+-----------+
2 rows in set (0.00 sec)
- 撤销
read、write访问权限
mysql> revoke select, insert on employees.dept_emp from 'employees'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for employees;
+----------------------------------------------------------------------------------------+
| Grants for employees@% |
+----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `employees`@`%` |
| GRANT SELECT (`first_name`, `last_name`) ON `employees`.`employees` TO `employees`@`%` |
+----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
- 撤销
列访问权限
mysql> revoke select(last_name) on employees.employees from 'employees'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for employees;
+---------------------------------------------------------------------------+
| Grants for employees@% |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `employees`@`%` |
| GRANT SELECT (`first_name`) ON `employees`.`employees` TO `employees`@`%` |
+---------------------------------------------------------------------------+
2 rows in set (0.00 sec)
# employees用户
mysql> select first_name,last_name from employees.employees limit 2;
ERROR 1143 (42000): SELECT command denied to user 'employees'@'localhost' for column 'last_name' in table 'employees'
mysql> select first_name from employees.employees limit 2;
+------------+
| first_name |
+------------+
| Georgi |
| Bezalel |
+------------+
2 rows in set (0.00 sec)
题外话：修改mysql.user表
所有用户信息及权限都存储在mysql.user表中。如果你有权访问mysql. user表，则可以直接通过修改mysql. user表来创建用户并授予权限。
如果你使用GRANT、REVOKE、SET PASSWORD或RENAME USER等账户管理语句间接修改授权表，则服务器会通知这些更改，并立即再次将授权表加载到内存中。
如果使用INSERT、UPDATE或DELETE等语句直接修改授权表，则更改不会影响权限检查，除非你重新启动服务器或指示其重新加载表。如果直接更改授权表，但忘记了重新加载表，那么在重新启动服务器之前，这些更改无效。
可以通过执行FLUSH PRIVILEGES语句来完成GRANT表的重新加载。
- 查询
mysql.user表以找出root用户的所有条目：
mysql> select * from mysql.user where user = 'root'\G
*************************** 1. row ***************************
Host: %
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *F30FE24EF72DF7E4189F600BF259AF6C40CB7A7F
password_expired: N
password_last_changed: 2019-07-16 08:40:01
password_lifetime: 0
account_locked: N
Create_role_priv: Y
Drop_role_priv: Y
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL
1 row in set (0.00 sec)
你可以看到root用户能从任意主机（%）访问数据库，只需更新mysql.user表host = 'localhost'并flush privileges重新加载授权表，即可将root限制为从localhost访问数据库：
mysql> update mysql.user set host = 'localhost' where user = 'root';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
三、修改密码&身份验证插件类型
alter user '{用户名}'@'{主机地址}' identified with {身份验证插件类型} by '{新密码}';
# 先查一下加密插件
mysql> select host, user, plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | dbadmin | mysql_native_password |
| % | employees | mysql_native_password |
| % | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
6 rows in set (0.00 sec)
# 再修改密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'new_root_pwd';
Query OK, 0 rows affected (0.01 sec)
- 身份验证插件类型
- mysql_native_password（MySQL 5.7版本）
- caching_sha2_password（MySQL 8.0版本）
四、设置用户密码有效期
可以设置一段时间作为用户密码的有效期，过期之后用户则需要更改密码。
当应用程序开发人员要求访问数据库时，可以使用默认密码创建该账户， 并将其设置为过期状态。 然后与开发人员分享此密码，他们则必须更改密码才能继续使用MySQL。
创建所有账户并设置其密码过期日期等于default_password_lifetime变量的值， 默认情况下用户被禁用。
1. 创建一个具有过期密码的用户
create user '{用户名}'@'{主机地址}' identified with {身份验证插件类型} by '{密码}' password expire;
mysql> create user 'developer'@'%' identified with mysql_native_password by 'Developer@123456' password expire;
Query OK, 0 rows affected (0.00 sec)
当开发人员第一次登录并尝试执行任何语句时，错误ERROR 1820 (HY000)将被抛出。在执行此语句之前，必须使用ALTER USER语句重置密码：
# nangy @ nangy-vm in ~ [17:12:43]
$ mysql -u developer -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 8.0.16
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
- 开发人员必须使用以下命令更改密码
# developer用户
mysql> alter user 'developer'@'%' identified with mysql_native_password by '{new_developer_pass}';
Query OK, 0 rows affected (0.10 sec)
2. 手动设置过期用户
对于已经连接登陆的会话无效，再次登陆将生效
alter user '{用户名}'@'{主机地址}' password expire;
mysql> alter user 'developer'@'%' password expire;
Query OK, 0 rows affected (0.00 sec)
3. 要求用户每隔90天更改一次密码
alter user '{用户名}'@'{主机地址}' password expire interval 90 day;
mysql> alter user 'developer'@'%' password expire interval 90 day;
Query OK, 0 rows affected (0.01 sec)
4. 设置永不过期密码
alter user '{用户名}'@'{主机地址}' password expire never;
mysql> alter user 'root'@'localhost' password expire never;
Query OK, 0 rows affected (0.01 sec)
mysql> alter user 'dbadmin'@'%' password expire never;
Query OK, 0 rows affected (0.00 sec)
5. 用户密码重用策略设置（未完成）
MySQL允许限制重复使用以前的密码。可以根据密码更改次数、已用时间或两者来建立重用限制。帐户的密码历史由过去分配的密码组成。
五、锁定&解锁用户
如果发现账户有任何问题，可以将其锁定。MySQL支持使用CREATE USER或ALTER USER锁定用户。
- 通过将
ACCOUNT LOCK子句添加到ALTER USER语句来锁定账户
alter user '{用户名}'@'{主机地址}' account lock;
mysql> alter user 'developer'@'%' account lock;
Query OK, 0 rows affected (0.01 sec)
- 开发者会收到该账户已被锁定的报错消息：
# nangy @ nangy-vm in ~ [17:34:33]
$ mysql -u developer -p
Enter password:
ERROR 3118 (HY000): Access denied for user 'developer'@'localhost'. Account is locked.
- 确认后可以解锁该账户
alter user '{用户名}'@'{主机地址}' account unlock;
mysql> alter user 'developer'@'%' account unlock;
Query OK, 0 rows affected (0.01 sec)
六、为用户创建角色
MySQL的角色是一个权限的集合。
与用户账户一样，角色的权限可以被授予和撤销。用户账户被授予角色后，该角色就会将其拥有的权限授予该账户。
之前，我们为不同的用户创建了读取、写入和管理权限。对于写入权限，我们已授予用户INSERT、 DELETE和UPDATE权限。 现在你可以将这些权限授予某个角色，然后为用户分配该角色。通过这种方式，可以避免为许多用户账户单独授予权限的麻烦。
1. 创建角色
create role '{角色名1}', '{角色名2},…… ;'
mysql> create role 'app_read'@'%', 'app_write'@'%', 'app_developer'@'%';
Query OK, 0 rows affected (0.02 sec)
mysql> select host, user, plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | app_developer | caching_sha2_password |
| % | app_read | caching_sha2_password |
| % | app_write | caching_sha2_password |
| % | dbadmin | mysql_native_password |
| % | developer | mysql_native_password |
| % | employees | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | mysql_native_password |
+-----------+------------------+-----------------------+
10 rows in set (0.00 sec)
2. 使用GRANT语句为角色分配权限
grant {权限类型} on {schema}.{table} to '{role_name}';
# 读权限
mysql> grant select on employees.* to 'app_read'@'%';
Query OK, 0 rows affected (0.00 sec)
# 写权限
mysql> grant insert, update, delete on employees.* to 'app_write'@'%';
Query OK, 0 rows affected (0.00 sec)
# 全部权限
mysql> grant all on employees.* to 'app_developer'@'%';
Query OK, 0 rows affected (0.00 sec)
# 查看已授权限
mysql> show grants for 'app_read'@'%';
+-------------------------------------------------+
| Grants for app_read@% |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `app_read`@`%` |
| GRANT SELECT ON `employees`.* TO `app_read`@`%` |
+-------------------------------------------------+
2 rows in set (0.00 sec)
3. 创建新用户
如果不指定主机地址，则将采用%（任意主机）
create user '{用户名}' identified with mysql_native_password by '{密码}';
mysql> create user 'emp_read'@'%' identified with mysql_native_password by 'Empread@123456';
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'emp_write'@'%' identified with mysql_native_password by 'Empwrite@123456';
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'emp_read_write'@'%' identified with mysql_native_password by 'Empreadwrite@123456';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'emp_developer'@'%' identified with mysql_native_password by 'Empdeveloper@123456';
Query OK, 0 rows affected (0.01 sec)
mysql> select host, user, plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | app_developer | caching_sha2_password |
| % | app_read | caching_sha2_password |
| % | app_write | caching_sha2_password |
| % | dbadmin | mysql_native_password |
| % | developer | mysql_native_password |
| % | emp_developer | mysql_native_password |
| % | emp_read | mysql_native_password |
| % | emp_read_write | mysql_native_password |
| % | emp_write | mysql_native_password |
| % | employees | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | mysql_native_password |
+-----------+------------------+-----------------------+
14 rows in set (0.00 sec)
4. 使用GRANT语句为用户分配角色
你可以为用户分配多个角色，例如：可以将读取和写入权限都分配给emp_read_write用户
grant '{role_name1}', '{role_name2}',…… to '{用户名}'@'{主机地址}';
mysql> grant 'app_read' to 'emp_read'@'%';
Query OK, 0 rows affected (0.03 sec)
mysql> grant 'app_write' to 'emp_write'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> grant 'app_read', 'app_write' to 'emp_read_write'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> grant 'app_developer' to 'emp_developer'@'%';
Query OK, 0 rows affected (0.00 sec)
# 查看用户权限及所属角色权限
mysql> show grants for emp_read using app_read;
+-------------------------------------------------+
| Grants for emp_read@% |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `emp_read`@`%` |
| GRANT SELECT ON `employees`.* TO `emp_read`@`%` |
| GRANT `app_read`@`%` TO `emp_read`@`%` |
+-------------------------------------------------+
3 rows in set (0.00 sec)
生产环境为安全起见，请尽量不要使用%，并限制对部署应用程序IP的访问
5. 启用角色
设置了角色，如果不启用，用户登录的时候，依旧没有该角色的权限
MySQL中与用户角色相关的表：mysql.default_roles、mysql.role_edges
# 启用用户的单个角色
mysql> set default role 'app_read' to 'emp_read';
Query OK, 0 rows affected (0.00 sec)
# 如果一个用户有多个角色，使用以下命令
mysql> set default role all to 'emp_read_write';
Query OK, 0 rows affected (0.00 sec)
6. 验证一下
退出重新登录用户，就可以看到已授权的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| employees |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)
题外话：撤销用户的角色
revoke '{role_name1}', '{role_name2}',…… from '{用户名}'@'{主机地址}';
mysql> revoke 'app_read' from 'emp_read'@'%';
Query OK, 0 rows affected (0.10 sec)
mysql> show grants for 'emp_read'@'%';
+-------------------------------------------------+
| Grants for emp_read@% |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `emp_read`@`%` |
+-------------------------------------------------+
1 rows in set (0.00 sec)
七、删除用户、角色
mysql> drop user 'emp_read';
mysql> drop role 'app_read';
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
