Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

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.

Should We Security Patch Oracle Databases?

Spoiler: Of course!

Security patching of Oracle databases can be a touchy and complex subject for some companies. It is perceived to be complex; companies don’t want the downtime; business is worried that a security patch can break the applications; the application vendor maybe says his application is only certified with a base patch set version and so on.

There is also this perceived lack of customer knowledge of what Oracle has actually fixed in a security patch. I remember detailed and heated discussions at conferences and online at the start of the CPU regime many years ago where people refused to patch (seemingly) because they didn’t know what the patch fixed. In one sense I can understand this; if a customer finds a functional bug in Oracle and raised an SR and a patch wings its way to them; they know for sure if it fixed the functional issue or not. They can test it.

There seemed to be this logic to security patches at the time and after that because a customer didn’t know what a security patch fixed in any detail, they could not test it or be sure it would not affect their application. Oracle or indeed other vendors are not going to give out a list of security vulnerabilities and hacks. This, of course would then be useful to hackers of databases that have not been patched. Oracle do credit researchers in their advisories for security patches but usually not enough details to show how the issue can occur. I have been credited a number of times on CPU advisories and I know what the issue was as I found it each time BUT the customers didn’t and I didn’t know what other fixes were for. Of course, Oracle do not credit their own staff who find and fix security bugs released in the same patches.

We as customers must just get over this fact and accept we will never know exactly what issues were fixed in every security patch release.
Researchers and hackers can reverse engineer each released patch. This is not trivial with C language binaries or DLL/SO but it is not impossible for skilled researchers. I don’t do this so please don’t bother to tell me the legalities in relation to the license agreement; I am just stating people can do it and have done it but not me. Where the patch involves PL/SQL packages then its even easier to reverse engineer the patched PL/SQL as there are unwrappers on the internet.

Why mention reverse engineering?

Well, we will come back to that in a minute.

I have been conducting detailed security audits of Oracle databases for more than 20 years. When I started there was little out there on securing Oracle or indeed hacking Oracle in terms of books or papers or products or tools. People and companies told me that they only had a budget for firewalls or desktop security and whilst when speaking to companies and people at these companies they agreed it’s good to focus on data security and Oracle security they didn’t have time or a budget for it.

At that same time 20 years ago, Oracle had not started releasing any security patches yet; first came alerts " random security patches that got bigger and bigger up until the regimented quarterly security patches were started to be released.

Since the release of quarterly patches, I have asked groups of people at conference talks and also at my trainings whether they apply security patches. The results of these polls are usually between 10% and 30% of people apply patches. Of that group who have applied a patch I also ask who applies patches regularly; once a quarter or 6 months or even once a year. Always there is a larger subset of those who do patch but only once.

In other words, a smaller percentage of customers apply security patches regularly. This is not a good statistic but its not scientific and I didn’t talk to everyone who runs Oracle. Certainly, my experience of asking “do you patch or don’t you?” question is consistent across the years and it’s not changing.

What about databases I have seen; I have seen a lot! They sadly tend to follow a similar pattern as asking people these questions. A lot of customers don’t patch at all and some only patch once after the initial build or after a major upgrade but nothing in-between and some strangely patch the day before I come on site to do a security audit. A small number patch regularly but not enough do.

What should we do to secure data in an Oracle database; well at a high level we should focus on these elements:

  • Patch the Oracle database software

  • Harden the database

  • Implement access controls to the database; this could be network level or database level such as valid node checking or logon triggers

  • Implement user security, least rights

  • Implement data access controls

  • Implement context based security to allow fine grained access to resources and data



The first three are about platform security where Oracle is the platform and the last three are data security design aimed at securing the actual data.
In terms of security patches what can you do at a high level:

  • Patch regularly

  • Patch randomly

  • Don’t patch at all or maybe once the database was built or after a major update of an application or database

  • Try and work around not patching

  • Buy software that tries to replace the patch with a blocking or logging action



My experience is that most people don’t patch regularly or if they do it’s not regular. I do see some sites that do patch and have some semblance of regularity or at least not completely random. Some sites apply patches once a quarter or six monthly.

Not patching (or patching regularly) but also not even trying to work around the fact that there is no security patch is also not good enough. There are often no work arounds for security bugs fixed in quarterly security patches and some issues fixed are where an attacker can exploit the database without credentials or authenticating. These types of issues are dangerous and the only workaround in this case would be to prevent direct TNS access to the database; but what about the genuine database users who may need direct TNS access; my experience is that a large number of sites use open routing and blocking access would be difficult anyway. One step that can be done is limiting access to a list of known IP Addresses using tools such as valid node checking BUT this is a potential partial fix for one problem. What if a patch fixes issues in PL/SQL packages? Then valid node checking would not work; what could we do in this case; limit access to the package is possible but only if it’s not used genuinely in your application.

And the list goes on. Yes, we can do really good database security design and hardening and lock down access to everything and this may reduce the risk of exploitation of something fixed by a security patch but it’s never going to be perfect.

Another option that started to appear around 15 years ago is the idea of virtual patching. This is the idea that you cannot or don’t want to patch with Oracle’s security patch so you deploy network software that is a special version of an application firewall or intrusion detection / prevention system. The way this works is that network packets are sniffed (or shared memory attached and parsed) and attacks that could exploit issues fixed in Oracle’s security patch are detected. This is complex and prone to error or hacker bypass and requires the vendors of the virtual patch to reverse engineer (or guess!) what Oracle has fixed; then work out how that fix could be exploited and then how an exploit that could hack the database software can be detected.

As I stated above Oracle do not release details of what is fixed so this virtual patching is not perfect and involves a lot of work. Yes, I can see that a product such as this maybe be a vert short term barrier to when a patch of a particular system cannot be applied quickly but its not a perfect fix.

So, patch and apply Oracle’s security patches if you can; it makes sense; it builds belt and braces solutions and an underpinning of security of the Oracle software that hardening and data security design can be built on.

If your major concern is that an Oracle security patch may cause a performance issue or break something functionally if you apply it then one method is this; Wait until patch Tuesday when Oracle release the next security patch and then download the previous quarters patch and apply it; application testing as necessary. The logic of this is to allow everyone else to test Oracle’s patch for you over the previous quarter.

For me, it is important that security patches are applied. Security patches form part of the underpinning of data security (patch and harden) on which we build data security design (user controls, data access controls, context based security). I can appreciate the time needed if you must perform regression tests of applications but a suitable method must be created that allows regular security patching; this can be:

  • Apply within the quarter every quarter

  • Apply every quarter but apply the last quarter patch to allow for others testing it

  • Apply regularly on a longer timescale such as every six months



Workarounds can reduce the risk but cannot replace patches and virtual patches may be fine for very short periods but cannot replace actual patches.
The most important message is patch and patch within a regular regime BUT don’t forget all of the other security tasks for your data; hardening, user security, access controls, data security and context based security. There is no point patching once and then never again or patching before a security audit and never again.

Unwrapping PL/SQL Source Code and Proving the Code is Recovered

We get asked by people if we can recover customers PL/SQL quite a few times a year. This is because they no longer have access to the original clear text PL/SQL. We can of course get this code back for customers successfully. Most are asking about wrapped code from wrap.exe 9.2.0.8 or earlier.

Some ask us can we prove that the original code is really recovered properly? In the case of 10g and above we don't as such need to prove this because the 10g and above mechanism is not encryption as such (ok very weak) and it just puts the original code back. The only real advantage of 10g and 11g wrapping over 9i and lower is that the symbol table is no longer visible but the algorithm is much weaker.

The 9.2.0.8 and earlier wrap.exe is more complex as the process / the file created is the intermediate stage of the PL/SQL compiler written out to disk. I talked about this at Black Hat in Las Vegas many years ago in some details. Oracle also provided a sample unwrapper for years in the PSTUB function shipped with the database and also because it’s actually just ADA and the DIANA nodes used in PL/SQL are mostly the same as ADA for a lot of syntax. ADA unwrappers have existed for many years.

So for 9.2.0.8 how can we prove without access to the original source code that we have properly unwrapped and retrieved the missing code?

One way is to start with a wrapped PL/SQL file; then unwrap this file and get the clear text code back. Then wrap this new clear text file with the same version of wrap.exe used to create the original file. Then finally compare the two wrapped files; if they are the same we have correctly recovered the original source code. Complete recovery of source is possible and it is provable and indeed my clients are impressed and hire us because we can prove their code is recovered. I have created a sample test case here to show this process.

