Results 1 to 11 of 11

Thread: [RESOLVED] Current User inside Trigger Mysql

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Resolved [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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    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

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    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

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Current User inside Trigger Mysql

    Sorry, missed the MySQL in the title.

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    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

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Current User inside Trigger Mysql

    Thanks Zvoni, will try SESSION_USER

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Current User inside Trigger Mysql

    Thank you Zvoni, SESSION_USER does the trick, much appreciated

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Current User inside Trigger Mysql

    Quote Originally Posted by KenBZim2 View Post
    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

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    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
  •  



Click Here to Expand Forum to Full Width