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

Question

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?

Accepted Answer

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

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

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

Solution

Problem