Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Audit and Prevent Unwanted SQL Server Table Changes


By:   |   Last Updated: 2018-01-26   |   Comments (2)   |   Related Tips: More > Auditing and Compliance

Problem

The process of classifying a company into an industry segment has been around since the 1950ís.  Wikipedia has listed several popular taxonomies that are in current use.  Some industries are more regulated and have stricter compliance regulations than others.  The business requirement to provide an audit trail to a compliance officer when a security issue arises is ďtable stakesĒ in most organizations.

SQL Server has had the ability to define an audit specification at the server or database level since 2008.  However, the actual changed data is not readily available for reversing the unwanted action.  In todayís complex team environments, certain key tables need to be protected against inserts, updates and deletes.  As a database administrator, how can we audit and prevent unwanted table changes? 

Solution

A design pattern using an audit schema, a central audit table and table triggers can be used to log all user actions against a group of tables. For larger databases, the central audit table can be transitioned to one audit table to one data table design.  Spreading the audit data across multiple tables will allow the system to scale.  Because this design pattern uses database objects that are core to every modern version of SQL Server, this design will work with both on premise and in-cloud databases.

Business Problem

The medical industry has to comply with the Health Insurance Portability and Accountability Act of 1996 (HIPAA).  If you are interested, detailed information can be found on the Department of Health and Human Services website

I am going to re-use the simple database schema for a fictitious urgent care center called ďThe Last StopĒ.  Please see my prior article for details on how to setup this test database.  Our boss has asked us to showcase these new design patterns.  

Use existing Azure SQL database

I am going to use SQL Server Management Studio to manage my existing Azure SQL database.  Choose the database engine as the server type and enter the fully qualified name of the Azure SQL Server.  Pick SQL Server authentication as the security option and supply the login/password of the server administrator.  The image below shows a typical connect to server login window.

SSMS - Connect to server - Description: Starting our POC effort.

Right now, there is one custom user defined database.  Use the object explorer to drill into the hippa database and expand the tables list.  There are three tables in the active schema. 

The patient_info table contains patients that visited the clinic and the doctor_info table contains doctors that saw the patients.  The visit_info table has the overall medical readings taken during the visit as well as a medical diagnosis.

SSMS - View table objects - Description: Three tables in the active schema.

If we take a closer look at the user defined objects, we can see that each of the three tables have a surrogate primary key.  The visit_info table is used to join all tables together with foreign key relationships.

Query - user defined database objects - Description: Use the system catalog views to list objects.

Please make sure that your test database is all ready for the rest of the article.  Since the build script can be deployed to either locally using on premise resources or remotely using in cloud resources, it is up to you on where you want to do your testing.

Securing Audit Data

I think user defined database schemas are under-utilized in the real world.  One great feature behind this security object is the ability to group like objects and assign permissions to the group. 

/* 
   1 - Create audit schema 
*/ 
  
-- Delete existing schema 
DROP SCHEMA IF EXISTS [audit] 
GO 
  
-- Add schema for audit purposes 
CREATE SCHEMA [audit] AUTHORIZATION [dbo] 
GO 
  
-- Show database schemas 
SELECT * FROM sys.schemas 
WHERE principal_id < 16384 
GO 

The above code creates a new schema named audit.  We can query the system catalog view named sys.schemas to see our three user defined schemas:  active, stage and audit.  See the screen shot below for details.

Query - existing schemas in database - Description: Three custom schemas for our database.

Containing a database user

The contained database user feature was introduced in SQL Server 2012.  All Azure SQL database users should be defined this way since they will not be dependent upon the master database.   This is especially important when active geo-replication is used for high availability.

/* 
   2 - Create contained user 
*/ 
  
-- Add new user. 
CREATE USER [hippa_user] 
WITH PASSWORD=N'SzfX6ThnLeDPwpelMHYdV2MW', DEFAULT_SCHEMA=[active] 
GO 
  
-- Show the user 
SELECT * FROM sys.database_principals 
WHERE type_desc = 'SQL_USER' 
GO

The T-SQL code above creates a contained database user.  We can query the system catalog view named sys.database_principles to view our new user named hippa_user.

Query - contained database users - Description: The new hippa user for our POC.

The image above shows the default schema is set to active.  This is important since we will only give this user access to that schema via a database role.

Granting access via database roles

Another rarely used feature in database design is a user defined database role.  Roles are great when you are using either standard or mixed security.  They serve the same purpose as an active directory group.  The code below creates a role named hippa_role.

/* 
   3 - Create database role 
*/ 
  
-- Delete existing role. 
DROP ROLE IF EXISTS [hippa_role] 
GO 
  
-- Create database role 
CREATE ROLE [hippa_role] AUTHORIZATION [dbo] 
GO 

The next step in assigning security is to grant permissions to the role.  I am going to give out CRUD (create, read, update and delete) rights to the user.  Of course, we canít forget to add the user to the role.

/* 
   4 Ė Add user 2 role & apply permissions 2 schema 
*/ 
  
-- Apply permissions to schema 
GRANT INSERT ON SCHEMA::[active] TO [hippa_role] 
GRANT UPDATE ON SCHEMA::[active] TO [hippa_role] 
GRANT DELETE ON SCHEMA::[active] TO [hippa_role] 
GRANT SELECT ON SCHEMA::[active] TO [hippa_role] 
GO 
  
-- Add user to role 
EXEC sp_addrolemember N'hippa_role', N'hippa_user' 
GO 

The sys.database_role_members catalog view is a little more difficult to query than most.  It is a paired list of role and user ids.

/* 
   5 Ė Show role membership 
*/ 
  
-- Show role membership 
SELECT 
  DP1.name AS Roles_Name,   
  isnull(DP2.name, 'No members') AS Users_Name   
FROM 
    sys.database_role_members AS RM  
RIGHT OUTER JOIN sys.database_principals AS DP1  
    ON RM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN sys.database_principals AS DP2  
    ON RM.member_principal_id = DP2.principal_id  
WHERE 
    DP1.name = 'hippa_role' 

The query above will return the role name and user name in the current database.  See the screen shot below for details.

Query - user defined roles - Description: The new role associated with our user.

Make a central audit table

It is time now to leverage the audit schema that we created previously.  The name of our audit table is log_table_changes.  Basic information such as change date, change type, change by, schema name and object name will be recorded. 

What makes this table interesting are the default constraints.  We are capturing the user name, application name, and host name via calls to system functions.  Last but not least, the changed record(s) are stored as an XML document.

/* 
   6 - Audit data changes (table for DML trigger) 
*/ 
  
-- Delete existing table 
DROP TABLE IF EXISTS [audit].[log_table_changes] 
GO 
  
