-
Apr 28th, 2021, 12:20 AM
#1
Thread Starter
Lively Member
[RESOLVED] Current User inside Trigger Mysql
Hi All
I have a trigger on a table that basically acts as an audit
as it records the contents of a row before and after an update has taken place
and inserts these rows into and audit table
this all works according to plan, there is however one problem that i am having...
I would also like to record the identity of the user that has made the change.
I have tried using an insert of CURRENT_USER() and USER()
but both of these return root or root@localhost and NOT the username of the person
who initiated the change.
I can sort of get this as the trigger obviously executes on the server and not on the
client side. Does anyone have any idea of how to do this?
Regards
-
Apr 28th, 2021, 12:27 AM
#2
Re: Current User inside Trigger Mysql
It's not something I've ever used, so I have no examples for you, but this might be of use:
https://docs.microsoft.com/en-us/sql....%20%20More%20
-
Apr 28th, 2021, 12:46 AM
#3
Thread Starter
Lively Member
Re: Current User inside Trigger Mysql
Thanks jm, i am not sure if Mysql has an equivalent to The SQL Server CONTEXT_INFO, but will see if i can find something similar
-
Apr 28th, 2021, 01:00 AM
#4
Re: Current User inside Trigger Mysql
Sorry, missed the MySQL in the title.
-
Apr 28th, 2021, 01:06 AM
#5
Re: Current User inside Trigger Mysql
That's because your "client" probably uses an Application-User (vs. a "real" MySQL-User).
OTOH, if users have to provide login-data on the client-side, there's probably a user-table somewhere in your Database.
How's the update of those rows called? Is it a "direct" SQL-Update-Statement fired from the client side, or a call to a stored procedure?
In both cases you'll probably need a new column in that table (or more columns) recording user, date and time of the update/insert.
Then you can grab the information in your trigger, who changed what
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Apr 28th, 2021, 01:26 AM
#6
Thread Starter
Lively Member
Re: Current User inside Trigger Mysql
Hi Zvoni
Thanks for your reply.
The front end application was written by myself using VB.NET
Yes there is a user table in the database and yes there is a user login.
Each user has an entry in the Mysql user table with a Mysql user name and a Mysql password.
the user login uses this in the connection string as follows:
Server=10.0.0.6;Database=bw;Uid=ken;Pwd=2E63D;Connection Timeout= 40; respect binary flags=false;
Obviously i could insert the audit inside the front end application by performing an insert into the audit
table each time a user makes a change and insert the user's username or database ID when the change takes place.
I was however hoping to do this within the database itself by having a trigger on the transaction table that fires
before and after an update, recording the OLD and NEW values inside the audit table.
There is no stored procedure being used at present, but if a stored procedure will make this possible then
i am more than happy to use this method
-
Apr 28th, 2021, 02:32 AM
#7
Re: Current User inside Trigger Mysql
Ah, OK
Found something:
https://stackoverflow.com/questions/...ncorrect-value
https://dev.mysql.com/doc/refman/5.7...functions.html
According to the Links:
CURRENT_USER() returns the User who defined the Trigger resp. under which account the server itself is running
USER() and/or SESSION_USER() returns the Invoker of the Trigger
Have you tried the following:
In MySQL Workbench/phpMyAdmin:
Login as your "standard"-User (not root/dba)
1) "SELECT CURRENT_USER()"
2) "SELECT USER()"
3) "SELECT SESSION_USER()"
and compare the results
EDIT: I remember when i was playing around with Triggers in MySQL that i had a similiar problem.
IIRC, i created a Test-table with some bogus-columns, an audit-table with 4 columns (Row_ID of test-table, CurrentUser, User, SessionUser), and 2 triggers (AFTER INSERT, AFTER UPDATE), just writing those 4 values into the audit-table.
Then i just fired some INSERT/UPDATE statements against the test-table with different MySQL-Users
It was.... illuminating....
Last edited by Zvoni; Apr 28th, 2021 at 02:43 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Apr 28th, 2021, 02:51 AM
#8
Thread Starter
Lively Member
Re: Current User inside Trigger Mysql
Thanks Zvoni, will try SESSION_USER
-
Apr 28th, 2021, 03:08 AM
#9
Thread Starter
Lively Member
Re: Current User inside Trigger Mysql
Thank you Zvoni, SESSION_USER does the trick, much appreciated
-
Apr 28th, 2021, 04:49 AM
#10
Re: Current User inside Trigger Mysql
Originally Posted by KenBZim2
Thank you Zvoni, SESSION_USER does the trick, much appreciated
Ken, as a last thing you should maybe try:
What about multiple Users at the "same" time?
have you tried the same with 2 (or more) Users logged in at the same time, and firing Updates against that table (at the same time - concurrency not withstanding)?
It's just to make sure, that SESSION_USER() really returns the correct user
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Apr 29th, 2021, 02:00 AM
#11
Thread Starter
Lively Member
Re: [RESOLVED] Current User inside Trigger Mysql
Thanks Zvoni, good point, i will try that
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|