Hello? Is anyone out there? MySQL User Tracking

In many cases, database accounts accumulate over many years. Especially if no IAM (Identity Access Management) or “Singe-Sign-On” solution is used, the situation arises that accounts “remain lying around” although the real existing user is no longer in the company. Unfortunately, there is no internal system view (table / view) that directly answers this question. So how do you find such inactive accounts in your MySQL database?

Many Roads lead to Rome …

Often you want to verify and clean up your user accounts. Especially before upgrades, migrations, security audits or new application releases, the question is often asked:

Are all these accounts actually still needed?

In practice, there are several solutions. One solution would be to activate the “general-log” parameter ([1]) for a certain period of time. This ensures that all commands, including a connection (connect), are logged. After the defined period of time, all users who cannot be identified via the log file can first be blocked (“locked”) and, if necessary, removed (“dropped”) after a further period of time.

However, the “price” for this is high. Everything (!) is logged. This means that every (!) SQL command is documented. This might slow down the performance of the server and costs a lot of space in the file system.

Another possibility would be to set a password interval or an expiry date for passwords. Users who are active will sooner or later be forced to set themselves a new password. Inactive users will be blocked in this way after the time window has expired, and then removed after a further period if necessary. Here, too, there is a certain price. An application user who does not work interactively with a client may find himself in a situation where the password expires without a new password being set. The account would then be locked and would have to be unlocked again by an admin. In the meantime, the external effect on the clients and/or users of the application would certainly be negative.

Let's trigger it …

DBAs of other database products would certainly argue with a login trigger at this point. A login trigger triggers activities (e.g., SQL code) at the moment when a user logs in. MySQL knows triggers and triggering events, but unfortunately a login is not one of them. However, there is a DB parameter that can do something similar.

init_connect [1]

“init_connect” also executes code for all non-DBA users when a connection is established. However, one can certainly live with this restriction (all non-DBAs). Who works on a database as a DBA should be known in the company in any case.

What is still missing are a few data structures (table) and some SQL, or procedural code, which should be executed by each user when logging in.

DIY … 

In the first step, we take care of the data structures with which we want to record which account is still being used:

drop database if exists my_tools;
create database if not exists my_tools;

CREATE TABLE my_tools.user_track (
    user VARCHAR(255),
    last_login TIMESTAMP,
    logins INT,
    PRIMARY KEY (user)
);
 

To do this, we create a database with a log table (see above). In addition, we need a procedure that fills this table:

DELIMITER //
    
CREATE PROCEDURE my_tools.p_track_user()
SQL SECURITY DEFINER
BEGIN
INSERT INTO my_tools.user_track VALUES (user(), now(), 1)
ON DUPLICATE KEY UPDATE last_login = NOW(), logins = logins + 1;
END;

//
DELIMITER ;
 

The procedure either creates a new record for a user (INSERT part) or increments a count variable for an already known account and sets a new date (ON DUPLICATE KEY part). In addition, we need an authorization concept that allows every user to describe the table and call the procedure. In our case, both were created as user “root” (i.e., the admin of the MySQL server):

create role user_track;
grant insert, update on my_tools.user_track to 'user_track';
grant execute on my_tools.* to 'user_track';
 

We use a role for this purpose, to which the corresponding rights are assigned.

The fact that we now have to assign the role to everyone (including all future users) is a little unpleasant. But that is the only way. A general assignment to each user (e.g., “GRANT TO PUBLIC”; as would be possible with Oracle databases) unfortunately does not work with MySQL. To make life a little easier, we write a quick SQL script:

mysql> SELECT
    ->     CONCAT('grant \'user_track\' to \'',
    ->             user,
    ->             '\'@\'',
    ->             host,
    ->             '\';',
    ->             'set default role \'user_track\' to \'',
    ->             user,
    ->             '\'@\'',
    ->             host,
    ->             '\';')
    -> FROM
    ->     mysql.user
    -> WHERE
    ->     user NOT LIKE 'mysql.%'
    ->         AND account_locked != 'Y'
    ->         AND super_priv != 'Y';
 

This script generates the SQL code to give the individual accounts the necessary rights:

grant 'user_track' to 'anybody'@'%';set default role 'user_track' to 'anybody'@'%';
grant 'user_track' to 'nobody'@'%';set default role 'user_track' to 'nobody'@'%';
 

After we have assigned the rights to the database accounts, we activate the parameter “init_connect”, which then starts the logging for each new (!) connection of a user.

set global init_connect="call my_tools.p_track_user();";

And here are the final results …

In our lab environment, we quickly “produced” a few logins with two or three users. The result is as expected. In our case, the last login is documented with date and time and the entirety of a user's logins. Certainly, more complex scenarios / evaluations / more information are possible here.

mysql> select * from user_track;
+-------------------+---------------------+--------+
| user              | last_login          | logins |
+-------------------+---------------------+--------+
| anybody@localhost | 2023-05-04 11:54:56 |      9 |
| nobody@localhost  | 2023-05-04 11:22:37 |      2 |
| root@192.168.32.2 | 2023-05-04 11:22:12 |      2 |
+-------------------+---------------------+--------+
3 rows in set (0.00 sec)
 

Of course, one must be aware that even this procedure can only be a snapshot and cannot generate 100% certainty. There may still be users who did not log in but might be active nevertheless. For this reason, the accounts in this scenario should again only be locked and not deleted immediately.

Conclusion

The solution works and has already been used by us in this way in various projects! There are certainly other database products that solve this scenario more “elegantly” and possibly more efficiently. In practice, however, this approach has proven itself and is ultimately not overly complex.

Do you have questions about the operation of MySQL? Talk to us.