| masking card number first 10 ten digits (merged) [message #436922] |
Tue, 29 December 2009 10:09  |
avrillavinge
Messages: 98
Registered: July 2007
|
Member |
|
|
need to mask the atm card number first ten digits and display only last 4 digits to specific users
but to certain users entire number should be available
What we did we got the last 5 digits of this number using substr function
substr(<object>,length(<object>)-5,length(<object>)
i want to put xxxx in place of first ten digits and show last 4 or 5 to certain people .
which is best way to achieve this ..can we achieve this in oracle please guide me .
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: masking card number first 10 ten digits (merged) [message #436943 is a reply to message #436922] |
Tue, 29 December 2009 12:24  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
avrillavinge wrote on Tue, 29 December 2009 11:09
i want to put xxxx in place of first ten digits and show last 4 or 5 to certain people .
Just because I am a stickler for poor requirements, do you want 4 x's to replace the first 10 digits or 1 x per digit?
A sloppy way to do it would have a view with a public synonym that masks the data, but in the CEO's local schema, their view would not mask it.
But I have to agree with the other posts and either everyone of no one should access to the data. On the other hand, if the account is ever compromised, all blame would fall upon the CEO.
|
|
|
|
|
|
|
|
|
|
| Re: masking card number first 10 ten digits (merged) [message #436968 is a reply to message #436922] |
Wed, 30 December 2009 00:14 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
This is a great question because it brings to light the eternal struggle between application perspective and database perspective. By this I mean to emphasize the philosophy from which an IT team builds its solutions. The basic question would be this: do we create a solution that is imlemented by applications in their code, or do we create a solution that is implemented in the database and thus is hidden to all applications and enforced for all access to the data.
Naturally I prefer database oriented solutions because they cover the broader perspective but I also realize that at any moment the given state of database technologies we have to work with may support a database solution or may not, and even if they do offer a database solution, the database solution may require a commitment that some shops will not make. All these fine words aside, we have seen both suggestions made in this thread. We have seen examples of how to alter data returned using custom sql (application oriented vision of a solution), and we have had the suggestion made to use VPD (database oriented vision of a solution).
VPD is the database solution. Here is a link for you to start your search. If you read this page and do some additional research I think you will see that using VPD (Virtual Private Database) you can add a security policy to do COLUMN MASKING dynamically (for example based on the user id). This sounds like what you are asking for but beware, most Oracle advanced technologies have frustrating limitations you must live with.
Of course here is the rub. Suppose you implement a VPD security solution for your need. A developer is looking at the value and sees that they are getting XXXXXX1234. No matter what they do they cannot see anything else but this. They wonder if the data is bad. They wonder if the data is not bad, how they can see the real value and how the value gets masked. They cannot find any line of code in the application they are updating that can account for this masking behavior.
This is what I mean by commitment on the part of the IT shop. You must be willing to move beyond the limits of applications working with data and accept the fact that the database rules the data and it may alter the data any way it sees fit, without applications or developers having any knowledge of it occurring. An IT team either accepts this as a natural way of working with data, or they do not. Your success with various soltions will depend upon how much of your team accepts this database philosophy vs. those members of your team who cannot let go of outdated development methods.
I would go with a database solution (VPD and Security Policies or the View solution mentioned below) because with the future I see ahead of us (Oxley Sarbanes being one example we have seen), database solutions can survive in that future. But you will have to convince your mates to accept your vision.
Unfortunately as we will see, this advanced Oracle feature has at least on the surface (by reading the documentation) limitations. For example, the docs suggest that MASKING a column means to return NULL, not some other value. Thus you may not be able to use this feature to get what you want. MAYBE SOMEONE ELSE can provide a column masking VPD solution that returns the value requested by the OP rather than a basic NULL.
As a third alternative, and also a database alternative, you might wish to consider the old school method of creating a view to replace your table, using a CASE expression (DECODE in the old days) inside the view to determine what value to return for a column needing masking. Naturally this has its own set of problems but then choices make life interesting right?
If you want something badly enough you pay for it. If you don't then you do without it.
Good luck. Kevin
[Updated on: Wed, 30 December 2009 00:29] Report message to a moderator |
|
|
|
|
|
|
|
| Re: masking card number first 10 ten digits (merged) [message #436982 is a reply to message #436974] |
Wed, 30 December 2009 02:15 |
 |
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:no need 2 convert it to to_char strange isnt it
There is an implicit conversion from number to string.
But why do you put card number into a number and not a string column? This is not a good design.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
michel