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.

Happy 17th Birthday to this Oracle Security Blog

It is almost 17 years since I started this blog on the 20th of September 2004. I had actually already been sort of blogging without blog software before that since 10th February 2004 with my ramblings section of my website. The ramblings section was a list of posts/articles so was the forerunner to my blog when I installed but the site in general was a site to promote Oracle Security articles and papers.

The first post on the ramblings section was how to disable iSQLPlus which is not relevant anymore.

The first post to the blog in September 2004 was a simple introduction and why I wanted to blog and what i would include in this blog. The post was titled A new Oracle Security based weblog.

I was late to the party in blogging as the first blog is reputedly said to be by Justin Hall who recorded his life on his personal website but it was known as a blog and the term weblog was not used until 1997; first by Jorn Barger. WordPress did not launch until 2003, one year before my Oracle security blog but I used Greymatter blog software which was one of the first proper blog softwares to be released in 2000. The Wikipedia page even mentions me twice as I created some of the mods and also hosted the software for download as well as helped develop it for a while with Coldstone. I chose Greymatter because it is static and does not use a database unlike other popular blog packages of the time such as WordPress and Typepad and Movable Type. I liked Greymatter because it was configurable and because it was static and therefore not SQL Injectable.

I have posted some 1484 posts (including this post) during the 17 years; not as many in recent years as I did at the start. That is 87 posts a year on average, not bad! For quite some time at the start I managed to do a post a day but that dropped off in recent years and now I am trying to build that back up a bit again - but not to one post a day; I will never do that again as I am too busy with other work.

There are not many people who blog about Oracle Security and very few who did/do blog about Oracle security specifically that's why I keep covering this subject that is still very interesting to me especially in the times of data breaches and identity theft and more.

I also post to our social media profiles so also please feel free to visit these as well and also share, like and follow us there on these profiles:

Register for a Free Webinar with PFCLForensics for Breached Oracle Databases

I will be giving a free webinar hosted with our reseller/distributer in Slovenia and the Balkans region - Palsit. The free webinar is at 09:00 UK time or 10:00 CET time on the 22nd September 2021. In this webinar I am going to do a live walk through and do a live demonstration of our new product PFCLForensics. This will show the three core areas of the product:

  • Manage the Oracle database incident: The incident team can use the easy to use checklist to manage the response to any potential breach of the Oracle database

  • Live Response on an Oracle Database: Extract evidence and artefacts from the targeted Oracle database using our built in tools so that the most volatile data can be extracted first so making less impact on the database and future evidence. All extracted data is checksummed to allow a consistent view of the data

  • Forensic Analysis of an Oracle Database: Analyse all of the extracted evidence and create a timeline of events and artefacts. Also create a supporting timeline to allow surrounding data to be extracted. Use the time sync feature to ensure that all evidence is aligned with wall time. Use the reporting features to create a report of the attack



PFCLForensics is suitable for internal security teams, external investigators who are responsible to provide support and analysis during a breach response. Consulting companies can also use this toolset to provide support during a breach of an Oracle database for their clients.

PFCLForensics is also useful for internal and external teams to test and practice your response to a potential breach of your Oracle databases

To join this free webinar please visit the registration page. The webinar is in English.

PFCLForensics is released a tool for forensic analysis of a breached database

We have had a very busy year despite the Covid pandemic. I personally managed to catch covid last January and was very unwell for weeks with coviid and then many many weeks recovering after that. Then I managed to get problems with my kidneys that resulted in some surgery a couple of months ago. I am very well now. Despite these set backs we have written a new product to do live response and forensic analysis of a breached Oracle database and we made some major updates to one of our other products PFCLObfuscate to add new features as well as we added updates and fixes to other products as well

PFCLForensics showing a simple open project


