DBMS_SQL access denied

They get it as soon as they start IFS just opening various windows or running some IFS standard reports. It has worked fine before for these users but these errors started to occur yesterday. Have not heared that there where issues with any quick reports or attachements.

Hi, We restarted our MWS’s and seems issue went away, at least for now.

/Anna

Hello,

One of our customers has the same problem: 

Did you manage to solve it?

Thank you for your help! 

The answer to this is based on this info from Oracle doc:

Preventing Malicious or Accidental Access of Open Cursor Numbers

An error, ORA-29471, is raised when any DBMS_SQL subprogram is called with a cursor number that does not denote an open cursor. When the error is raised, an alert is issued to the alert log and DBMS_SQL becomes inoperable for the life of the session.

If the actual value for the cursor number in a call to the IS_OPEN Function denotes a cursor currently open in the session, the return value is TRUE. If the actual value is NULL, then the return value is FALSE. Otherwise, this raises an ORA-29471 error.

As of App10/UPD18 there is at least one place in the program that may try to close a cursor which is not opened (as happened in our case) Quick_Report_API.Validate_SQL_Expression___, we fixed it ourselves, using the technique in #1 below.
There might be other cases like that.

The key part is what is bold above.

So after this happens, those parts of code, anywhere else in the system,  which use DBMS_SQL, will throw an exception from that Oracle session. Other Oracle sessions will be fine.
App server restart will solve this, until next improper usage of DBMS_SQL.

More info form Oracle support below, in #3 containing a permanent - although insecure, so not recommended - solution.

1. Change code to protect all close cursor operations with the following is_open check.

if dbms_sql.is_open(mycursor) then
DBMS_SQL.CLOSE_CURSOR(mycursor);
end if;

-or-

2.  Implement and use the new parameter security_level with dbms_sql.open_cursor which applies fine-grained control to the security of the opened cursor. Level 0 allows all dbms_sql operations on the cursor without any security checks. Document 556301.1 contains more details.

dbms_sql.open_cursor (
SECURITY_LEVEL in integer)
return integer;

-or-

3. To completely turn off the security features in dbms_sql, you can set the following event.  However this is NOT a recommended solution.

alter system set "_dbms_sql_security_level" = 384 scope=spfile;
+ DB restart

Hi @kjro ,

This error "DBMS_SQL SECURITY ERROR: ORA-29471" is a generic Oracle issue which will be thrown when trying to close a cursor that is already closed using dbms_sql.close_cursor.  @jasahu has explained it in his comment.

Oracle has provided solutions for this issue in their official documentation. Since this is a DB issue, it has to be handled from DB level with the help of a Database Administrator. Please refer the below documents when resolving this issue.

ORA-29471 Error When Executing dbms_sql.close_cursor ( Doc ID 760560.1 )

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

Hope it helps.

Thank you,

Aswin.

I have not reported it to IFS because we needed an quick fix (and from IFS we could not expect that) as this bug can have major consequences , once it occurs the only workaround was to restart App Server.

So we fixed it locally in QuickReport-Cust.plsql:
 

@Overtake Core
FUNCTION Validate_SQL_Expression___ (
   validate_status_      OUT VARCHAR2,   
   stmt_                 IN  CLOB,
   is_import_validation_ IN  BOOLEAN DEFAULT FALSE) RETURN VARCHAR2
IS
BEGIN

$TEXTSEARCH   
      WHEN OTHERS THEN
         dbms_sql.close_cursor(cursor_);
         validate_status_ := 'INVALID';
$TEXTREPLACE
      WHEN OTHERS THEN
         --(+) 230215  JASAHU  C_U01-3 (START)
         IF dbms_sql.is_open(cursor_) THEN
         --(+) 230215  JASAHU  C_U01-3 (FINISH)
            dbms_sql.close_cursor(cursor_);
         --(+) 230215  JASAHU  C_U01-3 (START)
         END IF;
         --(+) 230215  JASAHU  C_U01-3 (FINISH)
         validate_status_ := 'INVALID';
$TEXTEND   
   
END Validate_SQL_Expression___;
 

Hi

Error handling still not corrected but it seems that patch 163601 from UPD17 masks problem.