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

learn more… | top users | synonyms (4)

29
votes
7answers
1k 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 ...
22
votes
3answers
52k 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 ...
16
votes
4answers
18k views

What is an audit table?

What are audit tables? How are they useful? I came across them reading this article.
10
votes
4answers
3k 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 ...
10
votes
4answers
13k 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 ...
9
votes
3answers
1k 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 ...
9
votes
1answer
171 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 ...
8
votes
4answers
307 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 ...
7
votes
3answers
2k 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
3answers
5k 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 ...
5
votes
7answers
499 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
1answer
164 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
299 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
168 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
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
3answers
416 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 ...
4
votes
2answers
3k 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 ...
4
votes
2answers
788 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
32 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
2answers
86 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 ...
4
votes
2answers
820 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, ...
4
votes
1answer
119 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 ...
4
votes
1answer
683 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
1k 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 ...
3
votes
6answers
6k 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
635 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
234 views

How to capture sp_refreshsqlmodule event

My issue: executing sp_refreshsqlmodule will update the sys.objects.modify_date value, but does not fire a DDL event that can be used by a DDL trigger. So if using a DDL trigger to show object change ...
3
votes
1answer
342 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 ...
3
votes
1answer
646 views

How to reuse an update trigger for multiple tables in postgresql?

In the database for a new project, I've started the convention of having each table have a last modified timestamp column. To implement this, I wrote a trigger for each table: CREATE TRIGGER ...
3
votes
1answer
316 views

Can this be accomplished with Oracles Fine Grained Auditing?

We need to audit when a table is accessed (either select, update or delete) and I know Oracles Fine Grained Auditing can handle that. The issue we are having is that all of our users use the same ...
3
votes
1answer
65 views

SQL Server 2016 Audit Options

Often law or organization policies require an application to track changes to certain table rows in a database. The changed data is often also viewable through some application page. Some common ...
3
votes
1answer
360 views

How to get the daily amount of committed transactions on a given MSSQL database?

The number of transactions for the current connection can be obtained with @@TRANCOUNT. How can you get a daily counter of the globally committed transactions on a given database?
3
votes
1answer
3k 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 ...
3
votes
0answers
120 views

How can I generated a list of all DB2 Objects including Last Modified Date of object on AS400? [closed]

I am going through an audit, of an AS400 application. AS400 has a DB2 database, and I need to generate a list of all DB2 Objects/Tables including their last modified date. Can anyone help me? Thanks
3
votes
0answers
236 views

Exclude a User From an Audit [duplicate]

I need to set up a SQL Server Audit to capture all database activity for all logins except one - the login our application runs under. In this case the login I want to exclude is CPSDOM\cpointedb. In ...
3
votes
0answers
1k 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
0answers
499 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 ...
3
votes
3answers
538 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 ...
2
votes
2answers
130 views

Who changed the size of the transaction log?

Can I know, who and when changed size of database transation log?
2
votes
1answer
70 views

Auditing/Logging Help

I'm working on a request to start logging some additional data for PCI compliance. I need to log who, when, and what changed on a set of tables. I set up SQL auditing which would work fine except ...
2
votes
2answers
12k views

Trigger to audit table changes

I have created a trigger to audit changes to a table: CREATE TRIGGER [dbo].[iudt_AutoAuditChanges] ON dbo.CPTCategoryMaster AFTER INSERT,DELETE,UPDATE AS BEGIN SET NOCOUNT ON; ...
2
votes
2answers
2k views

Is it possible to monitor role and privilege grant/revoke using triggers?

Is it possible to monitor role and privilege grant/revoke using triggers? I' aware of doing that using Oracle audit tools however, it's interesting is it possible to do that using triggers.
2
votes
4answers
15k views

ORA-09925: Unable to create audit trail file

I'm using Oracle 11.1.0.6.0 db, on Linux 64 bit server on Amazon cloud. I'm getting following error when I run the connect command SQL> connect / as sysdba ERROR: ORA-09925: Unable to create ...
2
votes
3answers
2k views

What are the best practices for triggers to maintain a revision number on records?

I have an audit trail implemented on an application database that captures old value, new value, modification time, and user inserting it into another table. I'm now wanting to add a revision number ...
2
votes
2answers
147 views

Logging only mySQL logins on heavily used system

I have an audit requirement where they want all database access (logins) logged. We have a very large distributed system of hundreds of mySQL servers (shards, slaves, slaves of slaves) that are ...
2
votes
1answer
366 views

Reconciling dm_os_windows_info windows_sku values, gathering same info from pre SQL Server 2008 R2 instances

I'm developing an inventory system for RDBMS deployments. I'm starting with SQL Server, and later will be pulling in info for Oracle & other RDBMS deployments. When you query [dm_os_windows_info] ...
2
votes
1answer
53 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,
2
votes
1answer
137 views

Disable Oracle auditing for a particular user?

I have one application user in my Oracle 11g DB which does logins/logouts very often. Since this user is not a human, but an application I would like to exclude the login and logout actions of this ...
2
votes
1answer
92 views

Investigating specific SELECT queries

For a particular row in a table how can I determine the number of reads done to it such as SELECT * FROM MyTable WHERE ID = <something> and when and where the read query executed? I'm ...
2
votes
2answers
397 views

How do I maintain more audit history?

I created a SQL Server audit and when I view the audit logs I only see today’s audit. I am saving it to a file and I want to store audit for one year. Is there a way to store database and server audit ...