Xian Stannard Xian Stannard - 3 months ago 9
MySQL Question

Grant permissions to a set of databases matching a pattern in MysQL 5.0

I'm lead to understand that the following grants all proveleges to all databases that name begin with 'xian_', but mysql complains about a syntax error

near ''xian_
...

GRANT ALL PRIVILEGES ON 'xian_%.*' TO xian@'192.168.1.%';


What is the correct syntax?
Am I right in thinking that the
_
needs escaping to
\_
too as it is also a wildcard?

Answer

Use ` instead of ' in the database name, and escape the _

GRANT ALL PRIVILEGES ON `xian\_%`.* TO xian@'192.168.1.%';
Comments