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.

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.

Answer 2

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