I had a rather disturbing conversion with my wife a couple nights ago, she had called a service provider to discuss a bill. The customer service rep asked her for her social security number to verify her identity. When my wife told the customer service rep she was not comfortable giving out that information, the customer service rep told her that he has her full social security number on the screen in front of him. WTF. This is a large corporation, and I’m wondering if they are ignoring the problem until there is a leak and they get embarrassed because after all it cost money to protect data.
Do you store sensitive information? Does you’re user base have access to that information? Does you’re user base need to see that sensitive information? I have always operated on the principle of “need to know.” This simply states, does a person need to know something in order to do their job? If the answer to that is no, then don’t give them the information.
In the customers table we are storing two very sensitive pieces of information, SSN and Credit Card Number. Now sit back and think, what would happen to your company / job / reputation if this information were to leak?
1 SQL> desc customers 2 Name Null? Type 3 ----------------------------------------- -------- ---------------------------- 4 ID NOT NULL NUMBER 5 SSN VARCHAR2(25) 6 FNAME VARCHAR2(55) 7 LNAME VARCHAR2(55) 8 UPDATE_DATE DATE 9 CREDIT_CARD_NBR VARCHAR2(19) 10 CREDIT_LIMIT NUMBER 11 12
Before we start, lets populate customers.ssn and customers.credit_card_nbr with random data just to make sure there is no live data is involved in this demonstration.
1 SQL> @redaction_demo 2 SQL> conn rlockard@pdev 3 Enter password: 4 Connected. 5 SQL> 6 SQL> update customers set ssn = 7 2 ceil(dbms_random.value(0,9)) || 8 3 ceil(dbms_random.value(0,9)) || 9 4 ceil(dbms_random.value(0,9)) || '-' || 10 5 ceil(dbms_random.value(0,9)) || 11 6 ceil(dbms_random.value(0,9)) || '-' || 12 7 ceil(dbms_random.value(0,9)) || 13 8 ceil(dbms_random.value(0,9)) || 14 9 ceil(dbms_random.value(0,9)) || 15 10 ceil(dbms_random.value(0,9)), 16 11 credit_card_nbr = 17 12 ceil(dbms_random.value(0,9)) || 18 13 ceil(dbms_random.value(0,9)) || 19 14 ceil(dbms_random.value(0,9)) || 20 15 ceil(dbms_random.value(0,9)) || ' ' || 21 16 ceil(dbms_random.value(0,9)) || 22 17 ceil(dbms_random.value(0,9)) || 23 18 ceil(dbms_random.value(0,9)) || 24 19 ceil(dbms_random.value(0,9)) || ' ' || 25 20 ceil(dbms_random.value(0,9)) || 26 21 ceil(dbms_random.value(0,9)) || 27 22 ceil(dbms_random.value(0,9)) || 28 23 ceil(dbms_random.value(0,9)) || ' ' || 29 24 ceil(dbms_random.value(0,9)) || 30 25 ceil(dbms_random.value(0,9)) || 31 26 ceil(dbms_random.value(0,9)) || 32 27 ceil(dbms_random.value(0,9)); 33 34 1666 rows updated. 35 36 SQL> 37 SQL> commit; 38
So lets connect at app_user and see what we can see. Umm, I don’t like that. Imagine the damage that could be caused if a less then honest employee went on a shopping spree. I know of once such case where a customer service rep had access to information like this. When the theft was discovered, we estimate over $250,000 was stolen over a six month period.
1 SQL> conn app_user/app_user@pdev 2 Connected. 3 SQL> select ssn, credit_card_nbr from rlockard.customers; 4 /* snip a lot */ 5 428-72-3919 2671 6148 1798 1588 6 251-71-5615 7925 4145 5815 3778 7 345-69-2348 9457 1637 2244 2527 8 967-55-1843 7474 9595 9119 7212 9 969-21-1324 4642 3219 4458 6715 10
What if we could create a policy that only let the user see the last four digits of the ssn and the last for digits of the credit card number? We use the dbms_redact.add_policy to a table column. in this case we are redacting rlockard.customers.ssn and giving it a policy name of cust_sensitive. There is also the expression; expression when evaluated to true the policy will be used. We are also passing function_type that is dbms_redact.partial and function_parameters. Function parameters uses the predefined constant dbms_redact.redact_us_ssn_f5. This redacts the first five characters of the social security number.
1 SQL> conn rlockard@pdev 2 Enter password: 3 Connected. 4 SQL> 5 SQL> declare 6 2 begin 7 3 dbms_redact.add_policy ( 8 4 object_schema => 'RLOCKARD', 9 5 object_name => 'CUSTOMERS', 10 6 policy_name => 'CUST_SENSITIVE', 11 7 expression => '1=1', 12 8 column_name => 'SSN', 13 9 function_type => dbms_redact.partial, 14 10 function_parameters => DBMS_REDACT.REDACT_US_SSN_F5 15 11 ); 16 12 end; 17 13 / 18 19 PL/SQL procedure successfully completed. 20
Okay that takes care of SSN, and oh by the way “expression => ‘1=1’ is not required on Oracle 12C. function_parameters uses the constant constant ‘DBMS_REDACT.REDACT_US_SSN_F5 that redacts the first five characters of the SSN.
Now lets take care of credit_card_number. In this case because we have already added the cust_sensitive policy we are going to alter the policy to add a column (this is the action parameter.) We then add credit_card_nbr and use the predefined constant dbms_redact.redact_cc16_f12.
1 SQL> 2 SQL> BEGIN 3 2 DBMS_REDACT.ALTER_POLICY ( 4 3 object_schema => 'RLOCKARD', 5 4 object_name => 'CUSTOMERS', 6 5 policy_name => 'CUST_SENSITIVE', 7 6 column_name => 'CREDIT_CARD_NBR', 8 7 action => DBMS_REDACT.ADD_COLUMN, 9 8 function_type => DBMS_REDACT.PARTIAL, 10 9 function_parameters => DBMS_REDACT.REDACT_CCN16_F12, 11 10 expression => '1=1' 12 11 ); 13 12 END; 14 13 / 15 16 PL/SQL procedure successfully completed. 17
Well Oracle also provides us with the constant DBMS_REDACT.REDACT_CCN16_F12 that redacts the first twelve characters of a sixteen digit credit card number. So what does this look like.
1 SQL> 2 SQL> conn app_user/app_user@pdev 3 Connected. 4 select ssn, credit_card_nbr from rlockard.customers; 5 /* snip a lot */ 6 XXX-XX-1979 ****-****-****-8336 7 XXX-XX-4224 ****-****-****-2794 8 XXX-XX-3475 ****-****-****-2386 9 XXX-XX-8333 ****-****-****-5569 10 11 SSN CREDIT_CARD_NBR 12 ------------------------- ------------------- 13 XXX-XX-6396 ****-****-****-1936 14 XXX-XX-8118 ****-****-****-7557 15 XXX-XX-8122 ****-****-****-9645 16 XXX-XX-3169 ****-****-****-2112 17 XXX-XX-2722 ****-****-****-1597
Well we have connected as app_user, and the data is now redacted to a point where it is semi-secure. I would not be comfortable with this being public, but it’s a big improvement over displaying the entire social security number and credit card number.