Re: Read Only User

From: John Shaw <John.Shaw_at_correctionscorp.com>
Date: Thu, 16 Dec 2004 10:50:30 -0600
Message-Id: <s1c16883.037@mail.correctionscorp.com>

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_at_yahoo.com> 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

Received on Thu Dec 16 2004 - 11:08:07 CST