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
ormysql.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
orlast_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
mariadb