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:

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.

Tags: ,
  • Ryan

    Thanks! This has been bugging me for weeks.

  • http://bevelsmith.com Kristin

    Thanks for the informative post, but I believe this information (at least on my current version of CPanel) is outdated. mysqlup now reads as follows:

    #!/bin/sh
    echo “scripts/mysqlup is deprecated. mysql is now updated by the rpm.versions system when you run upcp.”
    exit 1

    When I look through /scripts/upcp I don’t see anything that refers to mysql, removelockntmp, or securemysql .

    When I make the following change to securemysql:

    print MYSQL qq{UPDATE mysql.user SET Lock_tables_priv=’N’,Create_tmp_table_priv=’N’ WHERE User!=’root’ and User!=’MYUSER’;n};

    i find that a couple of days later the script has been overwritten and my changes are gone, so MYUSER loses its privileges.

    I do see line of the 461 of the upcp script calls the following:

    $logger->update_pbar(95);
    if ( -x ‘/usr/local/cpanel/scripts/postupcp’ ) {
    $logger->info(“Running /usr/local/cpanel/scripts/postupcp”);
    system ‘/usr/local/cpanel/scripts/postupcp’;
    }

    if ( -e ‘/var/cpanel/hooks.yaml’ && -x ‘/usr/local/cpanel/scripts/hook’ ) {
    $logger->info(“Running Standardized hooks”);
    system ‘/usr/local/cpanel/scripts/hook’, ‘–category=System’, ‘–event=upcp’, ‘–stage=post’;
    }

    I don’t seem to have the postupcp script per the first if, but do have the hook file called in the second if. Would it be appropriate to add the

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

    as suggested in the last option to this hook file? This is way beyond my comfort level…

    • Phil

      Kristin, did you ever get a reply or were you able to “fix” this problem? If so, how?

  • http://the-grilling-greek.blogspot.com Evan Panagiotopoulos

    Nice! This was such a mystery. I couldn’t figure out who was changing the lock_tables setting. Of course, all of the cron jobs were failing.
    Thanks for taking the time to write this.

    Evan

  • http://the-grilling-greek.blogspot.com Evan Panagiotopoulos

    Also,
    # Don’t run if MySQL is not used
    exit if ( -e ‘/etc/securemysqldisable’ || -e ‘/etc/mysqldisable’ );

    If you touch /etc/securemysqldisable it should work.

    Evan