SQL queries to find users & roles with admin privileges - Drupalgeddon: Drupal SA-CORE-2014-005

Hackers have been targetting the creation of admin users in attacks since the Drupal SA-CORE-2014-005 security release. This can be done in various ways:

  • Rename the existing super admin user (uid 1), and change the password;
  • Insert a new user which has the admin role;
  • Amend existing roles, giving them admin permissions.

Here are some SQL scripts that can be run against your site databases to help you try and identify changes related to uses with admin rights and admin roles.

Find user accounts with admin privileges created after the 15th Oct 

Here is a SQL query that can be run to find all user accounts, that have admin permissions, and were created after the 15th October 2014.

SELECT * FROM users AS u 
INNER JOIN users_roles AS ur ON u.uid = ur.uid 
INNER JOIN role AS r ON ur.rid = r.rid 
INNER JOIN role_permission AS rp ON r.rid = rp.rid 
WHERE rp.permission IN('administer filters', 'administer users', 'administer permissions', 'administer content types', 'administer site configuration', 'administer nodes') 
AND u.created > UNIX_TIMESTAMP(STR_TO_DATE('Oct 15 2014', '%M %d %Y '));

Find user accounts with admin privileges & those which have logged in after 15th Oct

This differs from the previous script in that it also returns all admin users who have logged in via the login form since the 15th October.

SELECT u.uid, u.name, from_unixtime(u.created) AS created, u.mail, from_unixtime(u.login), from_unixtime(u.access) FROM users AS u 
INNER JOIN users_roles AS ur ON u.uid = ur.uid
INNER JOIN role AS r ON ur.rid = r.rid
INNER JOIN role_permission AS rp ON r.rid = rp.rid
WHERE rp.permission IN('administer filters', 'administer users', 'administer permissions', 'administer content types', 'administer site configuration', 'administer nodes') 
AND (u.created > UNIX_TIMESTAMP(STR_TO_DATE('Oct 15 2014', '%M %d %Y ')) OR u.login > UNIX_TIMESTAMP(STR_TO_DATE('Oct 15 2014', '%M %d %Y ')))
GROUP BY u.uid;

Find new roles that didn't exist before

If you have a lot of sites with similar roles, you can quickly check if any additional ones have been added with this SQL script. You will need to alter it to include any additional roles, or remove roles not used.

SELECT * 
FROM role AS r
WHERE r.name NOT IN (
'administrator', 
'anonymous user', 
'authenticated user');
Justin Chevallier

Justin Chevallier

Avid Drupal site builder & user for +10 years.

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.