1 / 22

Application Data and Database Activities Auditing

Application Data and Database Activities Auditing. Dr. Gabriel. Auditing Process. Auditing Negatively affects performance Can be costly Clearly defined objectives are necessary. DML Action Auditing Architecture. Is used for application data auditing

justus
Download Presentation

Application Data and Database Activities Auditing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

  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) • Restrictions—Transact-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,customerid,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 CREATE 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 @old money, @new money, @user varchar(100) select @old=amount from deleted select @new=isnull(amount,0),@user=username from inserted insert into tAuditTrail (tablename,columnname,oldamount,newamount,updatedby) values ('tpayments','amount',@old,@new,@user) end Create trigger trgtPayments_audit_del on tPayments for delete as declare @old money select @old=amount from deleted insert into tAuditTrail (tablename,columnname,oldamount,newamount,updatedby) values ('tpayments','amount',@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 @customerid int, @amount money, @reason varchar(255), @username varchar(100) as declare @transid int select @transid=max(transactionid)+1 from tpayments insert into tpayments (transactionid,customerid,amount,paymenttype,username) values(@transid,@customerid,@amount,‘CR‘, @username) insert into tApplicationAuditTrail (action,actionentity,actionvalue,reason,updatedby) values ('ISSUE CREDIT','PAYMENT',convert(varchar(max),@amount),@reason,@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_AUTHORIZATION_DATABASE,CREATE_CERTIFICATE, ALTER_CERTIFICATE,DROP_CERTIFICATE,CREATE_CONTRACT,DROP_CONTRACT, GRANT_DATABASE, DENY_DATABASE,REVOKE_DATABASE,CREATE_EVENT_NOTIFICATION,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_FUNCTION,CREATE_PARTITION_SCHEME, ALTER_PARTITION_SCHEME, DROP_PARTITION_SCHEME,CREATE_PROCEDURE,ALTER_PROCEDURE, DROP_PROCEDURE,CREATE_QUEUE,ALTER_QUEUE,DROP_QUEUE,CREATE_REMOTE_SERVICE_BINDING, ALTER_REMOTE_SERVICE_BINDING,DROP_REMOTE_SERVICE_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 set nocount on declare @data xml set @data = EVENTDATA() insert into changelog(databasename, eventtype, objectname, objecttype, sqlcommand, loginname) Values(@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'))

  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 ?

More Related