In which system tables is TiDB user privilege information stored?

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

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

| username: OnTheRoad

In MySQL, user privilege information is stored in the INFORMATION_SCHEMA.SCHEMA_PRIVILEGES and INFORMATION_SCHEMA.TABLE_PRIVILEGES tables. You can retrieve all privilege information with a single SQL statement.

USE INFORMATION_SCHEMA;

SELECT  V.USER AS Username,
        V.HOST AS Host,
        V.FULLNAME AS Fullname,
        V.OBJ_NAME AS ObjectName,
        V.PRIVILEGES AS Privileges,
        V.GRANTS_SQL AS GrantSQL
  FROM 
(
SELECT SUBSTR(SUBSTRING_INDEX(T1.GRANTEE,'@',1),2,LENGTH(SUBSTRING_INDEX(T1.GRANTEE,'@',1))-2) AS USER,
       SUBSTR(GRANTEE,LENGTH(SUBSTRING_INDEX(T1.GRANTEE,'@',1))+3,LENGTH(GRANTEE)-LENGTH(SUBSTRING_INDEX(T1.GRANTEE,'@',1))-3) AS HOST,
       T1.GRANTEE AS FULLNAME,
       'DB' AS PRIV_CLASS,       
       CONCAT(T1.TABLE_SCHEMA,'.*') AS OBJ_NAME,
       GROUP_CONCAT(' ',T1.PRIVILEGE_TYPE ORDER BY T1.GRANTEE, T1.TABLE_SCHEMA, T1.PRIVILEGE_TYPE) AS 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,';') AS 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) AS USER,
       SUBSTR(GRANTEE,LENGTH(SUBSTRING_INDEX(T2.GRANTEE,'@',1))+3,LENGTH(GRANTEE)-LENGTH(SUBSTRING_INDEX(T2.GRANTEE,'@',1))-3) AS HOST,
       T2.GRANTEE AS FULLNAME,
       'TABLE' AS PRIV_CLASS,       
       CONCAT(T2.TABLE_SCHEMA,'.',T2.TABLE_NAME) AS OBJ_NAME,
       GROUP_CONCAT(' ', T2.PRIVILEGE_TYPE ORDER BY T2.TABLE_SCHEMA, T2.TABLE_NAME, T2.PRIVILEGE_TYPE ) AS 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, ';' ) AS 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;

The same SQL does not work in TiDB. In TiDB, these two tables are empty. If you want to retrieve all user privilege information with a single statement, you need to query mysql.db and mysql.tables_priv (which requires row-column transformation).

What considerations led TiDB to make this adjustment?

2
| username: db_user | Original post link

You can take a look at this
https://github.com/pingcap/tidb/blob/master/privilege/privileges/cache.go

3
| username: forever | Original post link

:+1: Is there still a lack of global permissions? :grin:

4
| username: OnTheRoad | Original post link

Yes, the example SQL only includes database-level (<database_name>.*) and table-level (<database_name>.<table_name>) authorizations. For global and column-level authorizations, you can refer to the example and add them additionally.

5
| username: alfred | Original post link

It has indeed become quite troublesome. Will future versions consider simplifying it?

6
| username: OnTheRoad | Original post link

Here is the SQL code to query user permissions in MySQL (including global, DB, TABLE, and COLUMN levels):

USE INFORMATION_SCHEMA;

SELECT 
    V0.USER,
    V0.HOST,
    V0.ACCOUNT_LOCKED,
    V1.Full_Name,
    V1.Privilege_Class,
    V1.Object_Name,
    V1.Privileges,
    V1.Grant_SQL
FROM MYSQL.USER V0 
LEFT OUTER JOIN 
(
    SELECT V.USER User_Name,
        V.HOST Host,
        V.FULLNAME Full_Name,
        V.PRIV_CLASS Privilege_Class,
        V.OBJ_NAME Object_Name,
        V.PRIVILEGES Privileges,
        V.GRANTS_SQL Grant_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.User_Name AND V0.HOST=V1.Host
WHERE V0.HOST <> 'localhost'
7
| username: Lucien-卢西恩 | Original post link

Hello, in order to provide more comprehensive authentication capabilities for the distributed database, the design of the privilege table will be somewhat different from MySQL. It is recommended to refer to the official documentation before use: 权限管理 | PingCAP 文档中心