SELECT statement that will mask a value stored on a DB2 table

Hi All,

I have a situation where I want to mask an employeeID column in a database (DB2).  I want the data stored on the database to remain 'in-the-clear', but I want to be able to create  a SELECT statement that grabs the masked value, rather than the full string.  The masking rules are as follows:

1. If the employeeID is less than 10 digits, then pad field before scrubbing.
2. If the account number is 7 or more digits scrub to the last 4 digits of the bank account number, preceded by lower case xxx to the required length.
3. If the account number is 6 digits or less scrub to the last two, preceded by lower case xxx to the required length.

Examples of the data stored on the DB, and desired values from the SELECT statement are included below:

Length      employeeID (stored on DB)      employeeID (desired value)
1      1                  xxxxxxxxx1
2      12                  xxxxxxxx12
3      123                  xxxxxxxx23
4      1234                  xxxxxxxx34
5      12345                  xxxxxxxx45
6      123456                  xxxxxxxx56
7      1234567                  xxxxxx4567
8      12345678                  xxxxxx5678
9      123456789                  xxxxxx6789
10      1234567890            xxxxxx7890
11      12345678901            xxxxxxx8901
12      123456789012            xxxxxxxx9012
13      1234567890123            xxxxxxxxx0123
14      12345678901234            xxxxxxxxxx1234
15      123456789012345            xxxxxxxxxxx2345

Any help that could point me in the right direction would be much appreciated!  Many thanks!!!

-Mike
morcianiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi morciani,

Do you want to SELECT based on these rules (which may well result in multiple rows returned for a querty) or just mask the returned results so that the client side doesn't see the entire value?

What is the data type involved?  The function will be similar regardless of the type, but an entirely different set of functions will be needed.


Kent
0
morcianiAuthor Commented:
Hello- I would like the SELECT to perform the masking, so the masked value is returned to the client.  I would expect that the query returns multiple rows, based on parameters that I pass into the WHERE clause.  The datatype of the employeeID is CHAR(20).  Thank you so much for the help!
0
Kent OlsenData Warehouse Architect / DBACommented:
It appears that all of the employee ID values are stored left justified, if so try this:

Note that if employeeID is null or blank, this will need a bit more code.  Also, a value of 20 digits is an issue.

Kent

SELECT
  case when posstr (employeeID, ' ') = 2 then left ('xxxxxxxxx' || employeeID, 10)
       when posstr (employeeID, ' ') < 8 then 
            left ('xxxxxxxx' || substr (employeeID, posstr (employeeID, ' ') - 2, 2), 10)
       when posstr (emloyeeID, ' ') < 11 then
            left ('xxxxxx' || substr (employeeID, posstr (employeeID, ' ') - 4, 4), 10)
       else substr ('xxxxxxxxxxxxxxxxxxxx', 1, posstr (employeeID, ' ') - 5) ||
                   substr (employeeID, posstr (employeeID, ' ') - 4, 4)
  end
FROM ...

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbmullenCommented:
please ignore the WITH it was just for my testing..

what I really don't understand, why do numbers of length 6 or less only keep the last 2 digits but 7 or greater keep 4 digits?

do you want a function that always returns 10 or more charaters?

with my_table as (
select '12345678901234567890' my_column from sysibm.sysdummy1 union all
select '1234567890123456789' my_column from sysibm.sysdummy1 union all
select '123456789012345678' my_column from sysibm.sysdummy1 union all                            
select '12345678901234567' my_column from sysibm.sysdummy1 union all
select '1234567890123456' my_column from sysibm.sysdummy1 union all
select '123456789012345' my_column from sysibm.sysdummy1 union all
select '12345678901234' my_column from sysibm.sysdummy1 union all
select '1234567890123' my_column from sysibm.sysdummy1 union all
select '123456789012' my_column from sysibm.sysdummy1 union all
select '12345678901' my_column from sysibm.sysdummy1 union all
select '1234567890' my_column from sysibm.sysdummy1 union all
select '123456789' my_column from sysibm.sysdummy1 union all
select '12345678' my_column from sysibm.sysdummy1 union all
select '12345678' my_column from sysibm.sysdummy1 union all
select '1234567' my_column from sysibm.sysdummy1 union all
select '123456' my_column from sysibm.sysdummy1 union all
select '12345' my_column from sysibm.sysdummy1 union all
select '1234' my_column from sysibm.sysdummy1 union all
select '123' my_column from sysibm.sysdummy1 union all
select '12' my_column from sysibm.sysdummy1 union all
select '1' my_column from sysibm.sysdummy1)
select  my_column, length(my_column) my_column_length,
   case when length(my_column) < 7
    then right(repeat('x',20) || rtrim(ltrim(right(my_column,2))) ,10)
    when length(my_column) < 11
    then right(repeat('x',20) || right(my_column,4),10)
    else right(repeat('x',20) || right(my_column,4),length(my_column))
    end padded_column
from my_table
order by my_column
;

Open in new window

0
morcianiAuthor Commented:
This is EXACTLY what I needed!  It functions exactly as I need it to.  Many thanks!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.