block a field
- From: "David Boyd" <davidb158@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 18 May 2006 11:51:58 -0400
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
Other related posts:
