Scripting SQL Commands

Overview

One or more SQL commands can be defined to be performed before DataVeil masks are executed and/or after the DataVeil masks are executed.

These scripts are defined specifically for each database in a masking project.

Therefore, to add or modify scripts you first select the applicable database in the Project explorer tree to set focus for that database, then open the Masking tab in the main window.
Note: The Masking tab is context-sensitive according to the current focus. Therefore, if a table was selected in the explorer tree then the masking configuration relevant for the selected table would be displayed in the Masking tab. Similarly, if a column was selected then masks configured for that column would be displayed.
 

 

 

Pre-Masking Scripts

The SQL commands defined in the Pre-Masking Script tab shall be performed at the beginning of a masking project execution.
 

Perform after license validated

If this checkbox is selected then DataVeil shall first count all sensitive values and perform a license check prior to performing the pre-masking scripts. Enabling this option shall ensure that the pre-masking scripts are not performed unless the license check is successful.

If this checkbox is not selected then DataVeil shall perform the pre-masking scripts prior to counting sensitive values and performing a license check. This option may be necessary if the pre-masking scripts manipulate sensitive tables that will affect the row count. Therefore, performing the scripts prior to the license check shall ensure that the sensitive value count is correct and that the masking plan is generated correctly.
 

The pre-masking scripts are always performed before DataVeil begins automatically disabling any triggers, constraints or indexes as may be required and prior to any DataVeil masks being performed.

Each script in this table is executed in sequential order, as they appear in this table, from top to bottom. If an error occurs then no further pre-masking scripts are performed and the masking project is terminated; however, post-masking scripts shall still be performed as part of the Finalization phase.

Please note that you can override SQL error codes within each script so that specific, or all, error codes are treated as warnings or even ignored altogether. Please refer to the "Script Editor" section below for details.

 

Post-Masking Scripts

The SQL commands defined in the Prost-Masking Script tab shall be performed near the end of a masking project execution. This occurs after DataVeil has restored triggers, constraints and indexes but before normal transaction logging is resumed (on DBMS's where DataVeil is able to make changes to transaction logging, such as on SQL Server where recovery model is set to Simple mode during masking).

Each script in this table is executed in sequential order, as they appear in this table, from top to bottom. Unlike in the pre-masking script, if an error occurs then execution shall continue with the next post-masking script in the table.

 

Script Editor

To add or modify a script you can click on the Add or Edit buttons as required.

Use the Up and Down buttons to alter the sequence of scripts to be executed. They shall be executed sequentially in appearance order in the table, from top to bottom.
 

 

Description

This descriptive text shall be written to the logger when this script is performed.
 

SQL Code

This SQL code shall be submitted to the DBMS for execution.

It may contain one or more SQL statements.

It may contain macros. For example, a macro definition could be an entire block of SQL text. Therefore, this SQL Code panel could contain just a single macro reference (e.g. #my_user_function) which DataVeil would expand during masking runtime. This makes it easy to maintain and re-use a script in many masking projects. For example, if the code needs to be modified at some time in the future then only the one macro definition will be modified and all references from all projects will effectively be automatically updated.
 

 

Combo box option:  Perform each line as a separate query

Each line of the SQL Code shall be submitted to the DBMS as separate queries.

For example, this would be useful for multiple INSERT queries to populate a table.
 

Oracle users: Each line should not end with a semicolon, otherwise Oracle may return an error such as "ORA-00933: SQL command not properly ended". This does not appear to be an issue for SQL Server or MySQL where each line can end with a semicolon or not.
 

Combo box option:  Perform entire code as a single query

The entire SQL Code shall be submitted to the DBMS as a single query.

This is suitable for any multi-line query that must be executed together, such as when performing a CREATE FUNCTION that spans many lines.

 

Interruptible

If selected then this SQL can be interrupted while it is still executing such as when a user performs the Stop command.
  

Perform during Preview Run too

If selected then this script shall be performed during Preview Runs too. Otherwise it shall be skipped during Preview Runs.
 

Write SQL Code to Logger

If selected then the text in the SQL Code field shall be written to the logger.
  

Exception Handling

You can define how DataVeil is to respond to an SQL exception that may occur while performing a script. The choices are:

Error - The exception shall be handled as a normal error and the masking project shall be terminated.

Warning - The exception shall be logged as a warning and execution shall continue.

Ignore - The exception shall be ignored as if it did not occur. i.e. Execution shall continue and no indication of the exception shall be logged.

 

All SQL exceptions are to be treated as

All exceptions encountered by this script shall be handled as indicated in the corresponding combo-box.
 

Individual SQL exception codes are to be handled as follows

Individual exception codes can be overridden using this option.

In the screen capture example above, the SQL code attempts to drop a column. If an exception 4924 occurs then it is to be ignored. This is an SQL Server error code indicating that the 'alter table drop column' failed because the column does not exist. This could happen and be acceptable because the column may have already been dropped by a previous masking run.

The Default combo-box shows how any other exception code that is not explicitly listed should be handled.