More DBMS_RLS and Fine-Grained access control

From: Dan Looby <dan.looby_at_oit.gatech.edu>
Date: Wed, 17 Mar 2004 06:43:39 -0500
Message-Id: <p06020418bc7de217a6da@[130.207.163.55]>

First I want to thank those who responded to my last inquiry with some great help/resources.

I created a policy function based upon subject to restrict users from updating, inserting or deleting schedule data if not in their subject area(s). I added the policy (DBMS_RLS.ADD_POLICY). And then I tested it:

SQL > CONNECT auser
Enter password:
Connected.
SQL> SELECT COUNT(*)
   2 FROM CLASS_SCHEDULE
   3 WHERE TERM_CODE = '200402'
   4 AND SUBJECT_CODE = 'SPAN';    COUNT(*)

SQL> DELETE
   2 FROM CLASS_SCHEDULE
   3 WHERE TERM_CODE = '200402'
   4 AND SUBJECT_CODE = 'SPAN'; 0 rows deleted.

Great!

When an attempt is made to update CLASS_SCHEDULE for the same term and subject SQL also returns '0 rows updated'. Marvelous! When the user attempts to insert a class into the schedule for a subject they aren't permitted to they get 'ORA-28115: policy with check option violation'. Terrific!

Now the troublesome part. Then I tried the user in Forms 6 (patch set 14). Oracle is 9.2.0.4. Queried up a class with 'SPAN' as the subject, updated a field, committed the record and got 'FRM-40400: Transaction complete: 1 records applied and saved'. First thought: oops! But re-query of the record shows the value was not changed. Whew. Block does have a KEY-COMMIT trigger that simply contains a COMMIT_FORM command.

So why does forms say one record was updated (it wasn't) while SQL states zero records were updated? If user doesn't re-query after COMMIT and exits the form he/she isn't asked about committing changes (since the database wasn't actually updated) and is therefore erroneously led to believe the change was made.

Dan

--

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Daniel P. Looby                     email: dan.looby_at_oit.gatech.edu

Lead Systems Analyst
Enterprise Information Systems/OIT A meeting is an event at

Georgia Institute Of Technology       which minutes are kept
845 Marietta Street                   and hours are lost!

Atlanta, GA 30332-0305
Office Phone: 404-894-9587

Fax: 404-894-8945

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Wed Mar 17 2004 - 05:39:32 CST