Secure Data: Quickstart Guide

Setup

1. Create a database - for simplicity name the database secure_data
2. Execute the .sql file found at trunk\main\src\db\secure_data.sql

You have now created the secure_data database.

The next step is to create a database that contains the data you wish to secure, for this quickstart guide follow these steps:

1. Create a database - for simplicity name the database my_data
2. Execute the following SQL:

CREATE TABLE tbl_Contact (ContactId int NOT NULL identity(1,1), Title varchar(10), FirstName varchar(20), Surname varchar(20), DateOfBirth datetime NULL)

Create Required Database Objects

1. Build the solution
2. At the command prompt, navigate to the source code directory:

trunk\main\src\SDObjectCreator\bin\Release

Run the following command: SDObjectCreator.exe tbl_Contact

After a short while the program will execute and it will create the following:

• A column named SecurableId is added to the table named tbl_Contact
• A View is created named Contact that is a view of the data in the tbl_Contact table.
• A Trigger is created on the table named tbl_Contact that audits inserts, updates and deletes.

Application

Login to my_data and run the following SQL:
INSERT INTO Contact (Title, FirstName, Surname, DateOfBirth) VALUES ('Mr.', 'Bill', 'Gates', '1955-10-28')
INSERT INTO Contact (Title, FirstName, Surname, DateOfBirth) VALUES ('Mr.', 'Neil', 'Armstrong', '1930-08-05')
INSERT INTO Contact (Title, FirstName, Surname, DateOfBirth) VALUES ('Sir', 'Richard', 'Branson', '1950-07-18')

Run SELECT * FROM Contact and this will return your contacts. Now create a new login and run the SELECT statement again and no records will be returned (this is becuase the new login does not have access to this data-it has been secured.

Execute the following on the secure_data database to view the audit trail:

SELECT * FROM tbl_audit_securable

This will show the changes made to the data as an XML document as well as the user id of who made the changes.