-- Add the table 
CREATE TABLE [audit].[log_table_changes] 
( 
  [chg_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL, 
  [chg_date] [datetime] NOT NULL, 
  [chg_type] [varchar](20) NOT NULL, 
  [chg_by] [nvarchar](256) NOT NULL, 
  [app_name] [nvarchar](128) NOT NULL, 
  [host_name] [nvarchar](128) NOT NULL, 
  [schema_name] [sysname] NOT NULL, 
  [object_name] [sysname] NOT NULL, 
  [xml_recset] [xml] NULL, 
  CONSTRAINT [pk_ltc_chg_id] PRIMARY KEY CLUSTERED ([chg_id] ASC) 
); 
GO 
  
-- Add defaults for key information 
ALTER TABLE [audit].[log_table_changes] 
  ADD CONSTRAINT [DF_LTC_CHG_DATE] DEFAULT (getdate()) FOR [chg_date]; 
  
ALTER TABLE [audit].[log_table_changes] 
  ADD CONSTRAINT [DF_LTC_CHG_TYPE] DEFAULT ('') FOR [chg_type]; 
  
ALTER TABLE [audit].[log_table_changes] 
  ADD CONSTRAINT [DF_LTC_CHG_BY] DEFAULT (coalesce(suser_sname(),'?')) FOR [chg_by]; 
  
ALTER TABLE [audit].[log_table_changes] 
  ADD CONSTRAINT [DF_LTC_APP_NAME] DEFAULT (coalesce(app_name(),'?')) FOR [app_name]; 
  
ALTER TABLE [audit].[log_table_changes] 
  ADD CONSTRAINT [DF_LTC_HOST_NAME] DEFAULT (coalesce(host_name(),'?')) FOR [host_name]; 
GO

The screen shot below shows the new table named log_table_changes.

Query - audit table and stored procedure - Description: Two keys components used in the design pattern.

This current database only has three tables.  Therefore, creating a table trigger by hand is not a problem.  What happens when the database has hundreds of tables.  In the next section, we will create a stored procedure named manage_table_triggers which will automate the process.

Managing table triggers

The actual code behind the stored procedure is quite large.  Therefore, I am more concerned about explaining the complete algorithm versus you understanding each line of code.  The manage_table_triggers stored procedure takes three parameters as input. 

The @target_schema_name parameter defaults to the active schema.  However, you can supply any schema name with one exception.  Creating a trigger on the log_table_changes table will cause a never ending cycle when the trigger fires. 

The @command_action parameter has two correct values.  The drop action removes all audit triggers from all tables in a given schema.  The create action clobbers the current audit trigger with a new one.

The @verbose_flag is used to optionally print the transact SQL statements that are being executed by the stored procedure.

Using a cursor to compose and execute dynamic transact SQL inside a maintenance procedure is an excepted practice.  This custom stored procedure assumes the number of tables to create an trigger on is limited.  The resulting DML after triggers use the inserted and deleted tables to capture and store the changed data as a XML document in the audit table.

The enclosed script creates the custom stored procedure and is provided as reference.  However, you do not need to understand the code to start auditing your tables.

The code snippet below creates triggers on the active schema.  If we query the sys.triggers, sys.tables and sys.schemas system catalog views, we can list the newly created triggers.

/* 
   7 - Dynamically make DML after triggers to capture changes 
*/ 
  
-- Add triggers to tables 
EXEC [audit].[manage_table_triggers] @target_schema = 'active', @command_action = 'create', @verbose_flag = 1 
GO 
  
-- Show the triggers 
SELECT 
  s.name, 
  tr.name, 
  tr.type_desc, 
  tr.is_instead_of_trigger 
FROM 
  sys.triggers tr 
  join sys.tables t on tr.parent_id = t.object_id 
  join sys.schemas s ON t.schema_id = s.schema_id 
WHERE 
  s.name = 'active' 
GO 

As expected, there are three triggers for the three tables in the active schema.  See screen shot below for details.

Query - audit triggers - Description: The new table triggers attached to the active tables.

Testing audit security

It is important that the audit logs and/or triggers created in this process canít be tampered with by the average user.  Otherwise, why create our auditing design pattern?

Letís connect to the SQL Server engine using the hippa_user account.  Since there are not login entries for this contained database user in the master database, it is important to specify the default database using the connection properties tab.

SSMS - Connection properties - Description: Using the properties tab to select a default database.

Unlike the jminer account that can see the centralized logging table and custom stored procedure, the hippa_user account does not have any rights to the audit schema.

SSMS - Database objects - Description: Schemas hide the audit objects from the normal user.

The typical user can see the audit trigger on the tables.  However, they do not have rights to view the definition of the trigger.  This action executed thru SSMS was denied below.

SSMS - Unable to view definition - Description: The current security does not allow the average user to view the trigger definition.

The worst case scenario would be the ability for the user to drop the auditing trigger.  Again, the typical user does not have such rights.  See the denial of this action via SSMS below.

SSMS - Drop audit trigger. - Description: The current security does not allow the average user to drop the trigger.

In a nutshell, we now have auditing in place on the three user defined tables in the active schema.  Add new users to the hippa_role to keep everything secure.  The next task in this proof of concept is to test auditing of DML statements.

Testing audit logging

The software development life cycle dictates that we test each object that we create.  Some testing scenarios have both positive and negative inputs. 

For instance, if we bulk insert into an Azure SQL database, does the stored procedure work if the file exists. That is our positive test case.  In contrast, does the bulk insert correctly fail and alert us when the file does not exist?  That is our negative test case.  Letís start testing.

The Campbell family has visited the urgent care center today.  The hypothetical application software returns all patients with the correct last name.

-- Show the Campbell family 
select * from active.patient_info where last_name = 'CAMPBELL' 
go 
Query - viewing patient information - Description: The Campbell family.

Ashely is complaining of influenza like symptoms.  A record of her vitals is logged for the visit.

-- Add a new visit 
insert into active.visit_info values (getdate(), 125, 60, 98.6, 120, 60, 487, 'Influenza', 11, 1); 
go 
Query - Insert new visit. - Description: Ashely Campbell has influenza.

However, the data entry was incorrect since she has a low grade temperature.  An update to the record is made by the nurse.

-- Update the visit 
update active.visit_info 
set diagnosis_desc = upper(diagnosis_desc), patient_temp = 98.4 
where visit_id = 21 
go 
Query - Update recent visit. - Description: Correcting a data entry issue.

The current software keeps the most recent visit in the active schema.  Thus, the older record is deleted from the system.

-- Delete first visit 
delete from active.visit_info where visit_id = 11; 
go 

The above actions might be a test script you write for your system.  Did the actions get correctly recorded to the log table?  Of course the answer is yes.  We have an insert, update and delete action stored as positive test cases.  We can consider the select statement a negative test case since nothing is logged for that action.

See the image below for details.

Query - Audit records - Description: The resulting audit data captured by the triggers.

If you double click on the xml document, SQL Server Management Studio will open the information in a new window.  The screen shot below shows the record that was deleted from the visit_info table.

Query - XML record data - Description: This is the detailed information from the delete statement.

This design pattern is better than SQL Server Auditing.  Not only does it record changes, the information to readily available to reverse the unwanted action.

Reversing Unwanted Actions

As a database administrator, there will be times in which the business line will want to reverse an unwanted action.  This section goes over the code necessary to perform these tasks.

The delete statement can be reversed with an insert statement.  I am not using the chg_id in the where clause since there is only one action.  In real life, you would supply this value.  The code below inserts the captured xml record.

/*  
    8 Ė Reverse delete Action  
*/ 
  
-- Find deleted record 
DECLARE @xml1 XML 
SELECT @xml1 = xml_recset FROM [audit].[log_table_changes] WHERE chg_type = 'DELETE'; 
  
-- Insert lost record 
WITH cte_Captured_Record 
as 
( 
SELECT 
  Tbl.Col.value('visit_id[1]', 'int') as visit_id, 
  Tbl.Col.value('visit_date[1]', 'datetime') as visit_date, 
  Tbl.Col.value('patient_weight[1]', 'real') as patient_weight, 
  Tbl.Col.value('patient_height[1]', 'real') as patient_height, 
  Tbl.Col.value('patient_temp[1]', 'real') as patient_temp, 
  Tbl.Col.value('patient_systolic[1]', 'int') as patient_systolic, 
  Tbl.Col.value('patient_diastolic[1]', 'int') as patient_diastolic, 
  Tbl.Col.value('diagnosis_icd9[1]', 'int') as diagnosis_icd9, 
  Tbl.Col.value('diagnosis_desc[1]', 'varchar(128)') as diagnosis_desc, 
  Tbl.Col.value('patient_id[1]', 'int') as patient_id, 
  Tbl.Col.value('doctor_id[1]', 'int') as doctor_id 
FROM @xml1.nodes('//Record') Tbl(Col) 
) 
INSERT INTO [active].[visit_info] 
SELECT * FROM  cte_Captured_Record; 
GO 

The update statement can be reversed with another update statement.  Again, I am not using the chg_id in the where clause since there is only one action.  The code below updates the current record with the captured xml record.

/*  
    9 - Update Action  
*/ 
  
-- Find updated record 
DECLARE @xml2 XML 
SELECT @xml2 = xml_recset FROM [audit].[log_table_changes] WHERE chg_type = 'UPDATE'; 
  
-- Reverse record change 
WITH cte_Captured_Record 
as 
( 
SELECT 
  Tbl.Col.value('visit_id[1]', 'int') as visit_id, 
  Tbl.Col.value('visit_date[1]', 'datetime') as visit_date, 
  Tbl.Col.value('patient_weight[1]', 'real') as patient_weight, 
  Tbl.Col.value('patient_height[1]', 'real') as patient_height, 
  Tbl.Col.value('patient_temp[1]', 'real') as patient_temp, 
  Tbl.Col.value('patient_systolic[1]', 'int') as patient_systolic, 
  Tbl.Col.value('patient_diastolic[1]', 'int') as patient_diastolic, 
  Tbl.Col.value('diagnosis_icd9[1]', 'int') as diagnosis_icd9, 
  Tbl.Col.value('diagnosis_desc[1]', 'varchar(128)') as diagnosis_desc, 
  Tbl.Col.value('patient_id[1]', 'int') as patient_id, 
  Tbl.Col.value('doctor_id[1]', 'int') as doctor_id 
FROM @xml2.nodes('//Record') Tbl(Col) 
) 
UPDATE cur 
SET cur.patient_temp = prv.patient_temp 
FROM [active].[visit_info] as cur JOIN cte_Captured_Record as prv 
ON cur.visit_id = prv.visit_id 
GO 

The insert statement can be reversed with a delete statement.  Please supply the chg_id when you use this code in production.  The code below deletes the current record.

/*  
    10 - Insert Action  
*/ 
  
-- Find inserted record 
DECLARE @xml3 XML 
SELECT @xml3 = xml_recset FROM [audit].[log_table_changes] WHERE chg_type = 'INSERT'; 
  
-- Remove identified record 
WITH cte_Captured_Record 
as 
( 
    SELECT Tbl.Col.value('visit_id[1]', 'int') as visit_id 
    FROM @xml3.nodes('//Record') Tbl(Col) 
) 
DELETE 
FROM [active].[visit_info] 
WHERE visit_id in (SELECT visit_id FROM cte_Captured_Record) 
GO 

Unfortunately, each of the three statements is tightly bound to the table structure of the audited table.  I leave the task to dynamically create the statement, given the schema and table name, as an exercise for you to try.

Preventing unwanted actions

So far, the article has been centered on auditing unwanted actions.  How can we prevent inserts, updates and/or deletes on the doctor_info table?  Table triggers can be used to ROLLBACK the unwanted actions.

The code below prevents changes from happening to our static table.

/*  
    11 - Preventing unwanted data changes (static data) 
*/ 
  
-- Remove trigger if it exists 
IF OBJECT_ID('[active].[trg_sd_doctor_info]') IS NOT NULL 
  DROP TRIGGER [active].[trg_sd_doctor_info] 
GO 
  
-- Add trigger to prevent data changes 
CREATE TRIGGER [active].[trg_sd_doctor_info] ON [active].[doctor_info] 
FOR INSERT, UPDATE, DELETE AS 
BEGIN 
  
  -- Detect inserts 
  IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted) 
    BEGIN 
        ROLLBACK TRANSACTION; 
        RAISERROR ('inserts are not allowed on table  [active].[doctor_info]!', 15, 1); 
        RETURN; 
    END 
  
  -- Detect deletes 
  IF EXISTS (select * from deleted) AND NOT EXISTS (select * from inserted) 
    BEGIN 
        ROLLBACK TRANSACTION; 
        RAISERROR ('deletes are not allowed on table  [active].[doctor_info]!', 15, 1); 
        RETURN; 
    END 
  
  -- Detect updates 
  IF EXISTS (select * from inserted) AND EXISTS (select * from deleted) 
    BEGIN 
        ROLLBACK TRANSACTION; 
        RAISERROR ('updates are not allowed on table  [active].[doctor_info]!', 15, 1); 
        RETURN; 
    END 
  
