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 MS SQL, Oracle and Sybase databases.

Step 1 - Add database controls

Go to PC > Policies > Controls > New > Control.

Select Database Control Types and then click the control type you want to create: MS SQL Database Check or Oracle Database Check or Sybase Database Check.

In each control you'll define the SQL statement that you want to execute on your database. 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'

Go 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. 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.

Policy editor before scan

 

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.

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, the max rows we'll return for a MS SQL Database Check is 256 rows, the max rows we’ll return for an Oracle Database Check is 5000 rows and the max rows we'll return for a Sybase Database Check is 256 rows. To lower this limit, select the database control type in the compliance option profile and pick a new value.

Compliance Profile: Database Control Types options

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.

Policy Editor after 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.

Policy Editor After scan set criteria

 

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.

Policy Editor after scan test criteria

Step 5 - Run a report

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.

Sample report output for MS SQL Database Check

 

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'll 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.

Can you share some sample queries and the results from those queries?

Yes. Click here to see samples.