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
