MariaDB Grant wildcard database name

Asked Feb 25 '20 at 10:55

Active Feb 25 '20 at 11:09

Viewed 337 times

In my MariaDB database, I have created user with wildcard(%) database name with GRANT OPTION permission. When this particular user tries to run a GRANT command to another user over a GRANTED database it gives a

ERROR 1044 (42000): Access denied for user 'dbuser'@'localhost' to database 'dbprefix_dbpostfix'

If I check GRANTS of this particular user it showing like below.

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 `dbprefix%`.* TO 'dbuser'@'localhost' WITH GRANT OPTION

My database name is like "dbprefix_dbpostfix"

edited Feb 25 '20 at 11:09

asked Feb 25 '20 at 10:55

Nissanka

`Access denied for user 'user'@'localhost'` but You're granting for user `dbuser@localhost` – num8er Feb 25 '20 at 11:07
@num8er - I have updated the names. Basically it is same user. – Nissanka Feb 25 '20 at 11:09
i think You've to get list of databases with prefix and grant them – num8er Feb 25 '20 at 11:42
https://stackoverflow.com/questions/5988842/can-i-grant-access-to-databases-with-prefix-then-wild-card – num8er Feb 25 '20 at 11:42
have You done: `FLUSH PRIVILEGES;` after granting user? – num8er Feb 25 '20 at 11:45
@num8er Yes. I have flush the privileges – Nissanka Feb 25 '20 at 11:49
ok, You've granted, but have You created that users? cause grant inserts to mysql.user table row with grants – num8er Feb 25 '20 at 11:50
@num8er The databases are dynamically creating according to the pattern. Hence it is not possible to list the databases and grant – Nissanka Feb 25 '20 at 11:51
@num8er User is already there – Nissanka Feb 25 '20 at 11:51
nope, for every grant it creates new user in mysql.user table :) that's why it says access denied. if You don't believe me do: `use mysql; select * from user;` – num8er Feb 25 '20 at 11:52
I recommend You to use professional IDE like DataGrip or better MySQL Workbench and manage users correctly. – num8er Feb 25 '20 at 11:54
https://dev.mysql.com/doc/workbench/en/images/wb-navigator-users-privileges-login.png – num8er Feb 25 '20 at 12:38
Why does that user need more than one `DATABASE`? – Rick James Mar 02 '20 at 23:14

MariaDB Grant wildcard database name

0 Answers0