Set MySQL @variable for hostname user grants

The user grant syntax in mysql is problematic if you want to use a variable to set the hostname when you are creating users in a mysql script. The @ notation means the start of a variable normally, but it’s also used in the mysql grants… so if you try to do this:

SET @hostname='localhost';
GRANT SELECT, SHOW VIEW ON *.* TO 'username'@'@hostname' IDENTIFIED BY 'pass';

You get the error:

Lookup Error - MySQL Database Error: Malformed hostname (illegal symbol: '@')

Or if you take the single quotes out, you just get a syntax error.

The solution is to use a placeholder when you do the create user grants, then later replace all instances of the placeholder with the variable.

/* uncomment to set appropriate environment */
SET @hostname='localhost'; /* development */
/* SET @hostname='testing.hostname.com'; */
/* SET @hostname='production.hostname.com'; */

GRANT SELECT ON db_name.table_name TO 'username'@'env' IDENTIFIED BY 'pass';
GRANT SELECT, INSERT ON db_name.* TO 'username2'@'env' IDENTIFIED BY 'pass';
GRANT SELECT ON *.* TO 'username3'@'env' IDENTIFIED BY 'pass';
/* ... */

UPDATE mysql.user SET host = @hostname WHERE host = 'env';
UPDATE mysql.db SET host = @hostname WHERE host = 'env';
UPDATE mysql.tables_priv SET host = @hostname WHERE host = 'env';
FLUSH PRIVILEGES;

2 thoughts on “Set MySQL @variable for hostname user grants

  1. Greg M

    I’m not much of a SQL guy, even though I’ve used it professionally for almost 20 years. This is exactly what I needed to create a user, regardless of the DB address, etc. Thanks so much!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *