TiDB 用户权限信息存储在哪些系统表中？

OnTheRoad · 2022 年9 月 1 日 05:10

在 MySQL 中，用户权限信息存储在 INFORMATION_SCHEMA.SCHEMA_PRIVILEGES 和 INFORMATION_SCHEMA.TABLE_PRIVILEGES 两个表中。
通过一条SQL语句就可以将所有权限信息查出来。

USE INFORMATION_SCHEMA;

SELECT  V.USER 用户名,
		V.HOST 限定主机,
		V.FULLNAME 用户全名,
		V.OBJ_NAME 库表名称,
		V.PRIVILEGES 权限,
		V.GRANTS_SQL 授权SQL
  FROM 
(
SELECT SUBSTR(SUBSTRING_INDEX(T1.GRANTEE,'@',1),2,LENGTH(SUBSTRING_INDEX(T1.GRANTEE,'@',1))-2) USER,
	   SUBSTR(GRANTEE,LENGTH(SUBSTRING_INDEX(T1.GRANTEE,'@',1))+3,LENGTH(GRANTEE)-LENGTH(SUBSTRING_INDEX(T1.GRANTEE,'@',1))-3) HOST,
		 T1.GRANTEE FULLNAME,
		 'DB' PRIV_CLASS,	   
	   CONCAT(T1.TABLE_SCHEMA,'.*') OBJ_NAME,
	   GROUP_CONCAT(' ',T1.PRIVILEGE_TYPE ORDER BY T1.GRANTEE, T1.TABLE_SCHEMA, T1.PRIVILEGE_TYPE) PRIVILEGES,
	   CONCAT('GRANT',GROUP_CONCAT(' ',T1.PRIVILEGE_TYPE ORDER BY T1.GRANTEE, T1.TABLE_SCHEMA, T1.PRIVILEGE_TYPE),' ON ',T1.TABLE_SCHEMA, '.*', ' TO ',T1.GRANTEE,';') GRANTS_SQL 
  FROM SCHEMA_PRIVILEGES T1
 GROUP BY T1.GRANTEE,T1.TABLE_SCHEMA
 UNION ALL
SELECT  
	   SUBSTR(SUBSTRING_INDEX(T2.GRANTEE,'@',1),2,LENGTH(SUBSTRING_INDEX(T2.GRANTEE,'@',1))-2) USER,
	   SUBSTR(GRANTEE,LENGTH(SUBSTRING_INDEX(T2.GRANTEE,'@',1))+3,LENGTH(GRANTEE)-LENGTH(SUBSTRING_INDEX(T2.GRANTEE,'@',1))-3) HOST,
		 T2.GRANTEE FULLNAME,
		 'TABLE' PRIV_CLASS,	   
	   CONCAT(T2.TABLE_SCHEMA,'.',T2.TABLE_NAME) OBJ_NAME,
	   GROUP_CONCAT(' ', T2.PRIVILEGE_TYPE ORDER BY T2.TABLE_SCHEMA, T2.TABLE_NAME, T2.PRIVILEGE_TYPE ) PRIVILEGES,
	   CONCAT('GRANT ', GROUP_CONCAT(' ', T2.PRIVILEGE_TYPE ORDER BY T2.TABLE_SCHEMA, T2.TABLE_NAME, T2.PRIVILEGE_TYPE ), ' ON ', CONCAT(T2.TABLE_SCHEMA,'.',T2.TABLE_NAME), ' TO ', T2.GRANTEE, ';' ) GRANTS_SQL
  FROM TABLE_PRIVILEGES T2
 GROUP BY T2.GRANTEE,T2.TABLE_SCHEMA,T2.TABLE_NAME
) V
WHERE HOST <> 'localhost'
 and user ='etl'
ORDER BY V.FULLNAME,PRIV_CLASS,OBJ_NAME,PRIVILEGES,GRANTS_SQL;

