[Prior Post in Series]  [Next Post in Series]

In my prior posts on applying PCIDSS standards to protect your company’s data I showed now a DDL trigger (FOR LOGON) may be used to enhance logon security. In this post I will look at using the same DDL trigger (FOR LOGON) with some additional tables to implement temporal constraints on logon. The word temporal means time based.

In this post I will look at these facets of Payment Card Industry (PCI) Data Security Standard Requirements and Security Assessment Procedures Version 2.0 (PCIDSS)

  • The ability to enable accounts used by vendors for remote access only during the time period needed
  • The ability to grant logins a definite access period – typically for ISV consultants or other consultants.
  • The ability to restrict logins to working hours.

The Tables

We will add the following tables to our [Security] database we used in prior posts

  • [BreakGlass] – this allows all of the temporal constraints to be suspended if an urgent situation arises.
  • [UserRota] – this table contains information on regular users
  • [Vendors] – this table contains information on vendors
  • [LoginSecurity] – this table was used in our earlier post in this series

BreakGlass

This table has two columns and no more than one row. The two columns allow the constraints to be suspended for just employees, just contractors or both.

CREATE TABLE [dbo].[BreakGlass]( [Vendors] [bit] NOT NULL Default(0), [Employees] [bit] NOT NULL Default(0) )

UserRota

The UserRota table can get very complex depending on the staff schedules. A good handling of this complexity is to store the information as XML. I will give a simple illustration of this.

CREATE TABLE [dbo].[UserRota]( [LoginName] [nvarchar](128) NOT NULL, [RotaXml] [xml] NULL, [Active] [bit] NOT NULL DEFAULT (1), CONSTRAINT [PK_UserRota] PRIMARY KEY CLUSTERED ( [LoginName] ASC ))

Vendors

For Vendors, I actually take a strict approach to this issue. Vendors are authorized for no more than 72 hrs (for example a weekend) at a time without automatic recurring access. If you have ongoing contractors, then put them in the [UserRota].

CREATE TABLE [dbo].[Vendor]( [LoginName] [nvarchar](128) NOT NULL, [StartAccess] [datetime] NOT NULL DEFAULT (getdate()-(10)), [EndAccess] [datetime] NOT NULL DEFAULT (getdate()-(9)), CONSTRAINT [PK_Vendor] PRIMARY KEY CLUSTERED ( [LoginName] ASC )) GO ALTER TABLE [dbo].[Vendor] WITH CHECK ADD CONSTRAINT [CK_NoMoreThan72Hours] CHECK ((abs(datediff(hour,[StartAccess],[EndAccess]))<(73))) GO ALTER TABLE [dbo].[Vendor] CHECK CONSTRAINT [CK_NoMoreThan72Hours] GO

Login Security

This is the same as our earlier example.

CREATE TABLE [dbo].[LoginSecurity]( [LoginName] [varchar](120) NOT NULL, [IPAddress] [varchar](24) NOT NULL, [FailedCount] [int] NULL DEFAULT ((0)), [LastFailed] [datetime] NULL DEFAULT (getdate()-(365)), [SuccessCount] [int] NULL DEFAULT ((0)), [LastSuccess] [datetime] NULL DEFAULT (getdate() ) ON [PRIMARY]

Enforcement pattern

I opted to do positive assertions, if the user has permissions then they return from the trigger. If no permissions are found then the user falls through to a Rollback Tran and the login is denied. This keeps the logic simple to follow.

An example of a rota is shown below:

'<rota> <shift dw="1" starttime="8:00" endtime="12:00"/> <shift dw="4" starttime="12:00" endtime="22:00"/> <shift dd="2" starttime="08:00" endtime="22:00"/> <shift dd="8" starttime="08:00" endtime="22:00"/> <shift startdate="1/1/2010 12:00" enddate="12/1/2011 22:00"/> </rota>'

This XML translates to:

  • Weekday = 1 (Sunday), from 8AM to Noon
  • Weekday =4 (Wednesday), from 8AM to 10PM
  • On 2st day of month from 8AM to 10pm
  • On 8th day of month from 8AM to 10pm
  • Any time from January 1, 2010 at Noon until December 1st, 2011 at 10PM
The Coding Solution

The code below should be sufficiently commented to follow. There are three different rota patterns implemented:

  • By day of week
  • By day of month
  • By specific dates

There is one essential thing to remember: Always include the database name when referencing table. If a table cannot be located, the login will fail.

CREATE TRIGGER Logon_SecurityTemporal ON ALL SERVER FOR LOGON AS BEGIN DECLARE @UserName varchar(120) DECLARE @IP varchar(24) DECLARE @PostTime DateTime Declare @Data Xml Set @Data=EVENTDATA() SET @UserName=@Data.value('(EVENT_INSTANCE/LoginName)[1]','varchar(120)') IF @UserName is NULL BEGIN ROLLBACK TRAN RETURN END IF EXISTS (SELECT 1 FROM Security.dbo.Vendor WHERE @UserName=LoginName) AND EXISTS (SELECT 1 FROM Security.dbo.BreakGlass WHERE Vendors =1) RETURN -- Authorized as a break glass IF EXISTS (SELECT 1 FROM Security.dbo.UserRota WHERE @UserName=LoginName AND Active=1) AND EXISTS (SELECT 1 FROM Security.dbo.BreakGlass WHERE Employees =1) RETURN -- Authorized as a break glass if active -- Disable inactive user accounts at least every 90 days. IF EXISTS (Select 1 from Security.dbo.LoginSecurity WHERE @UserName=LoginName AND LastSuccess IS NOT NULL) BEGIN IF NOT EXISTS(Select 1 from Security.dbo.LoginSecurity WHERE @UserName=LoginName AND GetDate() < DateAdd(dd,90,LastSuccess)) BEGIN ROLLBACK RETURN END END -- Allow vendors when in their time slots IF EXISTS(Select 1 from Security.dbo.Vendor WHERE @UserName=LoginName AND GETDATE() BETWEEN StartAccess and EndAccess) RETURN -- Check for day of week schedule IF EXISTS ( Select 1 FROM Security.dbo.UserRota
CROSS APPLY RotaXml.nodes('//shift[@dw]') as ref(node) WHERE @UserName=LoginName AND DatePart(dw,getdate())=node.value('./@dw','int') AND cast(GetDate() as time) between cast(node.value('./@starttime','time') as time) AND cast(node.value('./@endtime','time') as time) ) RETURN -- Check for day of month schedule IF EXISTS ( Select 1 FROM Security.dbo.UserRota
CROSS APPLY RotaXml.nodes('//shift[@dd]') as ref(node) WHERE @UserName=LoginName AND DatePart(dd,getdate())=node.value('./@dd','int') AND cast(GetDate() as time) between cast(node.value('./@starttime','time') as time) AND cast(node.value('./@endtime','time') as time) ) RETURN IF EXISTS ( Select 1 FROM Security.dbo.UserRota
CROSS APPLY RotaXml.nodes('//shift[@startdate and @enddate]') as ref(node) WHERE @UserName=LoginName AND GetDate() between node.value('./@startdate','datetime') AND node.value('./@enddate','datetime') ) RETURN -- IF NOT FOUND, then DENY ROLLBACK END GO

 

Summary

Applying temporal constraints to logins is a good way to enhance security in SQL Server. The above pattern provides a basic pattern with some common problems solved.

Caution is Advised

If you blow the coding of the trigger, you may be unable to make ANY new connections to the database or SQL Server. Always keep at least two connected instances of SSMS open when you are defining and testing DDL triggers involving the logon. I have accidentally closed SSMS during debugging frustrations and put myself into a very awkward situation.

If you encounter problems with one of your open connections, execute:

 

DROP TRIGGER Logon_SecurityTemporal ON ALL SERVER

If you forget the “ON ALL SERVER”, you may get very confused because the simpler form will work but new connections may still fail.

 

DROP TRIGGER Logon_SecurityTemporal -- This is not sufficient

The reason to do “ALL” is simple – once a connection is made, the database may be changed on the connection – specifying the trigger on just one database allows the trigger to be bypassed.