DBMS_SQL SECURITY ERROR: ORA-29471!

"Ora-29471: Dbms_sql Access Denied" From Software Package After Upgrade (Doc ID 556301.1)

---

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Symptoms

Receiving error running DBMS_SQL in 11g.

ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 980
ORA-06512: at "SYS.BUILD_XVIEW", line 28
ORA-06512: at line 12 in table MakeXviews

This may have worked in 10g.

Changes

DBMS_SQL has been recoded with some security checks in 11g.   A new error has been introduced, ORA-29471, which indicates usage fails a security check.

Cause

Usage of DBMS_SQL has failed a security check.

Solution

The application must be recoded.  For a temporary work around, a new parameter security_level can be added into DBMS_SQL.OPEN_CURSOR.

security_level specifies the level of security protection to enforce on the opened cursor. Valid security level values are 0, 1, and 2. When a NULL argument value is provided to this overload, as well as for cursors opened using the overload of open_cursor without the security_level parameter, the default security level value 1 will be enforced on the opened cursor.

• Level 0 - allows all DBMS_SQL operations on the cursor without any security checks. The cursor may be fetched from, and even re-bound and re-executed, by code running with a different effective userid or roles than those in effect at the time the cursor was parsed. This level of security is off by default.

dbms_sql.open_cursor (
SECURITY_LEVEL in integer)
return integer;

• Level 1 - requires that the effective userid and roles of the caller to DBMS_SQL for bind and execute operations on this cursor must be the same as those of the caller of the most recent parse operation on this cursor.
• Level 2 - requires that the effective userid and roles of the caller to DBMS_SQL for all bind, execute, define, describe, and fetch operations on this cursor must be the same as those of the caller of the most recent parse operation on this cursor.

 

To completely turn off the security features in dbms_sql, you can set the following parameter :

alter system set "_dbms_sql_security_level" = 384 scope=spfile;

Please note that this setting is not recommended and that this workaround might cease to work in future versions.

Document reference:

Security model:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#i1027587
Open cursor:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#i997665