同样的 SQL 在 TiDB 中不适用了。在 TiDB 中，这两个表为空表。如果想一条语句查出所有用户权限信息，需要查询 mysql.db 和 mysql.tables_priv（需行列转换处理）。
TiDB 是出于何种考虑，做了这种调整？

db_user · 2022 年9 月 1 日 06:15

可以看下这个
https://github.com/pingcap/tidb/blob/master/privilege/privileges/cache.go

forever · 2022 年9 月 2 日 01:33

是不是还缺个全局权限

OnTheRoad · 2022 年9 月 2 日 03:51

对，示例 SQL 中只包含库级（<库名>.*）授权和表级（<库名>.<表名>）授权。全局和字段级别，可以参考示例，额外加上。

alfred · 2022 年9 月 2 日 06:10

确实麻烦了不少，后续版本是否考虑简化下呢？

OnTheRoad · 2022 年9 月 6 日 13:59

贴一下 MySQL 中查询用户权限的 SQL 代码（包括全局、DB、TABLE、COLUMN级别的权限）。
查询用户权限.sql (3.4 KB)

USE INFORMATION_SCHEMA;

SELECT 
			 V0.USER,
			 V0.HOST,
			 V0.ACCOUNT_LOCKED,
			 V1.用户全名,
			 V1.权限分类,
			 V1.对象名称,
			 V1.权限,
			 V1.授权SQL
  FROM MYSQL.USER V0 
	LEFT OUTER JOIN 
