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
- causes
-
MDEV-36030 sysschema.v_privileges_by_table_by_level test fails in network sandbox with hostname set to localhost
-
- Confirmed
-
- relates to
-
-
- Open
-
Activity
this is a good idea. sys schema is where various views over system informational tables are, this new view would fit quite naturally there.
OK, adding a new view to sys schema seems trivial, let's just clarify the requirements.
1. What the name should the view have? I couldn't come up with anything other than ALL_TABLE_PRIVILEGES.
2. Is SQL in the task description final or should be improved somehow?
3. Are the field names TABLE_SCHEMA, TABLE_NAME, GRANTEE, PRIVILEGES valid?
1. May be even just TABLE_PRIVILEGES? Sys schema has a view PROCESSLIST, so it can have views with the same name as in P_S or I_S and different (but related) content.
2. I didn't run that query, so I suggest to take it as a suggestion and adjust if it doesn't print the correct result or optimize as you see fit.
3. I'd suggest to use the same columns as in INFORMATION_SCHEMA.TABLE_PRIVILEGES. Ah, plus one more column, like, level ENUM('GLOBAL', 'SCHEMA', 'TABLE') (may be USER instead of GLOBAL. The latter seems more appropriate, but the former matches standard table name USER_PRIVILEGES)
Take a look at the attached MDEV-24486.sql. The query adds the LEVEL column as it was suggested, displays both tables and views and filters only privileges related to tables and views (as described here ).
maxmether, if we want to display all objects, then such filtering cannot be applied. Isn't it better to have separate views for different kinds of objects, like PROCEDURE_PRIVILEGES, TABLESPACE_PRIVILEGES etc, than to mix them all together?
yes, different, modelled under standard views. but I think such an extension should be a separate task anyway
- please double-check that your select does not open any tables (you only select TABLE_SCHEMA and TABLE_NAME, it should not need .frm to be actually opened, but let's double-check just in case)
- PRIVILEGE_TYPE IN is error-prone and actually already incorrect. LOCK is a db-level privilege, not a table-level. The correct set is defined by the TABLE_ACLS in sql/privilege.h
- please add there a comment, like "when changing this, don't forget to update tables_priv (mariadb_system_tables.sql and mariadb_system_tables_fix.sql) and <<...insert the file name of your new view here...>>"
- and, naturally, do this youself — update tables_priv and your view to match.
Any suggestions how to make it less manual are welcome
oleg.smirnov My point was mainly that when we design this feature we should take into account that we might want to extend this for all objects. Also for completeness sake. So that whatever we do for TABLES will look similar for other objects as well.
If we need separate JIRA tickets for this we should create them.
1. I put breakpoints at open_table(), readfrm(), open_table_from_share(), handler::ha_open() and couldn't see any signs that the server tries to open .frm.
2. Updated what you pointed out, although I don't think it makes sense to show CREATE TABLE and CREATE VIEW privileges since we are displaying existing tables/views (so they aren't on the list but I can bring them back if you disagree).
3. No good ideas how to make updating list of privileges less manual without large refactoring. However, I don't think that list is updated very often.
4. I couldn't figure out the upgrade process of sys_schema, so not sure will the new view be available for users upgrading their databases from previous versions.
5. Otherwise, bb-11.4-MDEV-24486 is ready for review.
How will this work with DENY and ROLES ?
I am not sure this can be done with a VIEW or SQL query when taking the above permissions into account.
Yes, both ROLES and DENY would have to be taken into account for this information to accurate. I am sure one could write a query (CTE or similar) that also gets the permissions of the ROLEs. However for DENY this will have to be taken care of in the version that DENY is introduced.
DENY doesn't exist yet. Roles should already work fine, the Grantee column will show the grantee whether it's a user or a role.
Double-checked: correct, roles are shown along with users in the Grantee field.
Test sysschema.v_table_privileges.test covers that.
Branches bb-11.4-MDEV-24486 and bb-10.6-MDEV-24486 are ready for testing
serg, can you please hint where should I push to from those two branches: bb-11.4-MDEV-24486 and bb-10.6-MDEV-24486?
It seems that there are no tests in v_table_privileges.test with level=GLOBAL or SCHEMA, it needs to add such ones.
The name of new view should be changed to
-- View: privileges_by_table_by_level
|
--
|
-- Shows granted privileges broken down by table on which they allow access and level on which they were granted
|
INFORMATION_SCHEMA.TABLE_PRIVILEGES shows what it should. While there is no "global" and "database" privileges in the standard, it clearly says that COLUMN_PRIVILEGES should only show privileges granted on the column level, not all columns where a user has any privileges on. Following this logic TABLE_PRIVILEGES should not show privileges granted on the database or global level.
I'm not closing this MDEV, as "easier way to retrieve all users that have privileges on a specific table" is still a valid request, even if it cannot be solved by the INFORMATION_SCHEMA.TABLE_PRIVILEGES table.