Re: block a field

Mark and Nigel,

Thanks very much for your input.

I should mention it in my previous mail. We tried function before. Since t1 is a huge table, the performance is very bad.

Thanks again.

David

From: Nigel Thomas <nigel@xxxxxxxxxxxxxx>
Reply-To: nigel@xxxxxxxxxxxxxx
To: davidb158@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Subject: Re: block a field
Date: Thu, 18 May 2006 09:58:55 -0700 (PDT)

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

----- Original Message ----
From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: davidb158@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Thursday, May 18, 2006 5:27:00 PM
Subject: RE: block a field

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

_________________________________________________________________
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


Other related posts: