LOGON TRIGGERS For Out-Of-Hours Security

Although many of us now work in a 24/7 environment, other still keep to a more traditional time table of database access. If you fall into the latter category, then please read on...

In SQL Server 2008, we have Logon Triggers. These are fundamentally DDL Triggers, created at the Server scope, that respond to a Login event. They fire after authentication has been made to the Instance, so would not be used as a form of security in the traditional sense. However, they can be useful for many other purposes.

There is much documentation on the Internet, as to how to use these triggers to prevent multiple logins by the same user, etc, so I will not focus on that here. Instead, I want to look at how you can use Logon Triggers to meet a requirement I sometime see, where clients want to limit their staff's access to the database, outside of working hours.

To begin, we will create an audit log table in the dbo schema. All logins should be given the permissions to write to this table...

CREATE TABLE Audit

      (

      SQL_Login          sysname,

      Access_Time       datetime2,

      Client_Machine    nvarchar(128)

      )

...A point of note, worthy of mention in this statement, is that sysname is not equivalent to nvarchar(128) NOT NULL, as opposed to nvarchar(30).

We will now look at how to create out Trigger. We begin by creating the Trigger's header...

CREATE TRIGGER Restrict_Login_Time

ON ALL SERVER

FOR LOGON

AS

...So here, you can see that we are specifying that the Trigger will be at the server scope, and will respond to a Logon event occurring. The next thing that we will want to do is define the unacceptable access hours. To do this, we will use an IF statement, and call the DATPART function. For the purpose of this example, let’s assume that the business does not want people to logon between 7PM and 6AM. However, we of course need to give ourselves a fail-safe, in case of emergency. For this reason, we will add a 'get-in clause' for our SQLAdmin account...

BEGIN

IF (DATEPART(HOUR, GETDATE()) BETWEEN 6 AND 19) AND (SUSER_SNAME() != 'SQL-01\SQLAdmin')

...If both of these conditions are met, we will want to accomplish two goals. Firstly, kick the user out, and secondly log their details to our audit table. We will achieve this with the following code...

     

     

BEGIN

ROLLBACK

INSERT INTO Adventureworks2008.dbo.Audit_Table (SQL_Login, Access_Time, Client_Machine)

SELECT SUSER_SNAME(),

GETDATE(),

EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(100)')

END

END

In this code, the most interesting part is the insertion into the Client_Machine column. This is using the XQuery Value method to query the event data. The event data is an XML document that is only available within the context of a DDL Trigger or Event Notification. The schema of the document will change, depending on the event, but for the Logon event, it is as follows...

<EVENT_INSTANCE>

　　　　<EventType>event_type</EventType>

　　　　<PostTime>post_time</PostTime>

　　　　<SPID>spid</SPID>

　　　　<ServerName>server_name</ServerName>

                <LoginName>login_name</LoginName>

                <LoginType>login_type</LoginType>

                <SID>sid</SID>

                <ClientHost>client_host</ClientHost>

                <IsPooled>is_pooled</IsPooled>

</EVENT_INSTANCE>

...The Value method itself must always return a single scalar value, which is mapped to a SQL Server data type. However, specifying [1], to signify this, is still arbitrary syntax.

A point worthy of note is that when you use Logon Triggers to audit, as we have in this example, you will see multiple rows, for each invocation of the trigger. This is because it will fire for each service that is running.