Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Who is modifying my data.

    A table is being updated

    How can I catch the updater.

    Is it possible to setup the update trigger and save info in another table.

    Which function will give APP ID.
    and if possible how can I have SQL TEXT used in the transaction.

    Thanks in advance.

    DBFinder

    DB2 v 8.1.12 Win 2k3

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    First off who has access to do an update?

    you can set up a trigger and using USER, I think, you can have what you need.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks,

    But that might not help ( all apps here use db2admin )

    Anyway via APP_ID to get IP.

    DBFinder

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I don't know really know much about db2audit, but I think it can do what you're looking for:
    DB2 UDB security, Part 5: Understand the DB2 audit facility

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    This utility cannot be used. This brings DB down. I tried to.

    Can we determin APP Id on the fly.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    event monitor for statements may do it

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    problem with monitors is unless you know when changes are taking place you have to run them all the time.

    Set up a trigger and foget about it.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Alright,

    I have setup a trigger, but will I get Application ID so that the workstation and application can be identified.

    Will CURRENT CLIENT_APPLNAME do.

    Let us see when it gets modified again.

    Thanx

    DBFinder

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    DBFinder, Depending on you setup, I would look at all of the Special Registers to see if any of them will get you the information you want. Some that might be useful:

    CURRENT CLIENT_USERID
    CURRENT CLIENT_WRKSTNNAME
    CURRENT SERVER
    CURRENT USER
    SESSION_USER
    SYSTEM_USER

    I would save all (or most all) of these. Some may not have any useful information, but it is better to collect the info and not need it than to not collect it and (maybe) need it.

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Finally Resolved,

    As I created the triggers, I forgot that I am writing for ver 8.2.

    I used client_applname in triggers which raised an exception.
    The exception went through ticketing system and at the end came to me.

    This exception includes everything and then I forwarded this to management.

    An java application was modifying the table which it should not. So the logical mismatch between developing and portal people.

    Thanks for all effort.

    DBFinder

  11. #11
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Do not stop there. Revoke their access to prevent them being able to do so in the future if they are not supposed to. Review your security. Sounds like you might have a security problem.

    Many companies do not give 2 cents to their security.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  12. #12
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Hi,

    I regard your opinion, I would have trained them if I would have been a decision maker.

    However, In our setup our CIO will do that. He knows how much data they have spoiled, and work around this damage.

    I hardly believe that they will take any punitive action, instead a corrective action is more important.

    A developers' team controls the portal and CIO is head of all, and they bring to his notice, any application whenever is deployed, including all java code. This comes to be everybody's (team's) mistake.

    My scope is only to hunt and report to my manager or CIO or the requestor. Still I got an advantage of hunting that I am going to write my code to do this quickly in future.

    Soon I discovered, this was communicated to all teams and higher authorities.

    Security actually is controlled at Application level, no DB level.

    Thanks all, I wouldn't have been possible without your support.

    DBFinder

Posting Permissions

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