(
SELECT V.USER 用户名,
			 V.HOST 限定主机,
			 V.FULLNAME 用户全名,
			 V.PRIV_CLASS 权限分类,
			 V.OBJ_NAME 对象名称,
			 V.PRIVILEGES 权限,
			 V.GRANTS_SQL 授权SQL
  FROM 
(
SELECT T0.USER,
			 T0.HOST,
			 CONCAT("'",T0.USER,"'@'",T0.HOST,"'") FULLNAME,
			 'GLOBAL' PRIV_CLASS,
			 '*.*' OBJ_NAME,
			 GROUP_CONCAT(' ',T0.PRIV ORDER BY T0.USER,T0.HOST) PRIVILEGES,
			 CONCAT('GRANT',GROUP_CONCAT(' ',T0.PRIV ORDER BY T0.USER,T0.HOST),' ON *.* TO ',CONCAT("'",T0.USER,"'@'",T0.HOST,"'"), CASE WHEN WITH_GRANT_OPTION = 'Y' THEN ' WITH GRANT OPTION;' ELSE '' END) GRANTS_SQL
	FROM MYSQL.GLOBAL_GRANTS T0
 GROUP BY T0.USER,T0.HOST,WITH_GRANT_OPTION 
 UNION ALL
SELECT SUBSTR(SUBSTRING_INDEX(T1.GRANTEE,'@',1),2,LENGTH(SUBSTRING_INDEX(T1.GRANTEE,'@',1))-2) USER,
			 SUBSTR(GRANTEE,LENGTH(SUBSTRING_INDEX(T1.GRANTEE,'@',1))+3,LENGTH(GRANTEE)-LENGTH(SUBSTRING_INDEX(T1.GRANTEE,'@',1))-3) HOST,
			 T1.GRANTEE FULLNAME,
			 'DB' PRIV_CLASS,	   
			 CONCAT(T1.TABLE_SCHEMA,'.*') OBJ_NAME,
			 GROUP_CONCAT(' ',T1.PRIVILEGE_TYPE ORDER BY T1.GRANTEE, T1.TABLE_SCHEMA, T1.PRIVILEGE_TYPE) PRIVILEGES,
			 CONCAT('GRANT',GROUP_CONCAT(' ',T1.PRIVILEGE_TYPE ORDER BY T1.GRANTEE, T1.TABLE_SCHEMA, T1.PRIVILEGE_TYPE),' ON ',T1.TABLE_SCHEMA, '.*', ' TO ',T1.GRANTEE,';') GRANTS_SQL 
  FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES T1
 GROUP BY T1.GRANTEE,T1.TABLE_SCHEMA
 UNION ALL
SELECT  
			 SUBSTR(SUBSTRING_INDEX(T2.GRANTEE,'@',1),2,LENGTH(SUBSTRING_INDEX(T2.GRANTEE,'@',1))-2) USER,
			 SUBSTR(GRANTEE,LENGTH(SUBSTRING_INDEX(T2.GRANTEE,'@',1))+3,LENGTH(GRANTEE)-LENGTH(SUBSTRING_INDEX(T2.GRANTEE,'@',1))-3) HOST,
			 T2.GRANTEE FULLNAME,
			 'TABLE' PRIV_CLASS,	   
			 CONCAT(T2.TABLE_SCHEMA,'.',T2.TABLE_NAME) OBJ_NAME,
			 GROUP_CONCAT(' ', T2.PRIVILEGE_TYPE ORDER BY T2.TABLE_SCHEMA, T2.TABLE_NAME, T2.PRIVILEGE_TYPE ) PRIVILEGES,
			 CONCAT('GRANT ', GROUP_CONCAT(' ', T2.PRIVILEGE_TYPE ORDER BY T2.TABLE_SCHEMA, T2.TABLE_NAME, T2.PRIVILEGE_TYPE ), ' ON ', CONCAT(T2.TABLE_SCHEMA,'.',T2.TABLE_NAME), ' TO ', T2.GRANTEE, ';' ) GRANTS_SQL
  FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES T2
 GROUP BY T2.GRANTEE,T2.TABLE_SCHEMA,T2.TABLE_NAME
 UNION ALL
SELECT T3.USER,
		   T3.HOST,
			 CONCAT("'",T3.USER,"'@'",T3.HOST,"'") FULLNAME,
			 'COLUMN' PRIV_CLASS,
			 CONCAT(DB,".",TABLE_NAME) OBJ_NAME,
			 GROUP_CONCAT(" ",CONCAT(upper(SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_PRIV,',',TT3.HELP_TOPIC_ID+1),',',-1)),"(",T3.COLUMN_NAME,")") ORDER BY upper(SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_PRIV,',',TT3.HELP_TOPIC_ID+1),',',-1)),T3.COLUMN_NAME) PRIV,	
			 CONCAT( "GRANT ",
							 GROUP_CONCAT(" ",CONCAT(upper(SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_PRIV,',',TT3.HELP_TOPIC_ID+1),',',-1)),"(",T3.COLUMN_NAME,")") ORDER BY upper(SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_PRIV,',',TT3.HELP_TOPIC_ID+1),',',-1)),T3.COLUMN_NAME),
							 " ON ",
							 CONCAT(DB,".",TABLE_NAME),
							 " TO ",
							 CONCAT("'",T3.USER,"'@'",T3.HOST,"'")							 
						 ) GRANTS_SQL
	FROM MYSQL.COLUMNS_PRIV T3 
	JOIN MYSQL.HELP_TOPIC   TT3 
	  ON TT3.HELP_TOPIC_ID < (LENGTH(T3.COLUMN_PRIV) - LENGTH(REPLACE(T3.COLUMN_PRIV,',','')) +1)
 GROUP BY T3.USER,T3.HOST,T3.DB,T3.TABLE_NAME
) V
WHERE HOST <> 'localhost'
) V1
ON V0.USER=V1.用户名 AND V0.HOST=V1.限定主机
WHERE V0.HOST <> 'localhost'

Lucien · 2022 年9 月 7 日 02:44

您好，为了提供更完善的分布式数据库的鉴权能力，在权限表设计会和 MySQL 有些不同，使用前建议参考官方文档 https://docs.pingcap.com/zh/tidb/v5.2/privilege-management#权限系统的实现