END; 
GO 

Testing the preventative trigger

Right now, we have two triggers defined on one table.  I do not want to log audit actions that will be rolled back.  Therefore, I will use the code below to disable the audit trigger.

-- Disable audit trigger 
DISABLE TRIGGER [active].[trg_ltc_doctor_info] ON [active].[doctor_info]; 
GO 
Letís try adding Jack Kevorkianís wife to our list of doctors. 
-- Inserts will fail 
INSERT INTO active.doctor_info ( first_name, last_name, infamous_desc ) 
VALUES ('Jackie', 'Kevorkian', 'Euthanasia'); 
GO

This action was blocked by our user defined trigger.

Trigger - Preventing inserts with after triggers. - Description: Any new inserts into the doctor table fail.

Many people have a nick name of Jack but a real first name of John.  Letís try changing Mr. Kevorkianís first name.

-- Updates will fail 
UPDATE active.doctor_info SET first_name = 'John' WHERE doctor_id = 1; 
GO

Again, this action was blocked by our user defined trigger.

Trigger - Preventing updates with after triggers. - Description: Any new updates to the doctors record fail.

For some reason, many of the older patients under Jack Kevorkianís care have died.  The director of the urgent care center has decided to let him go.  Letís try deleting his record from the system.

-- Deletes will fail 
DELETE FROM active.doctor_info WHERE doctor_id = 1; 
GO 

Okay.  Something unexpected has happened.  There is a foreign key constraint on the visit_info table.  Therefore, we canít remove Mr. Kevorkianís record without producing orphan records.

Trigger - Preventing deletes with table constraints - Description: Any table constrainst is fired first before any table triggers.

I do not care about bad data since I am testing out my new trigger.  We can disable the foreign check constraint with the following command.

-- Disable foreign key constraint 
ALTER TABLE [active].[visit_info] NOCHECK CONSTRAINT [fk_visit_2_doctor]; 
GO 

After trying to remove his record a second time, we get the expected result.

Trigger - Preventing deletes with after triggers. - Description: The trigger prevents any deletes from the doctors table.

To recap this section, table triggers can be used to prevent unwanted user actions.  Any table constraints violated by an action will fire first before our user defined trigger.

Summary

Today, we implemented an audit design pattern using an audit schema, a central audit table and audit table triggers to log all user actions against a group of tables.  This design is an improvement over standard SQL Server auditing since we have captured the actual changed data as an XML document.

Any unwanted insert, update or delete actions can be reversed using the XML record data.  Sample code for each DML statement was created and tested.

Unlike SQL Server auditing, table triggers can be used to prevent actions by rolling back the offending transaction.  Again, the three standard DML statements were tested.  It was surprising to find out that table constraints that are violated are triggered before any custom after triggers.

