Over the past few months I have been reading a book of the title “Cryptography in the Database: The Last Line of Defense” by Kevin Kenan.  I would certainly recommend this book; although I have yet to complete reading it in its entirety.  With all of the other things that are in mid-air in this juggling act that we call living I have found that my reading has turned into multiple short sprints rather than the long marathons of days past.

In this book a concept called “honeycombing” is very briefly introduced. This concept is the placement of tables within a database that contains seemingly highly confidential data. These tables have no purpose other than fulfilling the role of a decoy. When a data snooper happens upon these tables and performs a query to reveal its contents this occurrence is logged and the DBA or Security Officer is notified.

The concept of honeycombing a database is pretty clever. I have mentioned it to some of my peers and it has stirred up some interesting conversations. I personally have never employed this technique and also have never seen it employed by others. So, I thought I would experiment a bit and build one in a local database for kicks.

Building the honeycomb table was not unique to building any other table. The columns that were contained within the table were items such as Customer_ID,  CC_Number, CC_Amount, and CC_TransDate. To the criminally curious these columns implied that credit card transactions were to content of the table. I intentionally gave the table an alluring name of “CC_DATA” which would imply the same.  The data, all 50,000 rows, was populated with one of my new favorite tools: Red-Gate’s SQL Data Generator.

With the trap set I now pursued notification of the tripping of the snare. I first approached the use of triggers. I was quickly reminded that there are no triggers associated with SELECT. If data was updated, inserted or deleted within the table a trigger certainly could be utilized. Confounded but not discouraged, I searched for other solutions. Running a trace continually could certainly capture that a SELECT had occurred but notification through these means can be quite the challenge.

Through the use of the sysprocesses system table and the use of the following query I was able to identify when my honeycombed table was queried:

        SELECT 
                 SPID,
                 LOGIN_TIME,
                 LAST_BATCH,
                 HOSTNAME,
                 PROGRAM_NAME,
                 NT_DOMAIN,
                 NT_USERNAME,
                 NET_ADDRESS,
                 LOGINAME,
                 TEXT
        FROM 
                 MASTER.dbo.sysprocesses s
                 CROSS APPLY sys.dm_exec_sql_text(s.sql_handle)
        WHERE 
                 TEXT LIKE '%cc_data%'
                 AND TEXT NOT LIKE '%MASTER.dbo.sysprocesses%'

The issue is that as soon as the SPID that queried my honeycombed table executes another query the data is overwritten with information about the recently executed query. Once again the notification of this event still remains allusive.

While SQL Server 2005 does not appear to provide any intuitive means to employ effective notification for the purpose of honeycombing I am hopeful that will additional dialogue with others and Microsoft that an innovative solution can be found. I think that it would be timely to mention that I have not yet explored options that may be available in SQL Server 2008.

As a reader of my blog I would encourage you to join in and e-mail me any unique approaches to solving this conundrum. That is what being part of a professional community is all about – right? :D