XML FeedsContinuing onwards with the SQL Server auditing feature, let's start off by creating a simple audit that will capture some database level events.
Previously we looked at how to create a Server Audit Specification and creating a Database Specification follows the same steps. The main difference is the location, as well as the objects to capture. In the following example we will look at capturing delete statements on a database.
It is also important to note, that you cannot audit across databases, you will need to set up a Database Specification and its corresponding Audit per database.
Creating the Audit
We follow the same steps as previously by creating the Audit file:
Expand your instance >> Security >> Right-Click the audit folder >> Select New Audit
This should bring up a new window similar to this:
Creating the Specification
To Create a Database Specification you can find it under:
Database >> Security >> Database Audit Specification
The first configuration I have for the Database Specification, I am capturing on a database level, only deletes, and only when executed by user belonging to the db_datawriter role.
I executed the following query on my account which is sysadmin:
- USE testdb;
- GO
- DELETE FROM tab1 WHERE id BETWEEN 9990 AND 10000;
- GO
- DELETE FROM tab2 WHERE id BETWEEN 5000 AND 5100;
- GO
- DELETE FROM tab3 WHERE id BETWEEN 2000 AND 2002;
- GO
This resulted in no deletes being captured:
When executed as test_writer, which belongs to the db_datawriter role and I execute the following:
- USE testdb;
- GO
- DELETE FROM tab1 WHERE id BETWEEN 9900 AND 9990;
- GO
- DELETE FROM tab2 WHERE id BETWEEN 4500 AND 4990;
- GO
- DELETE FROM tab3 WHERE id BETWEEN 2500 AND 2560;
- GO
I receive the following results:
One thing to note on the output is under "audit_file_offset". All three values are 6144, which indicates that the three statements where executed in the same query.
As with the Server Audits, use the following query to retrieve the audit information as it is displayed above:
- SELECT event_time ,
- action_id ,
- succeeded ,
- session_id ,
- server_principal_name ,
- server_instance_name ,
- database_name ,
- [statement] ,
- audit_file_offset
- FROM fn_get_audit_file('E:\SQLAuditing\DBAudit*.sqlaudit',
- DEFAULT, DEFAULT)
The second example is just to audit a specific table:
Note the areas in the red squares, it changes to audit objects, then a specific table, and lastly to which principle it should be bound and again in this case it will be db_datawriter.
Again, I execute a delete statement:
- USE testdb;
- GO
- DELETE FROM tab1 WHERE id BETWEEN 1 AND 100;
- GO
- DELETE FROM tab2 WHERE id BETWEEN 400 AND 6000;
- GO
- DELETE FROM tab3 WHERE id BETWEEN 7000 AND 7050;
- GO
Inspecting the statement column, we can see that it only captured the delete statement that was executed against tab1:
To create the users, database, and populate it with the data I used - run the following:
- /*Creates the database and the logins*/
- USE [master]
- GO
- IF EXISTS(SELECT NAME FROM master.sys.server_principals WHERE name = 'testuser')
- DROP LOGIN testuser;
- IF EXISTS(SELECT NAME FROM master.sys.server_principals WHERE name = 'test_writer')
- DROP LOGIN test_writer;
- IF EXISTS(SELECT NAME FROM master.sys.databases WHERE name = 'testdb')
- DROP DATABASE testdb;
- GO
- CREATE LOGIN [testuser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
- CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
- GO
- ALTER LOGIN [testuser] ENABLE
- GO
- CREATE LOGIN [test_writer] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
- CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
- GO
- ALTER LOGIN [test_writer] ENABLE
- GO
- CREATE DATABASE [testdb] ;
- GO
- /* Grants the access for the logins */
- USE [testdb] ;
- GO
- CREATE USER [testuser] FOR LOGIN [testuser] WITH DEFAULT_SCHEMA=[dbo]
- GO
- CREATE USER [test_writer] FOR LOGIN [test_writer] WITH DEFAULT_SCHEMA=[dbo]
- GO
- EXEC sp_addrolemember N'db_owner', N'testuser' ;
- EXEC sp_addrolemember N'db_datawriter', N'test_writer' ;
- GO
- /*Creates the test tables */
- CREATE TABLE [dbo].[tab1]
- (
- [id] [int] IDENTITY(1, 1)
- NOT NULL ,
- [comment] [varchar](10) NULL ,
- [tabdate] [date] NULL
- )
- ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[tab2]
- (
- [id] [int] IDENTITY(1, 1)
- NOT NULL ,
- [comment] [varchar](10) NULL ,
- [tabdate] [date] NULL
- )
- ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[tab3]
- (
- [id] [int] IDENTITY(1, 1)
- NOT NULL ,
- [comment] [varchar](10) NULL ,
- [tabdate] [date] NULL
- )
- ON [PRIMARY]
- GO
- USE [master]
- GO
- ALTER DATABASE [testdb] SET READ_WRITE
- GO
- /* Random test data */
- USE testdb ;
- GO
- INSERT INTO tab1
- ( comment, tabdate )
- VALUES ( 'abcdefg', GETDATE() )
- GO 10000
- INSERT INTO tab2
- SELECT comment ,
- tabdate
- FROM TAB1 ;
- GO
- INSERT INTO tab3
- SELECT comment ,
- tabdate
- FROM TAB1 ;
- GO
Final Thoughts
Just based on auditing for delete statements on database can prove to be very dynamic when using the auditing feature. It doesn't even stop here, as this was only a demo, there are a lot more you can audit like stored procedures, functions, views, etc. This is a powerful feature especially for monitoring critical and highly sensitive databases.
To see what else you can audit with the Database Specifications go here and skip down to Database-Level Audit Action Groups.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.