June 25, 2009 at 4:25 pm
I'm working with data that has sensitive data points (such as SSN) that a user doesn't need 99% of the time but in an emergency they might need to access it.
My thought for how to do this would be:
Create a view which includes only non-sensitive data.
Create a second view which includes the sensitive data but we log when that data has been accessed.
The logging would be concerned simply with the fact that the view was accessed. It would not have to track which specific data points were accessed, although that could be handy functionality if it doesn't make it harder for the user to access it.
I'm wondering if there is a simple way we can do this or if you've got suggestions about alternative ways to achieve the same goal that exist entirely in SQL.
Ultimately, my concern is that someone can select the data in such as way that they can write a WHERE against it. For sake of argument, let's say the users are not skilled in SQL so I'd need to provide a very simple mechanism by which the data is accessed.
June 25, 2009 at 7:58 pm
You could do a server-side trace. Give your view a unique name and setup the trace. Log to a file or to a table & make it one of your daily checks to see if anybody has ben accessing the view. If you want the trace to be persistent across reboots, you would have to encapsulate it in a stored procedure, create it in master & set it to run on startup. Something like this:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_TraceDataAccess]
AS
-- ----------------------------------------------------------------------------
-- Creates trace to audit access to sensitive data view Needs to be in master.
-- Also remember to run sp_procoption 'usp_AuditLogintrace', 'startup', 'on'
--
-- Who When What
-- ------------ ---------- ----------------------------------------------------
--
-- ----------------------------------------------------------------------------
-- Create a Queue
DECLARE @rc int
DECLARE @TraceID int
DECLARE @maxfilesize bigint
DECLARE @filename nvarchar(245)
SET @maxfilesize = 50
-- Filename the trace will write to. Has yyyy-mm-dd-hh-mm-ss appended for uniqueness
-- Change path if necessary
SELECT @filename = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\TraceDataAccess_'
+ REPLACE(REPLACE(CONVERT(nvarchar(30), GETDATE(), 120), ':', '-'), ' ', '-')
EXEC @rc = sp_trace_create @TraceID output, 2, @filename, @maxfilesize, NULL
IF (@rc != 0) GOTO error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%SensitiveDataViewWithUniqueName%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 32c87d4d-10e8-41dc-b502-0477f9160c44'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
exec sp_procoption 'usp_AuditLoginTrace', 'startup', true
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
September 22, 2010 at 9:25 am
view
September 23, 2010 at 8:02 am
If a user doesn't need to view certain data except in an emergency, give tham another ID to use for the emergencies, and log the use of that ID. Grant access to the sensitive data to the occasional use ID's. It's also useful to have an application that lets users check out the ID and then changes the password when the ID is checked in.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply