GRANT with wildcard

Help Procedural SQL
1

Hi guys,

I need to GRANT user access to some databases that contain a certain prefix, but I noticed that the wildcard doesn’t work: GRANT SELECT ON xxx_%.* TO ‘user_xxx’@’%’; FLUSH PRIVILEGES;

New databases are added regularly and it’s a pain in the ass to re-apply GRANTS everytime.

Did I miss something?

Thanks

2

Hi, I’m one of the SingleStore users.

When you writing GRANT command, if you write DB name with `(Single quotation), you can use wildcard(%).

GRANT SELECT ON [DB_NAME]%.* TO ‘[USER_NAME]%’@’%’;

It is different from the character used for user name.
DB Name: (Single quotation)
User Name: ’ '(Apostrophe)

++))
I tested again and found that the command was not performed.
I think it’s because of security issues.

How about creating and performing a command as below?

SELECT CONCAT('GRANT SELECT ON ', A.SCHEMA_NAME ,'.* TO \'', B.USER, '\'@\'',B.HOST, '\';') AS grant_query 
FROM information_schema.SCHEMATA A, information_schema.USERS B 
WHERE A.SCHEMA_NAME like 'xxx_%'
AND B.USER like 'user_xxx%';

I hope my reply is helpful to you.

3

Hi guys,

We are tracking a feature request to be able to use wild cards in the db name of a GRANT. I’ve linked this forums post to the request.

-Adam

4

Oh so sorry but i am not sure about that… Thanks !!

5

We support wildcards in database names in GRANT. It’s documented here:

1 Like