CODEWITHSUNDEEP
mysql
Ahorner
Ahorner

Reputation: 214

MySQL granting privileges on wildcard database name to new user

I am trying to keep my MySQL user accounts as restricted as possible with privileges, only granting the bare minimum for what I need.

I have a user called creator which I want to use to create new users, create new databases, and assign those users privileges to those databases, but only to a database of a certain format.

To create the creator user, 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 assume that this would give me the ability to create a new user, and assign it any privileges to a table matching my_prefix_%, but that does not seem to be the case. Running the following queries as the creator user gives me an error.

CREATE DATABASE IF NOT EXISTS `my_prefix_test`;
CREATE USER 'test'@'localhost' IDENTIFIED BY 'password';

These work and I can create the user and the database just fine. Then trying to assign the privileges to the new user on the new database I get the following:

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 flushed privileges to be safe.

Does anyone have any insight into the issue? Would this be more easily accomplished with stored procedures? Thanks!

Upvotes: 3

Views: 4784

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270617

According to the MySQL GRANT documentation, wildcard characters _ and % need to be backslash-escaped to be used as literals in the database name. In your attempt at the top, you have escaped one _ as \_ but not the other. If you do escape both underscores, you can achieve correct grants. The trick though, is that you must also escape them later when issuing the GRANT statement as the creator user to the test user:

Login as root (or a 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, the grant works:

USE my_prefix_test;
CREATE TABLE t (id INT);
Query OK, 0 rows affected (0.00 sec)

Upvotes: 5

Related Questions