Re: RLS with exceptions

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.