I have a classic 3-tier web application build with MySQL and Tomcat.
I want to save the creator id of each database record for all tables at creator_id column or just log it somewhere. Current user is stored at the http session object. Modify all queries and pass creator id parameter is unacceptable.
Can I solve the problem using triggers, alter table commands etc.? What is the best way to do that?
PS. Hacks are acceptable and welcome.
Thanks.
The database can't possibly know which site user is sending the query, all it knows is which database user. And if it's a web application, it's probably the same database user all the time, no matter who is logged in on the website.
The short answer is that no, you're going to have to go with your "unacceptable" option, unless you want to create a database user for every site user, and have the site open the database connection using those, instead of one "shared" user. But that may end up causing more problems than it solves.
Based on what you say in your question, your logical application user ID is different than your database connection ID. IF that is the case how can the database possibly know what your logical application user ID is? unless you pass it in, there is no way for it to know who is doing what. You say that is is unacceptable to modify all queries to pass this in. However, you would only need to modify the saves where you want to record this "creator_id" value. You will need to modify those tables as well. Hopefully you have a table that contains all of these users and you can FK to the new column to this table.