1

I am newbie to MySQL and would like to know if there is a way to identify with which DB creds a change was made to a table/row.

We have given access to multiple users like the DBA, Support Team, Live Support and App development team. We are unable to find which user group has modified a certain table or a row, who deleted or insterted data. We do have logs as to which user has modified but that is only at the application level. Meaning, if our app user makes an entry using the application, we have a log to maintain that but we do not have a way to identify if DBA ran a bulk query that affected certain rows.

I have explored triggers but unable to find anything substantial.

I did ask around a couple of backend devs but I didn't get a satisfactory answer. I explored on the web with search terms like - mysql log user details who inserted data in a table, mysql: how to insert user details who run bulk query on MySQL.

1
  • 1
    Unless you have query logging enabled, I don't think there's a way.
    – Barmar
    Commented Aug 31, 2023 at 17:35

1 Answer 1

1

... a way to identify with which DB creds a change was made to a table/row.

The "normal" method of doing this is to implement Triggers that write, say, "change_user" and "change_timestamp" fields into each and every record as it is inserted or updated or, since you're concerned about deletes as well, writes copies of the data into completely separate tables to which nobody has update or delete access (remember to implement house-keeping to these tables to prevent them completely filling your database). Have a look in the Manual.

However, I suspect you have a more fundamental problem here.

You appear to be in the unfortunate position of trying to close your stable door [long] after your horses have stampeded away.
In other words, far too many people have far too much access to your databases.

You don't say so, but I'm going to assume you mean in a Production database here. Those are the ones that people worry about (although the same principles can apply everywhere).

We have given access to multiple users like the DBA, Support Team, Live Support and App development team.

Users should only have the access that they need to do their jobs and nothing more. The only people that should be capable of making database changes in Production are your DBAs. Not that they should be doing so on a regular basis.
Everybody else should get read access and nothing more.

There is no reason for members of an Application development team to be able to change Data in Production. Yes, they need to deploy changes to tables and code and such like, but that should be through an automated system wherein everything is scripted and tested and deployed by someone other than the Developer (Separation of Responsibilities and all that). Nobody should be making changes by hand.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.