MySQL grants privileges for the name of the wildcard database to the new user
I try to keep my MySQL user accounts as privileged as possible, only providing the bare minimum for what I need.
I have a user named creator
that I want to use to create new users, create new databases and assign those rights to users for those databases, but only for a database of a specific format.
To create a user creator
, I have the following (run as root):
GRANT CREATE USER, CREATE ON *.* TO 'creator'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON `my_prefix\_%`.* TO 'creator'@'localhost' WITH GRANT OPTION;
Now I would guess that this would give me the ability to create a new user and assign him any privileges on the table corresponding to my_prefix_%, but that doesn't seem to be the case. Doing the following queries as the user is creator
giving me the error.
CREATE DATABASE IF NOT EXISTS `my_prefix_test`;
CREATE USER 'test'@'localhost' IDENTIFIED BY 'password';
They work and I can just create a user and a database. Then, when trying to assign privileges to a new user on the new database, I get this:
GRANT ALL PRIVILEGES ON `my_prefix_test`.* TO 'test'@'localhost';
> ERROR 1044 (42000): Access denied for user 'creator'@'localhost' to database 'my_prefix_test'
I also dropped the privileges to be safe.
Does anyone have any understanding of this problem? Is it easier to do this with stored procedures? Thank!
source to share
According to the MySQL documentationGRANT
, wildcards _
and %
must be backslashes to be used as literals in the database name. In your attempt above, you will avoid one _
as \_
, but not the other. If you avoid both underscores, you can get the right grants. However, the trick is that you also have to get away from them later when issuing a statement GRANT
as a user to a creator
user test
:
Log in as root (or fully privileged user):
-- As root:
GRANT CREATE USER, CREATE ON *.* TO 'creator'@'localhost' IDENTIFIED BY 'password';
-- Escape both underscores here
GRANT ALL PRIVILEGES ON `my\_prefix\_%`.* TO 'creator'@'localhost' WITH GRANT OPTION;
Login as creator
:
CREATE DATABASE IF NOT EXISTS `my_prefix_test`;
Query OK, 1 row affected (0.00 sec)
CREATE USER 'test'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
-- Issue the GRANT but escape the underscores.
-- because creator@localhost does not have permission to grant on
-- a pattern like myXprefixXtest, as the unescaped _ wildcards would allow
GRANT ALL PRIVILEGES ON `my\_prefix\_test`.* TO 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Login as test
, grant works:
USE my_prefix_test;
CREATE TABLE t (id INT);
Query OK, 0 rows affected (0.00 sec)
source to share