Post reply

Auditing with triggers with applications

  • October 16, 2002 at 3:46 pm

    #78898

    We want to audit modifications to certain tables, but with the complication we have applications that are "trusted subsystems".

    Let me explain that last first... Let's say we have users U1, U2, etc. And we have some table T.

    Each user might access table T with some allowed permissions in SQL (general example is select only access so they cannot change anything).

    But each user might then use an application we have written to update information in table T. These "applications" may be a web page so access flows from the client thru IIS to SQL, or client compiled applications connecting directly SQL. In general we might want these applications to have permissions beyond what a user is permitted, so if the user is running our maintenance app for a file, they can modify a record, but if they use their ID to connect to the database they cannot.

    Generally what we do to enable this is connect to the database from the application via a SQL login that has the permissions, and the users connect via their windows accounts.

    Short version: User U1 can only read data in T directly, but when going through application A1 gains UPDATE privilege, and when going through A2 might get UPDATE and INSERT or some such.

    Here's the issue -- we want to keep track in an auditing table(s) when changes are made. Since the account connecting from each application is generic, we don't want that, we want the account that the user started under. Unless of course they connected directly, then we want that account.

    Here's our draft idea. Don't like it, but it's all we could come up with. Looking for better ideas.

    We put insert, update, delete triggers on all the relevant tables.

    The trigger does the usual to record what information we want to audit about the change, and then does the following:

    a) It looks first for a temp table #Username, and if found it grabs the (only) row/column in there and assumes it is the requisite user name, if not found

    b) It grabs the currently logged in user.

    Now here's the catch -- we ask everyone creating an application to do the following after they establish a connection:

    1) Create the temp table #Username

    2) Find the user name by which the user connected to it (e.g. do a challange/response from IIS, or grab the currently logged in user from a client executable), and stuff it in #username

    I think this can all work (though I haven't written all the code yet). But it's hardly reliable since it depends on everyone writing applications to modify the database tables remember to set this temp username. We could package that up in various macro-like code (depending on the environment) to make it easier, but they still have to remember.

    But I can't think of another possibility. What I really want to do is

    GRANT whatever ON T TO PUBLIC WHEN RUNNING APPLICATION X

    and have a secure way to identify application X. But I don't know of anything removely like that.

    Ideas welcomed?

  • October 16, 2002 at 4:35 pm

    #438730

    It would be so nice to have global variables

    Anyway to get hold of the username 2 options I know of,

    use SETUSER on each connection once connected, allows connection pooling but also allows use of suser_sname(). Have to create lots of users and they have to have the permissions.

    Second option, similiar to yours, a table or tables exist in master that hold connection information. You have an sp in you db (master if you need it more global) that creates a table for the connection i.e conInfoSpidX and inserts a row or insert a row for the spid in ageneric table. If the row already exists do update it.

    So initially you have lots of rows being created but once created you just have an update occuring when someone does something. The update should also be selective so it doesn't update the row if the username is the same as that set.

    I tested this and the performance is very good, space is negligible and can be extended for other information.

    When you want some information about the connected user you just retrieve the data from the table.

    Basin it all on spids is very safe, as you have no clean up to do, the next time some connects the data is set for that user.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply