Watch out for the underscore! Setting privileges using phpMyAdmin and mysql

I use a combination of phpMyAdmin and the mysql command line when administering my sites. I also like to use underscores within my database and table names. Yesterday, I came across a particularly interesting situation setting privileges on a database using both phpMyAdmin and mysql.

Lets make sure we're all on the same page when it comes to using underscores in schema object names. As you will see, the underscore is perfectly valid so this usage comes down to personal preference.

So, what's the issue?

I was setting up a Drupal site to use the Drush sql-sync command and need to add the Lock Tables privilege to a MySQL user. Initally, I created the privileges using phpMyAdmin. I am using version 3.4.10 and MySQL 5.5.24. Notice the underscores in both graphics.

Since I didn't have the Lock Tables privilege listed, I needed to add it. I shelled into my VPS and used the mysql command line. After issuing the GRANT command on "red_d7",

# Using the mysql command line.
mysql> GRANT ... LOCK TABLES ON red_d7.* TO... 

phpMyAdmin listed the following.

Notice a new privilege line was added with Lock Tables and the old one was left alone. The GRANT command does not override the old entry unless you add backticks and escape the underscore with the backslash.

 # Using the mysql command line.
mysql> GRANT ... LOCK TABLES ON `red\_d7`.* TO... 

I didn't determine this until I did a bunch of troubleshooting... In general, this isn't very intuitive. While I realize "_" and "%" are MySQL wildcard characters, I think phpMyAdmin shouldn't expose this level of detail to the end user.

Given the above graphic, there are two privileges for the database "red_d7". If you run a command that requires Lock Tables, you get the infamous "ERROR 1044 (42000) at line 40: Access denied for user ... to database 'red_d7' ". My assumption is MySQL finds the first privilege line and ignores the second; privileges don't appear to aggregate.

The Solution

My workaround was to Revoke (delete) the first line with the escape character leaving the second line with just the underscore.

I chose to Revoke the first line since since the database name "red_d7" works with both phpMyAdmin and the mysql command line. If you use phpMyAdmin to revise the privileges, it doesn't add the escape backslash back to the database name.

Comments, questions, corrections?? Let me know!

Add new comment

Enter your email address. This will not be displayed when your comment is posted.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Refresh Type the characters you see in this picture. Type the characters you see in the picture; if you can't read them, submit the form and a new image will be generated. Not case sensitive.  Switch to audio verification.