A regular part of a DBA job is moving production data to development environment. This sometimes poses a challenge as how to protect sensitive production data without spending more time more than you have to or buying or 3rd party tool to generate random values to replace existing sensitive production data. This post discusses an idea we are currently using in our development environment.
Before detailing our scrambling solution, here is a common question: What is the difference between encryption and scrambling.
Here are some of these differences:
|Encryption is an algorithm applied to data usually you would need an encryption key along with Oracle built-in packages||Replace existing characters or numbers with another character. The end result is same string size but with different characters.|
|Support for Encryption is built in Oracle||Not built in Oracle. You have to develop your own.|
|Encrypted column require an increase in size for DES, 3DES, 3DES encryptions||Same size.|
|Application needs to be aware of encrypted columns so data can be encrypted/unencrypted||Application doesn’t need to be aware of it.|
|Suitable for production||Suitable for development|
|Near impossible to crack||Scrambling is easier to crack unless you generate random values.|
|New data is also encrypted||New data is not scrambled but in development it should be safe|
How to Scramble Data in Oracle
The best way to perform scrambling is through translate built-in function.
Here is an example:
SELECT 'Hazem Ameen' as before_scrambling , TRANSLATE(lower('Hazem Ameen'), '.@,0123456789abcdefghijklmnopqrstuvxyzابتثجحخدذرزسشصضطظعغفقكلمنوهي', '.@,4214563875qwertyuiop[kjhbvabcdefxzgباتنمكضصثقفغعهخحجدظزوةىرذشسؤ') after_scrambling FROM dual;
|Hazem Ameen||iqgtj qjtth|
It is really up to you how you want to apply it in your environment. At the end would have to execute a statement like this on every column that requires scrambling:Update table_name set <column_name_to_scrambled> = translate (<column_name_to_scrambled>, ‘.@,0123456789abcdefghijklmnopqrstuvxyzابتثجحخدذرزسشصضطظعغفقكلمنوهي’, ‘.@,4214563875qwertyuiop[kjhbvabcdefxzgباتنمكضصثقفغعهخحجدظزوةىرذشسؤ’)
Here are some tips on how to apply this in your environment:
- If you have too many columns or the process of scrambling repeats often, insert table name along with columns to be scrambled in a table, then develop a PL/SQL procedure to read this table and execute the update statement dynamically.
- Updating large amount of data via a single update statement will take a very long time; instead you would open a cursor and loop through the data.
- Be aware that triggers on the table will slow down this procedure dramatically. If you can disable them first before running this procedure.
- This scrambling process doesn’t scramble dates or LOBS.
Hazem Ameen Senior Oracle DBA