Using Database User-Defined Controls
Use the database UDC type to create custom checks by executing SQL statements on databases. You can also use these checks to create policy reports on databases.
Currently, we support the following databases:
- MS SQL
- Oracle
- Sybase
- PostgreSQL/ Pivotal Greenplum
- SAP IQ
- IBM DB2
Step 1: Add Database Controls
Go to PA > Policies > Controls > New > Control.
Select Database Control Types and then click one of the following control types:
PostgreSQL/ Pivotal Greenplum Database Check
In each control you define the SQL statement that you want to execute on your database. This value can have a maximum of 32000 characters.
Only SELECT statements are supported for the database controls. For example, you can use the following SQL statement to list all fields from Customers where country is Germany AND city is Berlin:
SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin'
Click here to see sample queries and results
Step 2: Add Database Controls to a Policy
Create a new compliance policy (go here to learn how) or edit an existing policy, and add your database controls to the policy.
Make sure your policy has the database technologies selected in the control.
If you have not scanned yet, you will not see any actual data from your database in the policy when you view the control.
You can also run a compliance scan in default mode without any policy restrictions and then add database controls to the policy. This way you already have data from the scan for you to build policy and set criteria.
Step 3: Launch a Compliance Scan
Launch a compliance scan on the host running the database. Go here to learn how
You can edit the compliance option profile you’ll use for the scan to set the max number of rows you want the check to return. By default, up to 5000 rows are returned for Oracle and up to 256 rows for all other control types. Selecting the control type allows editing of this limit.
Step 4: Return to your Policy to Set Control Criteria
Edit your compliance policy using the policy editor to see the actual data returned by your scan.
Select a column and define the expected value. This is how you set the criteria that will determine pass/fail status for the control.
To ensure you get accurate results, make sure you provide appropriate Expected value for the selected Data-type. For example: If you select the data-type as List String then in the Expected Values field provide text value.
The first check box Set status to PASS if no data found and Criteria 1 use OR for control evaluation. This cannot be changed.
Click Add another column to add more criteria. You can add up to 5 criteria, i.e. Criteria 1, Criteria 2, Criteria 3 and so on.
You can choose AND or OR between each criteria. If you choose AND then both criteria must match to Pass. If you choose OR then at least one criteria must match to Pass.
Click Test Control to verify the criteria you set. Then save your policy.
Step 5: Run a Report
you can view PASS or FAIL status in your report like you do with any control. If the columns returned by the most recent scan are different than previous scans then you want to edit your policy to modify the criteria selected for the control.
Here is a sample report where the expected value matches the actual value, resulting in status PASS.
Common Questions
Why is my check marked as Fail even when Set status to PASS if no data found option is enabled?
After the scan if no data was found on the database that matched your SQL statement and if the Set status to PASS if no data found option is enabled in policy editor then all checks should be marked Pass. However, if one of your criteria 2 or later is set to AND then the AND condition fails and the check is marked as Fail.
Why can't I see the column names in the Actual Value section that I selected while setting the criteria?
In case the data returned in the latest scan does not return the columns selected by you while setting the criteria, then those will not be displayed. Depending on the AND or OR criteria set by the check will be marked as Pass or Fail.
If the columns returned by the most recent scan are different than previous scans then you want to edit your policy to modify the criteria selected for the control.
Why do I see different results when I choose string list vs. regular expression list with matches criteria?
When you choose the operator regular expression list with operator criteria matches then a partial match will result in a status of PASS.
If the operator is string list with the operator criteria matches then an exact match is needed to get a PASS status. A partial match in this case will result in a status of FAIL.