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?

Answer 1

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

• https://docs.oracle.com/cd/E11882_01/server.112/e41481/tdm_data_masking.htm#RATUG4018

• http://www.databasejournal.com/features/oracle/oracle-database-11g-working-with-the-data-masking-pack.html

Answer 2

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);

Answer 3

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