Pete Finnigan's Oracle Security Weblog

I was just made aware by a friend that my Oracle security blog was 20 years old just recently.

As its a big anniversary I think its worth a blog post to celebrate.

The blog started on the 20th of September 2004; so just slightly over 20 years ago. It was at the time as you can see from the first comment the only blog specifically dedicated to Oracle security and whilst a very small number of other blogs on Oracle Security have come and gone over the years mine is still going and still regularly updated and new posts added and almost ... 100% on the subject of Oracle security.

I have created 1580 posts including this one. I used some old blog software Greymatter written by Noah Gray that was written in Perl and for a time I was involved in the updates and added a few enhancements that I fed back to the community and some that remained private as the community left. The blog software is now not used 100% and i create posts by hand and none of the dynamic elements like comments and votes etc work as I have not just turned them off but removed the code to prevent any type of attack. So the site is not dynamic.

I do plan to move the blog to a new engine that I will create myself BUT it will be on the PC. I created a piece of software I called PFCLSocial some time ago to manage new blog posts as a sort of writing area and storage for half written posts. I will probably build the blog generation into PFCLSocial at some point where it will generate and update pages and all that's needed is to sftp the pages to the site.

When i started the blog I was talking about the Oracle 9i database and we saw 10g start to be more of a security target and subsequently 11g and the new password algorithm for the time which was based on SHA1. Then more than 10 years ago 12c and multitenant came out and 12.1.0.2 came with the new SHA2 password algorithm and a whole host of changes for CDB/PDB databases. In more recent times we have welcomed 23 in the blog and probably the biggest number of security changes in the database for many years.

At the time of the creation of my blog there were no quarterly security patches for Oracle databases and we still had alerts culminating in Alert 68 which was the massive one. The password algorithm (the DES one) was not known, the wrap mechanism was not known, Database Vault, Unified Audit and database firewalls were not known and much more. My Oracle security blog has been there through most of the story of Oracle database security.

I had posts about oradebug, BBED, I was talking about forensics in the database 18 years ago and was around for the start of SQL Injection when I wrote a 3 part paper on SQL Injection for a site called Security Focus. There was the Oracle worms, there were fuzzers. There was the rise and fall of many products from third parties in the space particularly around activity monitoring, intrusion detection, intrusion prevention and even virtual patching.

The security of the Oracle database and the data held in them has changed over the years but the core remains pretty stable.

I just looked back at some of the other birthdays I recorded in this blog. For the first birthday in 2005 i noted that I was getting around 10,000 visits a month, 64,000 visits a month on the 10th birthday of the blog in 2025 and now we get between 200,000 and 220,000 visits a month so its improving. More on the website stuff soon!!

Have a look at the blog archives linked on the right of every page and also some choice posts are promoted in our social channels from time to time.

#oracleace #sym_42 #oracle #security #20 #birthday #blog

We have just released version 2024 and we have added around 340 new checks to the analyser for PL/SQL to located PL/SQL security issues. We can identify a number of types of security issues in your PL/SQL that includes:

• Use of dangerous packages


• Use of deprecated code and packages


• Find data leakage or identifying potential issues through comments


• The use of undocumented packages or internal packages in your code


• Access to dangerous objects or objects that could leak useful information to an attacker


• Vulnerabilities such as SQL Injection as well as access to resources, the file system, exceptions and much more

We also check for all issues natively; i.e. direct use in PL/SQL code as well as check for most issues in strings as well so that if the code include dynamic PL/SQL or SQL then we can detect those cases as well.

The product is easy to use and I want to give a quick overview here. First open the application and go to the options screen and connect to the database. The user needs CREATE SESSION and also just SELECT ANY DICTIONARY for the application to work.

Once connected go to the "Schema" tab of the options dialog and click "refresh" to get a list of schemas. Once that has run select a schema to analyse. I am going to choose my ORABLOG schema. Next click OK and the schema is saved. Now in the main File Menu and click "Refresh Code List" and screen refreshes and lists all of the code in my ORABLOG schema:


