CODEWITHSUNDEEP
mysqlddl
Amir
Amir

Reputation: 2540

Granting all permissions to mysql schemas that start with a specific set of characters

I am trying to grant CRUD permissions on any SCHEMA that starts with a specific set of characters. I'm doing this with MySQL 8.x

Here is the example query I'm attempting to execute :

GRANT SELECT,INSERT,UPDATE,DELETE ON 'tc4_%'.* TO specific_user;

I want any Schema that is prefixed with tc4_ to have CRUD privileges. These schemas are yet to be created, so I can't explicitly assign the privileges explicitly before they are created.

Any ideas how to get this working?

Upvotes: 0

Views: 145

Answers (1)

O. Jones
O. Jones

Reputation: 108839

You must grant privileges on the tables in each schema after you create the schema. And, you cannot use that LIKE-style wildcard.

This SQL will generate a result set containing your GRANT operations applying the wildcard.

SELECT CONCAT(
       'GRANT SELECT,INSERT,UPDATE,DELETE ON ', 
        SCHEMA_NAME, 
        '.* TO ''username''@''hostname'';') cmd
  FROM information_schema.SCHEMATA
 WHERE SCHEMA_NAME LIKE 'te%'
UNION ALL SELECT 'FLUSH PRIVILEGES;' cmd   /* this line is optional */

Upvotes: 1

Related Questions