How to know which user update/insert or delete in MYSQL database?

Question

Using the triggers I could make the establishment of a historic recordings in MYSQL tables where i detect all changes in my database. My problem is that I want to know which user made ​​the change in my database?

Answer 1

Depends on what version you are.

You can install the audit_plugin:

INSTALL PLUGIN server_audit SONAME 'server_audit';

You can choose two outputs, file or syslog. The output format will be:

20140901 15:19:44,localhost.localdomain,root,localhost,4,133,WRITE,employees,salaries,
20140901 15:19:44,localhost.localdomain,root,localhost,4,133,QUERY,employees,'DELETE FROM salaries LIMIT 100',0

WARNING NOTICE

If you enable the audit_plugin (it's right with all logging processes like SLOW LOW, GENERAL LOG...) be careful to not saturate your disks in term of I/O. Depends of your workload, prefer write these types of logs on dedicated disks to minimize the impact on your MySQL instances. You can also throw them to syslog but you should have the architecture to handle them (ELK for instance).

Max.

Answer 2

There are several options, but here are two based on two options for how your end-users connect.

First, if your users connect as themselves. For example, if you have a user "annamaria" then in your trigger you can identify the user with the CURRENT_USER() function. Record CURRENT_USER() into your auditing table with each modification and you can track which user did what.

Second, if all users employ the same database connection (very common). In this case you want to get session information about your end user. In this case you would need to query various connection information from mysql.

If you are using MySQL 5.1.7 or later, and if you have your users connecting directly from their PCs to the database, and their PCs have meaningful names like AWOLFE_LAPTOP, then you can put that value into your audit table:

SELECT host FROM information_schema.processlist WHERE id = connection_id()

Apart from the logged-in user name (which your comment makes clear is unavailable) and the client machine ("host") name, I don't know of any reliable way to identify the user without changing your application.

You would need to revise the code that connects the user to the database, and set a session variable correspondingly, right after login. You would have to prompt the user for a user name, or perhaps a username and password. Then you'd use that to fill a session variable you could later use in your trigger.

SELECT @identified_user := usertab.userid from usertab where username = @username_from_input and password = @password_from_input

Security-conscious people please accept the following better-protected solution:

SELECT @identified_user = hashedcredentials(@username_from_input, @password_from_input);

Your primary problem arises from having all users connect as the same database user. ("One Big Database User" model.) Please, just change this!

Good luck!

Andrew Wolfe