Pete Finnigan's Oracle Security Weblog

I received an email from Peter Teoh today that said:

I ran the find_all_privs.sql on my SYS for 10gR2 and got the following limits error:

ERROR (write_op) => -20000
declare
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 158
ORA-06512: at "SYS.DBMS_OUTPUT", line 121
ORA-06512: at line 179

Thank you very much for the script!!!!! Terribly useful......

He is the first person to write to me and complain about a free script he has downloaded from my site. I wanted to investigate.

This issue refers to my free script find_all_privs.sql that is available from my Oracle security tools page or directly as find_all_privs.sql. This is one of a series of free scripts written in PL/SQL that i make available from my site tools page for checking privileges and access rights. This is a useful script as it gives a heirarchical view of privileges assigned, i.e. if scott gets single privilege via role_a via role_b via role_c this is visible in a heirarchical listing.

I ran the same test on 10gR2 against the SYS user and got:


SQL>
SQL> set serveroutput on size 1000000
SQL> @c:\scripts\find_all_privs
find_all_privs: Release 1.0.7.0.0 - Production on Sat Jun 23 15:10:34 2007
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF USER TO CHECK [ORCL]: SYS
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

...


TABLE PRIV => SELECT object => SYS.DBA_HIST_LOG grantable => NO
ERROR (main) => -20000
declare
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 180


For updates please visit /tools.htm

SQL>


It fails in the same mode. whats wrong with this picture in 10gR2? - well the limits for DBMS_OUTPUT for line size (up to 32767 from 255) and buffer (up to unlimited) were removed. Edit the script and comment out the line:


--set serveroutput on size 1000000


as so then run the same script again with the following change:


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set serveroutput on size unlimited format word_wrapped
SQL> @c:\scripts\find_all_privs


and it runs successfully:



...

TABLE PRIV => SELECT object => SYSTEM.MVIEW_RECOMMENDATIONS grantable => YES
TABLE PRIV => SELECT object => SYSTEM.MVIEW_WORKLOAD grantable => YES
TABLE PRIV => SELECT object => SYSTEM.REPCAT$_REPPROP grantable => YES
TABLE PRIV => SELECT object => SYSTEM.REPCAT$_REPSCHEMA grantable => YES
TABLE PRIV => UPDATE object => SYSTEM.DEF$_TEMP$LOB grantable => YES

PL/SQL procedure successfully completed.

For updates please visit /tools.htm

SQL> sho serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
SQL>


For 10gR1 and earlier where the DBMS_OUTPUT buffer limit still applies you have two options, the first is to replace DBMS_OUTPUT with your own package that doesnt have a buffer limit. I seem to remember that Tom showed how to do this in one of his books. I have done this myself also but only implememnted the functions i needed.

The second easier option is to use the other output format of my scripts to write to a file, enter 'F' for the second option (write the output to a file) and first set up utl_file_dir or a DIRECTORY object first and specifiy a file for the output. This option was added to avoid the buffer overflow ORU-10027 error in the first place as there is no easy workround prior to 10gR2.

I find it hard to believe that people take the time to complain about free scripts without instead taking the effort to look at the problem themselves and fix it. Afterall there is an interface to the script included to avoid the issue in the first place and for 10gR2 a simple edit will make it work.