贴一下 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'