The screen shows a list of all of the PL/SQL code in the ORABLOG schema (in this case) and the list shows to the left a small bar chart indicator in color of security issues located for each piece of PL/SQL. Red is most severe and Yellow the least. We have the numeric number of issues located, the name and type and whether its DEFINE or INVOKER and lots of details on when each piece of PL/SQL was created. On the right hand window we have a tree of each piece of PL/SQL ordered by type such as PROCEDURE.

The bottom left hand window shows schema wide issues not related to single piece of PL/SQL.

If we then click on a procedure such as CUSTA we can see the issues found for that procedure; or indeed any other piece of PL/SQL if we choose it. The screen changes:


The details for CUSTA issues are shown in the bottom right screen and if we click the SQL Injection issue we can see the PL/SQL source code and the issues that were located for this bug in the source code. All relevant issues are highlighted and the severity is shown as a coloured square to the left. If we hover over the SQL Injection issue:


We can see the pv_name is passed in in line 1, concatenated in line 8 and then used in the OPEN in line 9. There is no filtering or protection code so this is exploitable.

If you hover over each highlighted line you see more details of the issue or go to the table at the bottom right for details.

We can run a summary report across the whole schema or a detailed report that lists details of each issue found.

PFCLCode 2024 is very flexible and we have just shown a brief glimpse of it now. You can enable/disable checks to create a custom scan/investigation and you can also see progress in the dashboard by comparing any scans over a period of time to chart progress on fixing issues. The end user can create their own checks easily if needed and can edit ours.

The tool also allow a detailed view of granted permissions of each piece of PL/SQL and also of dependencies.

This is an easy to use tool that can be customised or used as-is and gives you a great overview of PL/SQL. Ask me for more details of PFCLCode 2024, ask for me to demonstrate the product live to you on webex or simply ask me if you would like to buy a license or if you would like us to audit your PL/SQL in your database for you.

#oracleace #sym_42 #secure #plsql #sqlinjection #static #code #analysis #securecode #codereview #pfclcode #pfclscan #audit #databreach

We have recently added around 750 new checks to our Oracle database scanner PFCLScan for our new version 2024 release that can be used to locate security issues in any Oracle database. We now have around 2000 security checks that can be run against any Oracle database to check its security posture.

We support scanning databases up to the latest 23c and as far back as Oracle 9iR2. Yes, although rare now, we do still get some customers running 9iR2 and 10g. Our normal focus on 19c as that seems to be the predominate database that our customers scan and work on and we see some on 21c and as I said some older but mostly 19c.

I will describe the scans and the results shortly but first some words about the test databases.

I wanted to compare the security of 6 different databases using the same scan checks and reports so that at least is consistent. The problem is not all databases are equal. We have legacy databases; we have multitenant databases, we have Enterprise Edition Databases, we have free editions such as XE and FREE. We also have Standard Edition. Each database that I tested is running on Oracle Virtual Box and some are local to the scanner on the same laptop and some are on our office server running via Oracle Virtual Box. Each database is running on different virtual hardware; the worse case being the 23c Free running on the limited spec allowed by the Oracle supplied image. Some are running on multi-processor with much more RAM.

Each database is similar in state; in that we created these test instances to test our software products and they are not production systems. Some have limited amounts of test applications and data and some are vanilla.

For the multitenant databases we connect to the PDB and scan but as a multitenant database must also have a CDB we should probably also consider the state of security of the CDB BUT as we wanted to compare database for database we will just look at the PDB in each database.

Also remember that in 11g or indeed a legacy install in an older multitenant database the password hashes for each user are stored in the same database, in early multitenant the password hashes for common users are available in the PDB but not in later databases so for the password cracking for instance we cannot crack common user passwords in the PDB.

We must also consider versions and features. A smaller number of features and parameters that affect security are deprecated or removed in later databases BUT new features and settings that can affect security are added in later versions.

So, if we take all of the versions, types, settings, legacy, multitenant, what's installed and more into account comparing 11g with 23c is clearly not like for like BUT its good enough to try and draw some conclusions.

The databases tested are 11g (standard Edition 2 - Legacy), 12.2c (Enterprise Edition, multitenant), 18c (Enterprise Edition, multitenant), 19c (Enterprise edition, multitenant), 21c (XE, multitenant) and 23c (FREE also multitenant)

We are not the same as CIS or DBSAT. We check a lot more things in the database; indeed we check much much more than CIS but we do cover similar areas but in more depth. We also check differently in some cases. Where standards often call for execute not to be granted to PUBLIC for a specific PL/SQL package such as UTL_FILE we check for everyone its granted to as that matters in terms of trying to lock down and secure your data in an Oracle database.

In general we want to understand a number of layers in securing an Oracle database; patch status, hardening, actual data security and of course context based security as well as audit trails. We cover all of these areas not just hardening

we use the Oracle user SYSTEM to connect for these tests BUT you simply need an Oracle database user that has CREATE SESSION, SELECT ANY DICTIONARY and SELECT ON SYS.USER$.

So, back to the tests. I connected to and scanned each of the 6 databases separately and then ran two reports; the single page summary report that gives a score for the whole database and also breaks down the issues into categories and gives scores for each. Rather than print in line here 6 reports I have summarised the scores into Excel and show an extract here:



If we first look at how the overall score for each database version changes over versions we can more easily see this in a graph:



This is interesting; taking into account the versions of database, data and types of database described above. Even if we consider the databases are EE, XE, STD, FREE and different versions the core security only really changes in terms of a very small number of function, features and parameters removed over time and more features, parameters and functions added over time. The graph shows that the security got better from 11g to 18c and then dropped again with 19c quite a bit and then improved for 21c and dropped again slightly for 23c.

What we cannot see easily is although the databases changed, the security parts changed in terms of quantity we cannot see how much changing settings from insecure to secure changed the overall picture; my feeling would be not a lot as the quantity change is likely to be much bigger and drown out limited setting changes BUT a much more detailed analysis would be needed and also on clean same edition just installed databases with no customer data / code / users added BUT I don't have that here today to test with; maybe in the future

How did I calculate these scores?

Each check executed against the database has a severity and also category. Checks that are severity 1 (critical) have a higher weight than checks that are severity high (2) and so on. We know how many checks are run per database and how many fail and pass per severity so we can calculate the percentage pass rate (the % secure) for the overall database and also the percentage pass rate at the category level

Next is a breakdown of the percentage pass rate per category for each database version tested:



The output is interesting and again shows how security, lets call it default security as we did little of our own work to these databases. If we look at patches each version is very similar with 19c and 21c coming out slightly better. This is mostly due to the state of the database as installed. In all cases from 11g through to 23c all databases were either a pre-packaged rpm from Oracle or a "yes, give me a default database" on install. This is because these are not production and are used only for development and test.

If we look at audit trail, 11g was around 60% pass rate, 12.2 got worse, 18c similar and then there was an improvement from 19c and 21c but this dropped off again with 23c. The dropping off with 23c could be the de-support of standard auditing.

Password security is interesting; the overall state is similar across all 6 database versions but it got clearly better from 11g to 18c, dropped a little with 19c and 21c and came back again slightly with 23c. These figures and skewed and comparing 11g to the later versions is slightly unfair as in 11g all passwords are in a single instance but for 12c to 23c we have looked at the PDB only but the CDB also has a set of passwords and after 12.1.0.2 the hashes for common users are not in the PDB.

Hardening is interesting; if we look at the trend, its clearly going down from 11g to 23c with a slight blip up for 18c. Hmm, is hardening getting worse as a default? most likely this is because the default improvements are very small but the number of things added that can affect security has grown over the versions and years and clearly more are not default in the most secure state.

User security is all over the place. in 11g it was much worse, 12 and 18 improved a lot, 19c dropped back to 11g levels and 21 and 23 improved again. Why? as we said above a few reasons; the 11g was legacy only from 12c we are only taking into account the PDB and also we must consider algorithms and default status of default accounts.

Data security is pretty level but clearly dropping slightly over the years and versions BUT there is little to no data in these test systems and my experience on real systems is that this area is usually the worst for customer databases as the security tends to be non-existent at the data level and/or its legacy style designs.

External access again is relatively flat across versions but as you can see the trend is downward slightly as we go from 11g to 23c

Vulnerabilities is all over the place, 11g is low as is 19c and 21c but the others fair better

