RE: block a field
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <davidb158@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 18 May 2006 12:27:00 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of David Boyd
Sent: Thursday, May 18, 2006 11:52 AM
To: oracle-l@xxxxxxxxxxxxx
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/
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