Today we have released a new product - PFCLForensics. This is a product aimed at helping customers do three things in the event of a breach or potential breach of their Oracle database:

  • Manage the breach: with a built in check list of actions for the responder to follow. The product is project based so you can work on multiple breaches and tasks at the same time

  • Perform live response: with built in policies that allow the most transient data to be gathered first from an Oracle database or a Unix/Linux server. There are also built in policies to get less transient data from the breached systems. The product also allows files to be loaded that could be part of the breach and allow sthe user to define rules for their own file types and load additional data as necessary. The tool also takes checksums of each piece of data and validates those every time the project is loaded or on demand by the user to ensure that the raw data has not changed

  • Forensic analysis: with lots of built in tools. The user can filter and sort the input data gathered and look for issues related to the breach. Potential evidence can be added to the timeline for the attack and even comments added to each artefact. The timeline can be viewed as a drillable graph of evidence over time or a complete end to end graph of the even distinctly showing how the attack occurred. Supporting evidence can be added to a separate time line. The tool also includes a built in word processor and sample report template so that you can easily write your report and have direct access to evidence and artefacts as well as timelines. The timelines can also be sync'd to wall time easily so that all disparate evidence is correlated on the same time; each piece of evidence as it is added is also correlated with all previous evidence


This is a great tool to help follow a process if a breach occurs and also to collect and analyse and report on any breach that may have occurred. The product details start here and you can follow the links there to learn more. There is also a 2 page flyer that can be downloaded.

Our engagement licenses are the best value for 30 days and just £145 per product. Contact us to purchase a license or to ask to see a demo before buying.

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.

Redo Log Endian and Magic Number

It has been a while since the last blog post. I had intended to post more since earlier this year but due to ill health with covid in January and February and now heavy business load we have had little time for blogging. But, we are fine, the business is good and we are heavily focused in helping customers and providing training and our software products and we will try and post more about Oracle security on this blog and social media in the coming months. I have nearly 20 blog posts written or written requiring editing and I am adding to that all of the time. So hopefully I will be adding some of these here soon.

We have been lucky throughout the pandemic to still get work and we are also focused on updating our training classes and also updating our software products.

We are working on a major update to PFCLScan our database security scanner. We are also working on updates to PFCLCode our product to analyse your PL/SQL for security coding issues. We are also adding updates to PFCLObfuscate our product to protect PL/SQL to make the product better.

But most importantly we are also close now to releasing our new product PFCLForensics - more details coming soon on this product. We are preparing the text and images for the product page on the website and also finishing testing and also adding some new features. The product at a high level has three main modes:


  1. Help manage the response to a breach of an Oracle database. We do this via a built in plan/check list that the incident responder can work through to ensure all the necessary steps are followed

  2. Live response. We have a number of built in policies that allow a user to create a project and then execute the plugins to pull live response data from the database or Unix/Linux server. We pull the most transient data first and in the right order. The product also supports the loading of files into it to include in the analysis. All of the evidence gathered for each project is checksummed and this is validated every time the project opens or on demand to ensure that it has not changed

  3. Forensic Analysis. We provide many tools and features to aid forensic analysis. The user can browse the live response and static response data and choose potential evidence and add it to a timeline. The user can add comments to each (or none) lines of data in the timeline. The data is automatically corelated and is also automatically viewable in a drill down graph and an absolute timeline graph so that the evidence can also be seen visually. Supporting evidence that is not necessarily part of the timeline of artefacts can be added to a "supporting evidence" timeline. The product also includes a word processor and as template for a report. Data can be added into the report as flat data or screenshots (built in).



There are many more features and I will show some of these very soon here and show more of how it works.

One area we have looked into is the fact that some DDL does not include a timestamp (Separate blog coming on this) for when it happened. For instance I issue the command "grant delete on orablog.credit_card to xx". The grant is stored in sys.objauth$ but there is no timestamp on this table to know when this grant was issued. We can get some (not reliable) idea from sys.obj$.mtime and use this in conjunction with the create date and the interface change date. But, this is not reliable as MTIME also changes if a compile is made for instance. Even worse each object only has one MTIME so its the last change not every change. The answer in forensics is add comprehensive audit trails to the database before a breach and you will have the evidence to use in a breach analysis. Most people don't have this audit trail BUT we can help with this; see PFCLATK which is a comprehensive audit trail that can be added in minutes to a database as a combination of our PL/SQL toolkit and consulting. We are working on PFCLATK and this will be added into PFCLScan as a separate product later this year to allow an interactive dashboard and also point and click admin of policy driven audit trails.

So, if you don't have an audit trail; what's left to answer when the grant occurred? redo is the only answer really. We should not dump redo to trace as this would affect the server during a forensic response BUT we can view the redo logs or archive logs as binary files and see the DDL. A simple strings command is not good enough as we don't get context. A complete redo block analyser is also not necessary. We have a block dumper included in PFCLForensics and we can dump block 0 and 1:


C:\backups\30_06_2020_3_9_14_1350\scanner\oscan\Release>bd -v -c bd.conf -x -b2 -i redo02.log -o redo.op

BD: Release 3.9.562.1453 - Alpha on Thu Jun 24 13:40:45 2021

Copyright (c) 2021 PeteFinnigan.com Limited. All rights reserved.

[2021 Jun 24 12:40:45] bd: Starting BD...
[2021 Jun 24 12:40:45] bd: Opening Output File [redo.op]
[2021 Jun 24 12:40:45] bd: Analysing BLOCK Input File [ redo02.log ]
[2021 Jun 24 12:40:45] bd: Process Hex dump
[2021 Jun 24 12:40:45] bd: Closing Output File [redo.op]
[2021 Jun 24 12:40:45] bd: Closing Down BD

C:\backups\30_06_2020_3_9_14_1350\scanner\oscan\Release>type redo.op
0x00000000: 00 22 00 00 00 00 c0 ff 00 00 00 00 00 00 00 00 ."..............
0x00000010: 67 c8 00 00 00 02 00 00 00 90 01 00 7d 7c 7b 7a g...........}|{z
0x00000020: a0 81 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x00000030: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................


I am interested in this post about the values 7d 7c 7b 7a. David Litchfield in his paper "Oracle Forensics Part 1: Dissecting the Redo Logs" calls this value a "magic" number that Oracle uses to determine that this is indeed a valid redo log. Oracle can be running on little endian systems such as Linux on Intel or Big endian systems. Also the block size shown here as "00 02" which is little endian so is 0x0200 or 512 bytes. A reader of block 0 of the redo log can get the block size, the number of blocks but also use 7d 7c 7b 7a to determine the endianness of the file. i.e. if its stored in the order here its little endian; if its stored in reverse then its big endian.

Whether Oracle intended this or not we can use the first few bytes of a redo log block 0 to decide how to process it.

More blogs soon, I promise!

Oracle Security Training Presentations

Why not make good use of your stay at home time and get excellent very cost effective training in all areas of securing data in your Oracle databases.

I have just made live a new set of training dates on our website for our Oracle Security Training
that will run in blocks including a teaching of each class by me on USA EST time zone and also on a UK/EU time zone. All of the training is written and taught by Pete Finnigan and is fast paced and very interesting including many demos. All of the new trainings are held on line and suit the current covid restrictions as you don't need to leave your home or desk.

The big advantage of us keeping class sizes small is that you get access to Pete Finnigan for the whole of the trainings and you are free to ask as many questions as you wish and to get the wisdom and experience of Pete on tap as well as devour the actual training materials

The class sizes are kept small to allow everyone to join in.

The Instructor



The instructor for all of the training is Pete Finnigan. Pete has 20 years experience in securing Oracle databases and a vast knowledge and experience in helping customers and conference and training attendees to secure data held in Oracle databases.

Available Classes



The classes are:



We have 6 classes and each are one day classes except the 2 day class "How to perform a security audit of an Oracle database". We also have set up the dates so that you can combine the how to perform an audit 2 day class with the one day hardening and securing Oracle class to create a 3 day class.

The classes are £440 GBP for the one day class and £850 GBP for the two day class (any tax, VAT etc will be added where we must)

Dates Available



We have lots of dates available and to choose from. Please see our Public Training Class Dates page for details of all of the classes to be taught by Pete Finnigan. We have classes to be taught on UK/EU time zone and also USA/EST time zones.

What Do You get?



With every class you get to interact with Pete Finnigan a well known expert in securing Oracle and the data in Oracle databases. He is always happy to interact, answer questions and discuss anything that is relevant to the materials of the class. We do keep to a schedule though so also be sure that we will keep to it.

Each class also comes with a copy of the slides and also a free download of all of the tools and scripts and demos that Pete will explain and work through. These tools and scripts are not toys written for a class and are tools that we use in commercial engagements so the classes are worth it for just the tools alone; so the teaching is a bonus and great value.

Booking



To book your place on any class simply send an email to info@petefinnigan.com and we will do the rest. Its simple, write today and book your place!!