APEX

When passing a cursor created by dbms_sql.open_cursor();
from a non-flows schema into wwv_flow_collection.create_collection_from_query_b() it can result in:

Error ORA-20104: create_collection_from_query_b
Error ORA-20104: create_collection_from_query ExecErr
Error ORA-29470: Effective userid or roles are not the same as when cursor was parsed

11g introduced a new security feature for cursors to avoid cursor snarfing/snoofing/stealing/etc. Any cursor created is linked w/ a user/session and can only be used by that user and session. Any attempt to pass it to another schema raises the aforementioned errors.




_=Possible APEX Solution=_




Would it be possible for APEX to use invoker rights for the intial curosr manipualtion/data retrieval and then use a definer's rights package to store the collection data? I'm not sure if it would solve all the security issues, but it might be worth a try.




I can not provide an example of this issue because it would require database access and the ability to create users/schemas, which the supplied APEX instances do not allow.




_=Work Around=_




There is a temporary work around however it required invoking unsuported Oracle features and potentialy destabalizes the security of an entire 11g instance. It is not a suggested approach, but stated here for the sake of completness or those left with no other choice.




Run as SYS as SYSDBA: alter system set "_dbms_sql_security_level"=0 scope=spfile;
Then bounce the database.




This can not be run as alter session or alter system w/o scope=spfile,




This reduced cursor security handeling to 10g and previous AS A DEFAULT. It is possible to still create secured cursors by supplying a 1 or 2 to to the dbms_sql.open_cursor call (ie. dbms_sql.open_cursor(1) ). If an 11g instance has other products installed on it, please be advised that if those products do not explicitly provide security levels then they will be created unsecured.















For some reason dbms_sql.open_cursor will only accept security levels of 1 and 2. If you supply 3 as a security level it will give an error stating that the security level is a range from 0-2. If you specify a security level of 0 it states that 0 is not allowed. This is true whether the fix above is used or not. If anyone has any insight on why this is or if it is possible to use a security level of 0 on demand, I would be very interested.




More on cursor snarfing: [http://www.davidlitchfield.com/blog/archives/00000023.htm]

Edited by: andrew.martinez on Oct 6, 2008 3:02 PM

Edited by: andrew.martinez on Oct 6, 2008 3:02 PM