MariaDB privileges for "SHOW DATABASES" vs "SELECT * FROM information_schema"
0
votes
1
answer
1367
views
I have a user with an access to several databases.
In **MariaDB 10.6.11** I cannot use the following command to list all accessible databases:
SHOW DATABASES;
/* SQL Error (1227): Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation */
On the other hand, I can use this:
SELECT SCHEMA_NAME
FROM information_schema
.SCHEMATA
ORDER BY SCHEMA_NAME
;
On a different server with **MariaDB 10.5.18** "SHOW DATABASES" works fine, even I think users on both servers have the same privileges:
MariaDB 10.6 user (not allowed to SHOW DATABASES):
SHOW GRANTS FOR 'dev-all'@'localhost';
GRANT USAGE ON *.* TO dev-all
@localhost
IDENTIFIED BY PASSWORD '...'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON dev-___
.* TO dev-all
@localhost
WITH GRANT OPTION
MariaDB 10.5 user (allowed to SHOW DATABASES):
SHOW GRANTS FOR 'dev-all'@'localhost';
GRANT USAGE ON *.* TO dev-all
@localhost
IDENTIFIED BY PASSWORD '...'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON dev-___
.* TO dev-all
@localhost
WITH GRANT OPTION
Is it caused by different MariaDB versions? Or am I missing something else?
What is the point of not allowing a user to list all of their databases? How can I fix this issue on MariaDB 10.6?
Asked by oneee
(23 rep)
Feb 3, 2023, 08:47 PM
Last activity: Feb 7, 2023, 08:15 AM
Last activity: Feb 7, 2023, 08:15 AM