programming4us
programming4us
DATABASE

Protecting SQL Server Data : Obfuscation Methods (part 1) - Character Scrambling

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
6/21/2014 9:44:26 PM

The word obfuscation is defined by the American Heritage Dictionary as follows:

"To make so confused or opaque as to be difficult to perceive or understand ... to render indistinct or dim; darken."

The word obfuscation, at times, can be used interchangeably with the term obscurity, meaning "the quality or condition of being unknown". However, there is a subtle difference between the two terms and the former definition is more appropriate since obscurity implies that the hidden condition can be achieved without any additional effort.

Many methods of "disguise", or obfuscation, are available to the Database Administrator that can contribute a level of control to how sensitive data is stored and disclosed, in both production and development environments.

  • Character Scrambling

  • Repeating Character Masking

  • Numeric Variance

  • Nulling

  • Artificial Data Generation

  • Truncating

  • Encoding

  • Aggregating.

Many of these methods rely on SQL Server's built-in system functions for string manipulation, such as SUBSTRING, REPLACE, and REPLICATE

Prior to diving into the details of these obfuscation methods we need to explore the unique value of another system function, called RAND.

The Value of RAND

The RAND system function is not one that directly manipulates values for the benefit of obfuscation, but its ability to produce a reasonably random value makes it a valuable asset when implementing character scrambling or producing a numeric variance.

One special consideration of the RAND system function is that when it is included in a user defined function an error will be returned when the user defined function is created.



This can be overcome by creating a view that contains the RAND system function and referencing the view in the user defined function. The script in Listing 1 will create a view in the HomeLending database that returns a random value, using the RAND system function. Since this view holds no security threat, we will make this available to the Sensitive_high, Sensitive_medium and Sensitive_low database roles with SELECT permissions on this view.

Listing 1. Generating random numbers using RAND.

Now, we can obtain a random number in any user defined function with a simple call to our new view. In Listing 2, an example is provided that produces a random number between the values of 1 and 100.

Listing 2. Testing the View.

Character Scrambling

Character scrambling is a process by which the characters contained within a given statement are re-ordered in such a way that its original value is obfuscated. For example, the name "Jane Smith" might be scrambled into "nSem Jatih".

This option does have its vulnerabilities. The process of cracking a scrambled word is often quite straightforward, and indeed is a source of entertainment for many, as evidenced by newspapers, puzzle publications and pre-movie entertainment.

Cracking a scrambled word can be made more challenging by, for example, eliminating any repeating characters and returning only lower case letters. However, not all values will contain repeating values, so this technique may not be sufficient for protecting highly sensitive data.

The Character Scrambling UDF

In the HomeLending database we will create a user defined function called Character_Scramble that performs character scrambling, which is shown in Listing 3. It will be referenced as needed in views and stored procedures that are selected to use this method of data obfuscation.

Included in this user defined function is a reference to the vwRandom view that was created in Listing 1. In essence, this user defined function will loop through all of the characters of the value that is passed through the @OrigVal argument replacing each character with other randomly selected characters from the same string. For example, the value of "John" may result as "nJho".

In order for the appropriate users to utilize this user defined function permissions must be assigned. The GRANT EXECUTE command is included in the following script.

Listing 3. The character scrambling UDF.

This user defined function takes advantage of system functions such as DATALENGTH which provides the length of a value, SUBSTRING which is used to obtain a portion of a value, REPLACE which replaces a value with another value and LOWER which returns the value in lowercase characters. All are valuable to string manipulation.

This UDF will be referenced in any views and stored procedures that are selected to use this method of data obfuscation, an example of which we'll see in the next section.

Other  
  •  SQL Server 2012 : Managing Resources - Limiting Resource Use, Leveraging Data Compression
  •  SQL Server 2012 : Tuning Queries (part 3) - Using the Database Engine Tuning Advisor
  •  SQL Server 2012 : Tuning Queries (part 2) - Gathering Query Information with Extended Events
  •  SQL Server 2012 : Tuning Queries (part 1) - Understanding Execution Plans
  •  SQL Server :ONE-WAY ENCRYPTION - Creating the Interface (part 2) - Setting and Verifying Permissions to the Stored Procedures
  •  SQL Server :ONE-WAY ENCRYPTION - Creating the Interface (part 1) - Creating the View, Creating the Stored Procedures
  •  SQL Server : Implementing One-Way Encryption (part 1) - Populate the Hash Columns, Verify the Implementation, Drop the Unencrypted Column
  •  SQL Server : Implementing One-Way Encryption (part 1) - Create the Primary Hash Column,Create a Secondary Hash Column for Searching
  •  Sql Server 2012 : Hierarchical Data and the Relational Database - Hierarchical Table Indexing Strategies
  •  Sql Server 2012 : Hierarchical Data and the Relational Database - Populating the Hierarchy (part 3) - The GetAncestor Method
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us
    programming4us
     
     
    programming4us