Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
There are 60 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Pete Finnigan's Oracle security weblog


Home » Archives » July 2011 » Cursor variable and global cursors security issues

[Previous entry: "Training, twitter, Oracle security products"] [Next entry: "Oracle Security Training in Denver, USA"]

Cursor variable and global cursors security issues

July 6th, 2011 by Pete


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 "The security impact of global cursors in Oracle PL/SQL" and the second is "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.

There has been 2 Comments posted on this article


July 7th, 2011 at 08:52 am

PeterPatrickGo says:

I also read about David Litchfield's paper for the security issue, very useful for me. wink



August 19th, 2011 at 09:17 am

fabio says:

Nice topic! Good job!


July 2011
SMTWTFS
     12
3456789
10111213141516
17181920212223
24252627282930
31      

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Weblog Home
Weblog Archives


Home
Oracle Security Tools page
Oracle security papers
Oracle Security alerts

Web Development
SQL Server Security

RSS 1.0 FEED
RSS 2.0 FEED
Atom 0.3 FEED
Powered by gm-rss 2.0.0


Valid XHTML 1.0!