First create a simple procedure to use for this test case. The code is just made up for this experiment:

SQL> get sample1.sql
1 create or replace procedure test_proc (pv_num in number,
2 pv_var in varchar2, pv_var3 in out integer) is
3 l_num number:=3;
4 l_var number;
5 j number:=1;
6 procedure nested (pv_len in out number)
7 is
8 x number;
9 begin
10 x:=pv_len*5;
11 end;
12 begin
13 case l_num
14 when 1 then
15 --
16 l_var:=3;
17 dbms_output.put_line('This is a header');
18 dbms_output.put_line('The number is '||l_var);
19 dbms_output.put_line('The case var is '||l_num);
20 when 2 then
21 --
22 l_var:=4;
23 dbms_output.put_line('This is a header');
24 dbms_output.put_line('The number is '||l_var);
25 dbms_output.put_line('The case var is '||l_num);
26 when 3 then
27 --
28 l_var:=6;
29 dbms_output.put_line('This is a header');
30 dbms_output.put_line('The number is '||l_var);
31 dbms_output.put_line('The case var is '||l_num);
32 else
33 dbms_output.put_line('wrong choice');
34 end case;
35 if ((j=1) and (j=3)) then
36 dbms_output.put_line('here is IF');
37 elsif ((j=2) or (j!=3)) then
38 dbms_output.put_line('The elsif clause');
39 else
40 dbms_output.put_line('else clause');
41 end if;
42 j:=4;
43 nested(j);
44 dbms_output.put_line('nested=:'||j);
45 for j in reverse 1..pv_num
46 loop
47 if mod(j,2) = 0 then
48 dbms_output.put_line('for loop with reverse');
49 end if;
50 end loop;
51* end;
SQL>

I can then wrap this with the 9i wrap utility:

C:\pete_finnigan_com_ltd\scanner-MASTER\unwrapper>wrap iname=sample1.sql oname=sample1.plb

PL/SQL Wrapper: Release 9.2.0.1.0- Production on Mon Jun 01 14:02:34 2021

Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.

Processing sample1.sql to sample1.plb

C:\pete_finnigan_com_ltd\scanner-MASTER\unwrapper>head sample1.plb


