Regarding Drupal site security, I am really surprised to see that MySQL settings do not keep an important place in Securing your site discussion.
Yet a very simple MySQL policy would have prevented Drupageddon to your site.
According to INSTALL.mysql.txt, MySQL user used by Drupal, must have the following minimal privileges on Drupal database:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES.
These settings covers the entire lifespan of the site, and they are necessary to deploy new or updated software.
The problem is that unlocking MySQL door for updates also leaves the door open for potential malware.
The simple advice is to forbid this possibility all the time except when Drupal developer actually updates the site.
The solution is to create two MySQL users per Drupal instance (one with full access to the database, and the other with restricted access) and switch to the first user when we deploy.
MySQL lets the database administrator to add or revoke grants on individual SQL tables. So first you remove access to the entire database to your normal Drupal MySQL user, then add grants to each SQL table using a PHP script which you keep out of web access.
All tables will have these grants:
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, CREATE TEMPORARY TABLES
Except the suggested three tables which will have a limited access:
$tables = array(
'role' => 'SELECT',
'role_permission' => 'SELECT, DELETE',
'system' => 'SELECT, UPDATE, DELETE',
);Thus, any malicious SQL injection would not be able to add any new role or modify permissions, or to add any new module.
You may probably remove UPDATE privilege to 'system' table as well (to be tested).
Also, if your site is not supposed to have a lot of users, you may also restrict access to 'users' table as well.
Any further suggestions would be welcome.
| Attachment | Size |
|---|---|
| protect_db.txt | 3.41 KB |
Comments
This definitely makes it
This definitely makes it harder to exploit a site and would protect a site against some of the automated attacks, but there are other attacks which can't be fixed by this type of technique. And, the attacks I saw were very smart - if Drupal adopted this system then they would adapt the attacks. For example, this wouldn't block the attack of putting a new entry into the menu system with an access callback that contains malicious code (e.g. see this article or this article).
knaddison blog | Morris Animal Foundation
This is definitely a good
This is definitely a good suggestion to restrict access to the following tables as well:
'menu_router', 'block', 'block_custom', 'filter_format', 'filter'.
I'm curious if that works!
I'm curious if that works! Those tables are inserted/updated pretty often, but perhaps procedures can be created so they are inserted/updated in a controlled manner with a different set of credentials (e.g. during deployments).
knaddison blog | Morris Animal Foundation
I had started to make a write
I had started to make a write up on something like this a few years back, but never got around to finishing. Here is the draft (old and probably has mistakes) for reference.
https://gist.github.com/CashWilliams/901f16e32b3d9f77a15f
The final list of SQL tables to protect
That's interesting. We have move the DB credentials out of settings.php file into two small include files, and switch them by changing the symbolic link, either manually or automatically, during deployment. Here is the final list of SQL tables to protect:
// Limit SQL access to the following Drupal tables:$tables = array(
'role' => 'SELECT',
'role_permission' => 'SELECT, DELETE',
'system' => 'SELECT, UPDATE, DELETE',
'menu_router' => 'SELECT, DELETE, INSERT',
'block' => 'SELECT',
'block_custom' => 'SELECT',
'filter_format' => 'SELECT',
'filter' => 'SELECT',
);
The 'block' table needs update, insert and delete
Clearing the caches calls drupal_write_record and db_delete on the block table.