mysql

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.

Solution

  • 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.

    Note: you need to escape the underscore "_" as shown, so "\_", because the underscore is a special character in MySQL that means "whatever single character", but you want it literally.

    The percentage "%" is used to indicate whatever suffix after the underscore.