More DBMS_RLS and Fine-Grained access control

From: Dan Looby <dan.looby@xxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Wed, 17 Mar 2004 06:43:39 -0500

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(*)
----------
        32

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@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Follow-Ups:
- Re: More DBMS_RLS and Fine-Grained access control
  - From: Tim Gorman

More DBMS_RLS and Fine-Grained access control

Other related posts: