Database Activities Tracking
DB Audit Expert can audit many activities. The collected data is vital for monitoring and enforcing database access and usage policies. The following activities can be audited:
- Login (failed) – Indicates that a login attempt to SQL Server from a client failed.
- Login (successful) – Occurs when a user successfully logs in to SQL Server.
- Logout – Occurs when a user logs out of SQL Server.
- Error – Indicates that error events have been logged in the SQL Server error log.
- Exception – Indicates that an exception has occurred in SQL Server.
- Privileged option – Occurs when a statement permission (such as CREATE TABLE) is used.
- Schema Object Access – Occurs when an object permission (such as SELECT, UPDATE, EXECUTE) is used, both successfully or unsuccessfully.
- Schema Object Derived Permission – Occurs when a CREATE, ALTER, and DROP object commands are issued.
- Backup/Restore – Occurs when a BACKUP or RESTORE command is issued.
- DBCC – Occurs when DBCC commands are issued.
- Grant/Deny/Revoke Privilege – Occurs every time a GRANT, DENY, REVOKE for a statement permission is issued by any user in SQL Server.
- Grant/Deny/Revoke Object Access – Occurs every time a GRANT, DENY, REVOKE for an object permission is issued by any user in SQL Server.
- Grant/Deny/Revoke Logins – Occurs when a Microsoft Windows login right is added or removed; for sp_grantlogin, sp_revokelogin, and sp_denylogin.procedures.
- Login Properties Change – Occurs when a property of a login, except passwords, is modified; for sp_defaultdb and sp_defaultlanguage procedures.
- Login Password Change – Occurs when a SQL Server login password is changed. Passwords are not recorded.
- Role Password Change – Occurs when a password of an application role is changed.
- Add / Drop Login – Occurs when a SQL Server login is added or removed; for sp_addlogin and sp_droplogin procedures.
- Add Login to Server Role – Occurs when a login is added or removed from a fixed server role; for sp_addsrvrolemember, and sp_dropsrvrolemember procedures.
- Add Database User – Occurs when a login is added or removed as a database user (Windows or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess, sp_adduser, and sp_dropuser procedures.
- Add/Drop Role Member – Occurs when a login is added or removed as a database user (fixed or user-defined) to a database; for sp_addrolemember, sp_droprolemember, and sp_changegroup procedures.
- Add/Drop Role – Occurs when a login is added or removed as a database user to a database; for sp_addrole and sp_droprole procedures.
- Tracing Settings Change – Occurs when tracing modifications are made using SQL profiler or stored procedure.
DBAs can choose only the activities that they need to audit. The list of activities can be customized using the Select Operations dialog. The auditing scope can be set to all, failed, or successful activities only.
In addition, users have the flexibility to limit the system audit to the parameters indicated by the following dialog box. From the screen below, users can filter the system audit based on databases, logins, hosts, applications, etc. This will allow users to audit specified events rather than auditing all the system events.
In real-time, DB Audit writes all selected system audit events into the SYS_AUDIT_TRAIL table, which is automatically created in the user selected database. Audit trail data in this table can be analyzed and monitored using many built-in reports and alerts. Because the SYS_AUDIT_TRAIL is a regular database table, users have the flexibility to develop custom reports using any reporting or data analysis tools of their choice. For example, to find out all failed SELECT statements for table CUSTOMERS, one could write a report based on the following query:
SELECT event_time, login_name, os_user, error_num, err_state, text_data
FROM audit.dbo.sysaudit_trail
WHERE success <> 0 AND obj_name = ‘customers’
Besides what we have already discussed, there is an archive option that can be used to archive the current audit data to other tables.