Insight on Joomla! user management, down to the database level

Joomla! 1.5 uses two tightly integrated mechanisms for authenticating users. For starters, we have the data stored in the jos_users table. It's the username, password and the group where the user belongs. There are some important pieces of information about this table. To begin with, all user ID's begin counting from 62. User IDs 1-61 are free and can't be assigned using the back-end. Then, the password is stored in the format md5:salt. Salt is a random string. The md5 part is the MD5 hash of the password and the salt. So, for a salt string "abcd" and a password "foobar", we have to calculate the MD5 hash of "foobarabcd". This calculation is easy to perform with an on-line MD5 calculation and results to 9aa618c6255a7259d747113d7e649925. So, the password field would be "9aa618c6255a7259d747113d7e649925:abcd". This means that we can create a username/password combination of our liking by assigning it a user ID less than 62.

Of course, this is not enough to log in to Joomla!. The other mechanism it uses is a cut-down version of phpGACL. In short, this mechanism allows the use of Access Control Lists which treats users as "Access Request Objects" (AROs) and what they can do as "Access Control Objects" (ACOs). This might be a bit over your head, but the overall concept is that if the user isn't marked as having access to the group he's supposed to be part of, he won't be able to login to the site. For all you need to know, each user is mapped to a single ARO and each ARO is mapped to one or more groups. If the user group assigned in the jos_users table is not in accordance to the group prescribed in the ARO-group mapping, you can't log in.

You might have guessed it, but this mechanism is not fool proof down to the database level. First off, the table which maps users to AROs (jos_core_acl_aro) has its IDs start from 10. This allows us to insert rows with IDs 1-9 without the fear of accidentally disabling another user's login ability. The other database table involved (jos_core_acl_groups_aro_map), which maps AROs to groups, is a simple glue array of a regular many-to-many relationship - it holds IDs of both ARO and group - is easy to append to.

Putting the insight to good use

Having this insight is good, but putting it to real use is even better. Let's say that we want to create a new Super Administrator user called "hacker", with a password of "hacker", using nothing but database manipulation, i.e. without using the Joomla! back-end interface. If you read the previous three paragraphs you might have figured out how: we just need to run three SQL commands. We'll first focus on what these commands are, then move on to a practical way to deliver them.

The first SQL command will create the Joomla! user and looks like this:

REPLACE INTO `jos_users` (`id`, `name`, `username`, `email`, `password`, `usertype`, `block`, `sendEmail`, `gid`, `registerDate`, `lastvisitDate`, `activation`, `params`) VALUES ('60', 'T. Hacker', 'hacker', '
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 ', '18b42a5df78808abca92bc021a191991:abcdefghijklmnopqrstuvwxyz012345', 'Super Administrator', '0', '0', '25', '2000-01-01 00:00:00', '0000-00-00 00:00:00', '', '');

This creates a user with ID 60 and his name set to "T. Hacker". Now, we'll craft our next SQL command which creates an ARO object out of the Joomla! user:

REPLACE INTO `jos_core_acl_aro` (`id`, `section_value`, `value`, `order_value`, `name`, `hidden`) VALUES ('8', 'users', '60', '0', 'T. Hacker', '0');

This created an ARO object with ID 8. Do note that the 'name' field must match the 'name' field of the jos_users entry! Next up, we'll map this ARO object to the Super Administrator group, which goes by the group ID 25:

REPLACE INTO `jos_core_acl_groups_aro_map` (`group_id`, `section_value`, `aro_id`) VALUES ('25', '', '8');

There you go! The user is active and ready for login. If you're wondering why I use REPLACE instead of INSERT, well, it's simply because I wouldn't like the query to fail if the database record already exists.

Delivering the SQL code

This is all sweet and well, but we do have to use a database utility like phpMyAdmin to run this SQL code. Most people would find this a daunting task by any measure. Not to mention that for each and every site we are building we have to undergo the same procedure. This is far for optimal. It would be lots easier if we could just "install" this workaround on a Joomla! site just like we can install components or modules. In fact, we can!

As you might already know, the Joomla! components' installation process is able to run arbitrary SQL commands, as a means to allow component writers to create tables and pre-populate them with data. But, wait! Isn't running arbitrary SQL what we want to do? Yes. So, we'll create two files - one file for the SQL commands and one serving as the installation blueprint of our fake component - pack them in a ZIP file and deploy them. We'll name the first file install.mysql.sql and put the previous SQL commands inside it:

1
2
3

REPLACE INTO `#__users` (`id`, `name`, `username`, `email`, `password`, `usertype`, `block`, `sendEmail`, `gid`, `registerDate`, `lastvisitDate`, `activation`, `params`) VALUES ('60', 'T. Hacker', 'hacker', '
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 ', '18b42a5df78808abca92bc021a191991:abcdefghijklmnopqrstuvwxyz012345', 'Super Administrator', '0', '0', '25', '2000-01-01 00:00:00', '0000-00-00 00:00:00', '', '');
REPLACE INTO `#__core_acl_aro` (`id`, `section_value`, `value`, `order_value`, `name`, `hidden`) VALUES ('8', 'users', '60', '0', 'T. Hacker', '0');
REPLACE INTO `#__core_acl_groups_aro_map` (`group_id`, `section_value`, `aro_id`) VALUES ('25', '', '8');

Do note that I replaced the jos_ database prefix with the generic placeholder #__. Joomla! will pick it up and replace it with the correct database prefix on-the-fly, ensuring our "hack" works on all Joomla! installations. Next up, a file named hack.xml with the following contents:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE install SYSTEM "http://dev.joomla.org/xml/1.5/component-install.dtd">
<install type="component" version="1.5.0" method="upgrade">
    <name>Hack</name><author>Nicholas K. Dionysopoulos</author>
    <creationDate>April 15th, 2010</creationDate>
    <copyright>(C) 2010 Nicholas K. Dionysopoulos</copyright>
    <license>GNU GPL 3</license><version>1.0</version>
    <description>Creates a hacker Super Administrator account</description>
    <install>
        <sql>
            <file driver="mysql" charset="utf8">install.mysql.sql</file>
            <file driver="mysqli" charset="utf8">install.mysql.sql</file>
        </sql>
    </install>
    <administration>
        <files>
            <filename>install.mysql.sql</filename>
        </files>
    </administration>
</install>

Without much ado, we simply put both files inside a ZIP file, named hack.zip. Next up, login to your site's back-end as an Administrator or Super Administrator (the Manager group can't install extensions). Go to Extensions, Install/Uninstall menu item, use Browse to locate this ZIP file and hit "Upload and Install". Ignore any error messages, log out from the site's back-end and then log-in again using "hacker" as both the username and password. Look, mom, I'm a Super Administrator!

Disabling the old Super Administrator account

This is the easiest part of the process. As you are logged in as a Super Administrator to your own site, you can go to Joomla!'s Users Manager page. First, demote him to the Registered level and click on Save. Then, edit again, set "Block" to "Yes" and save again. Your old Super Administrator user has lost his privileges and is now locked so that nobody can use it to log in to your site.

Apparently, you can write two UPDATE statements and put them in our fake component to achieve the same goal. I'll leave that as an exercise to the reader

Many people would argue that we would be better off if we simply changed the user's ID instead of creating a brand new account, as demonstrated in this Bodvoc's blog post. This approach works well if you have a brand new site. But if your site already has content, doing so would leave all content items previously owned by user #62 orphans. Fixing all such records in all installed components is a might task. Ergo, it's much easier creating a new user with an unthought of user ID and disabling the old user, keeping content association and increasing your site's security in a single go.