Pete Finnigan's Oracle Security Weblog

I posted two blog entries on the subject of "Instrumentation - a god send for speed freaks - a god send for data thieves" and "Is it possible to steal data with just ALTER SESSION?" over the last few days and in response to the latter post Alex has added a comment linking to a response posted to his blog post.

I just want to cover a few points here. I didn't mean just that utl_file_dir was set to "*" or that DIRECTORY objects exist that point at the trace directories. These were examples; as other scenarios also existed that would allow access to the trace files. I have seen external tables already exposing the alert log and also trace files. I have seen Oracle's SQLTXPLAIN installed in three database in the last year (this also exposes trace directories).

A couple of examples in the last year were a site with somewhere around 10 users that had ALTER SESSION out of around 200 users (so around 5%), in this database the utl_file_dir parameter was set to "*". Another database had around 400 users with ALTER SESSION from around 1800 users (22%); in this database SQLTXPLAIN was installed and every user of the database could access the DIRECTORY object that pointed at udump.

Alex asks whether ALTER SESSION is a bigger issue that access to the file system and suggests that access to the file system is the bigger issue. I would agree as there are a huge number of possibilities to get to the file system (whether they are enabled by default or not is not the issue - as they could be). But i would also agree that both issues are a problem and that we must concentrate on the data first not specific parameters.

The problem with Oracle is the complex heirarchy of privileges and access. Oracle is a complex product and the access paths not just to data but also to the operating system are often complex.

Take a simple table called CREDIT_CARD, it may have privileges granted by the owner to other users, there may also be a heirarchy of views/triggers/ PL/SQL built on top of the table each with their own privilege models. Then take the scenrio presented first in this discussion that there are often other methods to get at the data such as from the SGA, Trace files, redo logs, archive logs, Log-miner, list files, export files, backups, data-files....(there are many more); then take the scenario that the data is actually often duplicated in the database in many tables and then the same heirarchies sit above each of those copies of the data. The problem of securing the data (CREDIT_CARDS in this case) is not a flat world view but one of knowing "where" the data is and taking actions to prevent its theft.

The same ideas can be thought about with the access to the OS. There are many ways to get to the OS from the database, utl_file_dir, DIRECTORY OBJECTS, Java, user C, built-in packages that use Oracles own C functions, Oracle Text that Alex has written about many times, scheduler, external tables, initialisation parameters, classic packages such as UTL_FILE, all the packages that referenence and use UTL_FILE and those that reference those; then do the same exercise for all packages provided by Oracle that allow acess to the OS (using Java, C, DIRECTORY objects or any other method). Then we have the same complexity issue that we have with the data. The access if not there by default is not secure as other users could create the access. Therefore we also need to consider privileges such as the JAVA roles that allow access to the FilePermission JVM privileges, the rights to grant Java privileges, the rights to create Java code and use existing privileges, CREATE ANY DIRECTORY and then a level above the rights that allow granting of the rights we have just discussed such as GRANT ANY PRIVILEGE or GRANT ANY ROLE. we also need to consider default users with bad passwords that allow someone to also either use rights directly or gain those rights indirectly.

A second idea to think about is that DBA type staff already have SYSDBA, OSDBA, the DBA role, IMP_FULL_DATABASE, ALTER USER (change SYS password - in this context ALTER USER is good enough to access a trace file on the file system BUT why would you bother when you can simply log in as SYS?), ALL PRIVILEGES and many more. The issues with security and data theft are not "flat" that is there are many ways to steal and many types of people who may steal. Question: What if the DBA wants to steal credit card data, knows audit is enabled on the credit card table? - maybe he can simply dump the buffers and read them directly and avoid direct table access? -

we must not be blinded by the fact that access is not available to PUBLIC.

This is not any sort of definitive list of issues it is meant to be a discussion of the complexity issues involved in securing Oracle. Securing an Oracle database is complex because we MUST consider the data first not simply follow checklists.

These ideas (many paths to the data and also different routes to the file system) are what I discussed in my 2 hour Oracle Security masterclass at the end of last year in Birmingham at the UKOUG conference.