Executing User-Supplied Queries with DBMS_SQL

Executing User -Supplied Queries with DBMS_SQL

The DBMS_SQL default package allows SQL to be dynamically executed. Owned by SYS it has been defined with the AUTHID CURRENT_USER keyword so it runs with the privileges of the invoker. This protects the DBMS_SQL procedures against direct attacks, but if called from another PL/SQL program that uses definer rights it can be problematic . Before we get to how the DBMS_SQL procedures can be dangerous, let's examine how it works. Consider the following code:

 DECLARE C NUMBER; R NUMBER; STMT VARCHAR2(200); BEGIN       STMT:='SELECT 1 FROM DUAL';       C :=DBMS_SQL.OPEN_CURSOR;       DBMS_SQL.PARSE(C, STMT, DBMS_SQL.NATIVE);       R := DBMS_SQL.EXECUTE_AND_FETCH(C);       DBMS_SQL.CLOSE_CURSOR(C); END; 

Here a cursor, C, is opened using the OPEN_CURSOR function. The SQL statement, 'SELECT 1 FROM DUAL', is then parsed using DBMS_SQL.PARSE(C, STMT, DBMS_SQL.NATIVE). Once parsed, the query is executed using DBMS_SQL.EXECUTE_AND_FETCH(C). Alternatively, the DBMS_SQL.EXECUTE(C) function could be called followed by a call to DBMS_SQL.FETCH_ROWS(C). Finally, the cursor is closed with DBMS_SQL.CLOSE_CURSOR(C). Any query can be executed by these procedures. This includes calls to GRANT, CREATE, and ALTER. When an attempt is made to run such a query using DBMS_SQL, however, an error is returned.

 ORA-01003: no statement parsed ORA-06512: at "SYS.DBMS_SYS_SQL", line 1216 ORA-06512: at "SYS.DBMS_SQL", line 334 

It has, however, succeeded. To see this in action, run the following queries:

 SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE = 'DBA'; returns GRANTEE ------------------------------ SYS WKSYS SYSMAN SYSTEM 

Then run

 DECLARE C NUMBER; R NUMBER; STMT VARCHAR2(200); BEGIN       STMT:='  GRANT DBA TO PUBLIC';  C :=DBMS_SQL.OPEN_CURSOR;       DBMS_SQL.PARSE(C, STMT, DBMS_SQL.NATIVE);       R := DBMS_SQL.EXECUTE_AND_FETCH(C);       DBMS_SQL.CLOSE_CURSOR(C); END; / 

This returns

 ORA-01003: no statement parsed ORA-06512: at "SYS.DBMS_SYS_SQL", line 1216 ORA-06512: at "SYS.DBMS_SQL", line 334 

But then running

 SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE = 'DBA'; 

again, this time, returns

 GRANTEE ------------------------------ SYS WKSYS  PUBLIC  SYSMAN SYSTEM 

Now run

 REVOKE DBA FROM PUBLIC; 

You don't want to leave that role assigned.

As far as security is concerned the key procedure is DBMS_SQL.PARSE. A more secure option is to run the PARSE_AS_USER procedure of the DBMS_SYS_SQL package instead. This procedure parses the statement using the privileges of the current user and not the definer of the procedure. So assume SYS has created two procedures P and Q as follows :

 CREATE OR REPLACE PROCEDURE P AS  C NUMBER; R NUMBER; STMT VARCHAR2(200); BEGIN       STMT:='GRANT DBA TO PUBLIC';       C :=DBMS_SQL.OPEN_CURSOR;  DBMS_SQL.PARSE(C, STMT, DBMS_SQL.NATIVE);  R := DBMS_SQL.EXECUTE_AND_FETCH(C);       DBMS_SQL.CLOSE_CURSOR(C); END; / GRANT EXECUTE ON P TO PUBLIC; CREATE OR REPLACE PROCEDURE Q AS  C NUMBER; R NUMBER; STMT VARCHAR2(200); BEGIN       STMT:='GRANT DBA TO PUBLIC';       C :=DBMS_SQL.OPEN_CURSOR;  DBMS_SYS_SQL.PARSE_AS_USER(C, STMT, DBMS_SQL.NATIVE);  R := DBMS_SQL.EXECUTE_AND_FETCH(C);       DBMS_SQL.CLOSE_CURSOR(C); END; / GRANT EXECUTE ON Q TO PUBLIC; 

When SCOTT executes procedure P the grant succeeds, but if SCOTT runs procedure Q the grant will fail with

 ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SYS_SQL", line 1585 ORA-06512: at "SYS.Q", line 8 

Assuming that the more secure DBMS_SYS_SQL.PARSE_AS_USER has not been used, but rather, DBMS_SQL.PARSE, in a PL/SQL procedure and user input is passed to it, there's potential for abuse by attackers .



Database Hacker's Handbook. Defending Database Servers
The Database Hackers Handbook: Defending Database Servers
ISBN: 0764578014
EAN: 2147483647
Year: 2003
Pages: 156

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net