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/

--
http://www.freelists.org/webpage/oracle-l