Repeating Character Masking
Over recent years the information that is presented
on a credit card receipt has changed. In the past, it was not uncommon
to find the entire primary account number printed upon the receipt.
Today, this number still appears on credit card receipts; but only a
few of the last numbers appear in plain text with the remainder of the
numbers being replaced with a series of "x" or "*" characters. This is
called a repeating character mask.
This approach provides a level of protection for
sensitive data, rendering it useless for transactional purposes, while
providing enough information, the number's last four digits, to
identify the card on which the transaction was made.
In the HomeLending database, we will create a user defined function called Character_Mask, as shown in Listing 4,
which performs repeating character masking, which again can be
referenced as needed in views and stored procedures that are selected
to use this method of data obfuscation.
This user defined function will modify the value that is passed through the @ OrigVal argument and replace all of the characters with the character passed through the @MaskChar argument. The @InPlain
argument defines the number of characters that will remain in plain
text after this user defined function is executed. For example, the
value of "Samsonite" may result in "xxxxxxite".
In order for the appropriate users to utilize this user defined function permissions must be assigned. The GRANTEXECUTE command is included in the script.
This user defined function takes advantage of system functions such as DATALENGTH, which provides the length of a value, and REPLICATE
which is used to repeat a given character for a defined number of
iterations. Both of these are valuable to string manipulation.
To illustrate the use of this, and our previous Character_Scramble user defined function, to present data in a masked format to the user, we will create a view in the HomeLending database, called vwLoanBorrowers, for the members of the Sensitive_high and Sensitive_medium database roles.
This view, shown in Listing 5, will present to the lender case numbers, using the Character_Mask user defined function, and the borrower names using the Character_Scramble user defined function.
The vwLoanBorrowers view, without the use of the masking user defined functions, would have returned the data set shown in Table 1.
Table 1. The non-obfuscated result set.
| Lender Case Number | Borrower Name |
|---|
| 9646384387HSW | Damion Booker |
| 8054957254EZE | Danny White |
However, with the user defined functions in place the masked data set shown in Table 2 is returned:
Table 2. The results returned after character masking and scrambling.
| Lender Case Number | Borrower Name |
|---|
| XXXXXXXXX7HSW | o akdenbimr |
| XXXXXXXXX4EZE | ni ahtydwe |
