Home » SQL & PL/SQL » SQL & PL/SQL » dbms_sql access denied (Oracle 11GR2)
| dbms_sql access denied [message #494253] |
Sat, 12 February 2011 07:42  |
dgloeppky
Messages: 28
Registered: May 2010
|
Junior Member |
|
|
All,
I have the following function that was working in our 10G env, but we are moving to an 11GR2 env. I am getting a "DBMS_SQL access denied" error message, and need a solution. I know that there are new security enhancements to DBMS_SQL, but I don't know how to rework the code.
Here it is.. it is quite long, but I want you to see everything that is going on. Basically, the function is passed a cursor(number).
PROCEDURE p_display(p_cursor_id NUMBER) IS
TYPE v_link_value_tab IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
TYPE v_link_number_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_has_key BOOLEAN := FALSE;
v_link_value v_link_value_tab;
v_link_number v_link_number_tab;
v_empty_link_value v_link_value_tab;
v_empty_link_number v_link_number_tab;
v_replace_key1 VARCHAR2(1000);
v_replace_key2 VARCHAR2(1000);
v_count NUMBER := 0;
v_rows_affected NUMBER;
v_col_cnt NUMBER;
tab_desc dbms_sql.desc_tab;
v_data VARCHAR2(1000);
v_attrib_row_num NUMBER := 0;
v_has_attribute BOOLEAN := FALSE;
v_place_holder NUMBER;
v_err_num NUMBER;
v_err_msg VARCHAR2(100);
v_element_str VARCHAR2(1000);
BEGIN
v_place_holder := p_cursor_id;
v_rows_affected := dbms_sql.execute(p_cursor_id);
dbms_sql.describe_columns(p_cursor_id, v_col_cnt, tab_desc);
FOR i IN 1 .. v_col_cnt LOOP
BEGIN
dbms_sql.define_column(p_cursor_id, i, v_data, 1000);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
FOR j IN 1 .. tab_column.count LOOP
-- column has an attribute
IF BITAND(tab_column(j).attribute_flag, AF_KEY) = AF_KEY THEN
v_count := v_count + 1;
v_link_number(v_count) := tab_column(j).column_number;
v_has_key := TRUE;
END IF;
END LOOP;
v_count := 0;
LOOP
IF dbms_sql.fetch_rows(p_cursor_id) > 0 THEN
FOR i IN 1 .. v_col_cnt LOOP
-- read in the column data
dbms_sql.column_value(p_cursor_id, i, v_data);
FOR j in 1 .. v_link_number.count LOOP
if v_link_number(j) = i then
v_link_value(j) := v_data;
end if;
END LOOP;
END LOOP;
htp.p('<TR>');
-- search for a column that has attributes
FOR i IN 1 .. v_col_cnt LOOP
FOR j IN 1 .. tab_column.count LOOP
-- column has an attribute
IF i = tab_column(j).column_number THEN
v_has_attribute := TRUE;
v_attrib_row_num := j;
EXIT;
END IF;
END LOOP;
-- read in the column data
dbms_sql.column_value(p_cursor_id, i, v_data);
IF NOT v_has_attribute THEN
htp.p('<TD>' || v_data || '</TD>');
ELSE
IF BITAND(tab_column(v_attrib_row_num).attribute_flag,
AF_HIDDEN) = AF_HIDDEN THEN
NULL;
ELSIF BITAND(tab_column(v_attrib_row_num).attribute_flag,
AF_RADIO) = AF_RADIO THEN
-- htp.p('<TD>'|| v_data||'</TD>');
v_element_str := '';
v_element_str := '<TD><INPUT TYPE=RADIO NAME="' || tab_column(v_attrib_row_num)
.control_name || '" VALUE="' || tab_column(v_attrib_row_num)
.attribute_value || '" ';
FOR i IN 1 .. v_link_number.count LOOP
v_element_str := NVL(REPLACE(v_element_str,
'%' || i,
v_link_value(i)),
'');
END LOOP;
IF v_data IS NULL THEN
v_element_str := '<TD> </TD>';
ELSIF v_data = 0 THEN
v_element_str := v_element_str || tab_column(v_attrib_row_num)
.html_attribute || '></TD>';
ELSIF v_data = 1 THEN
v_element_str := v_element_str || 'CHECKED ' || tab_column(v_attrib_row_num)
.html_attribute || '></TD>';
END IF;
htp.p(v_element_str);
ELSIF BITAND(tab_column(v_attrib_row_num).attribute_flag,
AF_CHECKBOX) = AF_CHECKBOX THEN
-- htp.p('<TD>'|| v_data||'</TD>');
v_element_str := '';
v_element_str := '<TD ALIGN=CENTER><INPUT TYPE=CHECKBOX NAME="' || tab_column(v_attrib_row_num)
.control_name || '" VALUE="' || tab_column(v_attrib_row_num)
.attribute_value || '" ';
FOR i IN 1 .. v_link_number.count LOOP
v_element_str := NVL(REPLACE(v_element_str,
'%' || i,
v_link_value(i)),
'');
END LOOP;
IF v_data IS NULL THEN
v_element_str := '<TD> </TD>';
ELSIF v_data = 0 THEN
v_element_str := v_element_str || tab_column(v_attrib_row_num)
.html_attribute || '></TD>';
ELSIF v_data = 1 THEN
v_element_str := v_element_str || 'CHECKED ' || tab_column(v_attrib_row_num)
.html_attribute || '></TD>';
END IF;
htp.p(v_element_str);
ELSIF BITAND(tab_column(v_attrib_row_num).attribute_flag,
AF_LINK) = AF_LINK THEN
IF NOT v_has_key THEN
htp.p('<TD><A HREF="' ||
NVL(tab_column(v_attrib_row_num).attribute_value,
'') || '"' || tab_column(v_attrib_row_num)
.html_attribute || ' > ' || v_data || '</A></TD>');
ELSE
v_replace_key1 := tab_column(v_attrib_row_num)
.attribute_value;
v_replace_key2 := tab_column(v_attrib_row_num)
.html_attribute;
FOR i IN 1 .. v_link_number.count LOOP
v_replace_key1 := NVL(REPLACE(v_replace_key1,
'%' || i,
v_link_value(i)),
'');
v_replace_key2 := NVL(REPLACE(v_replace_key2,
'%' || i,
v_link_value(i)),
'');
END LOOP;
htp.p('<TD><A HREF="' || v_replace_key1 || '"' ||
v_replace_key2 || ' > ' || v_data || '</A></TD>');
v_replace_key1 := '';
v_replace_key2 := '';
END IF;
ELSE
htp.p('<TD>' || v_data || '</TD>');
END IF;
END IF;
v_has_attribute := FALSE;
END LOOP;
htp.p('</TR>');
ELSE
EXIT;
END IF;
END LOOP;
dbms_sql.close_cursor(v_place_holder);
v_has_key := FALSE;
v_counter := 0;
-- tab_column.delete(1,tab_column.count);
-- v_link_value.delete(1,v_link_value.count);
-- v_link_number.delete(1,v_link_number.count);
tab_column := empty_tab_column;
v_link_value := v_empty_link_value;
v_link_number := v_empty_link_number;
EXCEPTION
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 100);
htp.p('<H3> Error Message from CCUTIL.cwa_html_table.p_display ' ||
v_err_num || '-' || v_err_msg || '</H3>');
if dbms_sql.is_open(p_cursor_id) then
dbms_sql.close_cursor(v_place_holder);
end if;
raise;
END p_display;
Cheers,
DGL
|
|
|
|
|
|
| Re: dbms_sql access denied [message #494262 is a reply to message #494259] |
Sat, 12 February 2011 09:25  |
dgloeppky
Messages: 28
Registered: May 2010
|
Junior Member |
|
|
This is this interesting bit... I don't know which line and don't know how to trap this error. Unfortunately I can't remember how I initially got the code to
point me to a DBMS_SQL access denied problem... which I believe it ORA-29471. (I've been on no sleep trying to resolve this) What happens is that the PL/SQL page simply goes to a blank page.
But, if I simply put a are the BEGIN, code works beautifully, so I'm fairly certain the problem resides somewhere in this procedure.
Cheers, and any help you can give is greatly appreciated.
[Updated on: Sat, 12 February 2011 09:30] Report message to a moderator |
|
|
|
|
|
| Re: dbms_sql access denied [message #494266 is a reply to message #494264] |
Sat, 12 February 2011 10:04 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
|
You might want to remove the WHEN OTHER exception handler, since it's basically just hiding the real error. The error message you will get then will be much more useful.
|
|
|
|
|
|
|
|
|
|
| Re: dbms_sql access denied [message #494278 is a reply to message #494272] |
Sat, 12 February 2011 13:48 |
dgloeppky
Messages: 28
Registered: May 2010
|
Junior Member |
|
|
Trace file /software/oradump1/PRDN/diag/rdbms/prdn/PRDN/trace/PRDN_ora_24086.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /software/sworacle/oraban11202
System name: SunOS
Node name: sunprod1
Release: 5.10
Version: Generic_144488-04
Machine: sun4u
Instance name: PRDN
Redo thread mounted by this instance: 1
Oracle process number: 102
Unix process pid: 24086, image: oracle@sunprod1
*** 2011-02-11 15:24:24.237
*** SESSION ID:(2321.83) 2011-02-11 15:24:24.237
*** CLIENT ID:() 2011-02-11 15:24:24.237
*** SERVICE NAME:(SYS$USERS) 2011-02-11 15:24:24.237
*** MODULE NAME:(httpd.worker@(TNS ) 2011-02-11 15:24:24.237
*** ACTION NAME:() 2011-02-11 15:24:24.237
DBMS_SQL SECURITY ERROR: ORA-29471!
532e5c1d0 1825 package body SYS.DBMS_SQL
534878d88 98 package body CCUTIL.CWA_HTML_TABLE
4ce487520 2121 package body CCBPAPI.SC_COURSE_REG_UI
4ce487520 1593 package body CCBPAPI.SC_COURSE_REG_UI
4e26871b8 33 anonymous block
*** 2011-02-11 15:24:51.932
DBMS_SQL SECURITY ERROR: ORA-29471!
532e5c1d0 1825 package body SYS.DBMS_SQL
534878d88 98 package body CCUTIL.CWA_HTML_TABLE
4ce487520 2121 package body CCBPAPI.SC_COURSE_REG_UI
4ce487520 1593 package body CCBPAPI.SC_COURSE_REG_UI
4e26871b8 33 anonymous block
|
|
|
|
| Re: dbms_sql access denied [message #494279 is a reply to message #494268] |
Sat, 12 February 2011 13:48 |
 |
Barbara Boehmer
Messages: 9005
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your procedure has errors and does not even compile. However, some of the errors are due to missing variable declarations that may be present in a package that contains the procedure. You need to provide all relevant parts, including the enclosing package and how it is being called. Due to the new protections in 11g against SQL injection, it does not take much to cause access to be denied and to remain so until you reconnect. It can be as simple as trying to open and close cursors or check them. Your p_cursor_id is an input variable, so you should not be trying to assign anything directly to it. You should assign it to something else, then use that. You should also assign it to only one such variable, then close only that variable. Please see the simple example below that reproduces the error, not necessarily in the manner that your procedure does. In order to correct it, I have reconnected, then re-run the code without the second attempt to close a cursor. Notice that, even with checking to see if it is open before trying to close it, it causes an error. So, if your procedure has already closed it or failed to open it or tried to assign something directly to it as an input parameter, then an error goes to your exception section, and it checks to see if it needs to be closed, then it causes the error. You should remove the exception section and comment out most of the code and test one little piece at a time, until you find which piece causes the error. Don't forget to reconnect before testing revised code. If you can at least get it to tell you what line number the error occurs on, instead of displaying a blank page, then that would be a start.
-- reproduction of error:
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE p_display
2 (p_cursor_id NUMBER)
3 IS
4 v_place_holder NUMBER;
5 v_cursor_id NUMBER;
6 v_col_cnt NUMBER;
7 tab_desc DBMS_SQL.DESC_TAB;
8 v_data VARCHAR2(1000);
9 BEGIN
10 v_place_holder := p_cursor_id;
11 v_cursor_id := p_cursor_id;
12 DBMS_SQL.DESCRIBE_COLUMNS (v_cursor_id, v_col_cnt, tab_desc);
13 FOR i IN 1 .. v_col_cnt LOOP
14 DBMS_SQL.DEFINE_COLUMN (v_cursor_id, i, v_data, 1000);
15 END LOOP;
16 DBMS_OUTPUT.PUT_LINE ('------------------------------');
17 WHILE DBMS_SQL.FETCH_ROWS (v_cursor_id) > 0 LOOP
18 FOR i IN 1 .. v_col_cnt LOOP
19 DBMS_SQL.COLUMN_VALUE (v_cursor_id, i, v_data);
20 DBMS_OUTPUT.PUT_LINE (v_data);
21 null;
22 END LOOP;
23 DBMS_OUTPUT.PUT_LINE ('------------------------------');
24 END LOOP;
25 IF DBMS_SQL.IS_OPEN (v_cursor_id) THEN
26 DBMS_SQL.CLOSE_CURSOR (v_cursor_id);
27 END IF;
28 IF DBMS_SQL.IS_OPEN (v_place_holder) THEN
29 DBMS_SQL.CLOSE_CURSOR (v_place_holder);
30 END IF;
31 END;
32 /
Procedure created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE calling_proc
2 AS
3 v_refcursor SYS_REFCURSOR;
4 v_cursor_id NUMBER;
5 BEGIN
6 OPEN v_refcursor FOR SELECT * FROM dept ORDER BY deptno;
7 v_cursor_id := DBMS_SQL.TO_CURSOR_NUMBER (v_refcursor);
8 p_display (v_cursor_id);
9 END calling_proc;
10 /
Procedure created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> EXEC calling_proc
------------------------------
10
ACCOUNTING
NEW YORK
------------------------------
20
RESEARCH
DALLAS
------------------------------
30
SALES
CHICAGO
------------------------------
40
OPERATIONS
BOSTON
------------------------------
BEGIN calling_proc; END;
*
ERROR at line 1:
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1037
ORA-06512: at "SCOTT.P_DISPLAY", line 28
ORA-06512: at "SCOTT.CALLING_PROC", line 8
ORA-06512: at line 1
-- reconnect and correct problem by remove second closure check:
SCOTT@orcl_11gR2> CONNECT scott/tiger
Connected.
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE p_display
2 (p_cursor_id NUMBER)
3 IS
4 v_place_holder NUMBER;
5 v_cursor_id NUMBER;
6 v_col_cnt NUMBER;
7 tab_desc DBMS_SQL.DESC_TAB;
8 v_data VARCHAR2(1000);
9 BEGIN
10 v_place_holder := p_cursor_id;
11 v_cursor_id := p_cursor_id;
12 DBMS_SQL.DESCRIBE_COLUMNS (v_cursor_id, v_col_cnt, tab_desc);
13 FOR i IN 1 .. v_col_cnt LOOP
14 DBMS_SQL.DEFINE_COLUMN (v_cursor_id, i, v_data, 1000);
15 END LOOP;
16 DBMS_OUTPUT.PUT_LINE ('------------------------------');
17 WHILE DBMS_SQL.FETCH_ROWS (v_cursor_id) > 0 LOOP
18 FOR i IN 1 .. v_col_cnt LOOP
19 DBMS_SQL.COLUMN_VALUE (v_cursor_id, i, v_data);
20 DBMS_OUTPUT.PUT_LINE (v_data);
21 null;
22 END LOOP;
23 DBMS_OUTPUT.PUT_LINE ('------------------------------');
24 END LOOP;
25 IF DBMS_SQL.IS_OPEN (v_cursor_id) THEN
26 DBMS_SQL.CLOSE_CURSOR (v_cursor_id);
27 END IF;
28 -- IF DBMS_SQL.IS_OPEN (v_place_holder) THEN
29 -- DBMS_SQL.CLOSE_CURSOR (v_place_holder);
30 -- END IF;
31 END;
32 /
Procedure created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE calling_proc
2 AS
3 v_refcursor SYS_REFCURSOR;
4 v_cursor_id NUMBER;
5 BEGIN
6 OPEN v_refcursor FOR SELECT * FROM dept ORDER BY deptno;
7 v_cursor_id := DBMS_SQL.TO_CURSOR_NUMBER (v_refcursor);
8 p_display (v_cursor_id);
9 END calling_proc;
10 /
Procedure created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> EXEC calling_proc
------------------------------
10
ACCOUNTING
NEW YORK
------------------------------
20
RESEARCH
DALLAS
------------------------------
30
SALES
CHICAGO
------------------------------
40
OPERATIONS
BOSTON
------------------------------
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
|
|
|
|
| Re: dbms_sql access denied [message #494283 is a reply to message #494281] |
Sat, 12 February 2011 14:27 |
|
Barbara Boehmer
Messages: 9005
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
As stated previously, if you remove the exception section, then the full error stack should include what line number within the procedure is causing the first problem that it comes to, as it does in the reproduction that I posted. If you leave the exception section in place, them it obscures that.
|
|
|
|
Goto Forum:
Current Time: Wed May 31 20:11:16 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
