Forum Stats

  • 3,741,767 Users
  • 2,248,474 Discussions


SSN column data masking

2635870 Member Posts: 2
edited Jun 17, 2019 10:37AM in Database Security - General


I need to create a user defined function to mask SSN column in multiple tables in our Oracle people soft QA database. The values should be identical across all tables for data integrity. The requirement is to pass the table name and column name as variables.

Could anyone help with a sample code?




  • Emad Al-Mousa
    Emad Al-Mousa Member Posts: 716 Bronze Trophy
    edited Jun 12, 2019 5:47AM


    if you are looking for "dynamic data masking", then Oracle provides a licensed feature using DBMS_REDACT package.

    There are other alternatives that you can look at:

    Encryption Decryption using DBMS_CRYPTO - Mohammad Nazmul Huda

    Or third party software.

    For STATIC data masking (which is a storage level permanent value change), example:

    CREATE TABLE scrambled

          (DATE_OF_BIRTH  DATE,

        FIRST_NAME VARCHAR2(20),

        POSTCODE NUMBER(8));


    INSERT INTO scrambled (date_of_birth, first_name, postcode)


            DBMS_RANDOM.STRING ('U', 20),

            LPAD (CEIL (DBMS_RANDOM.VALUE (1,99999999)), 8, '0')

        FROM   DUAL

        CONNECT BY LEVEL <= 10 ;

  • 2635870
    2635870 Member Posts: 2
    edited Jun 12, 2019 8:55AM

    Thank you for the response, Emad.

    Our requirement is to write a function and then pass the table name and column names as variables to mask the SSN value. Would you have an example for this scenario?



  • mseberg
    mseberg Member Posts: 7,004 Silver Crown
    edited Jun 17, 2019 10:37AM

    Sounds like a requirement I might question. SSN should be encrypted at the very least. I would also look at limiting the number of tables that have this data.

    Maybe I'm reading more into the question than is there, but based on the wording it almost sound like SSN is being stored as clear text. I'd be looking at Oracle Advanced Security.

    Best Regards


Sign In or Register to comment.