Home » SQL & PL/SQL » SQL & PL/SQL » masking card number first 10 ten digits (merged)
masking card number first 10 ten digits (merged) [message #436922] Tue, 29 December 2009 10:09 Go to next message
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 #436926 is a reply to message #436922] Tue, 29 December 2009 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'xxxxxxxxxx'||substr...

Regards
Michel
Re: masking card number first 10 ten digits (merged) [message #436928 is a reply to message #436922] Tue, 29 December 2009 10:44 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Thanks michel

also few users must see this entire field as they are CEO etc

how can i make it visible for them?

Please guide me
Re: masking card number first 10 ten digits (merged) [message #436929 is a reply to message #436928] Tue, 29 December 2009 10:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Look into virtual private database.
Re: masking card number first 10 ten digits (merged) [message #436932 is a reply to message #436928] Tue, 29 December 2009 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How Oracle can know he/she is a CEO?
And why a CEO should be able to see the atm card number?
Do you allow Amazon CEO to know your card number?

Regards
Michel

[Updated on: Tue, 29 December 2009 11:04]

Report message to a moderator

Re: masking card number first 10 ten digits (merged) [message #436938 is a reply to message #436922] Tue, 29 December 2009 11:15 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
your rite Smile michel


but the bank here has this requirement that few people must have access to all corporate big accounts

hence the question how to hide it for few people

any function available in oracle?
Re: masking card number first 10 ten digits (merged) [message #436939 is a reply to message #436938] Tue, 29 December 2009 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again "How Oracle can know he/she is a CEO?", the answer depends on this point.

Regards
Michel
Re: masking card number first 10 ten digits (merged) [message #436943 is a reply to message #436922] Tue, 29 December 2009 12:24 Go to previous messageGo to next message
joy_division
Messages: 4640
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 #436948 is a reply to message #436922] Tue, 29 December 2009 14:05 Go to previous messageGo to next message
bellouch
Messages: 5
Registered: December 2009
Junior Member
lpad(substr(<object>,length(<object>)-5,length(<object>),10,'x')

wish that can help

Abder
Re: masking card number first 10 ten digits (merged) [message #436949 is a reply to message #436948] Tue, 29 December 2009 14:08 Go to previous messageGo to next message
bellouch
Messages: 5
Registered: December 2009
Junior Member
should be:
lpad(substr(<object>,length(<object>)-5,length(<object>),16,'x')

wish that can help

Abder
Re: masking card number first 10 ten digits (merged) [message #436950 is a reply to message #436948] Tue, 29 December 2009 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or maybe:
SQL> select lpad(substr('124567890123456',-5),16,'x') from dual;
xxxxxxxxxxx23456

Regards
Michel
Re: masking card number first 10 ten digits (merged) [message #436968 is a reply to message #436922] Wed, 30 December 2009 00:14 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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 #436973 is a reply to message #436922] Wed, 30 December 2009 00:35 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
the card number is in number format

how can i get the last 4 digits only and rest masked by X

please guide as i couldnt find a funtcion to covert this number to character
Re: masking card number first 10 ten digits (merged) [message #436974 is a reply to message #436922] Wed, 30 December 2009 00:52 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
substr worked on the number datatype

no need 2 convert it to to_char strange isnt it

am on oracle 10g

Thanks for all teh hlep friends
Re: masking card number first 10 ten digits (merged) [message #436982 is a reply to message #436974] Wed, 30 December 2009 02:15 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: How to add minutes to date?
Next Topic: Sum of debit and Credit
Goto Forum:
  


Current Time: Mon Dec 05 08:46:40 CST 2016

Total time taken to generate the page: 0.08340 seconds