MySQL “Lock Tables” Disappears: Privilege Revoked Automatically

The Problem

If you have MySQL users who seem to spontaneously lose their “LOCK TABLES” privilege, you’ve come to the right place. I ran into this problem recently and, after way too much investigation, I tracked down the source of the problem. Read on to learn about the cause and a few easy solutions.

 

The Cause

If you’re having this problem, I’d bet that cPanel is installed on your system (it was the culprit in my case).  I’d also bet that, every day, a cron job runs a script called “cpup,” which is basically the cPanel updater script. It’s about a thousand lines long, but the line we care about (in my installation) is number 822:

cPScript::WHMAPI::status_cmd(‘/scripts/mysqlup’);

This line calls another script of similar length called mysqlup, which is basically the MySQL updater script. Check out my line 197:

system ‘/scripts/securemysql’, ‘-qFa’, ‘removelockntmp’;

Yep, we’re calling yet another script here. This one is called securemysql, and it’s meant to harden up the security of your MySQL installation. It can do things like removing anonymous users and making sure that root has a password.  Check out the code starting at my line 178 of that script:

if ( $actions{‘removelockntmp’} ) {
        print “Dropping global lock tables and create tmp tables permissions … ” if $verbose;
        print MYSQL qq{UPDATE mysql.user SET Lock_tables_priv=’N’,Create_tmp_table_priv=’N’ WHERE User!=’root’;\n};
        print “Done\n” if $verbose;
}

Jackpot!  The securemysql script runs a query that removes the “LOCK TABLES” privilege from every user except root.  In the same swoop, it removes the ability to create temporary tables.  Now that we know where the problem is coming from, it’s easy to fix it.

 

Solutions

I’ll avoid an extended discussion of the security and usability implications of allowing MySQL users to lock tables or disabling scripts designed to enhance security.  However, please consider them when deciding on a course of action here.

 

Option 1: Change the parameters to the securemysql call

 You may have noticed above that upmysql calls securemysql with a few additional parameters (yours may vary):

system ‘/scripts/securemysql’, ‘-qFa’, ‘removelockntmp’;

Simply deleting the action “removelockntmp” from the code will cause the ifstatement shown in the code block in the previous section to evaluate false and prevent the query from running.  My only concern here is that changes made to mysqlup may not be retained if that file itself is ever overwritten by a re-installation or updating of cPanel. 

 

Option 2: Disable the entire securemysql call

Depending on your needs, the best move may be to prevent securemysql from executing.  You can do this without altering any code by simply executing the following at the command line:

touch /etc/securemysqldisable

This is because of the following line at the beginning of the securemysql script:

exit if ( -e ‘/etc/securemysqldisable’ || -e ‘/etc/mysqldisable’ );

By creating the securemysqldisable file, you will cause that ifstatement to evaluate true and the securemysql script will exit before executing the code that disables LOCK TABLES.

 

Option 3: Restore the privileges automatically after they are revoked

There is a script called /scripts/postupcp that gets called at the very end of the upcp script.  If you’re not comfortable altering the mysqlup code or disabling the entire securemysql call, you can add lines to postupcp that restore the “LOCK TABLES” privilege to your users immediately after they are taken away by upcp, negating its effect.  To do this, simply add a line like the one below to the file for each user whose rights you need to restore:

mysql -e “GRANT LOCK TABLES ON *.* to ‘user’@’localhost’ identified by ‘pass’; FLUSH PRIVILEGES;”

The main concern here is that the MySQL user’s password is stored in plaintext in this file.  However, the file postupcp is owned and only readable by root, so you can make a personal call about whether this makes you comfortable.  Also, since postupcp is run by root, you probably won’t need to specify a MySQL username or password in the command (we don’t above).   However, if you do, that adds to the sensitivity of information in the command.

One Comment

  1. Posted February 18, 2010 at 9:40 am | Permalink

    hi

    thanks very much for posting this!

    I’ve been tearing my hair out trying to figure this one out and have had the tech support at vps.net stumped too!

    Think I’ll go with option 3… seems the safest way

    thanks again…


Post a Comment

Required fields are marked *
*
*

%d bloggers like this: