Temporal constraints on Login in SQL Server - Enterprise Applications on Microsoft SQL Server - Site Home

[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 2^st day of month from 8AM to 10pm
On 8^th day of month from 8AM to 10pm
Any time from January 1, 2010 at Noon until December 1^st, 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.