Authentication and Auditing: Incorrect Username in Audit tables

Discussion in 'ASP .Net' started by JimLad, Sep 12, 2006.

  1. JimLad

    JimLad Guest

    Hi,

    I've been tasked with reviewing the Authentication and Auditing of an
    application and database.

    ASP/ASP.NET 1.1 app with SQL Server 2000 database. Separate audit trail
    database on same server.

    The system is intranet based and currently uses Basic Authentication on
    IIS6. The application itself is mostly classic ASP, but has been
    migrated into a .NET 1.1 Framework Project. So there are both .asp and
    ..aspx pages. We have auditing triggers on the tables in the database,
    but the wrong username or no username are currently being inserted.
    Authenticated users have logons to db with full DML permissions

    We now have Active Directory. Most of the data access is done through a
    SQLXML virtual directory using templates,schemas and updategrams. This
    is using a shared login, so no user info is coming through to
    SUSER_SNAME().

    We intend to swap from Basic Authentication to Integrated Windows
    Authentication.

    Basically I want to get the real username available to the triggers in
    SQL Server. I also want to tighten up the security. I am unsure what I
    need in terms of impersonation, application roles or shared database
    role etc. I don't have the resources for a complete redesign, but could
    probably do some significant changes if necessary.

    My initial stab would be use Integrated Windows Authentication on IIS
    and SQLXML Virtual Directory. I think that means that I need to enable
    Impersonation for the aspx pages (the asp pages should impersonate by
    default ??). I should then enable an app role in the templates??? Can
    you use app roles with updategrams???? Also, given that the triggers
    reference a separate audit trail database, wouldn't an app role limit
    access to this?

    The other option is to use a shared db login for everyone and pass the
    actual username in as a parameter, but that would seem to require
    changes to every single query/sp in the database/app.

    I'm not sure that I explained myself very well above. Hope someone can
    help me figure this out! There are perhaps 2 issues here. I need to get
    the authenticated user's name through to the audit table with the
    minimum of fuss. 2 - I'd like to stop every man and his dog having
    write access to all the tables in the database via ODBC.

    Oh, I haven't mentioned app authorisation - that bit seems fine! A
    table in the database.

    Cheers,

    James
     
    JimLad, Sep 12, 2006
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Robey Holderith

    Python Code Auditing Tool

    Robey Holderith, Feb 2, 2005, in forum: Python
    Replies:
    9
    Views:
    448
    Skip Montanaro
    Feb 2, 2005
  2. CptDondo

    Auditing C code

    CptDondo, Sep 19, 2006, in forum: C Programming
    Replies:
    9
    Views:
    475
    Chris Torek
    Sep 24, 2006
  3. Elhanan

    auditing with context?

    Elhanan, Mar 12, 2009, in forum: Java
    Replies:
    4
    Views:
    368
    Arved Sandstrom
    Mar 13, 2009
  4. RM

    Auditing .net generated files

    RM, Oct 6, 2009, in forum: ASP .Net
    Replies:
    0
    Views:
    300
  5. Replies:
    2
    Views:
    172
    Vance Heron
    Mar 26, 2006
Loading...

Share This Page