Re: RLS with exceptions

Yes, by the way, that was me with the other post about the inaccurate sql%rowcount. That was before I identified that it occurs only when my user defined exception is raised in the policy functions. I tried to see if the exception was somehow affecting the sql%rowcount by putting: dbms_output.put_line('DELETEEXCEPTION' ||SQL%ROWCOUNT); in the delete policy function's exception handling code for my user defined exception. However, this is not even printing out. I also put this code (dbms_output.put_line('insertEXCEPTION' ||SQL%ROWCOUNT);) in the policy function for insert statements, where I have no user defined exception but a "When Others" clause that is executed when the user does not have insert permissions. This line is printing out with a correct rowcount of zero.
In article <965688589.4180.0.nnrp-12.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> There was someone on line a couple of
> days ago with a similar problem (but not
> mentioning RLS) where sql%rowcount
> was set to 1 when it should have been
> zero. Was this also you ?
>
> Is it possibly that your security function
> does an SQL query that returns one row ?
> If so, is it possible that the rowcount is from
> the SQL in the function and not applicable to the
> sql statement that caused the function to be called.
>
> Can you check this, and perhaps confirm it by
> putting a spurious extra sql statement (e.g. to
> delete 4 rows from a table) into the function to
> see if sql%rowcount comes back with other
> values.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> aowens2325_at_my-deja.com wrote in message
 <8mndhs$q2c$1_at_nnrp1.deja.com>...
> >Here is what happens. I have set the context for a user who does not
> >have update or delete permission for a table. When the user attempts
> >to update or delete the table, the policy function which sets the
> >owner_predicate, raises an exception. I then attempt to issue an
> >update and a delete statement for the relevant table. In my script
> >that issues the update and delete I am trapping the user defined
> >exceptions from the policy function in the 'When Others' part. I
 write
> >the sqlcode out to a table so I know the exception is trapped. I
 then
> >check the sql%rowcounts for these two statements. They both return a
> >1, even though the user does not have permission to execute either
> >statement and the record I am trying to update and delete does not
 even
> >exist in the table. If I issue these statements (update and delete)
 for
> >a user who does have permission then the sql%rowcount comes back
> >correctly. I hope this helps clarify the issue.
> >Thanks
> >In article <965683652.5068.0.nnrp-13.9e984b29_at_news.demon.co.uk>,
> > "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> >>
> >> I haven't tried this out, but I would have expected
> >> the query to return no rows at all and simply re-raise
> >> the exception raised by the policy function, as the
> >> policy function is executed before the query is parsed.
> >>
> >> Can you give us an example of what actually happens ?
> >>
> >> --
> >>
> >> Jonathan Lewis
> >> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
> >>
> >> aowens2325_at_my-deja.com wrote in message <8mn4d9
 $ilg$1_at_nnrp1.deja.com>...
> >> >I am implementing Oracle's RLS package and I have added user
 defined
> >> >exceptions to my policy functions. These user defined exceptions
 are
> >> >raised when a user does not have permission to execute the
 statement
> >> >type. I run a script that attempts to issue select, insert,
 update
 and
> >> >delete statements for a user on each table for which the policy
 has
> >> >been enabled. After each statement type I check the sql%rowcount.
> >> >I've discovered that if the user defined exception is raised by
 the
> >> >policy function, the sql%rowcount is not accurate. Any ideas as
 to
 why
> >> >this is happening and how it can be avoided???
> >> >
> >> >
> >> >Sent via Deja.com http://www.deja.com/
> >> >Before you buy.
> >>
> >>
> >In article <965683652.5068.0.nnrp-13.9e984b29_at_news.demon.co.uk>,
> > "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> >>
> >> I haven't tried this out, but I would have expected
> >> the query to return no rows at all and simply re-raise
> >> the exception raised by the policy function, as the
> >> policy function is executed before the query is parsed.
> >>
> >> Can you give us an example of what actually happens ?
> >>
> >> --
> >>
> >> Jonathan Lewis
> >> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
> >>
> >> aowens2325_at_my-deja.com wrote in message <8mn4d9
 $ilg$1_at_nnrp1.deja.com>...
> >> >I am implementing Oracle's RLS package and I have added user
 defined
> >> >exceptions to my policy functions. These user defined exceptions
 are
> >> >raised when a user does not have permission to execute the
 statement
> >> >type. I run a script that attempts to issue select, insert,
 update
 and
> >> >delete statements for a user on each table for which the policy
 has
> >> >been enabled. After each statement type I check the sql%rowcount.
> >> >I've discovered that if the user defined exception is raised by
 the
> >> >policy function, the sql%rowcount is not accurate. Any ideas as
 to
 why
> >> >this is happening and how it can be avoided???
> >> >
> >> >
> >> >Sent via Deja.com http://www.deja.com/
> >> >Before you buy.
> >>
> >>
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Aug 08 2000 - 00:00:00 CDT