As I wrote in a previous post, truncating tables or scrambling content might not remove the actual data from the datafiles. The examples I gave in that post were Oracle related and now I’ll show the same using MS SQL Server 2005. I’d like to thank Dmitriy Geyzerskiy for providing the actual working example.
create database Test
go
use Test
go
– Create a dummy table
create table aaa (a varchar(100));
go
BEGIN TRANSACTION
– Populate with dummy data (object names)
insert into aaa
select name from sys.all_objects;
COMMIT;
– Make sure the data is flushed to the disk
CHECKPOINT;
–get the file and page offsets
SELECT
CONVERT (VARCHAR (6),
CONVERT (INT,
SUBSTRING (sa.first_page, 6, 1) +
SUBSTRING (sa.first_page, 5, 1))) as [File offset],
CONVERT (VARCHAR (20),
CONVERT (INT,
SUBSTRING (sa.first_page, 4, 1) +
SUBSTRING (sa.first_page, 3, 1) +
SUBSTRING (sa.first_page, 2, 1) +
SUBSTRING (sa.first_page, 1, 1))) AS [First page]
FROM
sys.system_internals_allocation_units AS sa,
sys.partitions AS sp
WHERE
sa.container_id = sp.partition_id
AND sp.object_id = OBJECT_ID(’aaa’);
–Allow DBCC output in user window
DBCC TRACEON(3604)
–truncate the table
TRUNCATE TABLE aaa
–examine the contents of the page (all the objects from the truncated table are there)
DBCC PAGE (’Test’, — database name
1, — [File offset] from previous query
73, — [First page] from previous query
3) — extended output option
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.
I promised to blog a bit about my traveling, so here I go:
I was visiting customers in India and the US and giving presentations to Oracle user groups in the US. Amazingly, the state of US airports is just getting worse every month. Flying from Israel to India and from India to NY went without any problems. However not did a 35 minute flight from NY to Boston take 3 hours, but they managed to lose my suitcase in the process. Every flight I had in the US in the previous week was late.
Enough moaning and back to Oracle security… I would like to share with you some insights I had while giving presentations. First, it looks as if database security is getting more and more attention from both DBAs as well as IT managers. By show of hands at the presentations, I could see that at least some of the DBAs are handling security issues as part of their day-to-day job. But still, DBAs are not hearing the following from their managers - “last year you met your MBOs because no database breach had occurred. Here is your bonus…” - though many have heard the bonus speech for HA or performance MBO achievements.
Second, almost no one had deployed the July 2007 Critical Patch Update from Oracle. From a crowd of about 50, only 2 raised their hands.
Third and most startling of all, only about a third of the DBAs have ever deployed an Oracle CPU. Let me repeat that again - more than two thirds of DBAs in this small but significant sample have never deployed an Oracle CPU. Ever.
So this got me thinking - do we care about Oracle CPUs at all? Oracle was getting a lot of heat from security researchers for not providing security patches or providing them with irregular intervals. Finally, Oracle is stepping up to the plate with the patches. They provide them on regular basis, they announce the the patch before issuing it so organizations can prepare for them. They are improving coding techniques and code vulnerability scanning tools. And after all that, customers are still not protected. The reason for this is that the database is an extremely complicated piece of software and is the life-line of the organization. An enterprise will need to test the CPU thoroughly before deploying and testing takes a lot of time (months). This is further complicated by the fact that many organizations have applications running on top of Oracle databases, and those applications are not “forward compatible” and certified by their vendors to run on future Oracle versions.
Ironically, from a security standpoint the situation after a CPU is announced is actually worse than before it is announced: The hackers get a road-map of all the vulnerabilities while most organizations have not yet plugged those holes. This is a similar notion to hacking IPS software in order to retrieve vulnerabilities (see this black hat presentation).
I’m not saying that Oracle should stop providing CPUs. Quite the contrary, I’m saying that organizations must deploy CPUs as quickly as possible to keep this sensitive period short. Even considering the objective difficulties in applying patches, it seems that enterprises are not taking database vulnerability seriously enough. Also, organizations must have other solutions to mitigate the threat in post-CPU release period. Those solutions must not change the Oracle software at all or else they will fall into the same trap of interdependency, stability issues and so forth. They must provide virtual patches to externally test for attacks and plug the security holes from the outside.
I am curious to know other people’s experiences and views on this topic - so fire away…
Back after a short and much needed hiatus, I came across this piece by security analyst Eric Ogren on Computerworld’s website. He discusses how DBAs have become public enemy number one because of compliance mandates to exercise segregation of duties, and how this has been blown out of proportion to other, greater risks.
A few days pass, and the story about the Fidelity database breach comes to light (incidentally I chose this article from Computerworld as well). A senior DBA sold 2.3 million records, including bank account and credit card details, to a data broker.
So are DBAs “dangerous” or not?
Unfortunately, there is no denying the risk element. If risk is the arithmetical product of the probability of an incident happening and the potential damage that incident could cause, then due to the latter factor DBAs as well as other highly skilled insiders with access privileges pose a significant risk.
This does not mean, however, that there is a high probability of DBAs becoming malicious insiders. Obviously, the vast majority of DBAs pose no threat to their employers or clients, but the old adage of one rotten apple applies nonetheless. While there is a much higher probability that someone who is not a DBA would try and breach the database, the DBA is in a much better position to succeed should he or she really want to do that.
An external hacker would find it very difficult to achieve this kind of scale (millions of records) without insider cooperation. It is difficult to determine what direct damages this will bring to Fidelity and its customers, but the bad publicity is already quite significant: Running a news search on Google for fidelity data breach yielded 529 results at the time of writing.
Clearly, there is a problem here which cannot be ignored, but on the other hand, Eric’s conclusion was absolutely correct - DBAs are a part of the solution, and I would even stress that they are an essential part of the solution. The fact is, DBAs know more about database security than anyone else. They know more about database vulnerabilities, exploits and hacks, and more about how to address them than anyone else. Trying to implement a database security solution by circumventing or ignoring DBAs would be futile.
It is important, for security as much as for regulatory compliance reasons, to monitor and audit DBA activity. In fact, this should be done for all users who access the database. DBAs are first to understand this. If you work in a bank vault, you know there are CCTV cameras on you. You want those cameras on you. DBAs are in a similar situation and they understand this requirement completely.
What DBAs should not accept are two kinds of solutions that one sometimes comes across (sometimes it isn’t the tool but the implementation process):
Solutions that hinder or interfere with the DBA’s daily tasks - DBAs are primarily concerned with running databases efficiently. Any solution that jeopardizes this primary objective is counter-productive, and doomed to fail anyway because DBAs and other staff will find ways to circumvent it.
Solutions that ignore DBA input - As I suggested, DBAs are not as opposed to the notion of monitoring their own activities as some people think, so there is no real reason not to involve them. More importantly, I believe it is simply impossible to implement a solid database security solution without DBA cooperation. Any solution that ignores the specific data structures, user profiles, schemas and views simply cannot be doing a good job. Those are all managed by DBAs.
Finally, there is the question of priorities. Obviously my company sells database security monitoring products, so my view is not objective, but I’ll say this: Databases are still the most neglected parts of the enterprise IT infrastructure security-wise, especially when taking the magnitude of the threat into account. The Fidelity incident is just the latest in a long string of examples demonstrating this.
