Re: One way column in a view

From: Lewis C <lewisc_at_excite.com>
Date: Fri, 15 Apr 2005 01:00:35 GMT
Message-ID: <vi3u51ho4jr97ncch11mhnr6m6dg6oa246@4ax.com>

On Thu, 14 Apr 2005 22:12:53 GMT, "Randy Harris" <notvalid_at_no.spam> wrote:

>Anyone know of a way that I can create a view that would prevent viewing a
>certain column but still permit that column to be used in the where clause?
>I need for users to be able to look up data by the credit card number, but
>not be able to get a list of credit card numbers.
>
>The database is currently 8.1.7.
>

Unfortuantely you're running an database so old it is no longer supported. I'm not sure what version they added column masking in secure views but I know it's available in 10g and I'm almost positive that it's not available in 8i. I talk about it briefly in http://blogs.ittoolbox.com/oracle/guide/archives/003769.asp

You could probably do something tricky with a function. If you used a hashing routine and selected the hash, you could then call the hash on the way in.

Say something like,

Create view mytst as
select col1, col2, myhashroutine(cc_num) hash_creditcard_number from finapps_table;

Then you could query it as

select * from mytest where hash_creditcard_number = myhashroutine(cc_num);

Use a function based index for performance of course. I gues you could store the data already hashed and then you wouldn't need an FBI. Just use a regular index, i.e.

Create view mytst as
select col1, col2, hashed_cc_num
from finapps_table;

Then you could query it as

select * from mytest where hashed_cc_num = myhashroutine(cc_num);

If someone just selected all the data, all they would get was a set of crap data. If you use a one way hash you should be safe.

I'd still recommend upgrading though.

Lewis

Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database http://www.suite101.com/welcome.cfm/oracle

Received on Thu Apr 14 2005 - 20:00:35 CDT