Data Security is the most important area of course in all of this; we are of course trying to secure data BUT this test does not cover data well as my test systems do not have real applications in them

A sample top half of a single page score report is shown here:




Ask me for more details of PFCLScan 2024, ask for me to demonstrate the product live to you on webex or simply ask me if you would like to buy a license or if you would like us to audit your database for you.

#oracleace #sym_42 #ukoug #oracle #database #security #securityscan #vulnerabilities #datasecurity #databreach #datahacking #lockdown

Firstly I was very pleased to announce that I have been made an Oracle ACE Pro again for the year to come. I just received the Oracle ACE tee shirt, polo shirt, jacket and of course the ACE Certificate. The Oracle ACE program is great and brings together a lot of enthusiasts for the technology. Of course I specialise in Oracle database security and everything related to that subject. From security auditing, audit trails, secure coding on PL/SQL, forensics of databases and all of the features and technologies that relate to securing data in an Oracle database covering such features from time to time as Database Vault, VPD, OLS, TDE, Masking, Encryption and much more.

You can always read about what I am looking at here in this blog and also on all of our social media channels on Twitter (X), Facebook, LinkedIn, YouTube, Instagram and Threads where we also bring Oracle security content.

I am also interested in PL/SQL and write in languages such as PL/SQL, vb.net, Lua, C and others to a lesser degree. I keep promising to publish my series on writing a language interpreter in PL/SQL for a simple language and I will publish it. We have been very busy of late with the new versions of our software so this series will come as soon as we get a gap in the schedule to edit the content and then post.

We have also been digressing lately with the website dealing with broken links - we wrote a tool to find these and its worked well; we find more broken links than some other software we tested. We have also started to prepare the website for HTTPS. I know its still HTTP and some people message me from time to time to tell me this. The only reason to make the change really is because Google insists.

The site is not dynamic and we don't collect an data personal or any other data so there is nothing to protect as such BUT we are working on the change but the site is large (depending on how you count what is a valid page/file we have between 6.5k and 10k pages. Some such as .sql files are displayed as tools, examples but they are not web pages hence the counting issue. We get around 200k visitors a month to the website and have around 42k connections/followers on social media so we need to be careful with any big changes.

Onto TDE; a week or so ago David posted a short article on LinkedIn extoling the virtues of TDE and why people should use it to protect data. The post didnt have a title as such but stated "I use storage encryption, do i still need to encrypt the database". This is a short article and the main point that I got was the fact that PCI-DSS states that storage encryption is not sufficient to protect the data as the transparent nature of the storage encryption means that if an attacker gains access to the server then he/she can read the files and in particular the data files.

My comment was:

"
Hi David, Thanks for the short article. I agree TDE is useful but as you say "anyone who has access to the host can access data without logging onto the database" - The access is much narrower than this though. The access that would cause an issue is access to the datafiles and usually this is only the software owner - often called "oracle" or in the OINSTALL group. This access is usually limited as I say to the software owner so if you as an attacker have access to "oracle" then the problem is bigger. Then the attacker can simply do "sqlplus / as sysdba" and access all data transparently decrypted.

Similarly at the database level if the application grants access to the data that should be protected to public then any database user can access the data as its transparently decrypted.

You need a more rounded solution; with database permissions, connect protections, OS level restrictions on access to "oracle" and more

You should also implement a comprehensive audit trail in the DB and OS on the data that should be protected
"

The same issue in effect applies to TDE as per storage encryption; this is Oracles storage encryption and as such it transparently decrypts the data for viewing via any SQL interface. So if you have access to the server as Oracle then you can access the database as SYSDBA and read the data as can any user who is able to access the application.

The key message for me is that this is a layer below/above? storage encryption and the attacker needs privileged access to exploit it unless the application is lax in permissions and allows everyone to see data.

As I said in my comment on Davids article we need a layered approach to protect access to the OS and the database and also around data security itself and permissions and access designs.

Lets be clear TDE is a good product and useful but its a tool and as such must be combined in a complete design to protect the data at all levels


#oracleace #sym_42 #ukougtag #ukoug #oracle #security #tde #encryption #datasecurity #databreach