Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    4

    Question Unanswered: Report on users accessing a database

    Hi,

    I need a way to automatically generate a daily report of user names, time of access, and type of access (login, select, insert, update, delete, etc.) to a database.

    We need to audit all accesses from all accounts to find out who is accessing the payroll system. We have already defined the security to be as tight as possible - however we need to monitor all Administrator accounts to ensure they are not being used to obtain sensitive information.

    I have looked for some software to read the live SQL Transaction Logs, but none of the tools I have seen can do exactly what we require. For example, I've tried Lumigent Log Explorer (http://www.lumigent.com/products/le_sql.html), but it's not automated and the report output is just garble to our payroll staff.

    I have looked up all Microsoft SQL Server partners but their software is all about data recovery and data reporting. We need Security Reporting.

    I have created a general SQL Profiler trace that may suffice if everything else fails, but I still have no way of automating it - it relies on Real-Time monitoring which I can't trust (admin can turn this off, do their stuff, turn it back on). Also this is dependant on someone turning it off, save the log, turn it back on, every day - this person would be the Administrator - what admin would monitor themselves?

    [edit]
    - SQL Trace seems like a good idea, how do I read the trace log file? How do I automate a trace?
    [/edit]

    - Can we automate the SQL Profiler to read the transaction log to find all accesses to a database?

    - Does anyone know what we can do to automatically audit accesses to a database? What format can the report come in (pdf, xls, dbf, html etc.)

    - Recommended software to try?

    - Ideas for alternatives to try?

    - Any companies/developers out there who know of a tool or are developing a tool to provide this sort of report?

    - Is there a spec somewhere to let us write our own software to query the live transaction log?


    Thanks, I appreciate any help.
    Kind Regards
    James Payne

    IT Administrator
    Last edited by badseeds; 08-05-04 at 22:26. Reason: Found more information

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Assuming it is worth the cost, you can investigate the C2 security options built into SQL Server 2000. They are hideously expensive in terms of disk and system load if fully implemented, but they meet the U.S. Government requirements for C2 clearance (the best possible using standard commercial hardware).

    -PatP

  3. #3
    Join Date
    Aug 2004
    Posts
    4
    Pat, thanks for the recommendation.

    I had previously decided that C2 certification is probably not necessary in our circumstance. Plus, the SQL server does a few million transactions overnight just in updating some tables, and these tables are also accessed by Analysis Services OLAP Cubes when the cubes are updated, and when user's do a Drillthrough on the cubes (they go to the live table on the SQL Server).

    We just have too many different applications that access the SQL server, and it would make the log file very large and very messy. Plus we need the performance up fairly high (the server is a single Xeon 2.4Ghz with 1 Gig of RAM).

    I assessed a System Event Log Viewer (PsLogList - www.sysinternals.com) that can dump the event logs (system, application, security etc.) to wherever we want... this would probably be sufficient - if I can just get SQL Server to write to the event log those issues i'm interested in.

    I tried creating a Trace and running it as a job to start running in the background, but I can't see the table updating with new records. I also tried saving to a file. It appears I need to have SQL Profiler open to run the trace.

    Any further ideas?

    Thanks
    Regards
    James

  4. #4
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    what about using triggers? I use trigger on a table recording user logins to insert records to a worktable from which I later generate reports on user access, DML etc. Surely, performance factor must be considered first.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I remember discussing something like this idea at a previous Tech Ed, but I never really pursued the issue. The gist of the idea we were pursuing was to create a stored trace, and launch that trace via a startup procedure.

    I've still got my notes from that discussion (probably on a napkin), but I don't have ready access to them at the moment. Unfortunately I'm going to be up to my eyebrows in alligators until after sometime Labor Day, so finding them isn't likely in the near future either. I still remember the discussion pretty clearly though, so if you've got questions I can probably either remember or reconstruct them fairly easily.

    -PatP

  6. #6
    Join Date
    May 2002
    Posts
    73

    Trigger code

    Hi,

    It is possible for someone to include here the code for the trigger that was recommended. Seems to be a good idea

  7. #7
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    example of simple triigger that stores one row in login_info table every time someone logins to our accounting system (in other words update fields SY13027,SY13009 in table sy130100 to particular values)

    create trigger login_trigger
    on sy130100
    for update
    as

    begin

    declare @delmodule int, @inslogin int, @insmodule int

    select @delmodule=SY13027 from deleted --logical before image
    select @inslogin=SY13009, @insmodule=SY13027 from inserted --logical after image


    if @delmodule=-1 and @inslogin=1 and @insmodule=-1
    begin
    insert into login_info (login,fullname,company)
    select SY13001, SY13002, 'CR' from inserted
    end
    end

  8. #8
    Join Date
    Aug 2004
    Posts
    4
    Pat, I was also hunting a way to use a stored trace, I set up what I thought was enough to get one working but obviously my understanding is somewhat corrupted. I'll look into it some more before I make you wrack your brain for some answers, thanks for the offer. At least now I know it's a possibility.


    mojza, the trigger sounds like an easy way to keep track of user logins, thanks for the post. Performance probably won't be affected by this since the trigger will be put on a table that should not be modified often - basically a couple times near end of month, and start of month. If I implement it I will let you know how the performance degraded.


    Anyone know some great SQL Server resource sites that may aid in creating a stored trace?


    Regards
    James

Posting Permissions

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