Then I can show it is indeed wrapped by viewing the contents (Note the above commands are in a DOS box, the head command is on the same machine but from cygwin as the head command is available:

$ head -20 sample1.plb
create or replace procedure test_proc wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
7
9200000

Now load the wrapped file into an Oracle database and check its stored wrapped:

SQL> @sample1.plb

Procedure created.

SQL> select substr(text,1,60)
2 from dba_source
3 where name='TEST_PROC'
4 and rownum=1;

SUBSTR(TEXT,1,60)
------------------------------------------------------------
procedure test_proc wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd


SQL>

Now we can simply unwrap it using my PL/SQL based unwrapper:

SQL> @unwrap_c

unwrap_c: Release 1.4.0.0.0 - Production on Mon Jun 01 14:07:13 2021
Copyright (c) 2008, 2021 PeteFinnigan.com Limited. All rights reserved.

NAME OF OBJECT TO CHECK [P1]: TEST_PROC
OWNER OF OBJECT TO CHECK [SYS]: SYS
TYPE OF THE OBJECT [PROCEDURE]: PROCEDURE
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

create or replace procedure TEST_PROC( PV_NUM in NUMBER,
PV_VAR in VARCHAR2, PV_VAR3 in out INTEGER) is
L_NUM NUMBER:=3;
L_VAR NUMBER;
J NUMBER:=1;
procedure NESTED( PV_LEN in out NUMBER) is
X NUMBER;
begin
X:= PV_LEN * 5;
end;
begin
case L_NUM
when 1 then
L_VAR:=3;
DBMS_OUTPUT. PUT_LINE('This is a header');
DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
when 2 then
L_VAR:=4;
DBMS_OUTPUT. PUT_LINE('This is a header');
DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
when 3 then
L_VAR:=6;
DBMS_OUTPUT. PUT_LINE('This is a header');
DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
else
DBMS_OUTPUT. PUT_LINE('wrong choice');
end case;
if ( ( J = 1) and ( J = 3)) then
DBMS_OUTPUT. PUT_LINE('here is IF');
elsif ( ( J = 2) or ( J != 3)) then
DBMS_OUTPUT. PUT_LINE('The elsif clause');
else
DBMS_OUTPUT. PUT_LINE('else clause');
end if;
J:=4;
NESTED( J);
DBMS_OUTPUT. PUT_LINE('nested=:' || J);
for J in reverse 1.. PV_NUM loop
if MOD( J,2) = 0 then
DBMS_OUTPUT. PUT_LINE('for loop with reverse');
end if;
end loop;
end;
/

INFO: Elapsed time = [.1 Seconds]

PL/SQL procedure successfully completed.

For more information please visit http://www.petefinnigan.com

SQL>

Then the code can be taken and wrapped again with the same 9i wrap utility, first cut and paste the code and then save as a file sample2.sql:

SQL> get sample2.sql
1 create or replace procedure TEST_PROC( PV_NUM in NUMBER,
2 PV_VAR in VARCHAR2, PV_VAR3 in out INTEGER) is
3 L_NUM NUMBER:=3;
4 L_VAR NUMBER;
5 J NUMBER:=1;
6 procedure NESTED( PV_LEN in out NUMBER) is
7 X NUMBER;
8 begin
9 X:= PV_LEN * 5;
10 end;
11 begin
12 case L_NUM
13 when 1 then
14 L_VAR:=3;
15 DBMS_OUTPUT. PUT_LINE('This is a header');
16 DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
17 DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
18 when 2 then
19 L_VAR:=4;
20 DBMS_OUTPUT. PUT_LINE('This is a header');
21 DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
22 DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
23 when 3 then
24 L_VAR:=6;
25 DBMS_OUTPUT. PUT_LINE('This is a header');
26 DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
27 DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
28 else
29 DBMS_OUTPUT. PUT_LINE('wrong choice');
30 end case;
31 if ( ( J = 1) and ( J = 3)) then
32 DBMS_OUTPUT. PUT_LINE('here is IF');
33 elsif ( ( J = 2) or ( J != 3)) then
34 DBMS_OUTPUT. PUT_LINE('The elsif clause');
35 else
36 DBMS_OUTPUT. PUT_LINE('else clause');
37 end if;
38 J:=4;
39 NESTED( J);
40 DBMS_OUTPUT. PUT_LINE('nested=:' || J);
41 for J in reverse 1.. PV_NUM loop
42 if MOD( J,2) = 0 then
43 DBMS_OUTPUT. PUT_LINE('for loop with reverse');
44 end if;
45 end loop;
46* end;
SQL>

Now the 9i wrap utility can be used to wrap the file again; this time to sample2.plb:

C:\pete_finnigan_com_ltd\scanner-MASTER\unwrapper>wrap iname=sample2.sql oname=sample2.plb

PL/SQL Wrapper: Release 9.2.0.1.0- Production on Mon Jun 01 14:10:12 2021

Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.

Processing sample2.sql to sample2.plb

C:\pete_finnigan_com_ltd\scanner-MASTER\unwrapper>

We now have two wrapped files. The one created originally from the PL/SQL source code and now the second created from the unwrapped code. Now use the diff command again under cygwin on the same machine to compare the files:

$ diff -i -w sample1.plb sample2.plb
146c146
< 16 21 25 :2 16 15 :2 1 2 :2 8
---
> 16 21 25 :2 16 14 :2 1 2 :2 8
148,168c148,168
< 2 c 14 1e 22 :2 14 13 :2 2
< 3 :3 5 :2 3 6 c d :2 6 3
< :6 2 7 8 4 b :2 4 :2 10 19
< :3 4 :2 10 19 29 2b :2 19 :3 4 :2 10
< 19 2b 2d :2 19 :2 4 a :2 3 8
< 4 b :2 4 :2 10 19 :3 4 :2 10 19
< 29 2b :2 19 :3 4 :2 10 19 2b 2d
< :2 19 :2 4 a :2 3 8 4 b :2 4
< :2 10 19 :3 4 :2 10 19 29 2b :2 19
< :3 4 :2 10 19 2b 2d :2 19 :2 4 a
< :2 3 4 :2 10 19 :2 4 3 :3 2 7
< 8 9 :2 8 6 11 12 13 :2 12
< 10 :2 6 5 3 :2 f 18 :2 3 2
< 17 a b c :2 b 9 13 14
< 16 :2 14 12 :2 9 8 3 :2 f 18
< :2 3 1a 17 3 :2 f 18 :2 3 :5 2
< 5 :2 2 9 :3 2 :2 e 17 21 23
< :2 17 :2 2 6 b 13 16 2 13
< 2 6 a c :3 6 f 11 :2 f
< 4 :2 10 19 :2 4 13 :2 3 2 6
< 2 :9 1
---
> 2 b 13 1d 21 :2 13 11 :2 1
> 2 :3 4 :2 2 6 d f :2 6 2
> :6 1 6 7 2 9 :2 2 :2 f 18
> :3 2 :2 f 18 29 2d :2 18 :3 2 :2 f
> 18 2b 2f :2 18 :2 2 9 :2 2 7
> 2 9 :2 2 :2 f 18 :3 2 :2 f 18
> 29 2d :2 18 :3 2 :2 f 18 2b 2f
> :2 18 :2 2 9 :2 2 7 2 9 :2 2
> :2 f 18 :3 2 :2 f 18 29 2d :2 18
> :3 2 :2 f 18 2b 2f :2 18 :2 2 9
> :3 2 :2 f 18 :2 2 :4 1 8 a c
> :2 a 6 15 17 19 :2 17 13 :2 6
> 4 2 :2 f 18 :2 2 1 1d b
> d f :2 d 9 17 19 1c :2 19
> 15 :2 9 7 2 :2 f 18 :2 2 20
> 1d 2 :2 f 18 :2 2 :4 1 2 5
> :2 2 a :3 2 :2 f 18 23 27 :2 18
> :2 2 5 a 12 16 1d 12 1
> 4 9 b :3 4 e 10 :2 e 2
> :2 f 18 :2 2 12 :2 1 1d 5 :a 1
>
173,185c173,185
< :5 8 :7 a :2 9 :4 6
< d e :3 10 :6 11
< :a 12 :a 13 :3 e 14
< :3 16 :6 17 :a 18 :a 19
< :3 14 1a :3 1c :6 1d
< :a 1e :a 1f :3 1a :6 21
< 20 :3 d :f 23 :6 24
< 25 23 :f 25 :6 26
< 25 23 :6 28 27
< :3 23 :3 2a :4 2b :a 2c
< :4 2d 2e :2 2d :a 2f
< :6 30 :3 2f 2e 32
< 2d :2 c :7 1
---
> :5 7 :7 9 :2 8 :4 6
> c d :3 e :6 f
> :a 10 :a 11 :3 d 12
> :3 13 :6 14 :a 15 :a 16
> :3 12 17 :3 18 :6 19
> :a 1a :a 1b :3 17 :6 1d
> 1c :3 c :f 1f :6 20
> 21 1f :f 21 :6 22
> 21 1f :6 24 23
> :3 1f :3 26 :4 27 :a 28
> :7 29 :a 2a :6 2b :3 2a
> 29 2d 29 :2 b
> :7 1

The above dump shows the output of the differences.

It seems on the face of it that there are a lot of differences and therefore the code was not recovered properly but these differences are not a problem and are in fact just the “rows” and “columns” held in the IDL for the original source code. One of the attributes of every DIANA node is that it stores the original source code position of the PL/SQL language element it has translated to DIANA. Because the wrapper only does “its best” to reconstruct the actual row/columns positions for all keywords and variables and in fact we don’t really care about whether the keyword “FOR” or “IF” is in the same place as the original as this does not affect the functionality of the code itself.

There are differences shown above but the functional part of the wrapped code are all generated exactly the same as the original. Therefore the code was completely recovered. A look at the sample1.sql and the sample2.sql files shows the differences in the PL/SQL level in terms of line and column positions.
In a real situation of course we don’t start with PL/SQL source code as in this simple example but start with just a wrapped file of lost source code; this can be anything from 7.2.3 to 9.2.0.8. This just removes the first steps from the process shown above; so that we start with a wrapped file, unwrap it, wrap the results and then compare the two wrap files.

To completely recover PL/SQL from wrapped PL/SQL it is necessary to compare the before and after wrapped files to test that the code is the same except for row/col positions. The process can still have issues if the original wrap binary used (say for a very early version of Oracle) is not available to wrap the code a second time. This can cause slight discrepancies if a later wrap is used for testing % complete source recovery.

Our service to recover PL/SQL source code is here.