In short, the new design pattern satisfies stricter business requirements for auditing.  However, this design pattern does not capture the records that an employee is looking at via a SELECT statement.  In a future talk, I will go over custom stored procedures to track what data is viewed or manipulated by a user.

Next Steps
  • Exploring the available audit action groups.
  • Using database triggers to audit or prevent database changes.
  • Using custom stored procedures to track actual data that was viewed and/or manipulated by a user.


Last Updated: 2018-01-26


get scripts

next tip button



About the author
MSSQLTips author John Miner John Miner is a Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, February 06, 2018 - 7:45:04 PM - John Miner Back To Top

Hi Ahmad,

 

Please stay away from text, ntext and image.  They are deprecated data types.

 

Looking at books on line, the following paragaph applies to your user defined table named checks.

 

SQL Server 2017 does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers. The preferred storage for large data is to use the varchar(max), nvarchar(max), and varbinary(max) data types. Both AFTER and INSTEAD OF triggers support these data types in the inserted and deleted tables. 

 

Just change the data type of your base tables and try firing the trigger again.  It should work like a charm.

 

Sincerely

John Miner

The Crafty DBA


Tuesday, February 06, 2018 - 4:34:19 PM - Ahmad Back To Top

 Msg 311, Level 16, State 1, Procedure trg_ltc_checks, Line 1
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.


Learn more about SQL Server tools