It seems to me there are two common strategies for doing DB audit trails via a trigger:
1. On an update to a row, duplicate that row in another table with
identical rows, except for perhaps the extra columns which represent
change date and changed by. Eg. When there is an update to the Customer
table, record the changes in Customer_Audit.
2. On an update to a row, check which fields were updated. Then in a
common audit table record the table, row ID, field, previous value and
new value of the field.
I'm wondering about the pros and cons of each. More specifically, do
the pros and cons change if you are using an O/R mapper (I'm using
NHibernate.)
Some thoughts on method 1 . It seems nice for an O/R mapper, since you
could have, say, a CustomerAudit Object inherit from your Customer
object and just add the properties change date and changed by. A
problem with this is you're going to add a whole lot of objects - one
for each object which you want to audit. Another drawback is that it
could be difficult to generate a history for a particular property
which was updated. Let's say I want to see the history of changes to
the customer's status. I have to load a collection of CustomerAudit
objects (which could be costly). Then I have to iterate through them
and compare the status properties to generate a history of statuses.
This is a pretty labor instensive method if you compare it to method 2,
where the change is recorded by field, not row.
Some thoughts on method 2. It's nice since the changes are by field,
not row, which (as above) makes generating a history easy. On the other
hand, you can never have a snapshot of a particular object at a
particular point in time. Moreover, I'm not sure how foreign keys would
be handled elegantly. I record that customer.statusID changes from 3 to
6. I'd have to do a seperate join to the customerstatus table to get
meaning for 3 and 6 (which method 1 would do automatically).
Thoughts? Any preferred way to do this with an O/R mapper?
A few days ago I saw a article on the internet (i don't know where), which described a way for auditing all database activities (like deleting records per user etc.) Unfortunally I can't find this document. Can anyone help me with this?
We looking to audit tables in the database. In the tables auditing needs to be done only on few columns not all the columns. Is there any 3rd party tool available to do it can it auditing be done on columns only with sql server 2005. Trigger doesnt work in this scenario as it works per row it does not say which column in the row was updated/inserted/deleted.
For example in the below employee table, I want to audit(history) only ssn and dob columns - all the updates/inserts/deletes made to this column should be audited.
I have a table and the data in this table (for no rhyme or reason)is being deleted somehow. I'm looking for suggestions on how to audit this table and find out who or what process could be deleting my data.
Hi, i need to set up some security standands in sql 6.5/7 . These includes auditing login attempts success and failure, access to db objects etc. I know sql is has very limited capabilities . can anyone tell me how to implement this without using event viewer etc??
Hi folks. I am a new DBA and working on taking over several existing MSSQL 6.5 applications.
My company needs to be able to audit the following actions on MSSQL.
login logout insert into specific tables update in specific tables delete from specific tables
I am looking for a 3rd party product that provides these services for SQL 6.5. I have found Braintree products and they have really nice auditing but for Oracle and not MSSQL. I have also looking into using triggers to build an audit log for me but the applications we are using require periodic updates that would force me to rebuild the triggers. This may cause too much administrativia.
I need some help with this. I have a development server and all the developers log in as sa. Lately test data has been mysteriously deleted from selected tables. I need to track the time that the activity is taking place so I can figure out who might be playing this little game. Can anyone suggest what I can do to find this out? Your quick response will be appreciated.
Someone had changed the SA password on one of my servers. I need to find out who did this. Can you tell me if there is any historical information kept on any of the system tables that can tell me who (what machine name) and when (date and time)this was done? Does anyone have a 3rd party or inhouse developed task/procedure to report this kind of security issues?
hey all, i found this auditing trigger, currently it just kicks out what was changed and when, id like to add who cause the trigger to fire as well (currently its just set to the "inventory" table). check under the "add the audit fields" comment for info:
CREATE TRIGGER TRG_inventory ON [DBO].[inventory] FOR DELETE,INSERT,UPDATE AS
DECLARE @ACT CHAR(6) DECLARE @DEL BIT DECLARE @INS BIT DECLARE @SQLSTRING VARCHAR(2000)
SET @DEL = 0 SET @INS = 0
IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1 IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1
IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE' IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT' IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'
IF @INS = 0 AND @DEL = 0 RETURN
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[AUDIT_inventory]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1) BEGIN -- CREATE A MEMORY TABLE CONTAINING THE FIELDS AND TYPES OF THE TABLE DECLARE @MEMTABLE TABLE ( ID INT IDENTITY ,COLUMNAME SYSNAME ,TYPENAME VARCHAR(20) ) -- INSERT THE COLUMNAMES AND THE DATATYPES INSERT @MEMTABLE (COLUMNAME,TYPENAME) SELECT NAME,TYPE_NAME(XTYPE) FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[DBO].[inventory]') ORDER BY COLID
DECLARE @CUR INTEGER DECLARE @MAX INTEGER DECLARE @SQLSTR AS VARCHAR(8000) DECLARE @CURCOL SYSNAME DECLARE @COLTYPE AS VARCHAR(10)
-- SETUP VARIABLES SET @SQLSTR = '' SET @CUR=1 SELECT @MAX = MAX(ID) FROM @MEMTABLE
-- LOOP EVEY FIELD WHILE @CUR <= @MAX BEGIN
-- GET VALUES FROM THE MEMTABLE SELECT @CURCOL = COLUMNAME,@COLTYPE = TYPENAME FROM @MEMTABLE WHERE ID = @CUR IF @COLTYPE = 'INT' OR @COLTYPE = 'BIGINT' OR @COLTYPE='UNIQUEIDENTIFIER'
-- WE DO WANT TO COPY INT/BIGINT/UNIQUEIDENTIFIER FIELDS BUT IF THEY ARE AN -- IDENTITY OR A ROWGUIDCOLUMN WE DO NOT WANT TO COPY THOSE ATTRIBUTES
SET @SQLSTR = @SQLSTR + ' CAST('+@CURCOL + ' AS '+@COLTYPE+') AS [' + @CURCOL +'] ' ELSE
-- ANOTHER FIELD DO NOTHING JUST COPY IT AS IT IS SET @SQLSTR = @SQLSTR + ' '+@CURCOL + ' AS [' + @CURCOL +'] ' IF @CUR <= @MAX - 1 SET @SQLSTR=@SQLSTR + ',' SET @CUR = @CUR + 1 END
-- ADD THE AUDIT FIELDS SET @SQLSTR = @SQLSTR +',CAST('' '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE ' - tryin to add who made the update here, figure out what GETUSER translates to
-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_inventory] FROM [DBO].[inventory]' EXEC(@SQLSTR) END
IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_inventory] SELECT *,'INSERT' ,GETDATE() FROM INSERTED IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_inventory] SELECT *,'DELETE' ,GETDATE() FROM DELETED IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_inventory] SELECT *,'UPDATE' ,GETDATE() FROM INSERTED
Hello I am more of a reporting person, recently I was asked to create stored procedure for an upcoming ASP.NET application. We have a problem that we are facing and any suggestion would be very helpfull.
The problem is that we have like 8 differtent tables each with 10 to 15 columns in it. The front end application has pages with save , update , delete button which are insert,update,delete for each of the 8 tables i.e they save , update , delete these 8 tables
They want to a way to Update , this audit table which stores information like ---
Date User Table Column name Previous column name new column name
So for each row that was updated in those 8 tables , each column will have the above fields updated as a row of data
Initially we thought about triggers but it will be like 60 triggers ...Is there a better or other way of handling this?
I want to log all changes made to a table (only updates, since there will be no deletes or inserts).
I would like to see the user who changed it, the date and time, fieldname, old value, new value. If more fields are changed during the update, than add more records into the logging table.
I would like to create a simple trigger on a Customer table to fullfill two fields, on should be filled when a new customer is inserted (DateAdded) and the other when the customer is edited (DateEdited), Yes I know it is quite simple but as I am not a database expert I do not know how to solve this problem.
I am to try to find documentation about C2 auditing into SQL Server 2005. The only one doc I fand about it is: SQL 2000 C2 Admin and User Guide It cover SQL Server 2000 and Windows NT4.0.
I do not find any document who cover the security from SQL Server 2005.
I'm wanting to do some auditing with sql 2k and wish to get the users first and last name of the windows account to log into a table. You can easily access the windows account name by using the System_User keyword. However, is it possible to get the first and last name of the system_user? If so, how?
I am using the SCD Wizard and it is working nicely. Can someone point be to an article/tuorial that would explain how you could create an "audit trail" on the items that may have been changed (type I and II)?
Basically, what I want to be able to do is run a query that tells me what data may have changed. I figured I would have to create an auditkey field in my table which would then link the key to the change detail?
I am trying to write a t-sql query that will work on sql server 2000/2005 that will tell me if auditing is turned on and what it is set to. Have any of you ever come accross a script or SP that can do this? -Kyle
we have database and sql login TESTUSER that all front end application (VB6) user as sql user to be able to communicate with DB. we have around 30+ users machine that is running that front end application.....!
how would i do auditing (who change what data and when, etc, etc) on each user action if needed. for that do i need 30+ unique users for each machine.
Does anyone have a query to determine if auditing is turned on and what it is set to (It needs to be set to failed logins)? Also where the log directory is going? I need the query to work on both SQL Server 2000 and 2005 servers. Any help is appreciated. -Kyle
We are finding ourselves editing data within a sql database using tools such as MS Query, Access or VB. Is there anyway to log these edits? Auditing is set up within the application to log changes made by the users but not by third pary applications. ANy thoughts?
Hi, I need to audit any access to table (example table creditcards) profiler work fine if table creditcards referenced by select/ upadate/delete or procedure
but if I create view
create v_alex as select * from creditcards
profiler with all events would show only v_alex
Application modifing and number of views growing ...
Any way to trace accees to creditcards if it referenced by view ?
I am trying to create a sql script that will check the database instance for any new objects that are created in any database on my system. These objects I want to audit are users, tables, databases, stored procs, etc.. I also want the script to email me and write the information to a text log file. Thanks in advance for any help.
Hi, I need to write some T-SQL scripts to perform a database audit of several SQL Server 2000 databases that tracks all superuser logins and access to tables. I can do this in Oracle but I am lost with MS SQL Server. Can anyone point me in the right direction? Thanks!
Is there any RDBMS concept ,If i do any DML operations in any of table i need to know how many rows are inserted/updated/deleted in particular table. I dont want write any trigger to get those information. because the table count nearly 142.
has anybody investigated the cost of turning on AUDITING for the SQL servers? I am talking about enabling the entire C2 Security Audit mode. How much impact the auditing has on the database performance?
Is auditing for SQL 2005 any better, meaning less impact on performance?
Any sample, test, and/or numbers to support the arguments?
Please share any findings that you have or know. Thanks
I have more than 1100 tables in my databse. From existing application data will insert into Database tables. I need to track the tables module wise when data inserting/Updating into these tables.
One way is, I have to write the triggers for each table[1100 tables with auditing]. In my case this is not possible to write like that.
Is thesre any other solution to find the updated,inserted tables when data is changing from application without doing bulk changes.
We have about 50 SQL Servers (most 2000 but some 2005) in different domains and behind firewalls and we implementing the PCI rules for the use of credit card data. One thing we need to do is to audit users and their rights. I have a stored proc that can run each night and record users and permissions.
My main problem is trying to bring all that data together on a central box and then parse through it to see if I have any offenders. The parsing is not the issue, but the polling is. I could use the SA account or create a audit account with SA rights but both of those solutions go against the PCI mantra.
Can someone who has done this give me some guidance as to what you did. I know that there are some third party tools that would probably do this, but my department is on a shallow budget and my overtime is free.
Any suggestions would be greatly appreciated. Thank you!
