block a field

From: David Boyd <davidb158_at_hotmail.com>
Date: Thu, 18 May 2006 11:51:58 -0400
Message-ID: <BAY101-F1186C6FC03016ABC917FADEFA60@phx.gbl>

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

Received on Thu May 18 2006 - 10:51:58 CDT