How to implement context based DB auditing?

Question

I have a current DB driven application which has several methods for accessing data.

1. Web Application
2. Direct SQL Access users (I'm trying to remove these)
3. Client Server application
4. Batch inputs and outputs

I need to implement context based auditing as the current data auditing is not enough for retrospective identification of what processes caused the data changes.

I am currently thinking of hiding the data model behind XAPIs (Transactional APIs) and each action on the data model will have to supply some form of identifying associated action or reason for the data change which will be stored alongside the audited data itself.

Can anyone offer me a better method for achieving context based auditing that will cover all access into the database? Or even point out any obvious flaws in my current approach that I have missed?

Thanks in advance.

Morelock

This seems to be a real issue, I posted the same question in a related LinkedIn group and got no concrete answers. – Morelock

the audit should happen as deeply as possible - within the database itself. i am supposing you do not have unique user identification - i.e. your users log in with the same uid through some connection pool. You may consider each logging in with a unique account. – Counterpane

Randy, I agree, there is no real value in having any audit outside the database, hence the reason I suggest XAPIs. The users do have unique accounts but this does not solve the problem of auditing the context behind any changes. – Morelock

Still no solution out there? I might have to develop and out of the box solution and start selling it! – Morelock

Did you have a look at v$session? Usually it provides information about the context. – Yves

@Rene, thanks for the comment, I am well aware of v$session (I'm a qualified DBA) but it does not solve the inherent issue of change context in relation to a user's actions. i.e. Why the user is changing something. – Morelock

@Ollie. Given the list of apps in your question I thought you meant by context the application that is being used to make a change. If information about why a user wants to make a change is necessary than that is usually a business requirement and part of the application. – Yves

@Rene, It is a business requirement but not specific to any particular app that accesses the database. It has to be captured for all DB access, the safest method of achieving this is storing the context auditing in the DB itself. I was hoping someone might have done this before rather as it is a real headache writing all the XAPI's. Once done though the auditing is going to be VERY thorough :-) – Morelock

@Ollie. What do you mean by "why a user wants to make a change"? Shouldn't you than add a field to the table and a set of values for the user to pick from. I don't see that as part of "auditing". – Yves

Each change to data must have a "context" to the change so that not only the data change is audited but the reason for the change is also stored. If only it were as simple as adding a column to a table. Unfortunately there are many tables and though I could add a column I'd also have to maintain a picklist that was subject to change with the overheads that entails. Though not improssible I was hoping for a more elegant solution. So far building a set of XAPI's and enforcing a context to be added by users/batch processes/automatic feeds etc. seems to be the best method (if not the cleanest) – Morelock

As for whether or not is part of "auditing" or not as you see it, that is the requirement I have to follow and it is part of a future regulation the business will have to implement. (Sadly for me, I have the task of implementing it). – Morelock

I assume you already have triggers that populate the audit tables, and that they want you to add a "context" column to the audit tables. How about you add a procedure "start_transaction(in_context)" that everyone must call before each transaction? That procedure would insert the context into a global temporary table with "ON COMMIT DELETE ROWS"; the audit triggers would raise an exception unless they find the context in the GTT. When the session commits, the context is automatically cleared. Of course, you now have to go to each application and make sure it calls "start_transaction". – Carmelinacarmelita

That's open to answer. I would have thought that as most of the resons behind access to the data fall into known business processes the most common would be available in some sort of list of values with the ability to add new reason if necessary. Fundamentally though as long as there was some explaination it wouldn't matter if it matched a set of known values or not. It would be used as a guide as to the reasons behind the changes for the DBA's to see rather than used for display in an application or for general consumption. – Morelock

Jeffrey. That's a great suggestion, it could be implemented in the application and structured DB access. I suppose I could prevent the users raising exceptions (when forgetting to call "start_transaction") by encouraging them to use XAPI's I provide but if anyone with the relevent privileges issued direct DML on a table etc. they would not succeed unless they had set the context first. – Morelock

Answer 1

This is an older post, but I still want to provide a solution, may be it will be useful for someone.

Oracle provides "context" variables for each session. In an application that uses connection pool to connect to the database, Oracle provides a default namespace called "CLIENTCONTEXT". With in that namespace you can create variables such as USER ID and make sure this variable is set when a connection is handed off to server web requests. This way, inside the database you can identify which "web user" (or app user per say) request is being handled inside the database. e.g. dbms_session.set_context('CLIENTCONTEXT',user_id, ); Hope it helps.

Diagnose

Answer 2

we had a project where we were required to have detailed audit information on what was changed, when and by whom.

in our case, what we did, is improved our MVC solution, to keep audit trail when things were changed. in that situation, we were able to store auxiliary information, such as web user, ip, etc.

additionally we had mysql binary logging enabled, thus we could roll back full history if necessary and given the additional logs stored about accesses to distinguish the source of change.

in your case it would be somewhat trickier, if you don't have any layer between database and actual database accesses. so, I would suggest creating api for operations with data which would work as intermediary layer and would give you all the control you are looking for.

this should give you directions to get started with.

Moureaux

Thanks for the answer but auditing of the actual change (data audit etc.) is not the issue, it is the reson for the change that I need. Going down the XAPI route (as you have suggested) is the route I have already looked into (see the bounty description) so I am hoping for an answer that offers something different. – Morelock

I see. Well, I believe the way you have gone is proper and valid. Good luck. – Moureaux

Thanks, it might well be, but I asked the question in the hope that someone had some radical new method that I hadn't thought of that made it much easier to implement. – Morelock

Answer 3

EDIT added context specific portion of answer to bottom

• Every user has a log-in.
• Tie those log-ins to SQL Server Users.
• Use the SYSTEM_USER (ex: select SYSTEM_USER) for your auditing.

The only place where the above becomes tricky is for the web app.

• I don't know if your web application is internal or not, (if it's internal, using windows authentication with impersonation/delegation would work great)
• If it's external you'll have a system defined account that will verify log ins into the web app (and possibly do other privileged operations), then you can use the user's own credentials for db access during the session.
• • If you don't want to have a bunch of SQL Server Users you can do your own session management and create/drop the users on the fly (like when they log in / log out)

Here's some T-SQL to illustrate

-- AFTER SUCCESSFUL LOGIN
BEGIN
-- You would already have the user name and password
DECLARE @user varchar(32)
SET @user = 'tester'
DECLARE @pw varchar(32)
SET @pw = 'SuperTest123'
-- if the user logs in from 2 different sessions
-- keep the name more unique
SELECT @user = @user + REPLACE(NEWID(), '-', '')
-- build the dynamic sql to create a user
DECLARE @sql varchar(8000)
SELECT @sql = 'CREATE LOGIN [' + @user + '] WITH PASSWORD = ''' + @pw + '''; '
SELECT @sql = @sql + 'USE MyDatabase; CREATE USER [' + @user + '] FOR LOGIN [' + @user + '] WITH DEFAULT_SCHEMA = db_datareader; '
EXEC(@sql)
-- use these credentials for web apps sql connections
SELECT @user [UserName], @pw [Password]
END

-- AFTER LOGOUT / SESSION EXPIRATION
BEGIN
-- You would already have the user+guid used by the sql server
DECLARE @login varchar(32)
SET @login = 'tester3C8DA60B996C4E5881774D1FE4'
-- build the dynamic sql to drop user
DECLARE @sql varchar(8000)
SELECT @sql = 'DROP LOGIN [' + @login + ']; '
SELECT @sql = @sql + 'USE MyDatabase; DROP USER [' + @login + ']; '
EXEC(@sql)
-- user gone until next session
END

Context constraints can be achieved directly in the audit triggers.

• Table: TEMP_AUDITREASON
• • [User] VARCHAR(128) DEFAULT SYSTEM_USER
• • [Reason] VARCHAR(512)
• Trigger

This may be a little glib but...

IF EXIST(SELECT [Reason] FROM [TEMP_AUDITREASON] WHERE [User] = SYSTEM_USER AND [Reason] IS NOT NULL)
BEGIN
 SELECT @REASON = [Reason] FROM [TEMP_AUDITREASON] WHERE [User] = SYSTEM_USER
 -- clear it for the next transaction
 DELETE FROM [TEMP_AUDITREASON] WHERE [User] = SYSTEM_USER
END
ELSE
BEGIN
 -- SOUND THE ALARM!!! no reason was given
END

Leekgreen

LastCoder, forgive my ignorance but I am struggling to see how the contexzt is audited (I'm not great with T-SQL as i am working with Oracle as the tags specify). Are you saying that I should build up a list of context reasons in the TEMP_AUDITREASON table and reference them in the data audit triggers? – Morelock

I was thinking the User/WebApp/Batch would have to do an INSERT INTO TEMP_AUDITREASON before any action on an audited table (one with an audit trigger). The audit trigger would make sure there's a reason in TEMP_AUDITREASON then use that reason to fill in the Reason column of the audit table. – Leekgreen