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.

Securing APEX

I have liked APEX for many years and been involved in auditing and securing Oracle databases that include APEX for many years.

What surprises me sometimes is that those deploying and developing an APEX application treat it like a box and do not consider that APEX is written in PL/SQL, uses PL/SQL and is deployed to an Oracle database. Its like they believe that the database security is someone else's problem or they didn't realise that its in a database - with the data!! Sometimes, the Oracle database is a side issue for them; it's not considered in terms of security and the only factor for the customer is that an Oracle database is needed to run APEX so its provided and not secured in any meaningful way

I wanted to do a post now to highlight at a high level the areas that should be considered for securing an APEX application and database when using and deploying APEX in your database. Lets list and discuss briefly:

  • Web Based Security: Your APEX application may use JavaScript to enhance its features and operation but Javascript can be the target of attackers through input manipulation for attacks such as Cross Site Scripting (XSS) and more. This point is not just limited to JS but to any web level technology that can be used from APEX

  • Webserver and network security: The webserver is likely to be ORDS and it provides the web content to the end users. The security of ORDS must be considered and the network design of the database, ORDS and access must be secured

  • APEX Security: The APEX application itself must be secured at the APEX level. This includes user security, profiles, passwords, instance level security, deployment security, APEX database object security, workspace security and more

  • APEX Application security: APEX provides security at the application level and allows security of users, authorisation, authentication and page level security. The application in APEX is written as snippets of PL/SQL that are stored in the APEX repository and these also must be written with security in mind and secure web and database coding techniques used

  • Supporting PL/SQL: Often the bulk of the APEX application will be written in separate PL/SQL packages called from a thin veneer of PL/SQL in the APEX repository. These packages must be also secured and designed with security in mind. The schema that owns them must be limited in rights and access to he rest of the Oracle database. The PL/SQL must be written with secure coding techniques in mind. This includes avoiding SQL Injection but also managing access to resources and features securely from the applications PL/SQL

  • Schema design and security: The schema that owns the PL/SQL of the application must also be designed with security in mind. The data should be held in one schema and application code (PL/SQL) in another (at least) to allow grants to be made between code and data and not a free for all. Least privilege of the schemas must be observed

  • Database Level Security: Database level security is a complex topic of course and I have covered the basic areas here many times. At a high level we must patch and harden. These alone will not secure data. We must also then secure the data within the database. This is the biggest part of the task of securing data in an Oracle database. This part includes user access controls, user level privileges and data level privileges.

  • Context Based Security: An additional layer that can be applied is to use context based security such as Database Vault, Virtual Private Database, TSDP and more. The normal rights at the database level are granular but Oracle provides extra (often cost based) features that allow more controlled and fine grained security to be applied. We can also create context based security of our own using PL/SQL code, triggers and views. Context based security can also be used at the APEX level as well as the database level

  • Audit Trails: There must be well designed and useful audit trails implemented at the database and also at the PL/SQL level and APEX levels so that any potential misuse of the application or data can be captured to make any breach analysis or forensic analysis easier

  • OS Security: The operating system that hosts the database must also be secured. There are many layers that are similar to the database and APEX. We must secure defaults, hardening, users and privileges, audit trails and more

  • Admin and User Access: We also must consider how users and particularly types of admin users (sys admins, DBA, support etc) access the OS, the Oracle database and APEX. We must ensure that data is protected through these types of access and that the access is audited

Attacks are many and varied from the web level to abusing APEX itself or abusing the PL/SQL or database or even OS level. Securing an APEX application is multi-layered and we must consider all layers for security when we deploy an APEX application from OS to web.

In general we can take the same approach with each layer:

  • Review existing security policies

  • Review the actual systems (Database, OS, APEX, PL/SQL)

  • Understand the security requirements and design a new policy, update the existing policy, create a fixing strategy

  • Fix and secure all systems

  • Perform continuous review of all systems (Database, OS, APEX, PL/SQL...)

#oracleace #sym_42 #plsql #apex #oracle #database #security

Investigate an Oracle Database Breach

