Teradata Data Masking

Database
Highlighted
Enthusiast

Teradata Data Masking

Hi -

Could any one please let me know how we can do data masking in teradata .Is there is any function available to use on that perticular column for which we want masking...any help is appreciated...

Thanks

Tags (1)
4 REPLIES
Enthusiast

Re: Teradata Data Masking

We have implemented a security table that seems to work pretty good.

The table looks like this:

CREATE MULTISET TABLE META_T.EDW_SECURITY ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      EDW_SECURITY_KEY DECIMAL(18,0) NOT NULL,

      USER_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      FUNCTION_CODE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      RESTRICT_CODE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ROW_FILTER_VALUE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ACQ_SITE_ABBR CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ACQ_SRC_SYS_ABBR CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ACQ_SRC_TABLE CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ACQ_ROW_SEQ INTEGER NOT NULL,

      ACQ_ROW_STATUS_CODE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      SRC_USER_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      SRC_FUNCTIONAL_CODE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,

      BASE_REGION_CODE CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      BASE_INSERT_DTTM TIMESTAMP(0) NOT NULL,

      BASE_UPDATE_DTTM TIMESTAMP(0) NOT NULL)

PRIMARY INDEX EDW_SECURITY_NUPI ( USER_ID )

UNIQUE INDEX EDW_SECURITY_USI ( ACQ_SITE_ABBR ,ACQ_SRC_SYS_ABBR ,

SRC_USER_ID ,SRC_FUNCTIONAL_CODE );

When executing a query, the user is passed whether it is through SQLA or Query Banding in the Bi tool.  The query would look something like this:

SELECT  E.EMPLOYEE_KEY,

 E.PERSONNEL_KEY,

 E.HOME_EMPLOYEE_KEY,

 E.HOME_PERSONNEL_NBR,

 E.PERSONNEL_NBR,

 E.ERP_PERSONNEL_NBR,

 CASE WHEN E.FIRST_NAME = '?' THEN '' ELSE E.FIRST_NAME END AS FIRST_NAME, E.MIDDLE_NAME, E.LAST_NAME,

 CASE WHEN E.SECOND_LAST_NAME = '?' THEN '' ELSE E.SECOND_LAST_NAME END AS SECOND_LAST_NAME,

 E.SUFFIX,

 CASE WHEN S.RESTRICT = 'C' THEN E.GENDER ELSE '***' END AS GENDER

FROM databasename.EMPLOYEE E

   LEFT OUTER JOIN

  (SELECT COALESCE(MAX(RESTRICT_CODE), 'X') AS RESTRICT

   FROM  databasename.EDW_SECURITY

   WHERE USER_ID = CASE WHEN POSITION('_' IN USER) = 3 THEN SUBSTR(USER,4) ELSE USER END

     AND 1 = CASE WHEN (POSITION('_' IN USER) = 3 AND SUBSTR(USER,1,2) = FUNCTION_CODE) OR POSITION('_' IN USER) = 0 THEN 1 ELSE 0 END) AS S ON 1=1

If the user were not in the EDW_SECURITY table, the column (in this case GENDER) would be masked with '***'.  We have other processes around this type of security but just thought I would provide you with this flavor.

Joe

Enthusiast

Re: Teradata Data Masking

Thanks joe for the solution but from the above solution we need to join security table with the table and create a view ?

Instead that do we hae any other process to do ? please let me know.

Thanks

Re: Teradata Data Masking

An alternative (third-party) data masking solution should also be IRI FieldShield, since they connect to TD in Eclipse.  See: ww.iri.com/blog/vldb-operations/iri-workbench-teradata/

and www.iri.com/products/FieldShield.

Enthusiast

Re: Teradata Data Masking

You can think of a home-grown solutions that suit most befittingly your organization standards like udfs, XSPs or think of tools that work well with Teradata..... IRI,Protegrity.....

Even thru BAR' policy manager you can encrypt....