ORA-29471 in DBMS_SQL after upgrade to 11g Release 2
By: Date: 24.07.2012 Categories: !ENG,Errors,ORACLE Метки: ,
RUS: ORA-29471 in DBMS_SQL after upgrade to 11g Release 2

After update (migrations) to the version 11g release 2, at usage PLSQL code which uses packet DBMS_SQL it is possible to receive an error (the same code in 10g works without errors):

ORA-29471: DBMS_SQL access denied
©Bobrovsky Dmitry
The reason is that in 11g security measures for packet DBMS_SQL strengthened to protect the code from cursor snarfing, snoofing, stealing and other types of attacks.
©Bobrovsky Dmitry
More details it in MOS – “Ora-29471: Dbms_sql Access Denied” From Software Package After Upgrade [ID 556301.1]. As there is interesting article in blog David Litchfield’s Weblog – Oracle 11g DBMS_SQL Security Changes.
Dmitry Bobrovsky
In ID 556301.1 suggest to change the application code, explicitly to specify security_level by call DBMS_SQL.OPEN_CURSOR. Since it can be very labor-consuming temporary work around – changes of the hidden parameter _dbms_sql_security_level, ompletely turn off the security features in dbms_sql. For this purpose it is necessary to execute the command and to restart a instance.
Dmitry Bobrovsky
alter system set "_dbms_sql_security_level" = 384 scope=spfile;

Please note, the article pointed out that: “Please note that setting this is not recommended and that this workaround might cease to work in future versions”. So probably in the future it is necessary to make changes to old applications all the same.

More articles from the MOS:

DBMS_SQL 11g Security Error: ORA-29474 [ID 1298367.1]

Ora-29471 Error When Executing Dbms_sql. Close_cursor [ID 760560.1]

Запись ORA-29471 in DBMS_SQL after upgrade to 11g Release 2/a> впервые появилась Dmitry Bobrovsky Blog