Musings on Database Security

Just wanted to share with you a secret Hedgehog project I’ve been working on for almost two years. You can see a picture below.

I had an interesting conversation with Alexander Kornbrust yesterday about cloning databases. Most DBAs I know copy database files from production to create staging, integration and test environments. Those environments contain a lot of sensitive information (PII, CC, etc.) which is usually either deleted, scrambled or truncated. The problem with these solutions is that most DBAs forget that the database performs logical deletes and not physical deletes. This can be easily demonstrated on Oracle by the following simple steps that create a table, populate it using dummy data, truncating it and showing the data from the dump file:

• create table test(t varchar2(30));
• insert into test select object_name from user_objects where rownum < 1000;
• commit;
• select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test where rownum < 2;
• truncate table test;
• For the following step, replace ‘x’ and ‘y’ with the results from the previous select
• alter system dump datafile x block y;
• show parameter user_dump_dest
• Check out the new file in the user_dump_dest directory. The file will contain the truncated data in the block.

Of course, this is just an example but it is worth thinking about. It is also worth considering TDE to protect the data files from direct reading.

DBAs out there – what do you do to remove sensitive information from your non-production environments?

A somewhat technical post on MS SQL Server encrypted triggers.

It turns out that MS SQL Server 2005 has an issue with encrypted triggers in the model database. We’ve created an encrypted database level trigger on DDL operations in all databases including the model database so that when a new database is created the trigger will be created in the new database as well. The problem we’ve encountered is that the encrypted triggers are not correctly copied to the new database.

For example, here is the code for creating the triggers:

exec sp_MSForeachdb
‘use ?;
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = ”TEST_DDL_TRIGGER”)
DROP TRIGGER TEST_DDL_TRIGGER
ON DATABASE;’,
‘?’,
‘USE ?;
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
EXECUTE(”CREATE TRIGGER TEST_DDL_TRIGGER
ON DATABASE WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
– Do something…
END;”)’;
Now, let’s test this:
create database test;
GO;
use test;
create table tt (id int);

And voila -

Msg 102, Level 15, State 1, Procedure TEST_DDL_TRIGGER, Line 1
Incorrect syntax near ’0xfa86′.

So, is there any MS SQL Server expert out there who can shed some light on this behavior? It looks like a bug to me.