Call: +44 (0)7759 277220 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "Data breach concerns running rampant, survey finds"] [Next entry: "Using Field Programmable Gate Arrays (FPGA) to crack passwords"]

Script to find all privileges assigned to a user/role - users complaint



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 http://www.petefinnigan.com/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 http://www.petefinnigan.com/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.

There has been 9 Comments posted on this article


June 23rd, 2007 at 06:26 pm

Chief says:

Ya spelled "interface" incorrectly. satisfied



June 24th, 2007 at 11:13 pm

Paul says:

spelt with an "h" right?



June 24th, 2007 at 11:16 pm

Paul says:

as in.. intheface (n., v.) : to throw errors logs and criticism back to the source in as short a time as possible with as little thought as possible. common syndrome in the blogging and FLOSS communities. "Did you see that Pete's latest script got inthefaced?" crazy



June 25th, 2007 at 11:09 am

Simon says:

Chief, how can you complain about spelling with a sentence like "ya spelled "interface" incorrectly"

That is incorrect English, MS Word says: "ya" - yaw, yak, yam, yap, aye??



June 25th, 2007 at 01:43 pm

Sunder Iyer says:

smile
Maybe every script that's out there should have links to Oracle documentation or should contain suggestions such as 'RTFM' ...



June 25th, 2007 at 02:17 pm

Bill S. says:

Wow, I just read this following a link from Tom Kyte's blog and can only say:

Mr. Teoh, maybe Pete Finnegan can come over and run it for you on your machine - Pete, what do you say?
wink



June 25th, 2007 at 02:28 pm

PF says:

well of course I could pop over, especially if the weather is better than here smile



June 27th, 2007 at 06:50 pm

Dave says:

Some folks would b1tch if they were hung with a new rope. sad

Keep up the good work don't let the whiners get you down



July 7th, 2007 at 04:05 am

Chief says:

I would fully exPECT a new rope were I to be HANGED!! big grin