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 .