LogRhythm Audit Data Generation was implemented to meet Common Criteria requirements to generate audit records of auditable events. The LogRhythm Audit Generation makes use of a SQL Server audit trace that is output by SQL Server in the form of trace files. These trace files contain the audit data. The SQL Server trace captures all required audit events and produces trace files (.trc) at a configurable location.
Required Scripts for Audit Data Generation
The following scripts are required to implement the audit settings. These scripts are available under Documentation & Downloads on the LogRhythm Community. Click the SIEM tab, select the downloads page for your release, and then scroll to the Common Criteria Audit Scripts section.
- LogRhythm_Audit.sql. Creates a stored procedure in the master database called LogRhythm_Audit. When this stored procedure is executed the audit trace is defined, the audit events are defined, the audit filters are defined, and the audit trace is started.
- LogRhythm_Audit_Select.sql. Creates a stored procedure preferably in the LogRhythmEMDB database that allows for filtered reporting on a trace file or set of trace files. The LogRhythm_Audit_Select stored procedure must reside in the LogRhythmEMDB on Platform Managers.
The LogRhythm_Audit stored procedure configures the audit trace to record events to the audit trace files. The table below enumerates all event types that can be monitored for and recorded in the audit trace. Those highlighted are enabled in the default configuration to provide the minimal level of auditing to comply with the Security Functional Requirements for Common Criteria. The trace can be altered to include other event types as well as filter on any of the reporting fields recorded in the audit trace.
|14||Audit Login||Security Audit||Indicates that a user has successfully logged into SQL Server.|
|15||Audit Logout||Security Audit||Indicates that a user has logged out of SQL Server.|
|18||Audit Server Starts And Stops||Security Audit||Indicates that the SQL Server service state has been modified.|
|20||Audit Login Failed||Security Audit||Indicates that a user attempted to log in to SQL Server and failed.|
|82||LogRhythm Defined : Data Modified||Security Audit||Indicates that data has been INSERTED, UPDATED, or DELETED from a LogRhythm database table|
|102||Audit Database Scope GDR Event||Security Audit||Indicates that a GRANT, REVOKE, or DENY has been issued for a statement permission by a user in Microsoft SQL Server.|
|103||Audit Schema Object GDR Event||Security Audit||Indicates that a GRANT, REVOKE, or DENY was issued for a schema object permission by a user in SQL Server.|
|104||Audit Add login Event||Security Audit||Indicates that a login has been added or removed.|
|105||Audit Login GDR Event||Security Audit||Indicates that a Microsoft Windows login right was added or removed.|
|106||Audit Login Change Property Event||Security Audit||Indicates that sp_defaultdb, sp_defaultlanguage, or ALTER LOGIN was used to modify a property of a login.|
|107||Audit Login Change Password Event||Security Audit||Indicates that a user has changed their SQL Server login password.|
|108||Audit Add Login to Server Role Event||Security Audit||Indicates that a login was added or removed from a fixed server role.|
|109||Audit Add DB User Event||Security Audit||Indicates that a login has been added or removed as a database user to a database.|
|110||Audit Add Member to DB Role Event||Security Audit||Indicates that a login has been added to or removed from a role.|
|111||Audit Add Role Event||Security Audit||Indicates that a database role was added to or removed from a database.|
|112||Audit App Role Change Password Event||Security Audit||Indicates that a password has been changed for an application role.|
|113||Audit Statement Permission Event||Security Audit||Indicates that statement permission has been used.|
|114||Audit Schema Object Access Event Class||Security Audit||Indicates that object permission (such as SELECT) has been used.|
|115||Audit Backup/Restore Event||Security Audit||Indicates that a backup or restore statement has been issued.|
|116||Audit DBCC Event||Security Audit||Indicates that a DBCC command has been issued.|
|117||Audit Change Audit Event||Security Audit||Indicates that an audit trace modification has been made.|
|118||Audit Object Derived Permission Event||Security Audit||Indicates that a CREATE, ALTER, or DROP was issued for an object.|
|128||Audit Database Management Event||Security Audit||Indicates that a database has been created, altered, or dropped.|
|129||Audit Database Object Management Event||Security Audit||Indicates that a CREATE, ALTER, or DROP statement was executed on a database object.|
|130||Audit Database Principal Management Event||Security Audit||Indicates that principals have been created, altered, or dropped from a database.|
|131||Audit Schema Object Management Event||Security Audit||Indicates that a server object has been created, altered, or dropped.|
|132||Audit Server Principal Impersonation Event||Security Audit||Indicates that an impersonation has occurred within the server scope.|
|133||Audit Database Principal Impersonation Event||Security Audit||Indicates that an impersonation has occurred within the database scope.|
|134||Audit Server Object Take Ownership Event||Security Audit||Indicates that a server object owner has changed.|
|135||Audit Database Object Take Ownership Event||Security Audit||Indicates that there has been a change of owner for objects in database scope.|
|152||Audit Change Database Owner||Security Audit||Indicates that the permissions to change the owner of a database have been checked.|
|153||Audit Schema Object Take Ownership Event||Security Audit||Indicates that the permissions to change the owner of schema object have been checked.|
|154||Audit Database Mirroring Login||Security Audit||Indicates events to report audit messages related to database mirroring transport security|
|158||Audit Broker Conversation Event||Security Audit||Indicates events to report audit messages related to Service Broker dialog security|
|159||Audit Broker Login||Security Audit||Indicates events to report audit messages related to Service Broker transport security|
|170||Audit Server Scope GDR Event||Security Audit||Indicates that a GDR event has occurred for server permissions.|
|171||Audit Server Object GDR Event||Security Audit||Indicates that a GDR event for a schema object has occurred.|
|172||Audit Database Object GDR Event||Security Audit||Audit Database Object GDR Event Class|
|173||Audit Server Operation Event||Security Audit||Indicates that Audit operations have occurred in the server.|
|175||Audit Server Alter Trace Event||Security Audit||Indicates that the ALTER TRACE permission has been checked.|
|176||Audit Server Object Management Event||Security Audit||Indicates that a CREATE, ALTER, or DROP event has occurred for a server object.|
|177||Audit Server Principal Management Event||Security Audit||Indicates that a CREATE, ALTER, or DROP has occurred for a server principal.|
|178||Audit Database Operation Event||Security Audit||Indicates that various operations such as Check Point or subscribe query notification have occurred.|
|180||Audit Database Object Access Event Class||Security Audit||Indicates that a database object, such as a schema, has been accessed.|
The following triggers are built into the EMDB and are required to implement the audit settings.
- This stored procedure builds all necessary triggers and shadow tables to set up the Enhanced Database Auditing functionality
- Upon initial execution of this stored procedure, the contents of the following tables are evaluated to determine the audit policy to be used:
- AuditTableExclusion - If any table names are defined in this table, the audit policy will be an inclusion by default (Blacklist) policy (default)
- AuditTableInclusion - If any table names are defined in this table, the audit policy will be an exclusion by default (Whitelist) policy
- This table takes precedent over AuditTableExclusion
- If the same table name appears in both the AuditTableInclusion and AuditTableExclusion tables, that table will be included in the audit policy
- AuditLoginExclusion - Any database credentials present in the AuditLoginExclusion table in the LogRhythmEMDB database will not generate audit records
- By default, if no records are added to any of the tables listed above, ALL tables in the LogRhythmEMDB will be audited using triggers and shadow tables set up by the stored procedure
- By default, the LogRhythm service accounts are added to the AuditLoginExclusion table
- This is to prevent service accounts that frequently authenticate from various component services from generating audit events
- To use an exclusion by default (Whitelist) policy, prior to executing the stored procedure mentioned above, populate the AuditTableInclusion table in the LogRhythmEMDB database with table names to be audited
- This feature can be turned off and all data removed by running the stored procedure.
- Allows a user to query raw audit data from a shadow table.
- Allows a user to query a history of the row state at various points in time.