I have investigated a number of possible and later proved data breaches in Oracle databases over the years and more recently just before Christmas I was doing the same again for a client. I cannot talk about any specifics of course here of the recent client BUT i want to go over the basic steps and what we were trying to achieve.

The subject of a response to a breach is huge; there are many factors that must be taken into account as part of the investigation and also going forwards after the initial breach and into the fixing, correction, protection phase.

This is a huge subject that cannot be done detailed justice here as a blog BUT lets dive in and discuss the high level points of investigating a breach of an Oracle database. We will go through roughly chronologically at a high level and at the end bring in some elements that should be done now in advance:

  • Planning : In advance of any breach you should take some actions.

    • Appoint an incident response co-ordinator : Identify someone who will manage the response when it happens. This ideally should not be someone deeply involved in a breach. I.e. the DBA sounds like a good option BUT they will be needed in the response at a technical level and they could potentially be part of the attack

    • Identify and create an incident response team : Create a team in advance that will be put into action as soon as a breach occurs. Where that team knows what to do and how to do it

    • Create an Incident response process : Write down the steps that should be taken under any potential breach so that a structured response can be taken

    • Create a toolkit : Build a toolkit to use when a breach occurs that is validated and tested and where everyone knows how to use them

    • Provide Training: Ensure that all of the response team in advance of a breach are trained on how to respond to a data breach in an Oracle database and trained on how to use the relevant tools and how to review and analyse data that can be part of the breach

  • When a potential breach occurs verify it : If a breach is reported then the first step is not to disrupt business initially and verify that it really is a breach. How to do this is different based on each breach. For instance if someone says that personal data was breached and that data was for sale on the dark net or being promoted for free on public social media sites. In this case if we can show that the data is really ours - by content and order then we can get started and agree there is a breach. Another example could be that evidence is shown that someone is selling data or taking data out on disc or other means. This step is different per breach and we are trying to prove that the breach is real

  • Enable the Team : Once we know the breach is real, enable the team. The team co-Ordinator takes charge of everything and works to a check list that is your incident response process

  • Don't initially disconnect and switch off : The first reaction can be to disconnect the database from the network or pull the power cord. This may reduce the further risk BUT it removes the change to get all transient data

  • Perform Live Response : Collect the live data that is held in memory; users logged in, contents of current SQL and more

  • Collect less transient data : Collect the less transient data

  • Collect other data : Collect other relevant data such as server logs, web logs and more

  • Repeat the process on all servers/databases : Repeat the same process to gather data from all relevant systems

  • Shutdown / Disconnect : If its possible disconnect the database and server and shutdown

  • Perform Forensic analysis: Now we must build a timeline of relevant evidence and actions from the data that has been gathered in the live response. We must narrow the timeline to the actual breach; when it started and when it ended. Initially this timeline will be much wider but as the investigation grows we will narrow it to the main events that contribute to the breach

  • Answer questions : We must answer some basic questions; 1) how did the attacker get it?, 2) What rights did the attacker have?, 3) What did they see, steal? 4) What did they change?, 5) What could they have done with more skills?

  • Create a Report : At this stage we cab create a report that summarises the attack and answers the questions and suggests what is next

  • Fix, Rebuild or?: If we know exactly what happened we cab decide how to rectify the database and security and applications. The ideal scenario is to fix the database or data and of course security. Rebuilding will be a risk if the backups span the actual attack

  • Implement auditing : Implement a detailed audit trail so that any future breach is much easier to investigate and resolve

  • Implement data security : My experience is that often data security is not as good as it should be and in some cases its shocking. Implement a good secure data security regime

The above details the high level flow of the actions to take if there is a breach. One thing that pops out of an Oracle database forensic analsyis is that unlike analysis of a PC we cannot simply remove the discs of a database and byte copy them for analysis, for a number of reasons; license?, size, tools to analyse the database as data on disk.

Another key issue is the Heisenberg principal in Oracle. The more we question the database; the more we change the database and change the evidence. We also cannot simply take checksums of discs for the reasons above as we would in a PC analysis.

