Pete Finnigan's Oracle Security Weblog

I want to talk in this entry and continuing in another probably tomorrow about whether allowing your users to set SQL trace is a security risk or not. Why would setting trace be a security risk? Surely you want your people to be able to fix performance issues?

First let’s clarify what trace we are talking about. I am talking here about SQL trace, the trace files that are generated by setting event 10046 or indeed other events that can be used to generate trace files in the database. Some other events can be used besides 10046 to create trace files include 10053 which traces the optimizer or 10928 which traces the PL/SQL virtual machine. There are many others that also generate trace files.

How can trace be set for a user? - This is a good question. There are many ways provided by Oracle that can be used to set trace in your own session, i.e. for the user you are logged in as in the current session or for another user in a different session. Also a number of the methods of setting trace allow you to set the trace level as well either for your own session or for another’s session. The levels allow you to specify what details end up in the trace file such as bind data or wait events. I won't go into details about all of the methods available for setting trace as I have done that before on this site with a detailed paper showing I hope every way possible to set 10046 trace in the database.

OK, so back to the original question, why is it a security risk to allow users to set trace and to eventually create trace files? There are many reasons why this could be the case. Trace files contain a lot of sensitive data. They contain the actual SQL statements used by applications and also can contain the lower level SQL used by Oracle itself to satisfy the application SQL requests. This is known as recursive SQL.

Imagine an application that is closed source, maybe a commercial application. A hacker or more realistically a malicious employee could set trace and get all of the SQL issued by the application for a particular session. This when compared to sparse screens he normally sits in front of would include schema names, table names, view names, even triggers, PL/SQL executed on the user’s behalf, the fields accessed and actually processed and many more things. Worse still if he can enable trace to give him bind variable information he could access data that he could not see on the screen normally. In this way data could potentially be leaked.

The "user" could also see how to perform a SQL Injection attack from the information he gets in an SQL trace file. He could also dump certain dictionary structures and reveal passwords if the version of Oracle is early enough and not patched. I have also seen that it is also possible to reveal passwords in certain conditions on later versions. The hacker could reveal details that he should not get normally for such features as Row Level Security (See my two part paper on this subject for more details). If you put in the effort as a hacker then trace can be used to find out many details of an application and its databases structure that can be used to steal data or possibly gain access. So in my mind trace in the wrong hands can be a risk.

Now, let's not get carried away, we know that the trace files are created on the server and that they should not be accessible to all users. That’s a good thing. But what if the database is not secured and some of the methods that can be used to access operating system files are accessible to the hacker? - Then of course he could get to the trace information. If he could also access the server itself then potentially he could also read the trace files.

There is a way to get trace details on the client as well, this is by using the autotrace facility in SQL*Plus so we need to consider this aspect. Also we should not forget about SQL*Net (or Oracle Net as it’s now known) trace files as well. These can also reveal details of the actual SQL used. This is very difficult to protect against as a hacker could also sniff the network packets for the same information. We will talk about how to potentially protect against this aspect tomorrow. The third part of my SQL Injection paper "Detecting SQL Injection in Oracle" that i wrote for security focus talks about some of these methods. There is a link to it here.

I should also point out that I am considering here internal users, your companies staff that have access to client PC’s on the internal network here.

Some database users obviously have access to trace generation and the generate files themselves though. Maybe consultants ensuring the database and application performs, some of the development staff maybe? The DBA's certainly. But the rest of the normal users should not get access to trace generation techniques and should certainly not get access to the generated trace files.

In part 2 of this entry I will talk about how to audit the database to find out which users can set trace and what to do about those that can. I will also talk about how to protect against accessing the trace files themselves, what you might do about autotrace and also protecting against the other methods of SQL leakage.