Pete Finnigan's Oracle Security Weblog

I noticed a few days ago that David Litchfield had posted two new short papers on Oracle security; one is related to global cursors declared in PL/SQL packages, the other about cursor variable type SYS_REFCURSOR being passed out of functions/procedures. The first paper is " http://www.v3rity.com/global_cursors.pdf - (broken link) The security impact of global cursors in Oracle PL/SQL and the second is http://www.v3rity.com/sys_refcursors.pdf - (broken link) security considerations for SYS_REFCURSOR use in Oracle PL/SQL applications

These are nice papers, are usual from David but I think the picture (security problem) is bigger than David indicates in his papers. I have been exploiting the fact that cursors are simply a pointer (underneath in the C code of OCI/UPI) to a struct that handles data access for many years. If you start out with Pro*C and OCI as i did you are used to handling cursors!

When i started to do security audits I wrote a scanner called "oscan" which is not public and never will be but a lot of the functionallity in terms of security tests, methods and security checks are now in PFCLScan. The "oscan" script is huge (over 45,000 lines of SQL*Plus, SQL, PL/SQL and some hosts commands) but its all driven from one start SQL*Plus script oscan-{ver}.sql. One thing I did in the master version script was to modularise the code so that regularly used features became seperate SQL*Plus files which are effectively functions called by setting parameters via SQL*Plus defines. I also wanted to centralise various features such as internal (to the script) security code, input/output and encryption of output and whilst a seperate SQL*Plus file with a dynamic block works for Oracle security tests it doesnt work as a "function" or "package" to call from within another dynamic block of PL/SQL, you cannot call a SQL*Plus script with @@script.sql from within PL/SQL!!. The one underlying remit of my codewas to not create objects in the database being scanned hence the code is essentially a very large bunch of anonymous blocks. But it would be nice also to have the ability to create functions or packages without the privileges to do so and also without actually doing it so that the database doesnt gain any new objects as part of an audit. A few years ago I came up with using cursors to simulate a procedure/function in a SQL*Plus script.

As i said the whole purpose was to centralise the functionallity of certain things; the key one being encrypting output that is normally written with dbms_output.put_line() or put_text(); i needed a centralised solution rather than edit a huge amount of scripts each time i used "oscan" so that i could change the key for every client or every run of the script - effectively a one-time-pad.

I wanted to do (pseudo code):

create procedure write_op(in string)
create function encrypt(in string) out string
begin
derive key
do some encryption on the string using key
return the encrypted string
end encrpt
begin
dbms_output.put_line(encrypt(in));
end

In this way i could use "sed" and replace all my calls to dbms_output.put_line() with calls to write_op() and have a centralised write function that could also encrypt output (or not) but also have key management in one place. You may question the key management in a dynamic script but remember its run once from a controlled laptop, a different key each time and the output is collected to my laptop. I can then decrypt off site.

I should stop and add thanks to Tanel Poder as his snapper script inspired me to be more creative with SQL*Plus scripts when i first saw it . I have used his ideas to imitate a C pre-compiler. Tanel has also implemented getopt which is great, i dont do this as I use accept to get input but its very nice code. Have a look at Tanels script snapper.sql to see what i mean; this is one of the best SQL*Plus scripts around.

Back to the plot. I wanted to emulate the PL/SQL procedure above so i looked to cursors and dynamic blocks. I created a sqlplus variable to hold the cursor number and then a dynamic block to emulate the procedures above and within that block I parse the PL/SQL code that is the procedure - except its a dynamic block of code. I then use bind variables to pass in the string and within each use in other dynamic blocks do the bind and execute the cursor referenced by the SQL*Plus variable.

In most cases I was able to "sed" the code files and replace dbms_output.put_line('string') with begin dbms_sql.bind_variable(:c,':s','string'); :i:=dbms_sql.execute(:c); end; and it allowed me to have a global procedure used from thousands of other locations without needing to create an actual procedure.

Here is a simple example that illustrates the main process:

--
-- test of dynamic procedures for use in SQL*Plus
--
-- The aim is to emulate a procedure without creating it

-- --------------------------------------------------
-- Create the "procedure"
-- --------------------------------------------------

var lv_str varchar2(2000);
var curnum number;

-- null the string
exec :lv_str:='';

declare

begin
:curnum:=dbms_sql.open_cursor;
dbms_sql.parse(:curnum,'declare bv varchar2(2000):=:ipstr; begin dbms_output.put_line(bv);end;',dbms_sql.native);
end;
/

print curnum

-- -------------------------------------------------------
-- end of declaring the "procedure"
-- -------------------------------------------------------



-- -------------------------------------------------------
-- Call the dynamic procedure
-- -------------------------------------------------------

-- prime the string to print it
exec :lv_str:='hello world';

declare
n number;
begin
-- bind the variable
dbms_sql.bind_variable(:curnum,':ipstr',:lv_str);
n:=dbms_sql.execute(:curnum);
end;
/

-- --------------------------------------------------------
-- End of dynamic procedure call
-- --------------------------------------------------------


-- --------------------------------------------------------
-- Dynamic procedure call
-- --------------------------------------------------------
-- now try and write a second string
exec :lv_str:='Here is a second string';
declare
n number;
begin
-- bind the variable
dbms_sql.bind_variable(:curnum,':ipstr',:lv_str);
n:=dbms_sql.execute(:curnum);
end;
/

-- --------------------------------------------------------
-- End of dynamic procedure call
-- --------------------------------------------------------

-- finally close the cursor
begin
dbms_sql.close_cursor(:curnum);
end;
/


Running it gives:

SQL> @dyn_proc

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


CURNUM
----------
1760564375


PL/SQL procedure successfully completed.

hello world

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

Here is a second string

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL>

Anyway the point of all of this digression is that Davids research is great but there is further potential for security issues. As you can see above simply passing out an integer from a procedure / function where that integer is the cursor ID is just as bad. That cursor ID can then be used to execute the cursor again or re-parse it or close it. I can do the same with an actual procedure and use the integer in DBMS_SQL

David also explicitly discussed SYS_REFCURSOR variables which are weak typed cursor variables but the potential for the same issue is there with any cursor variable. For instance you can declare a type as REF CURSOR and then declare a variable to be of that type and that variable if passed into/out of a function it is the same issue. Anyway the point is it doesnt need to be a variable of SYS_REFCURSOR to be vulnerable any cursor variable is vulnerable in the same way in that it can be re-parsed, re-bound, re-executed. This is the way they were designed to work!

In terms of solutions the simple answer would be to not create global cursors in package headers and hide them inside package bodys instead where they cannot be directly accessed. Passing cursor variables is harder to solve when weak typed cursor variables such as SYS_REFCURSOR are used ; its better to use strongly typed cursor variables where the return clase is matched to a specific business table/requirement that way the SQL thats opened must return the same signature. They can still be abused but the abuse is more limited to the signature. Idealy dont use cursor variables.