Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 26 Apr 2005 @ 13:19:59 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Masking identification numbers
From:   Geoffrey Rommel

  We have some users on our system who need to know the "type" of credit card and information about transactions by a particular credit card using a "surrogate" card number, without knowing the actual card number.  

I would suggest two user-defined functions. One would convert the real number to the surrogate number (encrypting, in effect); the other would do the reverse.

To do the encryption, you could do something along these lines:

- Select a series A that contains the numbers 1 to n=the number of digits in the original number, in random order. For instance, if n=16, series A might be 15 8 16 10 7 9 4 12 1 14 3 13 11 2 5 6.

- Select n series B, C, ... Q of 10 randomly selected characters. If your surrogate key can be a character field, include alphabetic characters (but avoid I and O); if not, these will have to be permutations of 0..9.

Do the above steps only once. Then, for each number:

- Rearrange the digits of the original number using series A.

- Translate the first digit of the encrypted number using series B; translate the next using series C; etc.

As you can see, this algorithm is deterministic and reversible, so you can decrypt by simply reversing the steps.

The encrypting/decrypting could perhaps be done in SQL, but a UDF would be faster, and the code would be invisible to the end users.

The drawback to this approach, of course, is that a savvy user with access to enough of the original numbers and the encrypted numbers could easily figure out the algorithm. If the users are prevented from seeing the original numbers, that shouldn't be a problem.

If the users need to know type information that is somehow buried in the original number, it might be best to store that as a separate column, or derive it in a view.

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023