CODEWITHSUNDEEP
mysql
Andrew
Andrew

Reputation: 701

Grant all privileges on set of databeses with same prefix

I have a set of databeses with names like abc_base_1, abc_base_2, abc_base_3 abc_base_n.

I'd like to grant all privileges on all tables of all this databases to one non root user.

Generally, it seems that abc_ is a common prefix of group.

Questions:

  1. Is mysql has database groups? If yes - how to define them (something special or it just has to have same name prefix (like abc))?

  2. Is it possible to grant privileges on group of databases (or databases with names starts with abc prefix)? If yes - how to grant?

  3. Is this permissions distributes dynamically for all new databases, that has the same name prefix or I should grant permissions all the time, when new databases are created.

Upvotes: 2

Views: 2620

Answers (2)

Andrew
Andrew

Reputation: 701

I've found solution.

Privileges should be granted like this:

GRANT ALL PRIVILEGES ON `abc\_%` .  * TO 'someuser'@'localhost';

This expression provides the following rights:

  • access to all databases started with abc_
  • create new databases, started with abc_
  • access to new databases, started with abc_ but created by other users.

Upvotes: 1

user149341
user149341

Reputation:

Yes. You can grant permissions to a group of MySQL databases identified by a LIKE wildcard:

mysql> GRANT ALL ON `abc\_%`.* TO username@localhost;

Permissions are checked when a user attempts to access the database -- you do not need to repeat this GRANT statement when databases are created.

Upvotes: 5

Related Questions