Temporal constraints on Login in SQL Server

  • Article

[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<br>ON ALL SERVER <br>FOR LOGON<br>AS
BEGIN
DECLARE @UserName varchar(120)<br>DECLARE @IP varchar(24)<br>DECLARE @PostTime DateTime
Declare @Data Xml<br>Set @Data=EVENTDATA()<br>SET @UserName=@Data.value('(EVENT_INSTANCE/LoginName)[1]','varchar(120)')<br><br>IF @UserName is NULL
BEGIN<br>    ROLLBACK TRAN<br>    RETURN
END    <br><br>IF EXISTS (SELECT 1 FROM Security.dbo.Vendor <br>    WHERE @UserName=LoginName)<br>AND EXISTS (SELECT 1 FROM Security.dbo.BreakGlass<br>    WHERE Vendors =1)     <br>    RETURN -- Authorized as a break glass

IF EXISTS (SELECT 1 FROM Security.dbo.UserRota<br>    WHERE @UserName=LoginName AND Active=1)<br>AND EXISTS (SELECT 1 FROM Security.dbo.BreakGlass<br>    WHERE Employees =1)     <br>    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<br>     WHERE @UserName=LoginName AND LastSuccess IS NOT NULL)    <br>    BEGIN     <br>        IF    NOT EXISTS(Select 1 from Security.dbo.LoginSecurity<br>             WHERE @UserName=LoginName <br>            AND GetDate() < DateAdd(dd,90,LastSuccess))     <br>        BEGIN<br>            ROLLBACK <br>            RETURN<br>        END<br>    END

-- Allow vendors when in their time slots
IF EXISTS(Select 1 from Security.dbo.Vendor<br>    WHERE @UserName=LoginName <br>    AND GETDATE() BETWEEN StartAccess and EndAccess)<br>    RETURN

-- Check for day of week schedule
IF EXISTS (<br>Select 1 FROM Security.dbo.UserRota <br>CROSS APPLY RotaXml.nodes('//shift[@dw]') as ref(node)<br>WHERE @UserName=LoginName <br>AND DatePart(dw,getdate())=node.value('./@dw','int')<br>AND cast(GetDate() as time) between <br>    cast(node.value('./@starttime','time') as time)<br>AND<br>    cast(node.value('./@endtime','time') as time)  <br>    )    <br>    RETURN

-- Check for day of month schedule
IF EXISTS (    <br>Select 1 <br>FROM Security.dbo.UserRota <br>CROSS APPLY RotaXml.nodes('//shift[@dd]') as ref(node)<br>WHERE @UserName=LoginName <br>AND DatePart(dd,getdate())=node.value('./@dd','int')<br>AND cast(GetDate() as time) between <br>    cast(node.value('./@starttime','time') as time)<br>AND<br>    cast(node.value('./@endtime','time') as time)          <br>    )<br>    RETURN<br>    <br>IF EXISTS (    <br>Select 1 <br>FROM Security.dbo.UserRota <br>CROSS APPLY RotaXml.nodes('//shift[@startdate and @enddate]') as ref(node)<br>WHERE @UserName=LoginName <br>AND GetDate() between <br>    node.value('./@startdate','datetime')<br>AND<br>    node.value('./@enddate','datetime')          <br>    )<br>    RETURN    <br>        <br>-- 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.