Behavior of wildcard-based database grant changes with partial revokes
Posted by: Hasol Im
Date: May 01, 2021 05:37PM

I was playing with my personal test MySQL server when I found this strange behavior.

Following observation is based on MySQL server version 8.0.23-0ubuntu0.20.10.1 amd64, installed from apt package.

When 'partial_revokes' global variable is set to 'ON', schema-level grants with wildcards stop working.

To replicate this observation:
1. Start with 'partial_revokes' set to 'OFF'
2. Create a new user or use existing non-admin user
3. Grant select or all privileges on databases specified with wildcards to the user
4. Create database(s) matching the specification or use existing database(s)
5. Execute "SHOW DATABASES;" statement as the user
6. At this step, we can observe the database(s) are correctly listed
7. Now set 'partial_revokes' to 'ON'
8. Execute "FLUSH PRIVILEGES;" as admin and reconnect the user
9. Now execute "SHOW DATABASES;" statement as the user again
10. At this step, the database(s) shown in step 6 are no longer visible and any attempt to access the database(s) throws 'access denied' error.

In MySQL documentation regarding the partial revokes, (; 6.2.12 Privilege Restriction Using Partial Revokes) following information is written under 'Using Partial Revokes' section:

Partial revoke operations are subject to these conditions:

* Partial revokes must name the schema literally. Schema names that contain the % or _ SQL wildcard characters (for example, myschema%) are not permitted.

The documentation mentions how partial revokes can't be used with wildcards. However, the documentation does not mention how grants can't be used with wildcards when the partial revokes feature is enabled.

It is possible that this behavior is intended. If this is the case, I think the documentation should be updated to clarify how enabling partial revokes affect existing grants.

However, if this feature is not intended, then this article belongs to bug report section.

I hope this is not intended and the developers fix the bug, as partial revokes are very useful at protecting 'mysql' system database and wildcard grants are also useful of its own.

Options: ReplyQuote

Written By
Behavior of wildcard-based database grant changes with partial revokes
May 01, 2021 05:37PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.