May 16, 2008 at 4:12 pm
"There's a fine line between stupid and clever." - David St. Hubbins
A database logging scheme is implemented by including calls to a LogAuditEvent routine in stored procedures. LogAuditEvent adds records to this table:
CREATE TABLE [Security].[AuditLog](
[AuditLogId] [int] IDENTITY(1,1) NOT NULL,
[DateTimeStamp] [datetime] NOT NULL,
[AuditActionTypeId] [tinyint] NOT NULL,
[SourceObjectId] [smallint] NULL CONSTRAINT
[DF_AuditLog_SourceObjectId] DEFAULT ((0)),
[TargetObjectId] [smallint] NULL CONSTRAINT
[DF_AuditLog_TargetObjectId] DEFAULT ((0)),
[TargetId] [int] NULL,
[ModifiedBy] [Security].[userid] NULL,
CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED
(
[AuditLogId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Instead of the SourceObjectId and TargetObjectId fields in the table above, the original logging scheme had a variable of type sysname for the name of the stored procedure that was performing the action that was being logged (the source) and a second sysname variable for the table in which the change was being made (the target). In some cases a given stored procedure might modify more than one table so logging just the stored procedure name was not sufficient. I changed the LogAuditEvent routine and the Audit log table to use numeric identifiers instead of the actual database object names.
MS SQL Server already has an internal table of database objects and numeric identifiers but those IDs are not meant for being referenced by standard database applications. A simple table could be used to map names of stored procedures and tables to unique identifiers but what about creating an extended property for each database object of interest with a unique ID and accessing that as part of the logging scheme? An administrative routine could add an AuditId property set to a unique number to all user-defined tables and stored procedures. A GetAuditId function could be used in the audit logging stored procedures to retrieve the integer value given the name of a database object:
CREATE FUNCTION [Security].[GetAuditId]
(
@schema sysname
,@objname sysname
,@objtype sysname = 'table' -- 'table' or 'procedure'
)
RETURNS int
AS
BEGIN
DECLARE @RetVal int
SET @RetVal =
(SELECT CONVERT(int, value)
FROM fn_listextendedproperty (NULL, 'schema', @schema,
@objtype, default, NULL, NULL)
WHERE objname = @objname And [name] = 'AuditId')
IF @RetVal Is null
RETURN 0
RETURN @RetVal
END
Advantages to this scheme:
* IDs are part of the object definition.
* Audit logging table can be more normalized by storing IDs instead of text names.
Disadvantages
* Extended properties don't enforce referential integrity such as preventing duplicate, bogus or missing AuditIds.
* AuditIds are stored as character strings instead of actual integers and require conversion overhead.
* After new tables and stored procedures are added to the database during project development, the administrative routine must be run to create extended properties on the new objects.
Are there any other significant advantages or disadvantages? Is using extended properties to store database object IDs a reasonable way to go or would a user-defined mapping table be preferred? Is there a case to be made for logging the actual names instead of IDs representing the names?
Part of the database design involves tables where each record can be linked to one of several other tables. For example, there is an Address table where an address can be linked to an entry in a Company, Supplier, Branch, Customer or Employee table. The linkage is handled via SourceTypeId and SourceId fields. Here is the Address table definition:
CREATE TABLE [Common].[Address](
[AddressId] [int] IDENTITY(1,1) NOT NULL,
[SourceTypeId] [smallint] NULL,
[SourceId] [int] NULL,
[AddressTypeId] [tinyint] NULL,
[AddressLine1] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressLine2] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[State] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ZipCode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Created] [datetime] NULL CONSTRAINT [DF_Address_Created] DEFAULT (getdate()),
[Modified] [datetime] NULL,
[ModifiedBy] [Security].[userid] NULL,
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[AddressId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
EXEC sys.sp_addextendedproperty
@name=N'AuditId',
@value=N'12' ,
@level0type=N'SCHEMA',
@level0name=N'Common',
@level1type=N'TABLE',
@level1name=N'Address'
ALTER TABLE [Common].[Address]
WITH CHECK ADD CONSTRAINT [CK_Address]
CHECK (([ZipCode] like '[0-9][0-9][0-9][0-9][0-9]'
OR [ZipCode] like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'))
ALTER TABLE [Common].[Address] CHECK CONSTRAINT [CK_Address]
There is another simple table called SourceType which assigns IDs to individual tables.
CREATE TABLE [Common].[SourceType](
[SourceTypeId] [smallint] NOT NULL,
[SourceType] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_SourceType] PRIMARY KEY CLUSTERED
(
[SourceTypeId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
EXEC sys.sp_addextendedproperty
@name=N'AuditId',
@value=N'42' ,
@level0type=N'SCHEMA',
@level0name=N'Common',
@level1type=N'TABLE',
@level1name=N'SourceType'
As an example, the Company table has a primary key field called CompanyId. In the SourceType table, there might be a record with a SourceType value of "Company" and a SourceTypeId of 4. Within the Company table we might have a record with a CompanyId value of, say, 216. If we now want to link an address to this record in the Company table, we would create a new record in the Address table with a SourceTypeId of 4 to indicate that the record comes from the Company table and a SourceId of 216 to indicate the record within the Company table to which we are linking.
If every database object has a unique ID and those IDs are stored in extended properties (assuming the auditing schema was implemented this way) does it then make sense to scrap having a SourceType table containing object IDs and instead grab the object ID from the extended property? In other words, is it clever or stupid to employ the same extended properties technique that was used for storing auditing IDs as the means of linking records in common tables such as the Address table to other tables? Is there an entirely different design that would be preferable?
May 19, 2008 at 6:17 am
It seems to me like you are trying to solve a problem that does not really exist.
Let me recap - you have a logging scheme that needs to track some information every time a stored procedure runs.
Your audit table needs to keep track of some relationship information from a couple of tables, and I presume some additional information.
The problem you are really trying to solve is how to deal with an object name changing. So, I will ask, how often do your database object names change? In addition to that, do you really want your audit trail to reference the new object name when an object name has changed?
I don't really know your answers, but I would typically believe object names don't change much and that in most cases, I want the old name on the old portion of the audit trail. If this is the case, why not store the entire object name and eliminate all of the ID issues you have? If you have a name change you want reflected in history, and this only happens once a year, in the object rename script, update the history in the audit log.
Finally, if you are stuck on the ID thing - use the system ID's, not some scheme you are creating. If you do a backup and restore, the objectID's don't get changed, there is a pile of system functions to help you such as object_id() that are documented system functions that MS would give you fair warning before changing.
May 19, 2008 at 7:01 am
Micheal typed the words before I could. I'd go with the names, for the same reasons. They shouldn't change, and if they do, this helps you follow the history in your audit table.
If you don't want that, use IDs. If you migrate things across tables, I'd "audit" the migration as well.
May 19, 2008 at 8:05 pm
Thanks for the recommendations.
The idea of using the design to make it easier to change stored procedure names hadn't occurred to me. The motivation was more along the lines of normalizing the auditing data as is done with other tables containing non-auditing data.
I think that you are right that not using extended properties to store IDs is the way to go.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply