Details
-
New Feature
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
Description
The purpose of the TABLE_PRIVILEGES table in INFORMATION_SCHEMA seems to be a bit misleading.
It only shows those privileges that were specifically granted on the table level, but it may be taken as showing all users with privileges on a table, including those that were actually granted at the database or even the global level.
Right now results from three different tables have to be combined for this, e.g. using:
SELECT t.TABLE_SCHEMA |
, t.TABLE_NAME
|
, privs.GRANTEE
|
, GROUP_CONCAT(privs.PRIVILEGE_TYPE) AS PRIVILEGES |
FROM INFORMATION_SCHEMA.TABLES AS t |
JOIN ( SELECT NULL AS TABLE_SCHEMA |
, NULL AS TABLE_NAME |
, GRANTEE, PRIVILEGE_TYPE
|
FROM INFORMATION_SCHEMA.USER_PRIVILEGES |
|
UNION |
|
SELECT TABLE_SCHEMA |
, NULL AS TABLE_NAME |
, GRANTEE
|
, PRIVILEGE_TYPE
|
FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES |
|
UNION |
|
SELECT TABLE_SCHEMA |
, TABLE_NAME
|
, GRANTEE
|
, PRIVILEGE_TYPE
|
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES |
) privs
|
ON (t.TABLE_SCHEMA = privs.TABLE_SCHEMA OR privs.TABLE_SCHEMA IS NULL) |
AND (t.TABLE_NAME = privs.TABLE_NAME OR privs.TABLE_NAME IS NULL) |
AND (privs.PRIVILEGE_TYPE <> 'USAGE') |
WHERE t.TABLE_SCHEMA NOT IN ( 'mysql' |
, 'information_schema' |
, 'performance_schema' |
)
|
GROUP BY t.TABLE_SCHEMA |
, t.TABLE_NAME
|
, privs.GRANTEE
|
;
|
Attachments
Issue Links
- relates to
-
MDEV-32941 I_S.USER_PRIVILEGES has a wrong name and does not show roles
- Open