Questions tagged [audit]

Database auditing involves observing a database so as to be aware of the actions of database users.

Filter by
Sorted by
Tagged with
44
votes
9answers
5k views

How should deletions be handled in the database?

I'd like to implement an "undelete" feature in a web application such that a user can change her mind and recover a deleted record. Thoughts on how to implement this? Some options I've considered are ...
29
votes
4answers
93k views

How to find out who deleted some data SQL Server

My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters). I thought this could be ...
24
votes
4answers
42k views

What is an audit table?

What are audit tables? How are they useful? I came across them reading this article.
14
votes
2answers
38k views

Best way to design a database and table to keep records of changes?

I need to setup a history feature on a project to keep track of prior changes. Let's say I have two tables right now: NOTES TABLE (id, userid, submissionid, message) SUBMISSIONS TABLE (id, name, ...
14
votes
1answer
532 views

Can I tell when and who updated my database from v11 to v12 on Azure?

Earlier today I noticed that all my Azure SQL database servers where updated from v11 to v12. I am the only one in my company that should be touching those dials, but not the only person in my company ...
13
votes
5answers
5k views

eCommerce orders table. Save prices, or use an audit/history table?

Im designing my first eCommerce schema. I've been reading around the subject for a little while, and am a bit confused about the relationship between an order_line_item and a product A product can ...
12
votes
1answer
694 views

How to filter out Scalar Valued User-Defined Function usage from SQL Server Audit Data?

We have a SQL Server database which has a database audit specification which audits all execute actions on the database. CREATE DATABASE AUDIT SPECIFICATION [dbAudit] FOR SERVER AUDIT [servAudit] ADD ...
11
votes
3answers
15k views

Passing info on who deleted record onto a Delete trigger

In setting up an audit trail I have no problem tracking who is updating or inserting records in a table, however, tracking who deletes records seems more problematic. I can track Inserts/Updates by ...
11
votes
4answers
23k views

Audit logins on MySQL database

Is there a way to audit logins to MySQL? I'd like to be able to create a username for each employee and thereby create an audit trail of logins. However, googling has turned up no good results. The ...
11
votes
3answers
5k views

Find the identity of the client firing a query in SQL Server without using triggers?

I'm currently using Change Data Capture (CDC) to track data changes, and I wish to track the host name and IP address of the client submitting the query that made the changes. If there are 5 different ...
10
votes
1answer
56k views

how to get History of queries executed with username in SQL

I can get executed queries by using: SELECT deqs.last_execution_time AS [Time] ,dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ...
8
votes
4answers
329 views

What would I need to do to make sure my DB policies have to pass a security audit?

I have an audit coming up, and I was wondering what physical, electronic, and logical access controls an auditor would look for when auditing a database for an ERP system. I'm really new to this ...
8
votes
2answers
4k views

Extended events vs SQL Audit - performance implications

I would like to setup an audit trail kind of system on my database to monitor for UPDATE/INSERT statements on a specific table with very high activity. I have two options in front of me: using the SQL ...
8
votes
3answers
9k views

How to know the user doing a delete action on an audit table, when using a shared login?

Background information: I'm creating a collection of audit tables to keep track of updates and deletes to a set of data tables for my app. Audit records are created via triggers. DML in my app's ...
7
votes
2answers
3k views

Finding delete or update records

I have had three incidents in recent months where records in a table that have either been deleted or values updated to zero across a whole table. We have a team of four people who have the ...
6
votes
2answers
4k views

Track grant permission on tables

I'd like to see if there are any changes on the permissions granted for a specific table. For example, I don't want to have the login 'John' have the SELECT permission on the table Invoices. If ...
6
votes
1answer
6k views

SQL Server Audit Specification - filter DML by all but one user

I'm setting up a database audit. I'd like to capture any DML change that is issued by database user other than the service account related to the application. In short, this is to prove to our ...
5
votes
7answers
598 views

Policies RE database triggers in well-designed applications?

