Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: block a field

Re: block a field

From: Nigel Thomas <nigel_at_preferisco.com>
Date: Thu, 18 May 2006 09:58:55 -0700 (PDT)
Message-ID: <20060518165856.84358.qmail@web54701.mail.yahoo.com>


Function is nice - easily reusable across tables - but you can also do this in pure SQL using decode or case statement  

Create view t1_view as
Select other_field, decode((Select 1 into priv from security where user_name=user),1,id,null) id from t1;

or  

Create view t1_view as
Select other_field, case (Select 1 into priv from security where user_name=user) when 1 then id else null end id from t1;

Regards Nigel

How about:
Create function check_security(user_name varchar2) returning number is priv number;
begin
Select 1 into priv from security where user_name=user; Exception
  when no_data_found then
  priv := 0;
End;
/
Create view t1_view as
Select other_field, decode(check_security(user),1,id,null) id from t1;

Not compiled and tested, but, hopefully, you get the idea....

-Mark

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Boyd Sent: Thursday, May 18, 2006 11:52 AM
To: oracle-l_at_freelists.org
Subject: block a field

Hi List,

We're in Oracle9i. We want to block a field in a table for users that don't have the privilege to see its data. Currently we use a view and a security table to do this.

create view t1_view
as select other_fields, decode(priv, 1, id, '') id from t1, (select count(*) priv from security where user_name=user);

t1 is a huge table. The query does not use the index when I do (select * from t1_view where id='123'). However if I create the view in following way the performance is much better (note: I use 'select 1 from security' instead of 'select count(*) from security').

create view t1_view
as select other_field, decode(priv, 1, id, '') id from t1, (select 1 priv from security where user_name=user);

But the problem is if my user name is not in the security table, I got no rows returned when I do (select * from t1_view) instead of just the id field is blocked.

Does any one know if it's possible to return a value even a null value from (select 1 from security where user_name=user) instead of no rows returned when the user is not in the security table? or do you have a better way to block a field?

David



Express yourself instantly with MSN Messenger! Download today - it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Thu May 18 2006 - 11:58:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US