0

I am new to Drupal and realized that when we clear cache in Drupal 'drush cr'. It executes drop db commands.

This is a very big security issue in our company because granting drop privileges to a mysql user on a production server would be dangerous

What would be an alternate solution?

  In Connection.php line 686:
  SQLSTATE[42000]: Syntax error or access violation: 1142 DROP command denied to user 'xxxxx'@'100.100.100.100' for table 'webprofiler': TRUNCATE {webprofiler}; Array()

  In Statement.php line 59:
  SQLSTATE[42000]: Syntax error or access violation: 1142 DROP command denied to user 'xxxxx'@'100.100.100.100' for table 'webprofiler'

Correction

drush cr is not dropping tables. MySQL Truncate requires the DROP privilege as of MySQL 5.1.16. (Before 5.1.16, it requires the DELETE privilege). http://dev.mysql.com/doc/refman/5.1/en/truncate-table.htm

So this means when drush cr is run you need a user with drop privileges.

  • What do you mean it executes DROP statements? – Kevin Dec 20 '18 at 21:24
  • In Connection.php line 686: SQLSTATE[42000]: Syntax error or access violation: 1142 DROP command denied to user 'xxxxx'@'100.100.100.100' for table 'webprofiler': TRUNCATE {webprofiler}; Array ( ) In Statement.php line 59: SQLSTATE[42000]: Syntax error or access violation: 1142 DROP command denied to user 'xxxxx'@'100.100.100.100' for table 'webprofiler' – Ruben Benjamin Dec 20 '18 at 21:32
  • It’s not doing DROP statements, it’s running TRUNCATE statements to flush tables. TRUNCATE requires the DROP permission. Clearing the cache does not delete/restore tables. – Kevin Dec 20 '18 at 21:59
  • Thanks @kevin and Clive for pointing this out. Sucks that MySQL requires drop privilage for truncate to work. dev.mysql.com/doc/refman/5.1/en/truncate-table.html This is a big security issue for a production web deployment at least in my company. – Ruben Benjamin Dec 20 '18 at 22:29
3

As Kevin correctly points out in the comments, the cache clear is issuing a TRUNCATE command rather than DROP, but TRUNCATE does require the DROP permission.

If you look at the manual installation method in the install guide, you'll see that DROP is one of the permissions that is granted as part of the setup process:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON databasename.* TO 'username'@'localhost' IDENTIFIED BY 'password';

That's followed shortly after by:

Note: Unless the database user/host combination for your Drupal installation has all of the privileges listed above (except possibly CREATE TEMPORARY TABLES, which is currently only used by Drupal core automated tests and some contributed modules), you will not be able to install or run Drupal.

So DROP is simply a required permission for Drupal to work.

  • Plus, most host setups have the database server isolated from external access without ssh, or at all. So this permission really isn’t an issue. – Kevin Dec 20 '18 at 22:28
  • @Kevin you are right. even with isolated access having a db user with drop privileges if drush is in the server any hacker could run 'drush sql:drop' and so that makes it risky. thanks for providing some answers – Ruben Benjamin Dec 20 '18 at 22:34
  • Same thing if (heaven forbid) another remotely exploitable SQL vulnerability is found. It's a necessary evil unfortunately. Thinking about it more, it's not even just TRUNCATE that's required - the field API manages deleted data in tables that it needs to create and drop. So even if the permissions were separated, it's still not going to work. – Clive Dec 20 '18 at 22:38

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.