| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
--
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Boyd
Sent: Thursday, May 18, 2006 11:52 AM
To: oracle-l_at_freelists.org
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 18 2006 - 11:27:00 CDT
 |
 |