How to securely mask credit card numbers

Often banks need to send large amount of data to outside vendors to perform data analysis. It is safer to mask sensitive data items such as credit card numbers, social security numbers, etc. The following is one approach to securely mask credit card numbers.

1. Generate a random number for each credit card number.
2. Generate a rank for each random number using row_number().
3. Generate the masked credit card number based on the rank.
Step 1. create table tbl_card_w_rand_num as select card_number, dbms_random.random() rnd from tbl_cards;
Step 2. Generate the masked credit card numbers based on the rank of random numbers. Keep this table in a safe place.

create table tbl_masked_card_num as with tbl as(
select a.*, row_number() over(order by rnd) rnk from tbl_card_w_rand_num a)
select a.*, 'A'||lpad( rnk,15,'0') masked_card_num from tbl a;

Step 3. Create data using masked_card_num