Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [audit]

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

Database auditing involves observing a database so as to be aware of the actions of database users. Database administrators and consultants often set up auditing for security purposes, for example, to ensure that those without the permission to access information do not access it.

Questions with the audit tag regards the process of auditing in one or more databases.

More to read: http://en.wikipedia.org/wiki/Database_audit

415 questions
46
votes
9 answers

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 actually deleting the record in question and…
Abie
  • 885
  • 1
  • 8
  • 9
32
votes
4 answers

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 found from the transaction log (providing it hadn't…
Matt Wilko
  • 422
  • 1
  • 4
  • 7
32
votes
4 answers

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 tracking to happen in the application code, since…
guettli
  • 1,591
  • 5
  • 25
  • 51
30
votes
4 answers

What is an audit table?

What are audit tables? How are they useful? I came across them reading this article.
Matt Fenwick
  • 1,338
  • 3
  • 12
  • 28
26
votes
3 answers

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, userid, filepath) Example: I have a row in notes and…
Schwarz
  • 375
  • 1
  • 4
  • 6
18
votes
2 answers

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 ORDER BY deqs.last_execution_time DESC but I am…
Hazell B
  • 183
  • 1
  • 1
  • 6
15
votes
3 answers

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 including in the Insert/Update the field…
webworm
  • 555
  • 3
  • 6
  • 14
15
votes
5 answers

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 been purchased. It has various details, but the most…
GWed
  • 519
  • 9
  • 24
14
votes
1 answer

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 with the access. Since I did not initiate this…
Erik
  • 4,833
  • 4
  • 28
  • 57
13
votes
5 answers

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 more we can audit, the better. At the very least,…
asbestossupply
  • 295
  • 1
  • 3
  • 6
12
votes
1 answer

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 (EXECUTE ON DATABASE::[DatabaseName] BY…
Mark Iannucci
  • 783
  • 6
  • 21
12
votes
3 answers

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 clients logged in via the same user name, one…
Ritesh Bhakre
  • 535
  • 1
  • 8
  • 18
9
votes
3 answers

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 database will generally come from a login that a…
Jeremy Pridemore
  • 193
  • 1
  • 5
9
votes
2 answers

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 Server built-in Audit system or go with Extended…
karun_r
  • 349
  • 2
  • 4
  • 15
8
votes
4 answers

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 process and any guidance would be appreciated.
Robert Silvie II
  • 83
  • 1
  • 5
1
2 3
…
27 28