Archives of the TeradataForum

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

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

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