Numeric Variance
Numeric variance is a process in which the numeric
values that are stored within a development database can be changed,
within a defined range, so as not to reflect their actual values within
the production database. By defining a percentage of variance, say
within 10% of the original value, the values remain realistic for
development and testing purposes. The inclusion of a randomizer to the
percentage that is applied to each row will prevent the disclosure of
the actual value, through identification of its pattern.
In the HomeLending database, we will create a user defined function called Numeric_Variance
that increases or decreases the value of the value passed to it by some
defined percent of variance, also passed as a parameter to the
function. For example, if we want the value to change within 10% of its
current value we would pass the value of 10 in the @ValPercent argument.
A randomizer is added through the use of the vwRandom
view. This will vary the
percent variance on a per execution basis. For example, the first
execution may change the original value by 2%, while the second
execution may change it by 6%.
The script to create this Numeric_Variance function, which can be referenced as needed in other views and stored procedures, is shown in Listing 6.
To employ this method of masking in a development database, simply use an UPDATE statement to change the column's value to a new value, using our Numeric_Variance function, as shown in Listing 7.
Nulling
The process of nulling is the replacement of sensitive data with a NULL
value, thus rendering the sensitive data unavailable in the development
database. While this certainly protects the sensitive data, since the
values are no longer known in the database, it does present issues if
there are dependencies upon this data or constraints that do not permit
a NULL value. Also, use of nulling can also present
difficulties when trying to troubleshoot issues that specifically
involve sensitive data.
To employ this method of masking in a development database, simply use an UPDATE statement to set the column's value to NULL, as shown in Listing 8.
