From: aowens2325@my-deja.com Subject: Re: RLS with exceptions Date: 2000/08/08 Message-ID: <8mpbpb$5ic$1@nnrp1.deja.com> References: <8mn4d9$ilg$1@nnrp1.deja.com> <965683652.5068.0.nnrp-13.9e984b29@news.demon.co.uk> <8mndhs$q2c$1@nnrp1.deja.com> <965688589.4180.0.nnrp-12.9e984b29@news.demon.co.uk> X-Http-Proxy: 1.1 x69.deja.com:80 (Squid/1.1.22) for client 208.53.82.114 Organization: Deja.com - Before you buy. X-Article-Creation-Date: Tue Aug 08 16:16:48 2000 GMT X-MyDeja-Info: XMYDJUIDaowens2325 Newsgroups: comp.databases.oracle.server X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt) 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@news.demon.co.uk>, "Jonathan Lewis" 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@my-deja.com wrote in message <8mndhs$q2c$1@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@news.demon.co.uk>, > > "Jonathan Lewis" 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@my-deja.com wrote in message <8mn4d9 $ilg$1@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@news.demon.co.uk>, > > "Jonathan Lewis" 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@my-deja.com wrote in message <8mn4d9 $ilg$1@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.