Call: +44 (0)1904 557620 Call

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.

Oracle Security Search Is Annoying and protecting PL/SQL code

This post if not specifically about Oracle Security but I got here because of Oracle security so i am going to talk about Oracle security first...:-)

I am working this morning on a proof of concept code for a security solution for a clients database; so i am creating code for a high level design i wrote a couple of months ago to now demonstrate that the custom Oracle Security feature will work in practice for them and that its protected from bypass - I might talk about the actual solution here generally later if the client is happy for me to do so or simply discuss some of the protection features I use as they are my IPR. The solution for them is a security feature I am designing for their database also with secure coding in mind. I am implementing a feature in PL/SQL that has some protections built in to stop the feature being bypassed; its got PL/SQL software license type features added not because the client wants software license features added but to prevent someone from selecting the code from the database and adding it to their own database to run it, play with it and try and break it. The license features also try and make sure it runs in the right context in the installed database; this is an area (secure code, PL/SQL IPR protection, PL/SQL software license features, context based security...) I am realy interested in at the moment and that i have done quite a bit of work with for clients in the last couple of years; hence i will talk about all of these things at the UKOUG SIG on October 10th and also on December 5th at the UKOUG conference.

So these protections and context based checks stop code from being broken or reverse engineered to allow the hacker to understand how the code and protections work and also these protections try and make sure the code only runs when it is supposed to and also wont run if installed in another database. Of course no protection will work forever when its protecting code in a database that someone could take and run or simply study somewhere else privately until they break it. The idea is to make it very hard and also to make it take so long that they will give up.

I have four layers of protection on top of the original code:

layer 0) PL/SQL Code - just normal code you and I write
layer 1) Add in license features and context based protection
layer 2) Obfuscate the code with out PL/SQL Obfuscator PFCLOBfuscate
layer 3) Wrap the obfuscated code with 9i Wrap
layer 4) protect the wrapped code with WrapProtect our tool that stops unwrappers from; well unwrapping the code

The obfuscation with PFCLOBfuscate makes the code hard to read and removes meaning but it also means that when the 9i Wrap is used the symbol table no longer gives away secrets in the underlying PL/SQL code. Its then not possible to simply modify the wrapped file directly to change a setting or check as it would also change functionallity elsewhere so breaking the code from running. Using 9i Wrap is also better as getting a working 9i unwrapper is harder and more importantly all the work i have done over the years understanding the PL/SQL wrap mechanism and unwrapping PL/SQL now becomes useful as I have worked out hundreds of ways to prevent all known 9i and earlier unwrappers from working. When these hundreds of ways are also randomised there are literally thousands of protections. The 9i wrap and the WrapProtect program doesnt have to be used of course but it adds that final layer of protection that makes it very hard for most people to steal your PL/SQL based IPR or to even try and run that code elsewhere or in a different context.

For a hacker to break the code they must find an unwrapper, defeat the unwrap protection, defeat the obfuscations and then defeat the license and context features.

OK, the reason I started this post was that during my work this morning I wanted to search for something in google related to this work. I did a search and i have noticed a really annoying feature about this more and more recently; the results are completely flooded with single domain names; whilst the actual pages may have some relevant data on the single domains, i can tell from just the snippits visible they are not relevant for me and I dont want pages and pages of results for one domain. I did a search in and and the first five results are for various pages on, the sixth result was my site, the seventh was actually not relevant at all for the search; then 3 results for old books and then 27 results for (yes 27 results for one domain, google never used to do this!!!) and then one for me again.

What use is this? over 30 results for in the first four pages of google results. I did this search in firefox; in IE, i get first 4 results for, then two pages for my site then the same pages as before in a different order and then pages of So not only are the results flooded by single domains they are different between firefox and IE, why?.

I then checked for the same search and the results are much more balanced. I did the same check in which is a great little search engine that gives that clean simple feel that google did many years ago; i really like it.

Come on google, give us back that great search and remove all the gimics like auto-complete which also annoys me. The current google algorithm must favour large sites with lots of back links otherwise how do we get pages and pages of results from strong sites instead of varied results from lots of sites. OK, in my search came back and according to google it has 31 million pages in its index, its a huge amount of pages that carry a massive collective weight and some are clearly relevant but i want balanced search results to find what i need not just pages of results from one site.

OK, google officionados are going to tell me to log in and taylor the search to not include the domains that I dont want to see or to do other trickery but I really don't want to log in to search. I just want plain vanila results everyone else gets. I don't see why I need to log in to get better results; bing and duckduck and othger search engines don't need me to do so to give balanced results.

Also I think that this has not passed others by as when i check search results in google webmaster tools I see a big difference to not that long ago. I used to see people clicking through on terms more where i ranked in the first page of results I now see click through for results where my site is pages and pages down in the results; this means in my opinion that people are probably looking deeper to get what they want instead of clicking the first page only as was previoulsy the norm.

I started to use and a while ago but still by habit use google but it annoys me for some results so i am tending towards the others instead but i have used google since the 90s.

Oracles Java Patch

OK, its not Oracle database security but its big news and it is from Oracle. Oracle have recently released an out of band Java security patch which supposedly fixed serious security flaws; then a few days ago the guys at Security Explorations who reported the bugs said that Java is still vulnerable and the fix didn't patch the hole entirely. There have already been phishing attempts with fake Amazon order emails and others exploiting these bugs.

Back to the database; doesn't this attempt to fix Java sound like what was happening with Oracle database fixes 6 or 7 years ago. We all would have to say that the database CPU, patches, fixes and more are getting much better than they were in the bad old days of alerts such as the monster alert 68 and we are all aware that. This is good of course. The topics of conversations a few years ago (4 years at least) for instance at the Oracle Security round table at the UKOUG conference were always focused around CPU's and bugs, I remember one round table where the talk around the group was almost exclusively about bugs/hacks and of course fixes. Even just talking to people out at clients or conferences or anywhere really the talk aways degenerated to CPU's and bug fixes BUT I really feel that has changed now and people are focusing more on actual data security and not just patches. This is good. We also know of Oracles efforts at teaching staff about secure coding and their use of code analysers mentioned in old blog posts so we know for the database there has been a concerted effort to get better.

When i read the stuff about the Java fix and the patch not properly fixing the bugs (see links above) it so reminded me of the old database days and i made a note to blog about it. I did a quick dig and found a post "A Decade of Oracle Security" quoting David Litchfield; scroll down the linked page to 2005, January 6 and see what David is quoted as saying on BugTraq; sounds very familiar!


New Oracle Security Talks

I am going to be doing three sessions at the UKOUG conference this December in Birmingham. I am going to be chairing the Oracle Security Round table on the 4th December. I am also writing three new presentations; two for the conference and one for a SIG.

I will do two new papers on the 5th December for the UKOUG conference; the first is "Security controls for DBA's, power users and third parties" - this will talk about how to design security controls to allow DBA's, power users and others to access and use the database safely without creating a bigger risk than necessary; i am also going to talk about how to allow third party and power access by using context sensitive security controls. I will cover the issues and example solutions for the problems. The second new paper is "Building Practical Audit TrailsBuilding Practical Audit Trails" where I am going to talk about building usefuk audit trails using just the core features of the database. So we will cover designing, managing, tech setup, reports, alerts and more. I will also cover auditing of the audit trail itself to capture changes or unauthorised access to it. I will also cover audit of security controls and also discuss the obvious risks and trade offs in using database audit features and what we can do to reduce those risks.

The final new presentation will be on secure codeing in PL/SQL; this will be given at a UKOUG Sig in London on October the 10th. This talk covers the risks to your PL/SQL code, how it can be exploited - so obviously SQL Injection but other attacks, how to prevent them and also I will dicuss protective coding, securing your IPR in PL/SQL, how to make sure your code only runs where it is supposed to (so context based security again) and i will also talk about secure coding when creating security features in PL/SQL with a couple of examples.

OK, thats it for now.

New Oracle Security Presentation - Identity In The Database

The paper "Identifying Yourself in the Oracle Database" is available as a pdf to download from my Oracle security white papers page.

This is new paper in terms of it has not been posted to my site before but i have presented this paper at around 4 conferences so far; the UKOUG conference last year, a SIG, the OWASP chapter in Leeds and also the Norway Oracle user group earlier this year. I wrote a paper last year about identity and accountability in the Oracle database to illustrate the problem that in a normal database (EE or SE) without additional identity products what you see in the database as session details is not much and in terms of identifying an individual its difficult unless the individual can be tied to external sources first (smart card, two factor authentication, ldap, fixed IP address....). What I show next is that there are other sources of data that can help add some "bulk" such as the listener log or trace output or other logs.

What is worse is that when the database audit is enabled little sesison data that is useful ends up in the audit trail; in fact the worse issue is that only one value can be manipulated (legally) that ends up in the database audit trails. We can manipulate other values that do end up in the audit trail but that is spoofing. So we look at spoofing session details and cover those that are easy and those that are slightly harder. In fact the only one that is not easy to spoof is the proxy user; the actual username can be spoofed by use of BECOME USER. This leads to the fact that some spoofing can be done before the session and some like BECOME USER after so these should be detectable.

I also look at customising identity via application interfaces available in PL/SQL or via OCI or JDBC.

One the the issues with session values besides relying on them in audit trails is that they are often used to set up FGA, or VPD or system triggers or label security or.... This is a potential issue as security often uses security and we therefore have to protect the security features in just the same way as none security features; in other words if you rely on some value such as "os user" or "database user" in a context used in a secure application role or in VPD then you must ensure it has not been changed or meddled with.

I finish with a short discussion on detecting spoofing of session values and then talk about solutions. The detection and solutions are three slides from the total so most of the paper is about discussing the issues rather than how to detect and correct.

Maybe it would be a good topic for a new paper for me to create "part 2" and talk in much more details about detecting identity spoofing in the database and also about hardening and protecting against changes in these areas.