The ever popular fine grain access
create the function to call to execute the policy.
create or replace function DEPT_LIMIT (obj_schema varchar2, obj_name
varchar2)
return varchar2 is d_predicate varchar2(2000);
begin
d_predicate := 'department in
(select department from security_table
where userid = sys_context (''USERENV'', ''SESSION_USER''))';
return d_predicate;
end DEPT_LIMIT ;
/
-- add the policy
execute dbms_rls.add_policy
('ABC','DEPARTMENT','DEPT_POLICY','ABC','DEPT_LIMIT')
-- test that the policy got created
SQL> select dept_limit('dummy','dummy') from dual;
DEPT_LIMIT('DUMMY','DUMMY')
--------------------------------------------------------------------------------
department in
(select department from security_table
where userid = sys_context ('USERENV', 'SESSION_USER'))
SQL> select * from department
2 ;
no rows selected
Of course you'd have to set up a security table or hard code values for
users that you wanted to exclude.
/* so that we can see it all */
grant EXEMPT ACCESS POLICY to me;
>>> Charlotte Hammond <charlottejanehammond@xxxxxxxxx> 12/16/2004
8:51:25 AM >>>
Hi all,
I've been asked to shoehorn a user with "read only" access into a
database which wasn't designed to accommodate that.
Creating a role with select only on tables and views was easy but I'm
struggling with how to handle packaged functions (which allow indirect
access to view data). I can't grant execute on the whole package, as it
also contains procedures that allow data changes.
I could create wrapper packages with only the functions exposed, but
that looks like a great big maintenance swamp as this isn't a very
stable app and the developers keep on changing the package interfaces.
Any easier ideas? (9.2 btw)
Thanks
- Charlotte
---------------------------------
Do you Yahoo!?
Jazz up your holiday email with celebrity designs. Learn more.
--
http://www.freelists.org/webpage/oracle-l
-----------------------------------------
Disclaimer (i) This e-mail and any files transmitted with it are
confidential and intended solely for the use of the intended recipient(s).
If you have received this e-mail in error, please notify the sender
immediately and delete this e-mail and any associated files from your
system. (ii) Views or opinions presented in this e-mail are solely those of
the author and do not necessarily represent those of Corrections
Corporation of America. (iii) The recipient should check this e-mail and
any attachments for the presence of viruses. The company accepts no
liability for errors or omissions caused by e-mail transmission or any
damage caused by any virus transmitted by or with this e-mail.
--
http://www.freelists.org/webpage/oracle-l