|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
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.
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.
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.
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: