Re: block a field

From: David Boyd <davidb158_at_hotmail.com>
Date: Thu, 18 May 2006 13:49:05 -0400
Message-ID: <BAY101-F304ED9C8FAC3236559666EFA60@phx.gbl>

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_at_preferisco.com>
>Reply-To: nigel_at_preferisco.com
>To: davidb158_at_hotmail.com, oracle-l_at_freelists.org
>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_at_il.proquest.com>
>To: davidb158_at_hotmail.com; oracle-l_at_freelists.org
>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

Received on Thu May 18 2006 - 12:49:05 CDT