Part 1 of this article described configuring ApexSQL Audit to meet PCI DSS standards that developed to accomplish and to enhance security of payment card data. In this part, the requirement sections 7-Restrict access to cardholder data by business need to know and 8-Identify and authenticate access to system components will be described.
The PCI DSS requirements that will be covered in this article include:
7.1 – The access to SQL Server, SQL Server components and cardholder data stored in SQL Server must be limited only to those with a significant business reason that requires such access. The following access limitations should apply:
7.1.1 – SQL Server access rights must be restricted to privileged SQL Server users to the least privileges necessary to perform job responsibilities. “Least privileges” mean that once access to SQL Server is granted to a user, it must be granted to the minimum amount of data and privileges (defined also as “need to know” in PCI DSS terminology) needed to perform the defined job
7.1.2 – The “role-based access control” or RBAC assignment of privileges to SQL Server users are based on an individual personnel’s job classification and function. Role based access control enforcement is not limited to any specific authorization solution. All role based solutions will be considered as viable solutions as long as they are appropriately configured to enforce the principles of “least privileges” and “need to know”
7.1.3 – Organizations are obligated to create a strict policy regarding the access to SQL Server and data and fully controlled access. Therefore, the clear mechanism has to be established that define how and who have the right to grant access and to whom
7.2.2 – Assignment of privileges to individuals need to be based on job classification and function for SQL Server and SQL Server components with multiple users
8.5 – Proper SQL Server user identification and authentication management must be enforced for non-consumer users and administrators on all SQL Server components. The following can be covered by ApexSQL Audit:
8.5.1 – A DBA must control addition, deletion, and modification of SQL Server users, their credentials, and other identifier objects
8.5.4 – Requires that the solution that is implemented allow tracking of all SQL Server security procedures to ensures that after an employee leaves organization his access to SQL Servers under PCI DSS regulations, is terminated
8.5.5 – It is mandatory to remove/disable inactive user accounts at least every 90 days
8.5.9 – Requires a DBA to review all password creation and changes for SQL Server logins without exception
8.5.11 – Requires from the DBA that any password used for SQL Server login must follow the enforced password policy
7.1 Access to system components and cardholder data must be limited to only those individuals whose job requires such access. Access limitations must include the following:
7.1.1 Restriction of access rights to privileged user IDs to the least privileges necessary to perform job responsibilities, 7.1.2 Assignment of privileges is based on individual personnel’s job classification and function, 7.2.2 Assignment of privileges to individuals based on job classification and function
The more users that have the access to SQL Server that stores cardholder data, the higher the risk that the data could be used maliciously. To prevent any malicious use of cardholder data or to prevent the mishandling due to inexperience or lack of knowledge, access to SQL Server that contains cardholder data should be restricted only to persons with a significant and valid business reason. Therefore, the 7.1.1 requirements require assigning the minimum level of privileges as possible to each and every person with access right, that can grant only “need to know” to perform a job, and thus forcing “least privilege” as well. On the other hand, to meet the 7.1.2 requirement, or so called “role-based access control” (RBAC), which will restrict the access to SQL Server with cardholder data, based on the predefined roles that users are belong to. But, 7.1.2 requirement is dependent on the 7.1.1 requirement in terms that role-based access rights are viable only if they are able to fulfill the 7.1.1 requirement and “least privilege” and “need to know” rules.
ApexSQL Audit can help in fulfilling this requirement by ensuring the necessary auditing of server and database level permissions for all the users already exist in SQL Server, as well as any new users created
To meet these two requirements, besides setting the appropriate auditing (the auditing filter that fits this requirement), defining the necessary alerting and reporting on any change related to logins and/or users in SQL Server that includes all roles and permission changes, is required. By setting up the appropriate real-time alerts, the authorized PCI DSS compliance officer in charge will be able to react promptly, thus preventing potential damage that can be inflicted to the enterprise, but also to be alerted in real time to any access or access attempt to SQL Server cardholder data by users that are not authorized to have the access
This particular example of alert filter ensures that alert fill be triggered on any access to the specified databases and objects within these databases that contains the sensitive data by any user that is not one of the trusted logins listed in the condition.
In situations when the stricter access control has to be imposed that doesn’t allow selective permission for object accessing, which is the rule for databases where sensitive data are stored, condition from the previous filter where only selected Object names are set must be removed. In most cases it should be safe to exclude the alerting on the system tables and views access to prevent storing unnecessary data in the repository database. The filter will allow alerting on access to any user table and view, except the access to the system ones (highlighted part):
The audited data related to these particular requirements should be regularly reviewed and documented via generated reports as well as reported regularly to management.
To make reporting easier, the necessary custom reports should be defined and saved. The reports should be created using one of the above conditions, or both if there is a need for more precise and broader reports
7.1.3 Requirement for a documented approval by authorized parties specifying required privileges, 8.5.1 Control addition, deletion, and modification of user IDs, credentials, and other identifier objects
This requirement has much broader impact on organizations than just the SQL Server related part, as clear mechanisms have to be established on the company level, but a DBA (or DBAs) who is in charge and privileged to implement that security requirement on SQL Server must be the only one who have the ability and permissions to assign and/or to change any privilege to any user SQL Server user.
ApexSQL Audit can provide assistance when this requirement has to be implemented and maintained by reporting on any security changes related to any user privilege, but also to send immediate notification to the person in charge when any deviation of the established rules for assigning or changing user privileges is breached
To allow regular review and inspection for potential breaches and irregularities in light of this requirement, a custom report should be defined with the following condition:
This report will display all the changes made regarding the user privileges by anybody except the authorized DBAs listed in the report condition
If immediate reaction is required on any such unauthorized change or unauthorized attempt, the same condition should be used for creating the alert
8.5 Ensure proper user identification and authentication management for non-consumer users and administrators on all system components as follows:
8.5.4 Immediately revoke access for any terminated users, 8.5.5 Remove/disable inactive user accounts at least every 90 days
This is the requirement which is mainly related to the regular and appropriate reporting on all SQL Server users terminated, or users not terminated yet, even though they should be. In order to track these users the following auditing filter must be used:
Simple filter – server level
Simple filter – database level
Using the advanced filter this can be achieved via the following condition
The terminated users report has to be reviewed and analyzed regularly while compared to the list of users that are left organization, in order to verify that all related accounts are removed from the SQL Server and that all are removed timely within the 90-day frame as it is required
Therefore, it is recommended to create and save the report that will contain all data about the dropped logins and users, which can be then created regularly in a two mouse clicks
And the resulting report
8.5.9 Change user passwords at least every 90 days, 8.5.11 Use passwords containing both numeric and alphabetic characters
ApexSQL Audit is capable of tracking any security related changes for example a SQL Server login password related change, store the data related to those password changes in the central repository database and to create or export the report on any such password change.
But the most important ability of ApexSQL Audit is the ability to trigger alerts in real time not only on each password manipulation SQL Server event, but precisely on each type of password related manipulation of SQL Server login. This allows almost immediate action to be taken by the DBA or security admin on any such sensitive, password related security event
All password related events will be audited by default when the security related auditing filter for Alter login and Create login are set. But identifying the security mishaps via reviewing reports related to the password policy as required by PCI DSS could cause significant delay in identifying any issue related to the password requirements.
Therefore, it is recommended to create a set of alerts that will allow covering any individual SQL Server login password related event. The following general filter is recommended if a DBA or Security admin wants to be alerted on any password manipulation
But ApexSQL Audit allows much more precise notifications on such kind of events thanks to an embedded T-SQL parser that is embedded in the advanced filter. It is capable of parsing the actual T-SQL statement executed in the backend of the audited event and to use the user defined text as an advanced filter condition. In the above example, the Text data condition is used to check the executed T-SQL for containing the “WITH PASSWORD=” string, and the alert will be triggered if that string is in the T-SQL of the Alter/Create login event
Creating a custom reports to generate report on the password events only, will allow fast review of any password change and easy insight into any passwords manipulation. Having separate reports for Alter login and Create login events id is recommended
The Custom filter conditions for this are as follows:
Alter login password related filter
Create login password related filter
If needed, these reports can be defined per SQL Server, or for a group of SQL Servers instances at once, and the only change would be using the multiple SQL Server instances in the Server filtering condition
Requirements that have to be audited and reported on is that Enforce password policy must be applied for all SQL Server logins, which mean that used password must meet complexity requirements that will be enforced by SQL Server when passwords are changed or created. Enforcing a password policy is obligatory for PCI DSS and not applying this requirement is considered as direct violation of the PCI DSS 8.5.11 requirement. The requirement intention is to prevent any malicious individual from finding accounts with weak or non-existent passwords, which will allow them to hack the SQL Server and potentially access to credit cardholder’s data.
Therefore, ApexSQL Audit’s ability to define alerts, first and foremost, and reports, secondarily, with ability to detect any inappropriate use of Enforce password policy can grant high level of security for sensitive cardholder data
An alert filter definition that will send an alert notification in case that the wrong Enforce password policy setting is applied is shown below:
Considering that Enforce password policy is mandatory for all under PCI DSS compliance, it is irrelevant whether this requirement is not applied for created or altered logins, and therefore this particular alert condition is sufficient. Every breach of the Enforce password policy rule requires an urgent and adequate reaction of the person responsible for PCI DSS proper implementation
Saving the appropriate custom report is recommended as it will allow that every breach of the Enforce password policy rule is documented, analyzed, but also will provide confirmation that all such mistakes and/or breaches are corrected and how fast those were fixed
Example of a report:
The 8.5.9 PCI DSS requirement imposes the use of the Enforce password expiration as additional insurance. Not enforcing password expiration gives more time to malicious people to compromise a SQL Server account. Not directly required by PCI DSS, but a usual practice in most companies as standard or additional security measure is Specify old password requirement that will prevent exploiting of a “forgot to log out” scenario where a user can be compromised by a non-authorized person
To be alerted on lapses of these requirements, a new alert should be created with the filtering condition as below
For documenting this kind of PCI DSS violations, save the custom report with the filter condition presented as below
This article is part of a series
See previous part See next part
December 14, 2015
