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.
Well, finally I’m writing the third part of the blog. The thing that pushed me to finish this was a talk I had with Tim Hall of Oracle-base fame after his Unconference presentation in Oracle OpenWorld. Tim told me that his Java developers are claiming that adding user context information in an already existing application (Swing) is a non trivial task. You know, I’ve been hearing this from a lot of our customers and while I agree it is not trivial, I will try to outline a method of doing so without changing application code. In this day and age when there are advanced tools such as AspectJ and Spring framework, adding cross-cutting concerns to an application should not be an insurmountable task.
So, without further ad0, I will detail an AspectJ aspect that will wrap around an Oracle connection and add user context information to every statement. This aspect can be used with existing programs and also adapted and extended to catch login information in a Swing based application. I will build of the previous examples in providing the necessary infrastructure of domain and DAO classes.
As promised, this is the second of a three part blog entry discussing the propagation of middle-tier users to the database. This post will mainly concentrate on the Java side of things. I will show how to use Spring-framework’s excellent transactional support using AOP to add an additional advice, relying on ThreadLocal to pass application user identities from the web tier all the way to the database, and using annotations on the service layer to specify the module and action.
I strongly advise downloading the linked zipped source code at the end of the post if you’re planning on seriously reviewing the code.
Well, I threatened to post something more technical, and here we are. I’ve just come back from a business trip to the US, meeting some prospects and customers. As always, I was asked a lot of technical questions. One of the frequent questions I encounter is - how can we propagate the application user and context information to the database, for monitoring and prevention purposes?
I’m going to answer this question here for a specific technological setup of Java and Oracle, but the solution can be adapted to other technologies. Then I’ll give some examples using Spring framework and various Java database tools such as Hibernate and apache commons-dbcp.
In Oracle, there are a couple of methods to propagate application user info into the database. Two such methods are:
Lightweight user sessions that can be created on top of a single database session in the middle tier or using a connection pool. This method is available to applications using OCI using OCI specific calls and settings like OCI_ATTR_PROXY_CREDENTIALS and using the new JDBC drivers from Oracle. Please note that this method only works when all your application users are users in the database.
Tracking users, modules and actions using specific CONTEXT (USERENV) parameters. This method is available to OCI applications using OCI_ATTR_MODULE, OCI_ATTR_ACTION and OCI_ATTR_CLIENT_INFO. This method is also available to Java applications using Oracle JDBC driver (either type 2 or type 4).
We will focus on the second method using the Oracle thin JDBC driver as it does not mandate creating all application users in the database.
First, let’s stick to the bare minimum (no exception handling and no frameworks):
Here is what should have worked in Oracle 9i –
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
public class Test
{
public static void main(String[] args) throws SQLException, ClassNotFoundException { OracleDataSource dataSource = new OracleDataSource(); dataSource.setURL(”jdbc:oracle:thin:@localhost:1521:slavdev”); dataSource.setUser(”scott”); dataSource.setPassword(”tiger”); OracleConnection conn = (OracleConnection) dataSource.getConnection(); conn.setClientIdentifier(”Slavik”); Statement statement = conn.createStatement(); ResultSet rs = statement.executeQuery(”select sysdate, sys_context(’USERENV’,'CLIENT_IDENTIFIER’) from dual”); rs.next(); System.out.println(rs.getString(1) + ” - ” + rs.getString(2)); rs.close(); statement.close(); conn.clearClientIdentifier(”Slavik”); conn.close(); dataSource.close(); }
}
Surprisingly, this code actually works in 10gR2 giving the result: 2007-05-20 16:25:15.0 – Slavik. But on the other hand, making it work under 9iR2 is not so simple. Also, unfortunately, the setClientIdentifier() as well as clearClientIdentifier()are deprecated. So the recommended way to code this is to use the relatively new end-to-end metrics API.
Now the results are - 2007-05-20 16:31:55.0 - Simple Test, Test Application, Slavik, and we did not waste any roundtrips to the server while setting all those details.
Please notice that the metrics setting will be set on the connection for all statements until we reset it by setting nulls and Short.MIN_VALUE.
