Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1236
  • Last Modified:

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
0
morciani
Asked:
morciani
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now