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.

Strong Passwords with Oracle Wallets

I get involved a lot in recent years with Oracle SSL, TLS, TCPS, Kerberos and more. A lot more customers now are trying to use stronger database authentication as well as TLS/SSL encryption and many other features such as full or partial DN Matching and two way SSL authentication. I have a lot of notes in all areas of this including using separate wallets for each PDB from 19c and many more; I will blog again in the SSL area as a lot of companies are now coming to us for help to use this with their databases.

Today I want to look at wallets at a most basic level and the password used to protect a wallet. Can we create weak passwords (NOTE: We don't WANT to do that; I just want to understand if we can)

First lets create a wallet on a 19c database installed server. First lets create a directory to put the wallet in:

[oracle@oel19cee ~]$ mkdir wallets
[oracle@oel19cee ~]$ cd wallets
[oracle@oel19cee wallets]$

First can we create a wallet without a password:

[oracle@oel19cee wallets]$ orapki wallet create -wallet .
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again:
Operation is successfully completed.
[oracle@oel19cee wallets]$

Good, if I create a wallet and don't specify the password it asks me for one. What if I do the same and specify auto login:

[oracle@oel19cee wallets]$ rm ewal*
[oracle@oel19cee wallets]$ ls -al
total 4
drwxr-xr-x. 2 oracle oinstall 6 Feb 23 12:46 .
drwx------. 20 oracle oinstall 4096 Feb 23 12:43 ..
[oracle@oel19cee wallets]$ orapki wallet create -wallet . -auto_login
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again:
Operation is successfully completed.
[oracle@oel19cee wallets]$

Good, again, we cannot create a wallet without a password. OK, so can we create a wallet with a weak password:

[oracle@oel19cee wallets]$ orapki wallet create -wallet . -auto_login -pwd a
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

PKI-01002: Invalid password. Passwords must have a minimum length of eight characters and contain alphabetic characters combined with numbers or special characters.
[oracle@oel19cee wallets]$


So, we tried to create a wallet password of a and orapki didn't let me. It states that the password must be at least 8 characters and have numbers OR special characters. This means that we are not forced to use alpha, numeric and specials as that would increase the number of possible passwords to hack. It also doesn't state that we have to use UPPER and LOWER again this weakens the possible passwords available as well. We don't know the number of possible special characters that can be used in a password but we know the digits is 10, 0-9 so lets assume there are more than ten specials so the possible character space is 36 possible characters so a-z0-9. Remember we dont have to use specials.
Lets try a simple password:

[oracle@oel19cee wallets]$ orapki wallet create -wallet . -auto_login -pwd 1111111a
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.
[oracle@oel19cee wallets]$


This weakens the password somewhat as we allow 10 possible passwords in the first 7 slots and 26 in the last one. So we would have [using ^ to mean to the power] = 10^1 + 10^2 + 10^3 + 10^4 + 10^5 + 10^6 + 10^7 + 36^1 - we assume in this example that one character is a-z and is in position 8 and positions 1 - 7 are digits 0-9. So we have 10 + 100 + 1000 + 10000 + 100000 + 1000000 + 1000000 + 36 = 11,111,146 which is 11.1 million possible passwords. If a password cracker is doing 200 million guesses per second that would last 0.05 seconds only if we know the password construction - i.e. places 1-7 are digits and the last place is a-z.

OK, that's an extreme example and not real. Lets assume that all 8 slots can be 0-9a-z so 36 possible characters. So repeat the calculation this time in Excel:
Password calculation for 36 character keyspace

As we can see there are 2.9 trillion possible passwords and this would take around 4 hours on average to crack using a cracker running at 200 million guesses per second. This is a reasonable speed in this day and age as GPU based cracker because of bit coin mining can go enormously faster. This assumes that we can extract the password hash from the wallet and we know the algorithm used.

BUT there are no rules to force a user to only use lower case and digits (that's the minimum we can get away with) and a user can use upper case and specials. So we need to change the calculation to be a-zA-Z0-9#%$... so lets say 26+26+10+10 = 72 possible characters. The calculation is then:
Password calculation for 72 character keyspace

This is much better as it will now take 20.9 days to crack this password using the same cracker speed using the asumption that on average passwords will be cracked in 50% of the time needed to crack all possible combinations; some passwords might start with aaaaaa... and some zzzzzz... so because crackers start a,b,c... and then aa,ab,ac... and so on we would assume that some passwords are cracker faster than others so lets just use 50% of the time as an average or 42 days if we try all possible options.

We can simply create dictionary words:

[oracle@oel19cee wallets]$ orapki wallet create -wallet . -auto_login -pwd Passw0rd
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.
[oracle@oel19cee wallets]$

This makes cracking faster so instead of trying trillions of passwords we might try millions of words and "leet" speak.

There is no way to force upper, lower, digits and specials on wallets. People will gravitate to dictionary and lower and digits so the assumption would be that wallets can be cracked in hours or minutes if dictionary words are used. Make sure that when you create wallets for SSL, TDE etc that you use longer passwords than 8 characters and you use the full keyspace and do not include dictionary words.

Contact me at pete at petefinnigan dot com or DM me on social media if you would like any help with SSL and Oracle

How I Write an Oracle Security Training Course

Writing an Oracle Security Class
I mentioned a couple of weeks ago on Social media and also briefly in a blog post here that I am writing a new two day class "Oracle Database Vault Deep Dive". That is the working name at the moment but I may change it slightly before it is ready to teach in few weeks time. I also have not put any details or outline of the class on the website yet as I would like to make sure that it doesn't change flow at all before that is published.

We have 8 days of expert training on areas of Oracle Security from how to do an Oracle security audit, secure coding in PL/SQL, GDPR, Appreciation of Oracle security, Oracle forensics and hardening an Oracle database. With this new Database Vault class we will have ten days of training. These classes can be combined as one days, two days, three days and even we have done five days sessions here in our offices in York as well as on site at customers.

I am currently writing the new two day Database Vault class and I thought it would be nice to talk about how I write a new class as I have never done that before here in the blog and I have written a lot of training material and indeed presentations over the last 19 years of my company.

Writing a new class is a massive undertaking and takes a lot of efforts, planning and writing of the class materials and also demos and demo system. At each class the students get expert tuition, a copy of the slides as a set of pdfs; a copy of the scripts to build the test data used, a copy of all the tools I use (written in PL/SQL and SQL) and also a copy of all of the demo scripts.

Each class uses a demo system (Oracle database) and a couple of sample web based applications. I use three virtual machines to teach as well as SQL*Plus access from the host computer. I have built a new 21c database to use as the host of this class as well as two VMs that run Apache, PHP and OCI. I use a back office application with sample data that represents at a high level a typical business system with customers, shipping, suppliers, card details etc. This system was described in my post BOF: A Sample Application For Testing Oracle Security and the php is generated with a PL/SQL script that reads the data definitions from the database. This php can then be deployed to the web server. The other application represents a company website and is used to demonstrate the abuse and hacking of back office data from the public facing website.

Each class is built around me teaching using the slides as a pointer but with me discussing in details what's going on. So if you attend the class you get way more than just the slides as I don't read them out I talk around them in much more details. I also build the classes around a lot of demos; in the case of this new DV class there will be 80 - 90 demos and each is in a self contained re-runnable script. The students get the scripts to create sample data and also the demo scripts so that they can easily re-run them again and see how things work on detail and at their own pace.

Writing a class like this new two day Database Vault class is a massive undertaking and needs a lot of planning and writing to make sure it flows and tells a story. To create this class I take the following steps:

  • First create an outline of the class at a high level; chapters and very high level bullet points that cover the flow

  • Create a timing Excel to map out each lesson; the amount of slides, the boiler plate, the hidden slides and the number of demos. I use this Excel to make sure everything will fit properly in the end. I work on 1.5 minutes a slide and estimate (test) demos to see also how many slides they take up. I allow time for breaks and lunch and also questions. The class is run from 9-5 or whatever time zone I am teaching an equivalent and I plan for 6 hours of slide/demos a day. This works.

  • Create a test database and applications and virtual web servers and SQL and PL/SQL scripts to populate the same data needed for the demos

  • Then create outline slide decks for each lesson. I usually do 8 or 9 lessons whether the class is one day or two days. They are not equal size. I add the boiler plate, sections, open close of each class and blank slides for the first split of the layout - this always changes as its fleshed out.

  • Plan a flow of the high level demos that underpin the class on a separate plan and decide where they then fit in the whole layout - don't write them yet

  • Take a first stab at the slides and flesh out some slides where they can be written immediately in MS PPT and add text notes to some slides to start to create the flow. Start to identify some demos that are needed

  • Once I have about 40% of the slides started in this way; I then print out all slide decks and revert to pen and ink. I now go through the whole class and mark up every slide that stays with detailed pointers of what is going to be in each slide and also identify every demo and list these in a separate plan with notes on what each of the demos will do. Each demo also has a time estimate of how long to allow for each demo. This fleshes out the whole class

  • Now go through and count all the slides, boiler plate and total time per lesson for each demo and update the Excel timing and make sure all the slides and demos fit

  • Review the flow of the slides and demos and make sure it works

  • At this point the slides can be filled in quite quickly from the notes and mark up

  • Finally complete all the demo scripts

  • Test and go through everything


Thanks for listening. I hope my workflow and method of writing a complex and detailed training class helps someone else.

If you would like to book your place on my new Oracle Database Vault class or or indeed any of my classes then please email me on pete at petefinnigan dot com or send me a DM on any of our social media channels. We will have a public class in early March and we can do private classes for anyone. Just ask.

Happy 19th Birthday PeteFinnigan.com Limited

Just a short blog to wish my company PeteFinnigan.com Limited a happy 19th birthday. 19 years ago today, the 12th February 2003:
PeteFinnigan.com Limited Company Certificate

I registered and launched the company 19 years ago to specialise in all things Oracle security. We focus on three main areas:

  • Consulting in the area of Oracle Database Security: We have many years experience in performing detailed security audits of Oracle databases; helping people design audit trails; secure code reviews of PL/SQL; SSL; OKV; VPD; DV and much more

  • Expert Training in Oracle Security: We have currently 10 days of training materials that can be taught in one day, two day and also a three day class. We have also in the past done five day events. We teach a lot of areas including securing data in Oracle, how to perform a security audit, Database Vault deep dive, Secure coding in PL/SQL, Forensics, GDPR, Appreciation of Oracle security and more

  • Software products to help secure your data: We currently have software products that we have developed and sell; all except one in the Oracle security space


Thanks for listening and here's to another 19 years!!

As always please email me pete at petefinnigan dot come or talk to me on social media DMs

Pete, Did You Deliver The Wrong Product?

We sell a number of software products aimed at helping secure data in an Oracle database and we get this issue / point / question coming up from time to time. Yesterday morning I got an email from a customer who we bought a license for our product PFCLObfuscate which is used to protect your PL/SQL in your customers database. The question was; we ordered PFCLObfuscate but you sent us PFCLScan?

Maybe we need to explain this more clearly to customers and so it is worth me doing that now in this blog post.

We did ship the software correctly and the end customer did get PFCLObfuscate BUT they also got PFCLScan because all of our products are build on top of PFCLScan. In effect PFCLObfuscate, PFCLCode, PFCLForensics and PFCLCookie are all built as Apps in side of PFCLScan. Here is the launcher that starts if you purchase a license for a product without PFCLScan:
PFCLScan Applications Launcher

Then if you click the PFCLObfuscate option in this example the PFCLObfuscate app starts and behaves like a standalone windows application BUT PFCLScan is behind it providing the functions needed:
PFCLObfuscate Started from the lancher

This was an architecture decision I made a long time ago. I wanted to maintain one code base (which is very large now) and use the functionality of the core product (PFCLScan) within all of the other products. This saves development time massively, maintenance time and makes it easier to develop, build and ship these products. Each App also uses lower level features and functions within the main PFCLScan scanner so again saving development time. We use plugins in the scanner to do the work in each App; this means that a lot of the development time can be done in user space of PFCLScan rather than the core C code or .NET code. We can develop plugins that do the work and the GUI side just needs to open the output and display and manipulate it. We can also use the full reporting interface and language in each App and we can use the core trace and logging from PFCLScan in each App. We can use many features and functions of PFCLScan to make it much easier to develop new applications and this is what I planned from the start.

PFCLScan uses an open architecture that allows projects to be created in User Space that can have any number of policies and any number of checks and each check can be written in many different languages such as SQL, PL/SQL, Lua, Shell Script, SSH, DOS (Windows Cmd) and many more. Each check can also take input from previous checks in a static way similar to #defines in C and also dynamically at run time. The project based scanning feature is more powerful as we can run these projects also completely from the command line with one single command. This allowed us to create plugins for PFCLScan as a Plugin is simply a project to run in PFCLScan that can have a single input file (parameters) and a single output file which can be XML, JSON, Text, whatever you need. Even this aspect of plugins uses reusability in that the plugins call the scan engine and reporting engine.

We created the Apps as I discussed by using plugins to do a lot of the work and save development time but we also created two OEM plugins so far that allow our tools to easily allow our database security scans, or obfuscation, or PL/SQL code reviews to be added to other products; really easily.

Because of the projects / checks architecture we are not just limited to scanning an Oracle database; we can run anything through this architecture and thats what i planned from the beginning. For instance our build system is just a PFCLScan plugin. We have a simple license CRM in Excel (I know!) and we use a simple piece of vba to then run a PFCLScan plugin that builds the installer for each customer with the correct details, EULA, protection, Windows installer and more. Even the installer also uses the scanner to install itself.

I like recursive features and programs!

I will talk more about some of these features and ideas in later blogs in more details as they may be of interest to potential partners in the future; i.e. develop your own projects for other areas that we don't do now; we can partner and resell; embed our software into your product as an OEM; and more. Talk to me if you have any ideas.

So, we were told we sent the wrong product; we didn't. All of our products are actually PFCLScan and individual products are Apps built using and within PFCLScan.

Please email me at pete at petefinnigan dot com or send me a message in Social Media if you have any ideas around partnering using our powerful product stack and architecture.

How do we Train Staff to do Oracle Security?

I am asked this question comes up a lot and indeed this morning on a webex it came up again so I decided to discuss this question here. I started in this Oracle Security space a very long time ago; more than 20 years ago and whilst I know of a small number of people who have been in the Oracle Database Security space over these years there are not a lot of people out there that do Oracle Database Security specifically. There are not a lot of people who have focused on Oracle database security in a blog or books; there are some of course but a tiny percentage of the whole of information security practitioners. Why is this? I ask this as a rhetorical question. My answer needs a discussion in a few areas:

  • Is Oracle database security needed?: Yes of course, absolutely. Data is the new Gold rush and as the big players deal in data particularly personal data then every company of a certain size inadvertently becomes a potential data broker as well. The potential of the data value sometimes is more important than the actual product being sold. Data is important and it is being stolen at alarming rates because is safer for the criminals to steal data than to walk in to banks with guns; and data is probably more valuable than money

  • Do companies take the security of the data seriously?: Obviously companies feel that they do take data security seriously BUT my experience of working with companies and talking to people at companies and specifically looking at Oracle databases and talking about them is that data security is not taken seriously enough - in my opinion. How do i arrive at this opinion? well usually there is little to no evidence of security at the data layer itself. No defaults removed, passwords not changed for 14 years in one example recently, no password management; no data security at all - everything granted to everyone with admin rights. Why? we will come to that in a minute

  • Do companies have the skills to secure data at the data layer?: At a high level probably but the better question is do they have the skills to decide where and how and what to secure at the data layer?, no probably not. They often have DBAs and sometimes developers and with guidance and expertise they could secure data in the database

  • What about third party applications that use databases?:This is an interesting area as a lot of companies use big applications such as EBS or Peoplesoft or JDE etc but also companies use third party applications that use an Oracle database as a data store. I find in these cases its even harder to get serious about low level database security because it is usually deemed as a complete application including the database and not their problem


Some interesting problems and questions. Why do people not take data security in the Oracle database seriously? mostly because they lack skills to design and architect the changes within the database. Often also because the focus is on functionality and SLAs (Service Level Agreements) and often because the Oracle database is treated like a hidden and magic store that doesn't need to be changed or tweaked. They assume quite often that the database is secure; its not unfortunately because just like you need to design tables and code you need to design your own data security; its not Oracles job, its yours.

There is also a fear of breaking a working system "after the fact" by retrospectively adding security to the data. Yes, this can be the case if you don't know what you are doing. If the application is a big Oracle ERP or third party then they feel often the database level security cannot be changed.

The issue is that often an ERP controls the security at the ERP level BUT you can simply bypass this at the database level with ease and steal the data. So no matter the clever design and settings within the ERP if database access is possible then the data can be accessed.

So, back to the core question how do we solve this?, we need people who understand security and data security and how to tweak or change a database to protect the data. So is it easier to take a skilled security person and let them decide how to secure Oracle at a detailed level or is it easier to take an Oracle person and allow them to design security?

Both are hard; a skilled security person simply wont take on the task to learn a lot about Oracle to confidently know how to tweak and change the database or the application design to make data secure. An Oracle person conversely has the Oracle skills and knows what can and cannot be done to a database or data model - but this is a mixture of two or more Oracle roles (DBA, Architect, Developer,...) and do they have the time and inclination to also learn security.

I think we now know why there are not so many people out there that are specialised on securing data in an Oracle database; a skills gap from both sides. We can help with this. We can be your Oracle security expert on a call off consulting basis or we can help with designs or security audits and we also have 10 days of expert training in this exact space. Talk to me via social media or email me pete at petefinnigan dot com and we can help you solve this problem

Looking Forwards To 2022!!

NOTE: I wrote this post back in January and then just after posting it the web server crashed. So, I guess a small number of people may have seen it before. It is essentially the same post now except for slight edits and this note.

So a bit about the web server crash. I noticed that opening pages was OK but running connection via ssh took minutes and then when connected "ls" and other Unix commands also took minutes. I then could not run anything so decided to reboot it. The server went down and never came back. I had to then wait for the ISP to physically enter the server room and take a look; He could get it to start but one of the physical discs was gone, not recoverable. This meant we had to start again and reinstall apache, perl, php and more. These are later versions - the original site was patched up to date but it was an opportunity to update everything to later versions. Because we went to a later versions things had changed!!. I had to reconfigure apache in a lot of ways and also port the perl and php in the blog and forum and site generally. This took some effort but i quickly had the site displaying all the main pages again but getting the blog interface to work again took thousands of code changes to the Perl.

It was a not needed task at this time as I am very busy but the website now has a great footing again.

Here is the post that was posted in January and was there for a few minutes!!


Let's all have a successful 2022 and hopefully get over the current crisis in health and pandemics and move back towards a normal life for everyone. It has been a trying time for the last almost 2 years for the whole world and for me personally. Just over a year ago my dad died from covid; then I caught covid almost a year ago and was very ill for weeks and then took months to fully recover; I managed then to get issues with my kidneys and had surgery in the autumn last year and recovered and then got a very bad chest infection and cough in December that took weeks to get rid of; I was tested many times and it wasn't covid; we have to realise that not every illness is covid!!. I am very well now and worked through most of the above anyway but it seems like we all need a much better 2022.

I managed to visit and speak at the UKOUG (UK Oracle User Group) end of year conference at the Oval in London at the end of last year; this was great to get to a real live in person event again after such a long time. Whilst the numbers at the event were not as big as they have been in the past there was a good turn out and it was great to meet and chat with people in real life again. I did a talk on how to respond to a data breach of an Oracle database and live response and forensics.

Good turn out to my talk also!!

Despite all the health issues I had nothing really slowed down or stopped for me or my company in our pursuit of helping people secure their data and with Oracle security based projects. I even still managed to work even when I had covid except for the short time I was laid up in bed. We have made a lot of progress on our tools and software and other bits in 2021 and its worth a brief update here now and I will create more detailed blog posts of each of these soon to show more:

  • PFCLScan: We are working on a major update on our database security scanner for Oracle databases to be released early this year in 2022 and it will include many new checks and features and reports

  • PFCLObfuscate: We released a number of major changes to PFCLObfuscate that allows a customer to now do much better locking to a database of their PL/SQL code; much better string encryptions and a new method to allow obfuscation to be laser targeted.

  • PFCLUserRights: We have been developing a new product that will be released later this year called PFCLUserRights. This allows a detailed analysis of all users in the database for their rights and access. This is at the user level, individual settings level, privileges granted level and globally across all users. The main interface allows a simple color coded view of all users and their access and rights and shows green for good - keep, Red for remove and Yellow for attention. This will allow a rapid view of rights used and granted in the database

  • PFCLATK: PFCLATK has been around a while as a service based toolkit. We have an extensive PL/SQL based toolkit that we can use to easily map detailed audit trail designs to actual policy and settings in the database. At the moment this is sold as a service where we help a customer design pragmatic and useful audit trails - We guide the customer via meetings and build an events table of actual events that need to be captured in the database - i.e. "did an attack occur", "did someone share an account", "did some critical action occur that was not authorised"... we held the customer design this events table with input from us, their security and business. We then map this to our toolkit PFCLATK and then this can be deployed an dup and running quickly and simply. This is 10s of thousands of lines of PL/SQL. We will be adding a GUI interface to this later this year so that we can sell it just as a product and not a service. There is an admin interface to configure and deploy all of the events and also a dashboard to monitor every database target. We are currently working on changes to the toolkit for a customer assignment so its actively developed

  • PFCLCode: We made some changes to the rules and analyser in PFCLCode later in 2021 and these were released to existing customers and new customers. We will be adding more changes to PFCLCode soon as new checks and rules have been planned and will be developed and added and released soon



Also at the end of 2021 and being finished off now we (I mostly) are developing a new 2 day class "Deep Dive into Oracle Database Vault". I will be teaching this at the end of February online, live via webex and we have customers with places paid for already and we can welcome a few more if anyone is interested to the first event. I like Database Vault and we work on it for customers from time to time. I do get people asking for design and detailed consultancy in this area so we decided to create a new class. Contact me via social media or email if you would like to book your place. There are no details on our website but they are coming soon but if you contact me I can send the class outline to you. I will announce the dates for the first teaching here as well.

I have also been in detailed discussions to resell a product developed by another UK based company that allows PC's, servers etc to be searched for any type of data. This is a great product and will compliment our software offerings nicely. We focus on the database and now we can help with the servers. I will go into much more details about this product very soon as we announce it and release the pages on our site. At a simple level you can just choose a target to search and tell the software to look for personal details on the server/PC. You can of course go much deeper and the searches are highly parameterised, configurable, saveable and more. There is also an option to program actions based on what the search finds. As I said, more soon but this will fit nicely with the database side

We have also decided that there will be a much bigger presence with blogging and social media. This is a static blog in that it is not WordPress or similar and the changes to the blog are created at the time of the edits and the contents are not in a database; this is what drew me to this at the time. The blogging software in the website does not support draft posts BUT we have created a sophisticated Windows desktop application to allow a project based management of blog posts, tags, snippets of text to use with social media posts. It also has a queue system and calendar to allow you to easily split and process each blog post to then be posted across all of our social channels. I will discuss this more in a future blog post here but as we have put quite a bit of development time into this the intention is to also sell licenses for this blog management tool that will allow off line management no matter the blog or social channels.

I am also looking at packaging pre-recorded trainings from our 8 days of classes so that customers can download and watch on demand. These will be at a lower cost than the live trainings. We have been asked for this for years but I have held off because of the time needed to create and manage this but we have not created a structure plan for this. This will also allow easier updating and management but also allow us to create short versions of some classes to be packaged with our software as a complete solution. Again, more soon!!

OK, that's a long post. I will stop now, but expect more on each subject soon separately.

Also worth stating that everything we do works in the cloud as well. A database on premise or in the cloud can be secured using all of the same techniques at the database level

Joel Kallman Day

I saw a few tweets yesterday about Joel Kallman and liked a few and shared one (maybe two) and then I saw Tim Halls post that talked about Joel Kallman day. I decided to do a quick blog now to support this event that Tim has been running even though it ended yesterday; Sorry, I could not do it yesterday. ##

I didn't know Joel well and we had met and spoken at least 3 times over the years but possibly more, I just cannot recall exactly; two occasions I do remember. The first time we spoke in person was at DOAG a few years ago. I was there with a reseller of our scanner PFCLScan and was doing demos on the Loopback stand there. Joel came up to me and introduced himself and shook my hang vigorously and said "you are a Rockstar; but don't tell anyone in Oracle I said that" and he had a broad smile. We chatted for a while and I was immediately struck by his absolute friendliness and his great attitude to everyone around and the "subject" of Oracle in general; he was a great person.

I then spoke to Joel again after that at a UKOUG event where he came to find me because he had heard through the grape vine that I was going to include an exploit in Apex in my talk of that day. The grape vine wasn't strictly true. Yes I was going to use Apex as an example of a problem but it was not a bug in the traditional sense in Apex. The issue was that the full design implementation of Apex includes a lot of rights for the APEX_% schema and I was going to show a issue of how a privilege can be stolen. Joel was very gracious and courteous even before I explained my talks example. He was a great person, very friendly. We then talked about all sorts of things (all related to Oracle of course) and even about the key design decision in the core of Apex to use DBMS_SYS_SQL.

I definitely remember speaking to Joel somewhere else but I cannot remember now where or the details.

Great guy, sadly missed and a fantastic ambassador for Oracle.

#JoelKallmanDay #oracle #security #oracleace @oracleace @groundbreakers @OracleDatabase @OracleAPEX #orclapex @OracleSQLDev

Designing Good Audit Trails for an Oracle Database

I have been asked to speak at the UKOUG Autumn Tech event. This is an online conference event and the agenda grid is live and I will speak at 15:00 to 15:45 BUT the link to the details of my talk is incorrect as it points to a Graham Spicers talk. I have asked UKOUG to fix this but it doesn't matter for now as I can discuss the contents of the talk here.

The talk is all about building a good audit trail for your Oracle database. What I mean by good is a well designed audit trail and not one simply based on a set of technical settings BUT a well designed and thought out audit trail. First, who are you trying to satisfy? external regulations or even internal ones; do you want to be reactive - i.e. simply collect and store, do you want to be pro-active - i.e. use the audit trail in real time or semi real time to react to an incident and block or stop it. If you have to comply with regulations to gather certain audit trail evidence then there is no reason that you should simply comply with the regulations - often they are not good enough to use the audit to investigate a breach or to detect a breach. This is because they are designed by committee not designed by you and for your business.

One area I will focus on, then, of course is the good design. The audit trail needs to be designed first in terms of events; the events that you want to capture and we discuss these first as well as some sample events that I feel should be included. These are at a business level in a table in an MS Word document; these are not audit settings in the database. Once we have the list of events we can then decide what the technical solution is going to be (standard audit, unified audit, FGA, third party... ) and as part of this we decide what raw audit to be collected and how, then how to mine that raw audit to see if the event has occurred and we also bring in reporting and escalation and alerts. This is a designed audit not a list of random settings recommended by someone else.

I will also show the results of some hacking and what is captured with the base standard audit settings from Oracle and then implement a good set of policies in my database and show that the hacking is now captured in the audit trail.

Come along and learn about audit trails and good designs for capturing activity in the core database engine.

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 covid 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 main interface

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 which are 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!!

Happy 18th Birthday PeteFinnigan.com Limited

It has been an eventful year last year and 2021 started a bit strange due to lockdown. Last Friday our company PeteFinnigan.com Limited came of age; it was 18 years old. Wow, it has been a long and interesting journey for our company but an interesting and exciting journey!

This corona virus has been with us around one year already. The first cases in the UK (at the time) were here in York where I live. Two people were taken away in an ambulance from a hotel where the staff of the ambulance had full haz mat suits on. At least that part is not as precautionary now with ambulances seen driving around and no sign of full haz mat suits anymore; just staff in more normal PPE.

We were plunged into a lock down in the middle of March 2020 here in the UK and told to work from home; so we did for a few months and then gradually came back to the office after the lock down ended. We then had another short lock down in November. Then came January this year 2021 and the numbers of cases and deaths and people in hospital here in the UK went through the roof. At the start of this current lock down we started to work from home and then I caught covid myself from a family member. The others in my family recovered quickly but I had it for weeks and was laid up unable to do anything for weeks. I managed to get most of the symptoms that are listed by the NHS except severe difficultly breathing; which is the one symptom you don't want to get; in fact only now very recently do I feel much much better. I spoke to someone today who saw me today who remarked that when they saw me 2-3 weeks ago I looked terrible and looked like I was on my way to the morgue as I was not just pale but my skin was grey. Not good to know that, but at least I am much better now.

So how has the company been affected by Covid? well normally we would be travelling all over the world delivering consulting engagements and I would be teaching my Oracle Security training classes. Well, no travel but we have been providing training classes online for many customers on public trainings and also private events and we have been doing consulting engagements online also. We have done security audits, PL/SQL code reviews and provided expert consulting in many areas of Oracle security. I personally miss the face to face contact we get when teaching and also when delivering consulting but for training it is just as good online for the students; our classes are still live and me teaching, students can still see everything in terms of slides and my demos and my screen and of course students can interact as much as they need to and ask questions.

The progress with products was hampered slightly as we planned to release another new product PFCLForensics in January but due to my covid infection and also the lock down that has now been delayed but we will release it soon. This is a tool that can be used to perform immediate "live incident response" on a suspected (or known to be) breached Oracle database and also to then provide "Forensics analysis" within the tool. The forensics is done with a timeline that can be viewed in a number of ways. This allows the data that you collect that you identify as part of the breach to be ordered into this timeline.

Keep an eye out for more news on PFCLForensics coming soon.

During the last 2020 year we have released a number of new interim point release versions of PFCLScan; adding more facilities to it and checks and tools.

More importantly we have also release a completely new product and revamped completely another product.

The new product is PFCLCode a static source code analyser. This tool analyses your PL/SQL code for vulnerabilities but it also goes further than other source code analysers as we also look at the design and deployment of the code into the database; so also looking at things like hierarchy, design choices of the schema and individual pieces of PL/SQL as well as privileges. We, of course also analyse the PL/SQL code itself for issues.

The revamped product is PFCLObfuscate which is our product to protect your PL/SQL that has been deployed to a database. The product has been available for a while as a command line only tool. We have now added a complete GUI interface to it and released it during last year. This makes the tool easier to use as it also has productivity enhancements. This is a great tool and easy to use. Of course the command line operation is also still there if you require that to integrate the tool into any build system.

One of the major design decisions that I made last year whilst we were developing PFCLCode, PFCLObfuscate and PFCLForensics was to leverage the core functionality of PFCLScan.

The core scanner was designed as a framework that can connect to a database, server or whatever and run projects with policies comprised of checks written in many different languages including SQL, PL/SQL, Lua, Unix Shell, DOS scripts, sftp, even questions based and as we can run DOS commands you can actually run checks in any language you wish if you have the language interpreter on your PC. This power is enhanced in that the core engines of PFCLScan can actually be run as checks themselves. This creates powerful recursive checks where a project/policy/check can actually run the scanner itself as a check; ad infinitum - this allows automation and also structured scanning. Similarly with the reporting tool that we have created that sports its own very simple BUT powerful language. This framework allowed us to utilise the scanner within itself; so that development moves to "user" space and is not confined to the core development of the GUI. This lead to plugins that are actually just project/policy/checks in the scanner. You can create a project to do something useful either on the host PC or in a database or... and then convert this project to a plugin. This can then be run from the plugins screen as a tool.

We extended the plugin functionality to allow plugins to be executed in the GUI interface at certain designed "hook points". This means that the interface can be extended easily simply by adding "hook points" - simple code and then a plugin can be created in "user space" and assigned to the "hook point" in the plugins screen.

This extension and reuse of features along with some other changes lead us to design all of our new software products INTO PFCLScan. We create a new GUI screen for each product such as PFCLCode and this can be accessed via the "tools" menu in PFCLScan if you purchases PFCLScan + PFCLCode or if you purchase just PFCLCode then a launcher is presented when the tool starts up and you choose PFCLCode and only see the PFCLCode interface. Although each product such as PFCLCode has its own GUI screen / interface it uses core features of PFCLScan (trace, logging, users, login, plugins of course and more...). This means that we have one source code tree for all products and one build system and we can develop new products much faster as quite a bit is creating plugins and importantly using existing functionality.

All of this means that we can easily sell bundles of products together, add new products to sell in the same design model BUT we reuse the core functionality and not redevelop similar things over and over.

It gets better; well for us anyway. We also created the customer build system and activation system inside of PFCLScan using plugins. So PFCLScan is used to build PFCLScan.

I will expand on the products in a later post and talk more about what they can do to help you secure your Oracle databases and data.

We are also working on adding more training dates to our public training calendar. More dates will be added in the next few days, so please watch out for those. We have many training classes and all focus on how to secure an Oracle database. All of our classes are described on our "Oracle security training course" page. Have a look for details and a 2 page flyer for each class

We are also in discussions with a number of companies to partner with us to resell our software products or to partner with offering our consulting or trainings. We are always open to taking on more partners and resellers. The advantage for you is that you can offer training and services that you do not have the skills in house to offer and also resell ready made security products to help people secure data in their Oracle databases. Please
contact me to discuss if you would like to partner with us.

If you would like more information on any of our services please contact me now and I will be happy to help.

If you would like to book a place on a training class or to request a private class then please contact me and I will be happy to help.

If you would like to purchase a license for any of our software products or request a demo, then please contact me and I will be happy to help with that

TCPS Connection With an Oracle Instant Client

All of our products (PFCLScan, PFCLCode, PFCLObfuscate and PFCLForensics) can use an Oracle instant client to connect to the target database(s) or even a full client.

It is of course simpler to use an instant client if you don't have a full client on your PC as it is just a case of unzipping the instant client and copying the files and sub-directories (usually vc8, vc9 or vc14) to the place you want to use it.

One issue with an instant client is that until 19c it doesn't natively support TCPS and wallets with the EZCONNECT syntax. When you use 19c there is additional syntax to support this (I will do an additional blog post for this soon).

This blog post is based on making a TCPS connection to the database initially with SQL*Plus to prove the connection works and then with PFCLScan to show that we can connect in PFCLScan with a TNS alias and an instant client but also we can make a TCPS connection to the database/listener.

The following details this process and shows how PFCLScan works with this setup

Introduction



This is a short description to cover connecting from PFCLScan and also SQL*Plus from a client PC to a database that has SSL encryption setup.

This is also a test with an instant client and not a full Oracle client

This will also work with cloud setups and an autonomous database for instance. In this test case we instead made an SSL connection to a 12.2 SE Oracle database with SSL encryption and a self signed certificate and the same certificate in the client/server wallets for ease of setup. But this set up allows TCPS connection to the database and proves that the TCPS connection works.

Server Setup



I am not going to describe the server setup in detail but can provide copies of my sqlnet.ora, listener.ora and orapki commands if needed.

I set up a TCPS connection setup in my 12.2 single instance SE2 database. This uses port 2484 for the TCPS and has a self signed certificate in a wallet on the server. This wallet is then referenced in my sqlnet.ora and listener.ora on the server.

As I am using an instant client on the PC I do not have orapki to set up the client wallet as the instant client does not ship this. So, I created the wallet and installed the certificate on the server and then copied the wallet files to the PC for the client to use.

Client Setup



I created a directory on my PC and copied the 11.2.0.4 32bit instant client into this directory. I also copied the sub-directories vc8 and vc9 into the same directory:
Oracle client

The above is the basic instant client for 11.2.0.4 and also the sqlplus add on. They are both zip files so just copy the contents to the directory.

I copied the wallet files from the server and copied them into my client 11.2.0.4 instant client directory these are shown highlighted here:
Oracle wallets

I then set my TNS_ADMIN environment variable to point to my 11.2.0,4 client folder: c:\_aa\oracle_client:
tns_admin

I added the 11.2.0.4 instant client directory to my PATH:
environment

This can then be seen in my command prompt:
path

I now created my sqlnet.ora and tnsnames.ora and copied these also to the same 11.2.0.4 client folder these are shown highlighted:
oracle_config

My tnsnames.ora has the following content:

ORCL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCPS)(HOST=192.168.1.95)(PORT=2484))
(CONNECT_DATA=
(SERVICE_NAME=orcl.localdomain)))
My sqlnet.ora has the following content:
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS)
SSL_CLIENT_AUTHENTICATION=FALSE

