No question at this time
DBA Top 10
1 M. Cadot 91500
2 B. Vroman 40400
3 T. Boles 38600
4 A. Kavsek 36900
5 P. Wisse 27100
6 Y. Naguib 18800
7 J. Péran 16600
8 A. Khan 15950
9 A. Hudspith 15150
10 F. Pachot 13800
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 54 now available

Feb 04, 2014
(recommended update: build 1403)
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered46976
Total active users29515
Act. users last 24h41
Act. users last hour2
Hits last week1912
Hits last month7041
Go up

Masking in Oracle
Next thread: Too much Paging / Swapping happening in Production Server
Prev thread: RAC DB Manual DB Creation using ASM storage

Message Score Author Date
Hi Everyone, How we can mask a column value pre...... Sandeep Jain Nov 27, 2012
Hello Sandeep, you can search the documentation...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Nov 27, 2012
Thanks Bruno for your reply.Can u please suggest w...... Sandeep Jain Nov 27, 2012
Hello, unfortunately I don't know any free tool...... Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Bruno Vroman Nov 27, 2012
Thanks Bruno for your help ! ... Sandeep Jain Nov 27, 2012
Hi, http://www.dba-village.com/village/dvp_fo...... BenBart BartBen Nov 27, 2012
Sandeep, There are a couple things you need to ...... Adrian Lane Dec 03, 2012
You may use : select tname, replace(TNAME, 'RE'...... Tariq Jawed Dec 07, 2012

Follow up by mail Click here


Subject: Masking in Oracle
Author: Sandeep Jain, India
Date: Nov 27, 2012, 643 days ago
Os info: MS Windows
Oracle info: 11g
Message: Hi Everyone,

How we can mask a column value present in the production which is very sesitive like bank a/c no,Credit card nos etc while replicating to the test/development envirnoments in PL/SQL.Things that need to be considered is referential integrity,data types,length of the column and performace while execuing the code.Any help or pointer will be very helpful.Thanks !

Regards,
Sandeep
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Masking in Oracle
Author: Bruno Vroman, Belgium
Date: Nov 27, 2012, 643 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Sandeep,

you can search the documentation about "Data Masking"; this does exactly what you need.
Example of pointer:
  http://docs.oracle.com/cd/E11882_01/server.112/e16540/tdm_data_masking.htm#RATUG4004
(to see more: http://tahiti.oracle.com, pick the version 11.2, search "Data Masking")

But you have to know that this has a cost: this (the "data masking pack") is an expansive option.

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Masking in Oracle
Author: Sandeep Jain, India
Date: Nov 27, 2012, 643 days ago
Message: Thanks Bruno for your reply.Can u please suggest what else we can think which are freely available.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Masking in Oracle
Author: Bruno Vroman, Belgium
Date: Nov 27, 2012, 643 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts
Message: Hello,

unfortunately I don't know any free tool that would do the same. You have to develop your own tool (a PL/SQL package) to mask the sensitive data, but the task is of course complex...
Some fields are easy to mask because they have no link with anything else (example: a field "amount" can be replaced by something like random_number_in_range_0.2_2.0 * original amount, but to give an example: if you have a table that associates "customer_id" and "credit_card_number" and another table that associates "credit_card_number" and "purchases", you cannot simply put random data in the field "credit_card_number" or the links would be lost...

If the business really needs the data masking feature, the "cheapest" is maybe to buy the option, as developing your own solution it in fact a complete and complex project -that has been done by Oracle Development.
The Oracle tool provides lots of features that would be hard to reproduce (example: names can be "hidden" by replacing them with things that look like names: replacing "Bruno Vroman" by "John Stuart" looks better than replacing it by "Xefgregs Qszgfhrqs" -what you can do with "DBMS_RANDOM.STRING(..."; credit card are replaced by random numbers that look like credit card numbers (including Lhun check digit), phone numbers are replaced by pseudo phone numbers that look like phone numbers, ... you can also use an option "scramble": data in the column is kept, but the values are exchanged randomly between rows...

Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Masking in Oracle
Author: Sandeep Jain, India
Date: Nov 27, 2012, 643 days ago
Message: Thanks Bruno for your help !
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Masking in Oracle
Author: BenBart BartBen, Philippines
Date: Nov 27, 2012, 643 days ago
Message: Hi,


http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=57380&SkipA=0

User is in India, maybe you can brainstorm on what you can do with VPD and write a paper about it :-)

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Masking in Oracle
Author: Adrian Lane, United States
Date: Dec 03, 2012, 637 days ago
Message: Sandeep,

There are a couple things you need to consider; it's both the process and the algorithm used to provide the mask.

The most common method used to replace production values -- let's say credit card -- is ETL. You extract production data to some temporary location (3rd machine/database or temp schema on production database), mask or 'transform' the credit cards with fake proxies, then load into the test database. In this way production data does not touch the test DB.

Or you can load the production data into the test database and then transform/mask in place. This option is easier but carries risks that you'll leak production data in TXLogs or the data will be accessed before masking is complete. So keep in mind that any location that gets a copy of production data needs to be secured.

For a replacement algorithm: If it's simply credit cards you want to replace, there are open-source code snippets you can use for free. There's even a LUHN check algorithm to verify that the fake numbers you've created will look and act like the originals (see LUHN wiki page). Use as an application or as a stored procedure -- whatever works for you.

If you need to mask more complex data, or more correctly data that has complex relationships -- date and time ranges for example -- you really do risk introducing errors into your test environment if you are not careful. It's really easy to break Oracle apps with masking. If budget is a key consideration then there are open source tools / scripts you can use (use Google) but their output needs to be tested. If security is what's critical, find the budget and acquire a commercial masking tool. The tradeoff is time vs. $, but it's the vendors responsibility to get the masks right so they don't break your database. Oracle and a bunch of third party vendors offer solutions.

-Adrian
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Masking in Oracle
Author: Tariq Jawed, Pakistan
Date: Dec 07, 2012, 633 days ago
Message: You may use :

select tname, replace(TNAME, 'RE', 'ZE') FROM TAB

Fixed values can be made dynamic by creating algorithm table, hope this may will solve your problem.

good day!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here