Audit and Logging:Actions taken by users of a .NET Development Group application will be subject to audit logging that will track logins, logouts, and activities designated by the sponsors to be critical to the mission of the organization. All applications will keep an audit trail automatically to track user login and logout with a date time stamp and user name or identification tag.
If all changes to a critical table in a database need to be tracked, then a history table should be used to insert the updates to serve as a sequential audit trail. The initial row in a history table will be the first record inserted by a user, and all subsequent records will be an update audit trail inserted to track all changes made to the original record. Each new row should identify which user made the changes and when the changes occurred.
If only specific changes to an application need to be tracked, then a limited and smaller audit log table could be kept in the database. These non-critical activities should be designated and approved by the application sponsors and .NET Development Group Team Leadership Committee.
A sample stored procedure for tracking history is:
USE [Database]GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
-- =============================================
-- Author: .NET Team
-- Create date: 1/24/2000
-- Updated: .NET Team
-- Updated By: .NET Team
-- Description: Example of stored procedure for Insert/Update(Delete)
-- following technical standards
-- =============================================
CREATE PROCEDURE [dbo].[usp_Insert_Update_Role]@RoleID int,@ApplicationID int,@Name varchar(100),@Description varchar(256),@AdminRole int, @UserID int, @mode char(1) AS
BEGIN -- Stored Procedure Begin SET NOCOUNT ON; DECLARE @error int DECLARE @message varchar(256) DECLARE @identity int DECLARE @HistoryDescription varchar(256) IF UPPER(@mode) = 'I' -- Insert Mode BEGIN BEGIN TRAN InsertRoles INSERT INTO aspnet_Roles (fk_ApplicationID, [Name], [Description], AdminRole, ModifiedBy) VALUES (@ApplicationID, @Name, @Description, @AdminRole, @UserID) SET @identity = SCOPE_IDENTITY() SET @HistoryDescription = 'INSERTED ROLE ' + @Name + ' with ROW ID: ' + CONVERT(VarChar(5), @identity) + '.'
INSERT INTO [dbo].[aspnet_History] (fk_ApplicationID, [Description], fk_UserID) VALUES (@ApplicationID, @HistoryDescription, @UserID)
SET @error = @@ERROR IF @error = 0 BEGIN COMMIT TRAN InsertRoles END ELSE BEGIN ROLLBACK TRAN InsertRoles END -- IF @error = 0 END --IF UPPER(@mode) = 'I' ELSE IF UPPER(@mode) = 'U' -- Update Mode BEGIN BEGIN TRAN UpdateRoleUPDATE aspnet_Roles SET [Name] = @Name, [Description] = @Description, AdminRole = @AdminRole,ModifiedBy = @UserIDWHERE pk_RoleID = @RoleIDSET @HistoryDescription = 'UPDATED ROLE ' + @Name + ' at ROW ID: ' + CONVERT(VarChar(5), @RoleID) + '.' INSERT INTO [dbo].[aspnet_History] (fk_ApplicationID, [Description], fk_UserID)VALUES (@ApplicationID, @HistoryDescription, @UserID) SET @error = @@ERROR IF @error = 0BEGINCOMMIT TRAN UpdateRoleEND ELSE BEGIN ROLLBACK TRAN UpdateRoleEND -- IF @error = 0 END -- ELSE IF UPPER(@mode) = 'U' -- Update Mode ELSE IF UPPER(@mode) = 'D' -- Delete Mode BEGIN BEGIN TRAN DeleteRoleUPDATE aspnet_Roles SET Deleted = 1, ModifiedBy = @UserIDWHERE pk_RoleID = @RoleIDSET @HistoryDescription = 'DELETED ROLE ' + @Name + ' at ROW ID: ' + CONVERT(VarChar(5), @RoleID) + '.' INSERT INTO [dbo].[aspnet_History] (fk_ApplicationID, [Description], fk_UserID)VALUES(@ApplicationID, @HistoryDescription, @UserID) SET @error = @@ERROR IF @error = 0BEGIN COMMIT TRAN DeleteRoleEND ELSE BEGIN ROLLBACK TRAN DeleteRoleEND -- IF @error = 0 END -- ELSE IF UPPER(@mode) = 'D' -- Delete Mode END -- Stored Procedure End