Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2003
    Posts
    4

    Unanswered: getting application username to trigger

    Hello everyone,

    I need to populate some audit tables and include a username of the person who made the change. The problem is that the database requires only a common login. If the application has granted the user a right to perform a function, then he accesses the database through the common login.

    They want to use a trigger to populate the audit tables. How can I get the application username to the trigger so that it is stored in the audit table?

    Thank you for any help.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am fairly certain it would be something like

    CREATE trigger mytrigger after update on mytable
    REFERENCING NEW as newdata FOR EACH ROW MODE DB2SQL INSERT INTO MyAudit (col1, col1, ... , ChangedByUser, ...)
    VALUES (newdata.col1,newdata.col2,..., USER, ...);

    Using the Special Register USER to supply who is logged into the database.

    HTH

    Andy

  3. #3
    Join Date
    Apr 2003
    Posts
    4
    Hi Andy,

    Thanks for your response. Won't that just return the common, generic user since all application users are connected to the database via a common account?

    I need to store the username from the application side and store it in the audit tables. I just don't know how to get that to the trigger.

    Thanks!

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Let me make sure that I got this correctly.
    You are saying that the application connects to the DB using
    a fixed username/password. Then each person logs into
    the app via a login script that uses a "user" table for authentication.
    You want to record who makes changes/additions to certain tables based on the primary key of the "user" table.

    If this assumption is correct (and it is how we do things here BTW),
    Then I would suggest you do the following:

    Add a column to the auditted tables for who made that last change (e.g. UpdatedBy), with a foreign key to the "user" table. When the app issues an insert or update to the DB, it ALWAYS supplies the appropriate data for this column based on who is logged in to the app. This info is then available to the trigger to write the audit trail. If you audit inserts, updates, and deletes, then you need a trigger for each event for each table that is being audited. This will give you two things. 1) the audited table will contain who made the last change (you do not need the audit table to figure this out), 2) the audit table has a complete audit trail of all changes from creation to deletion.

    HTH

    Andy
    Originally posted by wildatheart67
    Hi Andy,

    Thanks for your response. Won't that just return the common, generic user since all application users are connected to the database via a common account?

    I need to store the username from the application side and store it in the audit tables. I just don't know how to get that to the trigger.

    Thanks!

  5. #5
    Join Date
    Apr 2003
    Posts
    4
    So the application will have to actually stamp the userID in the created_by/ updated_by field, and we just leave the other audit info to the trigger (old values, modified_date etc)?

    sorry for the basic questions...

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is the way we do it. It is the only way I can think of to get the info to the trigger so it can perform the audit.

    Andy

    Originally posted by wildatheart67
    So the application will have to actually stamp the userID in the created_by/ updated_by field, and we just leave the other audit info to the trigger (old values, modified_date etc)?

    sorry for the basic questions...

  7. #7
    Join Date
    Apr 2003
    Posts
    4
    Thanks so much for taking time to help. I really appreciate it.

  8. #8
    Join Date
    May 2006
    Posts
    4
    But how can you get the UserName (Application's Username not the common DB USer)of the one who delete the record?

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    To get who deleted a row, you would need to write a stored procedure to do the delete. The IN parameters would be then PK of the row you want to delete and the ID of the user. The SP would then log the delete to a log table of who, what, and when and then delete the row.

    HTH

    Andy

  10. #10
    Join Date
    May 2006
    Posts
    4
    can you pls give me an example.

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    create procedure deleterowfromtablex(IN pk int, IN usercode int)
    begin
    insert into tablex_delete_log values (pk,usercode, current timestamp);
    delete from tablex where tablex_pk = pk;
    end

    Andy

  12. #12
    Join Date
    May 2006
    Posts
    4
    is it efficient than using the context_info?

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What do you mean "context_info"?

    Andy

  14. #14
    Join Date
    May 2006
    Posts
    4

    context_info()

    column in the sysprocesses of master db

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by hmorales_2003
    column in the sysprocesses of master db
    If you have a question about MS SQL Server, you should post in the appropriate forum. This is an DB2 forum.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •