新的更安全更快的的认证方式
5.7中无密码管理
新增三个密码管理相关配置项
password_histroy=3 (不能和最近三次使用过的密码相同)
password_reuse_interval=90 (不能和最近90天使用过的密码相同)
password_require_current=ON (开启后修改密码需要验证旧密码,root用户不需要)
MySQL角色是指定的权限集合. 像用户帐户一样,角色可以拥有授予和撤消的权限:
以下列表总结了MySQL提供的角色管理功能:
考虑如下几种场景:
应用程序使用名为app_db的数据库 。
与应用程序相关联,可以为创建和维护应用程序的开发人员以及管理员账户。
开发人员需要完全访问数据库。有的用户只需要读取权限,有的用户需要读取/写入权限。
为清楚区分角色的权限,将角色创建为所需权限集的名称。通过授权适当的角色,可以轻松地为用户帐户授予所需的权限。
CREATE ROLE 'app_developer', 'app_read', 'app_write';
角色名称与用户帐户名称非常相似,由格式中的用户部分和主机部分组成.
主机部分,如果省略,则默认为%。用户和主机部分可以不加引号,除非它们包含特殊字符
与帐户名称不同,角色名称的用户部分不能为空
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';CREATE ROLE 'app_developer', 'app_read', 'app_write';
现在假设最初需要一个开发人员帐户,两个需要只读访问权的用户以及一个需要读取/写入权限的用户. 使用CREATEUSER创建用户:
CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';
要为每个用户分配其所需的权限,可以使用GRANT与刚才显示的形式相同的语句,但这需要列举每个用户的个人权限。相反,使用GRANT允许授权角色而非权限的替代语法:
GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';
结合角色所需的读取和写入权限,在GRANT中授权 rw_user1用户读取和写入的角色。
在GRANT授权角色的语法和授权用户的语法不同:有一个ON来区分角色和用户的授权,有ON的为用户授权,而没有ON用来分配角色。
由于语法不同,因此不能在同一语句中混合分配用户权限和角色。(允许为用户分配权限和角色,但必须使用单独的GRANT语句,每种语句的语法都要与授权的内容相匹配。)
要验证分配给用户的权限,使用 SHOW GRANTS
mysql> SHOW GRANTS FOR 'dev1'@'localhost';
+-------------------------------------------------+
| Grants for dev1@localhost |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
+-------------------------------------------------+
但是,它会显示每个授予的角色,而不会将其显示为角色所代表的权限。如果要显示角色权限,添加一个 USING来显示:
mysql> SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';
+----------------------------------------------------------+
| Grants for dev1@localhost |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost` |
| GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
+----------------------------------------------------------+
同样验证其他类型的用户:
mysql> SHOW GRANTS FOR ‘read_user1’@‘localhost’ USING ‘app_read’;
±-------------------------------------------------------+
| Grants for read_user1@localhost |
±-------------------------------------------------------+
| GRANT USAGE ON . TO read_user1
@localhost
|
| GRANT SELECT ON app_db
.* TO read_user1
@localhost
|
| GRANT app_read
@%
TO read_user1
@localhost
|
±-------------------------------------------------------+
mysql> SHOW GRANTS FOR ‘rw_user1’@‘localhost’ USING ‘app_read’, ‘app_write’;
±-----------------------------------------------------------------------------+
| Grants for rw_user1@localhost |
±-----------------------------------------------------------------------------+
| GRANT USAGE ON . TO rw_user1
@localhost
|
| GRANT SELECT, INSERT, UPDATE, DELETE ON app_db
.* TO rw_user1
@localhost
|
| GRANT app_read
@%
,app_write
@%
TO rw_user1
@localhost
|
±-----------------------------------------------------------------------------+
2.3 撤消角色或角色权限
正如可以授权某个用户的角色一样,可以从帐户中撤销这些角色:
REVOKE role FROM user; REVOKE可以用于角色修改角色权限。这不仅影响角色本身权限,还影响任何授予该角色的用户权限。假设想临时让所有用户只读,使用REVOKE从该app_write角色中撤消修改权限 :
REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM ‘app_write’; 碰巧,某个角色完全没有任何权限,正如可以看到的那样SHOW GRANTS (这个语句可以和角色一起使用,而不仅仅是查询用户权限可用):
mysql> SHOW GRANTS FOR ‘app_write’;
±--------------------------------------+
| Grants for app_write@% |
±--------------------------------------+
| GRANT USAGE ON . TO app_write
@%
|
±--------------------------------------+
从角色中撤销权限会影响到该角色中任何用户的权限,因此 rw_user1现在已经没有表修改权限(INSERT, UPDATE,和 DELETE权限已经没有了):
mysql> SHOW GRANTS FOR ‘rw_user1’@‘localhost’
USING ‘app_read’, ‘app_write’;
±---------------------------------------------------------------+
| Grants for rw_user1@localhost |
±---------------------------------------------------------------+
| GRANT USAGE ON . TO rw_user1
@localhost
|
| GRANT SELECT ON app_db
.* TO rw_user1
@localhost
|
| GRANT app_read
@%
,app_write
@%
TO rw_user1
@localhost
|
±---------------------------------------------------------------+
实际上,rw_user1读/写用户已成为只读用户。对于被授予app_write角色的任何其他用户也会发生这种情况,说明修改使用角色而不必修改个人帐户的权限。
要恢复角色的修改权限,只需重新授予它们即可:
GRANT INSERT, UPDATE, DELETE ON app_db.* TO ‘app_write’; 现在rw_user1再次具有修改权限,就像授权该app_write角色的其他任何帐户一样。
2.4 删除角色
要删除角色,请使用DROP ROLE:
DROP ROLE ‘app_read’, ‘app_write’; 删除角色会从授权它的每个帐户中撤消该角色。
2.5 角色和用户在实际中的应用
假设遗留应用开发项目在MySQL中的角色出现之前开始,因此与该项目相关联的所有用户都是直接授予权限(而不是授予角色权限)。其中一个帐户是最初被授予权限的开发者用户,如下所示:
CREATE USER ‘old_app_dev’@‘localhost’ IDENTIFIED BY ‘old_app_devpass’; GRANT ALL ON old_app.* TO ‘old_app_dev’@‘localhost’; 如果此开发人员离开项目,则有必要将权限分配给其他用户,或者项目参与人增多,则可能需要多个用户。以下是解决该问题的一些方法:
不使用角色:更改帐户密码,以便原始开发人员不能使用它,并让新的开发人员使用该帐户:
ALTER USER ‘old_app_dev’@‘localhost’ IDENTIFIED BY ‘new_password’; 使用角色:锁定帐户以防止任何人使用它来连接服务器:
ALTER USER ‘old_app_dev’@‘localhost’ ACCOUNT LOCK; 然后将该帐户视为角色。对于每个新开发项目的开发者,创建一个新帐户并授予其原始开发者帐户:
CREATE USER ‘new_app_dev1’@‘localhost’ IDENTIFIED BY ‘new_password’; GRANT ‘old_app_dev’@‘localhost’ TO ‘new_app_dev1’@‘localhost’; 其效果是将原始开发者帐户权限分配给新帐户。
MySQL8.0的用户和角色管理也越来越像Oracle了,8.0中有不少新的特性,变化还是很大的,需要DBA不断的学习和测试,更新对MySQL新版的认知,更好地运维MySQL数据库。未来MySQL数据库自治和智能数据库是必然发展趋势,对DBA来说是解放,也是挑战。
同时也非常感谢好友知名MySQL数据库专家吴炳锡老师在百忙中抽空对本文进行校对。
MySQL 8.0有一个称为“隐藏索引”的新功能,它允许快速启用/禁用MySQL Optimizer使用的索引。
一是如果你想删除一个索引,但又想事先知道效果。你就可以使它对优化程序不可见。这是一个快速的元数据更改,使索引不可见。一旦确定没有性能下降,就可以真正去删除索引。
关键的一点是,隐藏索引不能供优化器使用,但它仍然存在,并通过写入操作保持最新。即便我们尝试“FORCE INDEX”,优化器也不会使用它,虽然我认为我们应该能够在某种程度上强制它。可能会有这样的情况:
我们可以创建一个新的隐形索引,但如果想要测试它,必须使它可见。这意味着所有对应用程序有即时影响的查询都将能够使用它。如果目的只是想测试它,我不认为这是最好的方法,不是所有人的服务器上都有相同的数据大小和真实数据。强制隐藏索引这时候可能会很有用。
你有许多索引,但不确定哪一个未使用。你可以将一个索引更改为不可见,以查看是否存在任何性能下降。如果是,你可以立即更改。
你可能有一个特殊情况,只有一个查询可以使用该索引。在这种情况下,隐藏索引可能是一个很好的解决方案。
有两个选项
如果我们现在要删除索引,我们可以将其更改为隐藏。 但是使用“FORCE / USE INDEX”的查询怎么样? 他们是否会抛出一个错误? 如果强制不存在的索引,你会收到错误。 你不会看到隐藏索引的错误。 优化器不会使用它,但知道它存在。
show create table t1 G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`i` int(11) DEFAULT NULL,
`j` int(11) DEFAULT NULL,
`k` int(11) DEFAULT NULL,
KEY `i_idx` (`i`),
KEY `idx_1` (`i`,`j`,`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)
mysql> explain select * from t1 force index(idx_1) where i=1 and j=4;
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_1 | idx_1 | 10 | const,const | 2 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
mysql> alter table t1 alter index idx_1 invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from t1 force index(idx_1) where i=1 and j=4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 16 | 6.25 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)
mysql> explain select * from t1 where i=1 and j=4;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | i_idx | i_idx | 5 | const | 2 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
正如你所看到的,如果我们使用带有隐藏索引的“FORCE INDEX”,MySQL会执行全表扫描。 MySQL不会抛出任何错误,因为索引存在,但它不可见。 即使有另一个可用的索引,它也将执行全表扫描。 在大型表上,这可能会导致严重的性能问题。 即使MySQL在查询执行期间不抛出任何错误,它也应该会在错误日志中记录一个警告。
递归公用表表达式(CTE)是一个具有引用CTE名称本身的子查询的CTE。以下说明递归CTE的语法
WITH RECURSIVE cte_name AS (
initial_query -- anchor member
UNION ALL
recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;
形成CTE结构的基本结果集的初始查询(initial_query) 初始查询部分被称为锚成员。递归查询部分是引用CTE名称的查询,因此称为递归成员。递归成员由一个UNION ALL或UNION DISTINCT运算符与锚成员相连 终止条件是当递归成员没有返回任何行时,确保递归停止。
请注意,上述约束不适用于锚定成员。 另外,只有在使用UNION运算符时,要禁止DISTINCT才适用。 如果使用UNION DISTINCT运算符,则允许使用DISTINCT。
另外,递归成员只能在其子句中引用CTE名称,而不是引用任何子查询。
WITH RECURSIVE cte_count (n)
AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM cte_count
WHERE n < 3
)
SELECT n
FROM cte_count;
SQL在此示例中,以下查询:SELECT 1 SQL是作为基本结果集返回1的锚成员。以下查询
SELECT n + 1
FROM cte_count
WHERE n < 3
是递归成员,因为它引用了cte_count的CTE名称。递归成员中的表达式<3是终止条件。当n等于3,递归成员将返回一个空集合,将停止递归。下图显示了上述CTE的元素:
递归CTE返回以下输出:
递归CTE的执行步骤如下:
MySQL 8.0用户和角色管理 关于 MySQL 8.0 新特性“隐藏索引”的一点思考 MySQL递归CTE(公共表表达式)