I've heard all kinds of horror stories around gremlins living in database triggers, and--worse--systems being brought down by the addition of a trigger that caused a chain of cascading ones. I'm ...
5
votes
2answers
6k views

Add columns to all tables in a database if the columns don't exist

I have to implement auditing in a database with many (50+) tables. I am looking to add the user name and the time when the user altered the record as columns to all tables. However, some tables have ...
5
votes
2answers
491 views

Alternative to triggers on system views for user auditing

Even writing the title I'm cringing at the idea I've tried this (but hey, I've learned something in the process). I've got a problem with developers running migration scripts on the database and in ...
5
votes
1answer
591 views

Oracle Audit: Why Logons are less than 0.1% of logoffs?

I see the following quantities of logins versus logoffs vs logoffs by cleanup in a database: SQL> select action_name, count(*) qty from Dba_audit_session group by action_name order by 1; 2 3 ...
5
votes
1answer
358 views

Auditing database administrator actions with SQL Server 2014 Standard

We have a requirement to audit database administrator access to customer databases - at a minimum, knowing which customer database they were accessing (and ideally why). Without upgrading to SQL ...
5
votes
1answer
392 views

sp_tables blocking my other statements. Who runs it?

In our application we sporadically experience a locking issue caused by the execution of an sp_tables statement. We use SQL Server 2012. In our code and queries in our stored procedures we don't make ...
5
votes
1answer
686 views

How to audit users connections via ODBC to all dbs in SQL Server 2008 R2

I am not a trained DBA, but I manage a software application that uses SQL Server for the back end. The applications manages "projects" and each project has its own database. Because of the limitation ...
5
votes
1answer
182 views

SQL Server 2005 not collecting logins in the log file

I am currently running SQL Server 2005 Standard Edition on a Windows 2003 server machine. I have gone to the properties of the server and confirmed that the Login Auditing is set to both failed and ...
4
votes
4answers
640 views

Consolidating a row of data, based on previous rows

I'm trying to build a history table out of an audit log (ultimately to build out a type 2 dimension table). Unfortunately, the audit log only records the specific fields being changes. Here's a ...
4
votes
2answers
417 views

Windows Storage Server, SQL Server, regular files — means to protect from insider threats?

This is my first post here, and I am by no means qualified as a Windows Server or Storage Server 2008 or SQL Server 2008 administrator. I am mostly a Unix guy, however tasked with a job to investigate ...
4
votes
3answers
3k views

Auditing specific database events

For auditing purposes, we have a requirement in which we have to enable Auditing for theses events: DBA Logins Changes made by DBAs LIKE INSERT, DELETE, UPDATE, ALTER (etc.) Changes made by our ...
4
votes
4answers
2k views

How does the QUEUE_DELAY option for Server Audits work

I am working through how SQL Server Audits work and would like some clarity on how the QUEUE_DELAY options works. BOL defines the options as: Determines the time, in milliseconds, that can elapse ...
4
votes
2answers
5k views

MySQL Script to automate creation of audit (history) tables and triggers?

For regulatory and fraud requirements, we need to record every change to most of the tables in the DB. The way we did this very successfully with a previous project is to have a copy of each table, ...
4
votes
1answer
7k views

I want to find out in SQL SERVER 2012, who is creating SQL Logins and on which date

I was trying to find out in sql server 2012, who created login and which date. Can any body help on this thanks,
4
votes
1answer
3k views

Is Service Broker the best choice for auditing data changes on SQL Server Express?

My project is to audit 5 to 10 existing tables in our system without prolonging transactions. Whatever method is used, it has to work on SQL Server Express 2005 to (eventually) 2016. I've done ...
4
votes
1answer
2k views

Track all modifications to a PostgreSQL table

We have a table which has only roughly 500 rows, but it is very important for us. I want see all changes which happen to this table. The changes should get tracked with a timestamp. I don't want the ...
4
votes
1answer
5k views

Stored Procedure Processing and Error Log

I used to work for a company where they had this 3rd party Data warehouse Solution. Obviously all the objects and tables were hidden in the Supporting database so I don't have clear idea on what ...
4
votes
2answers
1k views

Audit user queries on SQL Server 2005

I'm in an unpleasant situation where I have a requirement to audit any queries executed by users via SSMS, SQLCMD, etc. on a SQL Server 2005 database. The situation is made worse by the inability to ...
4
votes
1answer
3k views

Multiple Filters for Databases on SQL Server Server Level Audit

I am attempting to create a Server Level Audit to track Database Level events in SQL Server 2014. I know I can create them at the database level but I want the audits all databases at the server ...
4
votes
4answers
6k views

Can SQL Server Audit be used to track hostnames or IP adresses of the users performing DML on a table?

I have been reading about SQL Server Audit, and so far I have assimilated that it tracks the command that has been executed, and some other additional information(SPID, object ID and stuff). But can ...
4
votes
1answer
1k views

Auditing - parameter values not being written to log

I am using auditing to track usage of some procs in the database. In the log I can see a record for the Procedure being run and, immediately afterwards,the SQL statement that is being run. The ...
4
votes
1answer
1k views

Tracking object use using SQL Auditing

I am looking for a solution to track object usage in my database. Objects such as tables, stored procedures, and functions. I discovered SQL Auditing and was under the impression that it's exactly ...
4
votes
3answers
2k views

Get notified when SQL Server 2008 Audit Server logs something?

I'm setting up SQL Server 2008 Audit Server to log any activities made by certain group of users of certain database. All I need to do is get notified when the Audit server logs something, so that I ...
4
votes
3answers
284 views

Capturing the Login Failure alerts specifc to bad password attempts

I am unable to figure out the way to capture only those failed logins, which have failed with the error: Login failed for user X. The password did not match... Yes I can read that info out from ...
4
votes
1answer
5k views

Enabling/disabling/changing Oracle auditing without a shutdown?

I have a large database that needs auditing on a very detailed level (every select, update, insert, and delete, along with the actual text of the statement) for about half the users. I know how to do ...
4
votes
1answer
54 views

Hiding or masking sensitive information in the SQL Server audit log

I'm looking into auditing of who are accessing the data in all tables in a database. We do have the additional requirement that we in the log can't have any information that can be used to identify a ...
4
votes
0answers
1k views

Auditing access to a linked server

I have a linked server with SQL Server configured with the security option "For a login not defined in the above list, connections will: Be made using the login's current security context". What I ...
4
votes
0answers
2k views

Preferred Methods of Indexing MS SQL Server Audit Data to Splunk

What is your preferred method for indexing MS SQL Server for Splunk? I am collecting audit data in various environments by various means (profiler, app logs, and extended events) but I hope to ...
3
votes
6answers
9k views

Trigger Performance : Is Using Trigger is a good way?

We are using SQL Server 2008 for a j2ee web application. I need to maintains the audits for updates for each column with their previous value and new value on one of my table which is very heavy, ...
3
votes
4answers
882 views

Data Change Audit Plan

My company is changing from a distributed Access Database model to using a centralized SQL Database. The datatables were designed such that all of the tables have a modified date. In discussion it ...
3
votes
2answers
5k views

Sql Server - How to convert Audit log into a table?

I am using Sql Server 2014, Standard Edition. My objective is to trace the history of operations on the tables (insert, update, delete), and to display on the web so the users are able to tell what ...
3
votes
2answers
2k views

How to find who changed a table value in SQL Server 2014? [duplicate]

We have a few tables on a database called mydb which is being used by applications to store, or retrieve records. Somehow, someone changed a value on the test table and that caused a serious issue. ...
3
votes
2answers
20k views

How to list all past remote login information

Instead of using "Remote Desktop" for connecting to SQL Server, I and my co-workers are using SQL Server Management Studio to apply changes to the remote server (each user has their own credentials). ...