Find the user creation date in MySQL / MariaDB
In MySQL, there is no way to track the user creation date. The table of users does not have an entry for saving the user’s created date.
Check the table mysql.user:
desc mysql.user
Field |Type |Null|Key|Default |Extra|
----------------------+-------------------+----+---+--------+-----+
Host |char(255) |NO | | | |
User |char(128) |NO | | | |
Password |longtext |YES | | | |
Select_priv |varchar(1) |YES | | | |
Insert_priv |varchar(1) |YES | | | |
Update_priv |varchar(1) |YES | | | |
Delete_priv |varchar(1) |YES | | | |
Create_priv |varchar(1) |YES | | | |
Drop_priv |varchar(1) |YES | | | |
Reload_priv |varchar(1) |YES | | | |
Shutdown_priv |varchar(1) |YES | | | |
Process_priv |varchar(1) |YES | | | |
File_priv |varchar(1) |YES | | | |
Grant_priv |varchar(1) |YES | | | |
References_priv |varchar(1) |YES | | | |
Index_priv |varchar(1) |YES | | | |
Alter_priv |varchar(1) |YES | | | |
Show_db_priv |varchar(1) |YES | | | |
Super_priv |varchar(1) |YES | | | |
Create_tmp_table_priv |varchar(1) |YES | | | |
Lock_tables_priv |varchar(1) |YES | | | |
Execute_priv |varchar(1) |YES | | | |
Repl_slave_priv |varchar(1) |YES | | | |
Repl_client_priv |varchar(1) |YES | | | |
Create_view_priv |varchar(1) |YES | | | |
Show_view_priv |varchar(1) |YES | | | |
Create_routine_priv |varchar(1) |YES | | | |
Alter_routine_priv |varchar(1) |YES | | | |
Create_user_priv |varchar(1) |YES | | | |
Event_priv |varchar(1) |YES | | | |
Trigger_priv |varchar(1) |YES | | | |
Create_tablespace_priv|varchar(1) |YES | | | |
Delete_history_priv |varchar(1) |YES | | | |
ssl_type |varchar(9) |YES | | | |
ssl_cipher |longtext |NO | | | |
x509_issuer |longtext |NO | | | |
x509_subject |longtext |NO | | | |
max_questions |bigint(20) unsigned|NO | |0 | |
max_updates |bigint(20) unsigned|NO | |0 | |
max_connections |bigint(20) unsigned|NO | |0 | |
max_user_connections |bigint(21) |NO | |0 | |
plugin |longtext |NO | | | |
authentication_string |longtext |NO | | | |
password_expired |varchar(1) |NO | | | |
is_role |varchar(1) |YES | | | |
default_role |longtext |NO | | | |
max_statement_time |decimal(12,6) |NO | |0.000000| |
To store the created date in the table, you must alter the table with the root user or create a seperate table to hold entry of it. In following i am using seperate table for holding user creation time.
If you want to save the creation time of users in MariaDB / MySQL, you can create a custom table to store this information and use triggers to automatically update this table whenever a new user is created. Here’s a basic example of how you can achieve this:
- Create a custom table to store user creation times:
CREATE TABLE user_creation_times (
username VARCHAR(100),
creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
2. Create a trigger to insert the creation time into the custom table whenever a new user is created:
DELIMITER //
CREATE TRIGGER after_user_creation
AFTER INSERT ON mysql.user
FOR EACH ROW
BEGIN
INSERT INTO user_creation_times (username) VALUES (NEW.User);
END//
DELIMITER ;
With this trigger in place, every time a new user is inserted into the mysql.user table, the trigger will automatically insert the username and the current timestamp into the user_creation_times table.
Keep in mind that you may need appropriate permissions to create triggers and modify system tables. Also, modifying system tables like mysql.user directly can have security implications, so make sure you understand the implications of doing so in your environment.
Smart way of Technology 