Data masking through jumbling the characters in Oracle DB

Question

Is it possible to perform data masking in Oracle DB by jumbling/shuffling the characters in a data column?

SELECT 'abcd' as "Normal Text" from dual;

SELECT 'badc' as "Masked Text" from dual; --expected

Off my mind, I can think of exploring the use of multiple RPAD and LPAD but that will be overly complicated. Is there a simpler solution to this?

Have a doubt. Do you want to Jumble the records in a column using plsql and later do the data masking? You want us to provide you a query to jumble/scramble the words alone? — Vimal Bhaskar, Jan 03 '17 at 07:38
There are many factors to data masking, some of them are mutually exclusive. Which of these do you want: 1) cryptographically secure output that nobody can break, 2) output that is the exact same size as the input, 3) a deterministic function (same input always returns the same output), 4) a method that does not require a pre-computed table. — Jon Heller, Jan 03 '17 at 07:50
You can use `translate(your_column, 'some chars', 'jumbled chars')` to make text unreadable. — Egor Skriptunoff, Jan 03 '17 at 08:18
The data masking is to ensure the orginal value is not shown but yet still contain the composition of the characters that made up the original value. It does not need to be cryptographically masked. It can be in the form of a SELECT statement. — Ted, Jan 03 '17 at 08:28
So do you want the letters just jumbled randomly or is it necessary to get the original string from the jumbled one (i.e. decrypt the encrypted string)? A *very* simply letter swapping is this: `reverse(text) as maked_text`. — Thorsten Kettner, Jan 03 '17 at 09:06

score 1 · Answer 1 · edited May 23 '17 at 12:01

Yes, data masking is supported in Oracle. Out of the box it supports masking certain type of data (like SSN, Credit card numbers, phone numbers) and also it has capability to generate random dates or strings etc. There is no built in routine for shuffling characters with in given text, but you can always look at other 2 options (shuffling data across rows or pick random data from another table ) for easy implementation.

If your need is to have shuffling with in string, you will have to write your own masking function and use them. This thread gives you algorithm to achieve same.

other links on data masking support in Oracle

score 0 · Answer 2 · answered Jan 03 '17 at 09:11

0

For exactly the result suggested by yourself (i.e. swap odd and even character poitions) you can use:

select regexp_replace(text, '(.)(.)', '\2\1') as masked_text
from (select 'abcd' as text from dual);

answered Jan 03 '17 at 09:11

Thorsten Kettner

89,309
7
49
73

Because of fixed pattern, it will be too easy to arrive at original value, which defeats data masking purpose. otherwise its a nice solution. – Vijayakumar Udupa Jan 03 '17 at 09:16

score 0 · Answer 3 · answered Aug 02 '22 at 18:31

This is an old thread, but I thought I would post this SQL as a method of scrambling text. This can be used for scrambling, but it is not cryptography. The resulting string can be used in a translate function:

WITH
starter
AS
    (SELECT ' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'    AS a
       FROM DUAL),
scramble (newstr, remaining, rndm)
AS
    (SELECT SUBSTR (a, 1, 1)
          , SUBSTR (a, 2)                                     AS remaining
          , TRUNC (DBMS_RANDOM.VALUE (1, LENGTH (a) - 1))     AS rndm
       FROM starter
     UNION ALL
     SELECT newstr || SUBSTR (remaining, rndm, 1)
          , CASE rndm
                WHEN 1
                THEN
                    SUBSTR (remaining, 2)
                WHEN LENGTH (remaining)
                THEN
                    SUBSTR (remaining, 1, LENGTH (remaining - 1))
                ELSE
                       SUBSTR (remaining, 1, rndm - 1)
                    || SUBSTR (remaining, rndm + 1)
            END                                                      AS remaining
          , TRUNC (DBMS_RANDOM.VALUE (1, LENGTH (remaining) - 1))    AS rndm
       FROM scramble
      WHERE LENGTH (remaining) > 0)
SELECT *
  FROM scramble where remaining is null;

This is done using DBMS_RANDOM as a pseudo random number generator. You will want to call dbms_random.seed to make it truly random.

Use the resulting string in a translate function:

SELECT translate (
       string
     , ' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
     , ' AvjcMlo1KOH45UZfmXipnPEgGBRSza2I63kNCWQwYuF8qh0teDr7TybVJLxds9')
  FROM sourcetable

Data masking through jumbling the characters in Oracle DB

3 Answers3