Re: More DBMS_RLS and Fine-Grained access control
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Mar 2004 21:47:39 -0700
The message returned by Forms was:
FRM-40400: Transaction complete: 1 records applied and saved
In the Oracle RDBMS, "records" do not exist -- the database has "rows"
instead. So, we know that it is Forms talking through this message, not the
database.
When you performed the update in SQL*Plus, you did not get an error message,
but a success message that also said "0 rows updated", which is still a
success message augmented by the row count from an API structure returned by
the RDBMS.
Forms is merely counting the number of "records" within its "block" that
were marked for update. Each "record" caused an ON-UPDATE forms trigger to
fire successfully, which in turn executed an update command in the RDBMS
successfully.
Essentially, Forms could do a much better job...
on 3/17/04 4:43 AM, Dan Looby at dan.looby@xxxxxxxxxxxxxx wrote:
> 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
----------------------------------------------------------------
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
-----------------------------------------------------------------
Other related posts:
