Part 1 of this article explains and provides instructions on how to properly set ApexSQL Audit in order to cover implementation of the Subpart B § 11.10 (a), § 11.10 (b) and § 11.10 (c) requirements of the Title 21 CFR Part 11 FDA’s compliance regulations. In this part, the rest of the Title 21 CFR Part 11 Subpart B will be presented and processed in form that will help ApexSQL Audit users easy setup of ApexSQL Audit for each specific requirement, with a short explanation of each requirement itself as well as what particularly ApexSQL Audit can cover and how
The Title 21 CFR Part 11 Subpart B, Section 11.10 requirements that will be covered in this article:
§ 11.10 (d) – when applied to SQL Server this requirement means that a company is required to adopt procedures and policies that will ensure that access to SQL Server where electronic data is stored must be according to defined authorization policies, which also considers the complete control of user rights for accessing SQL Server and electronic data
§ 11.10 (e) – in relation with SQL Server compliance, this requirement mean that the established SQL Server auditing must ensure that every change of electronic data is logged and a timestamp of such change must be the part of the collected auditing event generated as a consequence of electronic data change. Each change must be collected and stored independently of previous information, i.e. changes of the same electronic data multiple times must be logged and stored in the repository each and every time.
§ 11.10 (f) – organizations must establish the ability to enforce and track that SQL Server changes are executed in the specific sequences and to ensure the ability to check the sequencing of events as appropriate
§ 11.10 (g) – any alteration or creation of user accounts in the SQL Server that stores the electronic data records must be audited and any unapproved change must be addressed as soon as possible or immediately upon detection, to ensure that only authorized persons can perform the necessary changes
ApexSQL Audit
§ 11.10 (d) – Limiting system access to authorized individuals, § 11.10 (g) Use of authority checks to ensure that only authorized individuals can use the system, electronically sign a record, access the operation or computer system input or output device, alter a record, or perform the operation at hand 1
The more individuals that have access rights to make changes or read data from SQL Server that hosts the electronic data records, the bigger the threat that the electronic data records will be changed or used maliciously. To avert any such use of electronic data records, but also to preclude the possibility of corruption of data that user inexperience or lack of knowledge could cause, any access to SQL Server that holds the electronic data records (in some situations at minimum to tables that stores the electronic data records), must be restricted only to persons who are authorized or required to have access to electronic data records. The guideline for SQL Server compliance here is transferring the minimum set of privileges to each and every person that must have access rights, just enough to permit accessing only the electronic data records in a way to perform the duties required from them. On the other hand, to meet the SQL Server compliance for the 7.1.2 requirement, or so called “role-based access control” (RBAC), which will restrict the access to the SQL Server with cardholder data, based on the predefined roles that users belong to.
To implement this requirement, ApexSQL Audit can help with SQL Server compliance by making it possible to establish obligatory auditing of all SQL Server logins and database user’s permissions level that already exist in SQL Server, but also for any newly created user
Besides applying the necessary SQL Server auditing via ApexSQL Audit (defining the auditing filter that complies to this requirement), implementing the alerts that will be triggered on any or specific events in relation to this requirement might be essential to ensure the timely reaction by the person in charge. For any deeper analysis of accidents of this kind, the appropriate reporting focused on roles and permission changes of SQL Server users is recommended
In accordance to that mentioned above, ApexSQL Audit filter definitions should allow SQL Server auditing of all user permission changes on SQL Servers that stores electronic data records
To use the simple auditing filter:
By ticking the operation to audit to, in the right pane, will apply the simple auditing filter for the instance
The following server level filter settings should be used to track the Server level user permission changes
For SQL Server auditing of database level users, add the databases that contain tables with electronic data records using Add database
To ensure the appropriate SQL Server auditing of database users permission, the database level auditing filter should be set according to the below image
If the advanced filter is to be used instead, the below filter conditions should be used
These filters should be defined for any SQL Servers that holds the electronic data records
The same filter condition should be used for alerts that will cover these changes
To properly documented when any such change occurs and to establish fast and easy reporting for changes required with this Title 21 CFR Part 11 requirement and achieving the SQL Server compliance, a custom report should be created and saved for further use. Such a report will ensure any change will be reviewed and analyzed. The report can be also used in determining how fast such changes are corrected and/or handled. It is possible to create one cumulative report for all SQL Servers that are audited, but a better practice is to define and save individual custom reports for each SQL Server separately.
And example of the custom report filter that should be set to cover this is as follows
As for tracking the access of unauthorized users to electronic data records, ApexSQL Audit helps in applying SQL Server compliance by using own capability to track any access or access attempt to SQL Server tables that holds electronic data records. Users not permitted to read and use in any way electronic data records can be tracked via auditing any SELECT/SELECT INTO statement executed against those tables. This also includes the ability for triggering the real time alerts when such event occurs
SQL Server auditing of SELECT and SELECT INTO statements executed, for the tables that stores electronic data records can be achieved using the simple filter. Although, this filter doesn’t permit excluding specific user from SQL Server auditing for these operations exclusively
To exclude certain users from SQL Server auditing for executing of select and select into statements against the specific tables (so called trusted users), but audited for other events, the advanced auditing filter is more appropriate option to be used
In this example, the advanced filter setting will permit SQL Server auditing for select or select into access on specified tables and for specified databases, but also to exclude users with permission to perform these operations. In particular example, ReChie, Void and MAKINA will not be audited for select and select into
The alert filter below makes possible alert to be triggered when any access to the databases specified in the filter and tables in these databases that contains the electronic data records by any user that is not the so called “trusted user” listed in the “Login name” condition
The same filter should be used for creating and saving custom reports, as the audited data should be regularly documented and reviewed or reported to FDA on request
Here is an example for a custom report that will be generated using the above filter
§ 11.10 (e) – Use of secure, computer-generated, time-stamped audit trails to independently record the date and time of operator entries and actions that create, modify, or delete electronic records. Record changes shall not obscure previously recorded information. Such audit trail documentation shall be retained for a period at least as long as that required for the subject electronic records and shall be available for agency review and copying 1
When transferred to a SQL Server compliance, this requires tracking of any electronic data changes that occurs in audited SQL Server. Unlike the § 11.10 (a) where complete record of electronic data values changes has to be recorded and stored, in this particular requirement, tracking of any DML changes of the required data including the timestamp when the change was executed have to be done. Depending of the specific guideline one or another SQL Server auditing method could be implemented. ApexSQL Audit can audit any DML activity where actual data values don’t have to be stored using the SQL Server trace and without need for using the CLR triggers. This also includes the ApexSQL Audit ability to alert on any such event in real time
To accomplish SQL Server compliance with this requirement via the Simple filter
Select the database in the left pane that should be tracked for the DML changes and in Operations section expand the DML filter node and choose Delete, Insert and Update
Set the same filter for all databases that stores the electronic data records. This way Auditing of all DML changes for databases where this filter is specified will be ensured. If it is required that auditing should be performed for specific tables that holds the electronic data records, then use Include objects filter of the Objects section to set only the specific tables for SQL Server auditing
The same SQL Server auditing can be achieved using the Advanced auditing filter
Select the SQL Server instance in the left pane and select the Advanced filter type radio button in the right pane
Based on the logical expressions, advanced filter will require only two conditions to achieve the filtering criteria equal to one used above in the simple filter
Regardless of the filter type used (simple or advanced), ApexSQL Audit real-time alerting can be set to inform the person in charge timely on any unexpected data change. Even the DML operations can be audited for all tables within the selected database if required, it is recommended for alerts to be created inly for the tables that hosts the sensitive electronic data records, where each and every manipulation with those is the potential issue and requires to be examined as soon as possible
First select the Alerting tab and then New from the Manage tab
Select Auditing alert (from the drop down menu)
The advanced filter example that will trigger the alerts for DML events that occurs on specified tables in specified databases
In order to prevent unnecessary alerting for changes made by trusted users, the filter should be expanded to include the condition for excluding the specific logins via “is not” operand, and it will look like this:
And finally, to accomplish this specific requirement with SQL Server, the necessary reporting of electronic data records changes have to be done. Using ApexSQL Audit custom reports ensure excluding all unnecessary data and concentrate attention on the specific set of events needed for review.
Using the same filter as displayed for the alert, the image below shows the example of report where the Update operation is shown
§ 11.10(f) – Use of operational system checks to enforce permitted sequencing of steps and events, as appropriate
Operational system checks are specific to organizations in general. Complying to this specific requirement, an organization must establish and enforce specific procedure and policies. The ability of ApexSQL Audit to track and collect all changes or access to electronic data records, including the precise time stamp for each specific audited event, coupled with the ability for precise custom reporting on a very specific change type or set of specific events will grant the ability for the person in charge re inspect and analyze the exact event sequence to ensure that the proper sequence order on executing operations/events is followed or not followed, and to take the necessary safety measures or corrections when required, which can be of crucial help in implementing SQL Server compliance with Title 21 CFR Part 11.
Regarding this specific requirement, deployment of ApexSQL Audit in such system can help and assist in establishing and enforcing the required procedures and policies for SQL Server compliance, but it is up to the organization when enforcing of these is in question and to configure ApexSQL Audit to its particular requirements
Footnote:
- 1 – As defined by the U.S. Food and Drug Administration
February 25, 2016
