We have a centralized authentication library that 100+ applications use to authenticate users. That library has its own DB credentials. It would be extremely useful to log every time that library/user accesses a record in our software_user_permission table. I would want the log to exist not in a file but in a new permanent table.
What would be the best way to achieve this? or am I thinking in the wrong direction?
I want this to happen on the DB side without the library having to insert into the log table itself. Is this possible or commonly done with a trigger? What about a SQL Audit? The last thing I can think of is the library referring to a stored procedure that handles both the select and log statement.