Finding and protecting sensitive data within your database is an important task for every business to do.  Whether you’re tackling this task due to PCI requirements, GDPR requirements, or simply to protect your customers’ data, it’s an important step to securing your data and keeping it secure.  Not only does it protect your company, but it helps instill confidence from your customers knowing that their data is protected. 

But what if you have multiple databases?  What if each database has hundreds or thousands of tables, each with a dozen or more columns?  Where do you even begin to find where sensitive data might be hiding? 

Thankfully, Toad® for Oracle makes this a simple task with its Sensitive Data Protection module.  Toad’s Sensitive Data Protection module comes bundled with its own built-in library of rules to identify common formats for sensitive information.  With a little bit of knowledge of regular expressions, you can also add your own custom rules to identify sensitive data specific to your business.  You can then use those rules to find and protect sensitive data within your database and even set up tasks to monitor your database for any new unprotected data that may be added.

In this tutorial, I’ll walk you through how to create your own custom rules within Toad for Oracle.  I’ll also show you how to assign default policy actions to existing rules that can be easily applied to matching columns once they’re found.


How to define custom rules

Defining a custom rule is a very easy process within Toad for Oracle.  There are two types of rules:

  • Naming Convention
  • Data Contents

Naming Convention rules are based on the column’s name and are available in both the Sensitive Data Awareness feature of Toad for Oracle Professional as well as the optional Sensitive Data Protection module, whereas Data Content rules are based on column’s data and are only available in the Sensitive Data Protection module of Toad for Oracle.  For information about the difference between Sensitive Data Awareness and Sensitive Data Protection, visit the Guide to the Toad for Oracle Sensitive Data Protection Module

To create your own custom rule, simply open up Toad’s Options window and navigate to the Sensitive Data -> Search Rules page in the left-hand tree.  You should see the following:



This page allows you to view predefined rules as well as create new custom rules.  Let’s say we wanted to create a new rule to catch date of birth columns in my database.  To create our new custom rule, simply click the “Create a new rule” button in the toolbar and a new blank rule will be created and ready for editing.

For this new rule, I’ll enter “My DOB Rule” as the rule’s name, assign it to the Category of “Identifiers”, the Severity of “Privileged”, and the Rule Type of “Naming Convention” (since I’m testing against the column’s name).  The Category and Severity values are simply user-defined groupings which you can assign to better organize and manage your rules. 

Rule expressions are based on industry-standard regular expressions.  If you don’t know much about regular expressions, there are a lot of resources that can be found online to help.  Some of these resources include:

Note: Toad for Oracle’s regular expression engine is based on PCRE, which is an open source regular expression engine.  You can read more information about PCRE here.


Finally, I’ll enter the regular expression I’d like to use as well as a few sample values, separated by commas, to test my expression.  The resulting rule should now look like this:



The regular expression I used here may look a bit confusing at first, but there’s really not much to it.  Regular expressions are typically broken into groups of characters, which are enclosed in parentheses.  The first group “(?i)” is a special command that simply specifies that all matching should be done case insensitively.  The “\b” characters define an Anchor or Boundary for the word we’re looking to find (here, both beginning and end).  This keeps words like “adobe” or “dobbin” from appearing as matches. What’s in the middle simply specifies:

  • Look for a “d” (case insensitively) as the start letter of the word
  • Followed by the optional letters “ate” (the “?” character specifies that it’s optional)
  • Followed by a single optional space, “_”, “-“, or “.” character (the square brackets define a single character from the defined set of valid characters)
  • Followed by the required letter “o”
  • Followed by the optional letter “f”
  • Again, followed by a single optional space, “_”, “-“, or “.” character
  • Followed by the required letter “b”
  • Followed by the optional letters “irth”

Pressing the “Test” button shows that the first six test values match, while the values “Adobe” and “Dobbins” don’t.  That’s exactly what we want.  We can then press the “Save the current rule” button in the toolbar to save the rule. 

This rule is now ready to be used within Toad.


How to define default policy actions to rules

Defining rules to recognize sensitive data columns via naming convention or data contents is the first step to securing your data, but you still need to be able to do something when you find that unprotected data in your database. 

Within Toad’s Sensitive Data Awareness feature, you can know when you’re accessing a column containing potential sensitive information from the Editor or Schema Browser.  Within the Editor, you can even hover over the column to retrieve current policy information applied to the column and even right-click on the column to manually create policies if needed.  However, that is still an individual and manual process, opening a separate window, deciding what policies to apply based on the company’s internal rules, manually configuring them, and then executing the policy to set them.  Wouldn’t it be nice to be able to define those policies ahead of time so they can simply be applied on the fly whenever they’re needed?

In Toad 13.2 and higher, you can do just that through the “Default Policies” tab for the rule you created (or any other rule) above.  For our “My DOB Rule” we created above, let’s click the “Default Policies” tab to see any definitions for the rule.  As you can see, we don’t currently have a default policy defined for this rule:



To add a default policy, simply click the “Edit the Selected Rule” button in the toolbar.  For this example, we’ll add a default redaction policy for this rule.  All we need to do is check the box next to “Redact Column” and click on the “Configure” button to open the rule window.  We’ll then select “PARTIAL REDACTION” as the Function Type, enter “m1d1Y” as the Function Parameters and enter “1=1” as the Policy Expression.  You should see the following screen:



Finally, click the “OK” button and then click the “Save the current rule” button in the toolbar to save it.  The default policy for this rule is now defined.  It hasn’t yet been applied to any columns, but the definition has been defined to make it easier to apply “on the fly” later.  That rule’s definition should now show the following:



Now, when you reference a column that matches your new rule in the editor, it will not only flag as a match due to our new custom rule, but you can also right-click and apply the default policy we created to that column with a single click of the button if it hasn’t already been defined:




In this tutorial, we took a look at how to create a custom rule to help find potentially sensitive information in your database.  We also took a look at how to create a default policy definition that we can then easily apply to a column that matches that rule.  In the next tutorial, scheduled for release October 24, I’ll show you how to use the Sensitive Data Protection search feature within Toad to find and protect existing columns within your database and even apply default policies to those columns that were found. We will also take a look at how to create a recurring task to continue database monitoring going forward.


Free trial

If you don’t currently own a copy of Toad for Oracle or would like to try the features of the Sensitive Data Protection module, please visit our free trial page and request a trial.


Useful resources:

Video:Just learning about sensitive data protection? Watch this 2-minute video.

Tech brief:Find out how you can search for sensitive data in this 2-page tech brief.

White paper:8 Questions DBAs Need to Answer About Data Privacy and Protection.

Product page: Visit our product page on to learn how you can find and control sensitive data across all your Oracle databases.


Related Toad World blog posts:

Amphibian Evolution, GDPR and Sensitive Data Protection

Toad for Oracle Sensitive Data Protection Module FAQ

Guide to the Toad for Oracle Sensitive Data Protection Module


Questions? Start a discussion.

Have any additional questions? Click Start Discussion and this blog topic will be transferred to the Toad World Forum.

Start the discussion at