MariaDB: Verifying Deleted Users and Avoiding "Old User" Issues

Here's what you can do to troubleshoot:

• Check for Errors: Verify if there were any error messages when you tried to delete the user. This might give clues about why the deletion failed.
• Try Logging In: Even though you think the user is still there, try logging in with the old username and password. If the deletion was successful, you should get an access denied error.
• Verify User Table: If you're comfortable, you can directly check the mysql.user or mysql.global_priv table (depending on your MariaDB version) to see if the user is still listed.

Checking for Old Users in MariaDB

Checking for Login Errors (Doesn't directly check the table):

This code snippet attempts to login using the old username and password. If the deletion was successful, it will result in an access denied error.

-- Replace 'old_username' and 'old_password' with the actual values
SELECT 1 AS result
FROM mysql.user
WHERE User = 'old_username' AND Password = PASSWORD('old_password');

Note: This approach doesn't directly check the user table, but verifies deletion through login attempt.

Checking mysql.user table (For older MariaDB versions):

This code snippet queries the mysql.user table for the old username. If the user still exists, it will return a row.

SELECT *
FROM mysql.user
WHERE User = 'old_username';

Checking mysql.global_priv table (For MariaDB 10.4 and above):

This code snippet queries the mysql.global_priv table (relevant for MariaDB 10.4 and above) for the old username.

SELECT *
FROM mysql.global_priv
WHERE User = 'old_username';

Remember:

• Replace old_username with the actual username you're trying to verify.
• You might need appropriate privileges to access these tables.

• Check for a table containing user login information, like user_login or a custom table you might have created.
• Look for a field like last_login or last_access.
• Users who haven't logged in for a long period (defined by your needs) could be considered "old."

Example (assuming a user_login table with last_login field):

SELECT username
FROM user_login
WHERE last_login < DATE_SUB(CURDATE(), INTERVAL N MONTH);  -- Replace N with desired timeframe in months

User Activity Logs:

• If you have a system tracking user activity (e.g., application logs, database access logs), analyze those logs to identify inactive users.
• Look for entries related to the old user and set a threshold for inactivity to classify them as "old."

User Roles and Permissions:

• Review user roles and permissions. Users with unnecessary or outdated roles might be considered "old."
• Identify roles that are no longer relevant and then check which users are assigned those roles.

User Information Updates:

• Check for tables storing user information like profiles or settings. Look for timestamps related to the last update.
• Users who haven't updated their information in a long time could be considered "old."

SELECT username
FROM user_profile
WHERE updated_at < DATE_SUB(CURDATE(), INTERVAL N MONTH);  -- Replace N with desired timeframe in months