Common Criteria Audit Data Generation

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.

Audited Events

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.

Stored Procedures

The following triggers are built into the EMDB and are required to implement the audit settings.

LogRhythm_EMDB_Audit_Build_All_Tables_Triggers
- 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
LogRhythm_EMDB_Audit_Drop_All_Tables_Trigger
- This feature can be turned off and all data removed by running the stored procedure.
LogRhythm_EMDB_Audit_Table_Select
- Allows a user to query raw audit data from a shadow table.
LogRhythm_EMDB_Audit_History
- Allows a user to query a history of the row state at various points in time.