One of the biggest issues is that if the breach is READ based then without pre-defined database level audit trails in the database detecting any read action is difficult

It is clear that forensic analysis and live response of an Oracle database breach is difficult so it makes sense that training in this area is performed in advance of any potential breach so that the relevant teams know what to do and how to do it. It also makes sense to have tools ready for any potential breach and training in the use of those tools

Quite clearly it makes sense to secure your Oracle database before a breach and have detailed audit trails!!

Plan ahead

#oracleace #sym_42 #oracle #databreach #security #forensics #liveresponse #hacked #audittrail #audit

Happy New Year for 2024

It has been a while since my last blog on the 29th December on the ACCESSIBLEBY Clause in PL/SQL .

We had a well earned break after the New Year and myself, my wife and my youngest son visited New York City for 5 nights for my wifes 50th birthday and managed to walk over 40 miles whilst we were there. We saw all of the main sites including the Statue of Liberty, the Chrysler Building, Went up The Empire State, walked the Brooklyn Bridge, Central Park, the modern art gallery, the National gallery, Staten Island, Times Square, The Dakota Building, The flat Iron Building, the 911 memorial and site of the twin Towers, the new World Trade center, The Intrepid Museum and many more. It was a great break from Oracle Security and nice to take a proper holiday even though it was hard on my feet to walk such a distance!!

For quite some time I have managed to blog about Oracle Security once or sometimes twice a week. The good news is that the posting schedule up to the New Year will continue into 2024. So expect more technical blog posts every week and some more higher level posts throughout the next year.

We are working in many areas all related to Oracle security and these influence what I want to write about here. We have a custom application that we have created to manage future blog posts for the future. This just allows me to write and start many posts and add to them as time allows. We have over 170 posts currently part written, complete or just titles and some research n this small custom application. It's not supporting direct posts to the blog but it could and has some of that functionality built in already so we could post from it in the future.

We have a lot of things Oracle Security wise going on in the last months and also coming up in the New Year. I cannot talk about specific client details but these pieces of work can certainly inspire some blog posts that discuss the generics of things I have done and seen that could be useful to others. Here are some examples that I will discuss very soon:

  • Oracle Database Breach: I was asked to investigate a breach of an Oracle database via a web site that turned into a very large loss of data for the client. They wanted to know how the attacker got in and what they stole and what needed to be done to prevent another attack. I cannot discuss any specifics but I will discuss in a new blog post the overall process of investigating a data breach of an Oracle database. Watch out for that soon

  • Protect the PL/SQL in an Apex Application : I helped a customer protect their PL/SQL code in an Apex application. I will not of course go into any details of the customer system and code but I will highlight here in a new blog post the process we went through and what to do and what to look out for

  • Review an organisations Oracle databases and security policy: Before Christmas we reviewed the data security currently in place on a customers site for their Oracle databases and their policies. Again I cannot discuss specifics but I will create a blog post soon that will discuss the overall process to review the Oracle data security across an organisations Oracle database estate

  • Adding customisation to PL/SQL: I spoke here some time ago about writing assemblers, compliers, interpreters and more in PL/SQL in a post titled "Adding Scripting Languages to PL/SQL Applications - Part 1" and I do plan to return to that blog series very soon as I have done quite a bit of work on it. Someone contacted me because of that post about the idea of adding customisation to PL/SQL but not in PL/SQL. This is exactly what I intended to do with this series of blogs on compilers etc. The reason to not customise in PL/SQL such as you would in essence in an Apex application is to provide a much simpler interface for an end user where their are perhaps some library functions and a vert simple language where the customiser is not necessarily a programmer. Think of games where an end user can extend the game in a language such as Lua. Why not extend in PL/SQL? - its obvious, if you allow the end user to add PL/SQL to your code its an open SQL or PL/SQL Injection interface

Well that's a flavour of what's coming soon plus a lot more.

Happy New Year for 2024!!!!

#oracleace #sym_42 #oracle #database #security #securecode #plsql #forensics