SSL_VERSION=1.0
WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=(DIRECTORY=C:\_aa\Oracle_client)))

TRACE_LEVEL_CLIENT = support
TRACE_FILE_CLIENT = client.log
TRACE_DIRECTORY_CLIENT = c:\_aa\Oracle_client
DIAG_ADR_ENABLED = OFF
ADR_BASE = OFF

Testing the Connection
I can now use SQL*Plus to connect to the database. First I start a CMD prompt and CD to the scanner BIN directory (the location of oscan.exe):

C:\>cd _aa\PB\bin

C:\_aa\PB\bin>dir oscan.exe
Volume in drive C is OS
Volume Serial Number is C67F-6487

Directory of C:\_aa\PB\bin

04/11/2020 11:26 1,496,064 oscan.exe
1 File(s) 1,496,064 bytes
0 Dir(s) 76,606,251,008 bytes free

C:\_aa\PB\bin>

Now I run the pfclset.bat script. This moves me to the DATA directory:

C:\_aa\PB\bin>pfclset
pfclset.bat Release 1.0 Copyright 2015 PeteFinnigan.com Limited

C:\_aa\PD>

Now I can connect to my database using SQL*Plus and the TCPS connection to prove the connection works for the client:

C:\_aa\PD>sqlplus system/oracle1@ORCL

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 6 10:21:00 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SQL>

And confirm its TCPS

SQL> select sys_context('USERENV','network_protocol') from dual;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps

SQL>

Now I can set up a project in PFCLScan and set the connection details to use my tnsnames.ora alias ORCL:
pfclscan_connected

I am able to connect from an 11.2.0.4 instant client using tnsnames.ora alias and making a TCPS connection to my 12.2 database
Now I can run the scan:
pfclscan_complete

This works!