Application Data and Database Activities Auditing

1
Application Data and Database Activities
Auditing

• Dr. Gabriel

2
Auditing Process

• Auditing
• Negatively affects performance
• Can be costly
• Clearly defined objectives are necessary

3
DML Action Auditing Architecture

• Is used for application data auditing
• Data Manipulation Language (DML) companies use
  auditing architecture for DML changes
• DML changes can be performed on two levels
• Row level
• Column level
• Implemented using triggers and/or stored
  procedures

4
DML Action Auditing Architecture (continued)
5
DML Action Auditing Architecture (continued)
6
SQL Server Triggers

• A trigger is a special type of stored procedure
  that automatically takes effect when a language
  event executes
• 2 types
• DML triggers
• DDL triggers

7
SQL Server Triggers

• DML Triggers
• INSTEAD OF triggers
• are executed in place of the usual triggering
  action.
• can also be defined on views with one or more
  base tables, where they can extend the types of
  updates a view can support.
• AFTER triggers
• are executed after the action of the INSERT,
  UPDATE, or DELETE statement is performed.
• can be specified only on tables.
• is the default when FOR is the only keyword
  specified

8
SQL Server Triggers
Function AFTER trigger INSTEAD OF trigger
Applicability Tables Tables and views
Quantity per table or view Multiple per triggering action (UPDATE, DELETE, and INSERT) One per triggering action (UPDATE, DELETE, and INSERT)
Cascading references No restrictions apply INSTEAD OF UPDATE and DELETE triggers are not allowed on tables that are targets of cascaded referential integrity constraints.
Execution After Constraint processing Declarative referential actions inserted and deleted tables creation The triggering action Before Constraint processing In place of The triggering action After inserted and deleted tables creation
Order of execution First and last execution may be specified Not applicable
varchar(max), nvarchar(max), and varbinary(max) column references in inserted and deleted tables Allowed Allowed
text, ntext, and image column references in inserted and deleted tables Not allowed Allowed
9
SQL Server Triggers

• CREATE TRIGGER DDL statement
• creates a trigger
• Trigger condition
• Prevents a trigger from firing
• UPDATE() and COLUMNS_UPDATE() functions
• Logical tables
• DELETED contains original data
• INSERTED contains new data

10
SQL Server Triggers (continued)

• RestrictionsTransact-SQL statements not allowed
• ALTER and CREATE DATABASE
• DISK INIT and DISK RESIZE
• DROP DATABASE and LOAD DATABASE
• LOAD LOG
• RECONFIGURE
• RESTORE DATABASE
• RESTORE LOG

11
Implementation of an Historical Model with SQL
Server

• Create a history table
• Same structure as original table
• Add HISTORY ID column
• Possibly add Date Updated column
• Possibly add User Name column
• Other columns may be added
• Create a trigger
• inserts original row into the HISTORY table
• Or inserts values of updated columns only

12
Implementation of an Historical Model with SQL
Server

• Example
• Create table tPayments (
• transactionid int not null primary key,
• customerid int not null,
• amount money not null,
• paymenttype char(2) not null check(paymenttype
  in ('CH','CA','WT,CR)),
• referencenumber varchar(100) not null default
  ('') )
• Create table tPayments_History (
• historyid uniqueidentifier primary key
  default(newid()),
• transactionid int not null,
• customerid int not null,
• amount money not null,
• paymenttype char(2) not null,
• referencenumber varchar(100) not null default
  ('') ,
• dateupdated datetime default(getdate()) )

13
Implementation of an Historical Model with SQL
Server

• Create trigger trgtPayments_audit
• on tPayments
• for update,delete
• as
• insert into tPayments_History (transactionid,custo
  merid,amount, paymenttype,referencenumber)
• select from deleted

14
Another Implementation of a Historical Model with
SQL Server
alter table tpayments add username
varchar(100) drop trigger trgtPayments_audit CRE
ATE TABLE tAuditTrail( id uniqueidentifier NOT
NULL PRIMARY KEY DEFAULT (newid()), tablename
varchar(100) NOT NULL, columnname varchar(100)
NOT NULL, oldamount money NOT NULL, newamount
money not null, dateupdated datetime NOT NULL
DEFAULT (getdate()), updatedby varchar(100) NOT
NULL )
15
Another Implementation of a Historical Model with
SQL Server
Create trigger trgtPayments_audit_upd on
tPayments for update as if update(amount) begin
declare _at_old money, _at_new money, _at_user
varchar(100) select _at_oldamount from deleted
select _at_newisnull(amount,0),_at_userusername from
inserted insert into tAuditTrail
(tablename,columnname,oldamount,newamount,updatedb
y) values ('tpayments','amount',_at_old,_at_new,_at_user)
end Create trigger trgtPayments_audit_del on
tPayments for delete as declare _at_old money
select _at_oldamount from deleted insert into
tAuditTrail (tablename,columnname,oldamount,newamo
unt,updatedby) values ('tpayments','amount',_at_old
,,)
16
Implementation of an Application Actions Model
with SQL Server

• CREATE TABLE tApplicationAuditTrail(
• id uniqueidentifier NOT NULL PRIMARY KEY DEFAULT
  (newid()),
• action varchar(100) NOT NULL,
• actionentity varchar(100) NOT NULL,
• actionvalue varchar(max) NOT NULL,
• reason varchar(255) NOT NULL,
• dateupdated datetime NOT NULL DEFAULT
  (getdate()),
• updatedby varchar(100) NOT NULL,
• )

17
Implementation of an Application Actions Model
with SQL Server

• create proc pissuecredit
• _at_customerid int,
• _at_amount money,
• _at_reason varchar(255),
• _at_username varchar(100)
• as
• declare _at_transid int
• select _at_transidmax(transactionid)1 from
  tpayments
• insert into tpayments (transactionid,customerid,am
  ount,paymenttype,username)
• values(_at_transid,_at_customerid,_at_amount,CR,
  _at_username)
• insert into tApplicationAuditTrail
  (action,actionentity,actionvalue,reason,updatedby)
  
• values ('ISSUE CREDIT','PAYMENT',convert(varchar(m
  ax),_at_amount),_at_reason,_at_username)

18
DDL Triggers

• Are used for database activities auditing
• Are defined for certain DB events/activities
• Example
• CREATE TABLE ChangeLog(
• LogId int IDENTITY(1,1) NOT NULL,
• DatabaseName varchar (256) COLLATE
  SQL_Latin1_General_CP1_CI_AS NOT NULL,
• EventType varchar(50) COLLATE
  SQL_Latin1_General_CP1_CI_AS NOT NULL,
• ObjectName varchar(256) COLLATE
  SQL_Latin1_General_CP1_CI_AS NOT NULL,
• ObjectType varchar(25) COLLATE
  SQL_Latin1_General_CP1_CI_AS NOT NULL,
• SqlCommand varchar(max) COLLATE
  SQL_Latin1_General_CP1_CI_AS NOT NULL,
• EventDate datetime NOT NULL CONSTRAINT
  DF_EventsLog_EventDate DEFAULT (getdate()),
• LoginName varchar(256) COLLATE
  SQL_Latin1_General_CP1_CI_AS NOT NULL
• )

19
DDL Triggers (Cont.)

• create trigger backup_objects
• on database
• for CREATE_APPLICATION_ROLE,ALTER_APPLICATION_ROLE
  ,DROP_APPLICATION_ROLE,CREATE_ASSEMBLY,
• ALTER_ASSEMBLY,DROP_ASSEMBLY,ALTER_AUTHORIZATI
  ON_DATABASE,CREATE_CERTIFICATE,
• ALTER_CERTIFICATE,DROP_CERTIFICATE,CREATE_CONT
  RACT,DROP_CONTRACT, GRANT_DATABASE,
• DENY_DATABASE,REVOKE_DATABASE,CREATE_EVENT_NOT
  IFICATION,DROP_EVENT_NOTIFICATION,
• CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,
  CREATE_INDEX, ALTER_INDEX,DROP_INDEX,
• CREATE_MESSAGE_TYPE, ALTER_MESSAGE_TYPE,
  DROP_MESSAGE_TYPE,CREATE_PARTITION_FUNCTION,
• ALTER_PARTITION_FUNCTION,DROP_PARTITION_FUNCTI
  ON,CREATE_PARTITION_SCHEME,
• ALTER_PARTITION_SCHEME, DROP_PARTITION_SCHEME,
  CREATE_PROCEDURE,ALTER_PROCEDURE,
• DROP_PROCEDURE,CREATE_QUEUE,ALTER_QUEUE,DROP_Q
  UEUE,CREATE_REMOTE_SERVICE_BINDING,
• ALTER_REMOTE_SERVICE_BINDING,DROP_REMOTE_SERVI
  CE_BINDING,CREATE_ROLE, ALTER_ROLE,
• DROP_ROLE,CREATE_ROUTE,ALTER_ROUTE,DROP_ROUTE,
  CREATE_SCHEMA,ALTER_SCHEMA,
• DROP_SCHEMA, CREATE_SERVICE,ALTER_SERVICE,DROP
  _SERVICE,CREATE_STATISTICS,DROP_STATISTICS,
• UPDATE_STATISTICS,CREATE_SYNONYM,DROP_SYNONYM,
  CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
• CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,
  CREATE_TYPE,DROP_TYPE, CREATE_USER,ALTER_USER,
• DROP_USER,CREATE_VIEW,ALTER_VIEW,DROP_VIEW,
• CREATE_XML_SCHEMA_COLLECTION,ALTER_XML_SCHEMA_
  COLLECTION,DROP_XML_SCHEMA_COLLECTION
• As

20
Implementing SQL Profiler

• User interface for auditing events
• For each event you can audit
• Date and time of the event
• User who caused the event to occur
• Type of event
• Success or failure of the event
• Origin of the request
• Name of the object accessed
• Text SQL statement

21
Implementing SQL Profiler (Cont)

• Steps for implementing SQL Profiler
• From SQL Server Management Studio click on Tools
  and then on SQL Profiler
• In the SQL Profiler, click on File menu and
  select New Trace option
• Connect to SQL Server
• Select events based on the auditing objectives
• Select columns that you wish to track
• Select output options
• Run the trace

22
Questions ?