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
Go to PC > 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'll define the SQL statement that you want to execute on your database. This value can have a maximum of 32000 characters.
Note - 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'
Create a new compliance policy (go here to learn how) or edit an existing policy, and add your database controls to the policy. Tip - Make sure your policy has the database technologies selected in the control.
If you haven't scanned yet, you won't see any actual data from your database in the policy when you view the control.
Time Efficient Tip: 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.
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, we'll return up to 5000 rows for Oracle and up to 256 rows for all other control types. Select the control type to edit this limit.
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.
Note - 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.
You'll see 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'll want to edit your policy to modify the criteria selected for the control.
Here's a sample report where the expected value matches the actual value, resulting in status PASS.
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.
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'll want to edit your policy to modify the criteria selected for the control.
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.
Yes. Click here to see samples.