Pete Finnigan's Oracle Security Weblog

I read with interest Marks two posts today summarising his last year of blogging about Oracle. The first part is Revue Of 2004, Part One and the second is Review Of 2004, Part Two. Mark says it is a tradition to summarise the year and look back over the events of the year.

I liked Marks idea and thought I would add a very quick summary here about my own Oracle Security blogging exploits. I have not been doing this for long, in fact about 3 and half months in total. I started in mid September and promised to write most days about Oracle security, either short technical notes, longer articles, tools, updates to my site and of course news that affects Oracle security. It has been an interesting first three months and I have enjoyed looking out for interesting things to write and of course read about in the Oracle security world. I have, I think exceeded my remit of "at least posting most days" as i have made 189 posts in three and a half months and some days posted 4 and even 5 entries (I think!). I won't go into great detail like Mark, the highlights for me in my blog have been the news items relating to alerts and bug fixes and the media interest in my opinion on these subjects, its quite flattering I suppose. I have enjoyed reading many new papers and annotating their contents as I recommend them here and I have also enjoyed reading other Oracle bloggers (is that the correct term?) blogs usually looking for security items but also reading generally interesting articles and learning from the vast vault of knowledge out there on Oracle and security and technology in general.

I look forwards to making many more posts in the New Year and wish everyone all the best for 2005 and hope you all have a great new year!!

I just found a free Log analysis tool written in Perl and distributed under a BSD style license. The tool is called LMon and is at version 1.0 and written by Anders Nordby and LMon V1.0 is available from BSD Consulting. This tool allows near real time monitoring of logs and alerts can be sent by email when rules are matched or unknown data is encountered. Multiple rule hits can be buffered over a given time interval. The tool can also notice log rotates. The scripts structure is simple and is run from the command line controlled by a configuration file. Multiple instances can be run against different log files.

This looks like a very useful tool that can be used to monitor Oracle log files, such as the alert log, listener.log and many more. Trace files could also be monitored, database as well as Oracle Networking logs. Even packet capture logs could be monitored. The key in the Oracle arena is setting up the correct rules to look for the security events that are important.

The configuration task could be big but the tool looks like a very useful one for Oracle security practitioners. I have updated my Oracle security tools page to include this free tool.

I saw this evening a very interesting post from Lucas Jellema on the Amis blog. The post is titled "Enabling Role-based security management in Oracle Designer 6i/9i/10g through the Repository Object Browser" and is well worth a read. Lucas starts by telling us of the overwhelming task involved in managing users and privileges across many applications and folders. He also explains how difficult it is to add new developers to projects unless the Repository Object Browser (ROB) is installed.

Lucas tells us some of the history of Oracle designer web assistant and the fact that roles are not fully tested (implemented?) and that ROB is available as are roles but can they be used or not?

Lucas goes on to tell us how to re turn on support for repository roles and as he says switch off product support. He goes on to talk about working with roles, creating roles, granting roles, granting roles to other roles and users, reconciling roles, revoking roles, revoking privileges and deleting roles.

This is a very thorough and interesting article and well worth reading. Again it can be found here.

I saw an interesting post on the oracle-l list last week that I thought I might mention here. The post was titled "10gr2 Beta Testing XML DB" and talks about the fact that 10gR2 has some new XML DB functionality that can be signed up for testing under a beta program. The poster said he knew about it from the XML DB discussion groups (otn?, metalink?) and he posted a link to the beta program.

Whilst this is not directly an Oracle security related post, it is interesting for two reasons. The first is that access to any new feature before it is released is always useful for the security conscious DBA. Whether you can get this access without promising to test and be a proper part of the beta program is another thing, I suspect not!. Access to new features especially web type features is worth having in advance if they are likely to be used in the future in your organisations. This is so you can get a heads up on installation, use and the security configurations. Also if it’s still in a beta program any security concerns could be brought to light and fixed before the software is used in anger. The second reason is that as hinted its a web type feature so has an inherent security component in that its likely to be web facing or at least network facing, whether wide area or local is irrelevant. There will be security concerns that will need to be taken into account when using software such as this. Having a heads up may help you make it more secure if its used in the future, if its not used then knowing how to secure it even if its not used can still be useful.

My final thought with software like this that is in beta is that there are likely a lot of developers, DBA's and others that will take part and use it, any company involved needs to know that its involved, i.e. if keen employees take part does the DBA and security officer know and are they involved in ensuring security is maintained.

I was browsing http://www.orablogs.com - (broken link) orablogs last night and saw Stefan Roesch's post entitled "Determining the IP address of the cluster interconnect in 9i". Although my main interest is of course Oracle security I also enjoy looking into hidden, undocumented and internal details so this post perked my interest as it talks about the use of the undocumented kernel debugger oradebug.

Stefan informs us that its not possible to determine the interconnect IP Address from a database view in 9i RAC. He instead shows us how to use the oradebug command to find the IP Address. This is done with the oradebug ipc command and the tool writes a trace file containing the info needed. Stefan goes on to show us a complete example with how to find and read the trace file. Finding a trace file created by oradebug is done with the oradebug tracefile_name command.

I have links to two great papers about the oradebug on my oracle internals page

I saw an interesting post late last week on Oracle-l entitled "JDBC Thin and SQL*Net encryption" that talked about the posters need to be able to encrypt all Oracle network traffic as instructed by his auditors. He said he had some commercial JDBC based applications using the thin driver and he wanted to know if it's possible to enable the advanced security for them.

Richard posted a reply that said its possible to use JDBC thin and Oracle ASO using the oracle.net package. Richard gave some example code showing how to set the properties for the oracle.net package for oracle.net.encryption_client, oracle.net.encryption_types_client, oracle.net.crypto_checksum_client and oracle.net.crypto_checksum_types_client.

I saw an interesting post on the Oracle-l list yesterday by Jared entitled "Alert 68 vulnerabilities explained". This post simply told us that NGS have released details of the bugs they found as part of the alert 68 patch fix. They said they would release details of each bug found in their original advisory. The date given then was 31st November 2004, which of course doesn't exist and also passed some time back. The advisory also said that they would give Oracle customers 3 months before releasing details of the bugs. Jared's post gave a URL that doesn't work, someone has replied to this in the same thread and said as much. Jared has replied to say he found out about the bugs / advisories by email and he concurred that the URL's do not work.

NGS, released 10 advisories to the bugtraq mailing list on 23 December 2004. These advisories give details of how to exploit the bugs found. Some give enough details for the reader to easily create exploits, some whilst giving details would be harder for a reader to create an exploit for. As the NGS links in the advisories do not work, I will list the links to the advisories on bugtraq here:



It is obviously now very clear that anyone who has not applied the patches for alert #68 should do so without delay. Exploits for the details released will be written and circulated.

Apply the patch for alert 68.

I have been using webalizer for a couple of years or more on my site to monitor the web server logs and always found this an excellent piece of software. I decided sometime back when I added all of the new menus and pages to the site to add a statistics page. I have finally got around to sorting out a cut down version of the output of webalizer tonight and added it to my site. The statistics include the main index page for each month and the totals for each day for hits, visits, page views, kilobytes, files etc and also hits per hour. Please have a look if you are interested.

I just saw an interesting post in the Amis blog by Marco Gralike titled Just before X-mas about his recent attendance of a Chris Date seminar. Marco talks a lot about the supposed trust of the data that you (a user or a developer or whatever) reads from the database. He also goes on to emphasise the issues of security and security issues in the database and also the complexity of modern implementations that actually make it harder to make a database secure due to the huge code paths between the user and the data.

It does seem that security is a core theme in relational databases and for me in Oracle in particular. Security is one of the core tasks of a modern Oracle DBA and needs to be considered at all levels. It is very interesting to read this post and realise how deep security goes in the database world.

I was just surfing over at OTN and found that there is an index page with all of the JDeveloper presentations from the recent Oracle Open World. The page is here. Although none are specifically Oracle security documents, quite a few mention security in places and all are worth a look if you didn't make it to OOW.

I just found Bruce’s recent weblog entry entitled "Desktop Google Finds Holes" which I found interesting because of my recent thoughts on Information disclosure and leakage in the blog entries An interesting case of information disclosure,An interesting example of information leakage posted to my blog entry and also my own thoughts on google hacking - Information leakage and goole hacking.

Bruce’s new article is very interesting and is based on the new google desktop search (GDS) being able to find security issues in your own desktop computer or at least the computer you install it on. The beta GDA allows you to index your own computer with googles search technology. Bruce points out though that this indexing and searching can cause you to index files you might not want to be indexed such as bank details or private emails or even cached versions of encrypted documents (they are decrypted in the cache). Bruce points out that these are not issues with GDS but with the software that leaves the data lying about.

This is a very good article with a simple message, GDS is not the issue, it simply exposes security issues in other software that are already there. This is worth reading for the Oracle security interested person, for a start its a very good insight into how security impacts other simple software ideas such as searching and it also highlights the issues of critical data that is left lying around in caches, temp files and whatever... there are Oracle analogies, export files, redo logs, archive logs, ad-hoc report output, decrypted data read and spooled to list files.....

Thanks to an email from Gary Myers last night we have solved why the values of 3 and 7 are missing from the values listed in this table. This is rather obvious and as Gary suggested I am now kicking myself for not totally realising. I saw the connection of the first few records having numbers 0, 1, 2, 4, 8 but didn't realise the rest are just sums of these values. Let me illustrate. Firs the table contents again:

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Dec 22 14:50:20 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Personal Oracle9i Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL>
SQL> select * from sys.user_astatus_map;

STATUS# STATUS
---------- --------------------------------
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED

9 rows selected.

SQL>

OK, the status's work as follows. There are 5 basic statuses - These are

0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED

These are the core statuses and have what look like binary values, although the column used is a number:

SQL> desc sys.user_astatus_map
Name Null? Type
----------------------------------------- -------- --------------
STATUS# NOT NULL NUMBER
STATUS NOT NULL VARCHAR2(32)

SQL>

The additional statuses are simply sums of the core status numeric identifiers. e.g:

5 EXPIRED & LOCKED(TIMED) => 1 + 4 = 5
6 EXPIRED(GRACE) & LOCKED(TIMED) => 2 + 4 = 6
9 EXPIRED & LOCKED => 1 + 8 = 9
10 EXPIRED(GRACE) & LOCKED => 2 + 8 = 10

Therefore we can now deduce why the values of 3 and 7 do not exist. The value for 3 could only be made up of:

1 (EXPIRED) + 2 (EXPIRED(GRACE)) = 3

This would not make sense as they are mutually exclusive. Next for the value of 7. This could only be made up of:

1 (EXPIRED) + 2 (EXPIRED(GRACE)) + 4 (LOCKED(TIMED)) = 7

Again the same problem with having EXPIRED and EXPIRED(GRACE) which are mutually exclusive. The same arguments could be applied to why values of 11 - 15 which would include again the same pair or LOCKED and LOCKED(TIMED) together.

Thanks again to Gary for pointing this out to me, although I should have worked it out myself.

I got an email the other day from someone who asked me if I knew why account status's with key values of 3 and 7 were missing as indicated in the table SYS.USER_ASTATUS_MAP. The table is used to link the numeric status listed in SYS.USER$ to the text description for those status's. I will give the contents of the SYS.USER_ASTATUS_MAP table so you can see what I mean:

SQL> connect sys/change_on_install@sans as sysdba
Connected.
SQL> desc sys.user_astatus_map
Name Null? Type
----------------------------------------- -------- -----------------------
STATUS# NOT NULL NUMBER
STATUS NOT NULL VARCHAR2(32)

SQL> select *
2 from sys.user_astatus_map;

STATUS# STATUS
---------- --------------------------------
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED

9 rows selected.

SQL>

As you can see the status's of 3 and 7 are missing. The table is created and populated by the script $ORACLE_HOME/rdbms/admin/sql.bsq - The only other places the table is mentioned is in catalog.sql and migrate.bsq. From the order of insert statements in sql.bsq if new entries were added in order then a sort of binary sequence was started 0,1,2,4,8 (see the descriptions above), then 5,6,9 and 10 were added. But why were 3 and 7 not used, are there two other status's planned or used in the past? Do they have some internal meaning?

I was interested by the question but cannot answer it other than its probably not meaningful that status's 3 and 7 are missing. Doe anyone know? - let me know if you do.

I was just surfing over at Mark Rittman's weblog and found an interesting post entitled "Looking Forward To Oracle 10g Release 2". 10g Release 2 was of course announced at Oracle Open World and I have made a few blog entries here about it around that time, 10g Release 2 on the way?, Oracle Database 10g Release 2 keynote at Oracle Open World, Two more accounts of the Chuck Rozwat 10g R2 keynote at OOW , Justin talks more about the 10g R2 keynote at OOW and Oracle have made a press release about the database 10g release 2 announcement .

Marks account of the release is quite interesting as he sees it as I do as not a major upgrade on 10g R1 but in fact a fixing and tweaking exercise. Mark even suggests that some of the new changes may come as patch upgrades to 10g R1.

Mark goes on to talk about the key new features and changes. He talks about the new transparent encryption and key management within the database. Let's hope as Mark suggests that in a later release the key management facilities are expanded. Mark also points out that data in tables that use this feature will be written to backup in an encrypted state.

Mark also talks about the direct SGA access for statistics gathering and then goes on to talk about the coming .NET CLR support in 10g R2 and speculates on the levels of support that are going to be available. Mark's impression is that it will work initially through the EXTPROC features and possibly later be fully integrated in the same way as Java and PL/SQL are supported.

XQuery is also discussed as is the new PL/SQL API for data mining and a few other bits. The interest for me is the encryption, SQA direct access and the .NET CLR being integrated.

I have just updated my tools page to move the helix recovery CD from the commercial section to the free section where it should be and also to add two new commercial Oracle security auditing products. These are DB Audit 2.0 from SoftTree and Ambeo. I found these products from a thread on the Oracle-L mailing list entitled "Recommend good Oracle Auditing software?" where the poster was looking for an auditing solution that was central repository based and also had a GUI interface and canned reports. barbara recommended Ambeo here and John recommended DB Audit 2.0 here.

I was made aware by someone by email that my new sitemap generation was leaving a trailing double quote on the weblog entries text descriptions. I talked about my new sitemap in a recent blog entry here. I have even found this page very useful myself as its now easier to search for pages I am looking for when linking to them in blog entries and also in mailing list posts and answers to questions people email me about.

I was leaving the trailing quote fix till I had time to update the processing as it was not really affecting the readability of the page but someone has just emailed to let me know that I had missed the RSS feed for this weblog from the sitemap so I have just added it now.

I found an interesting post on the Oracle-l list last week about disabling writes to the Windows event log. Because I like undocumented or hidden or hard to find data I was immediately attracted to this post. The thread is called "disabling Oracle writes into NT event log" and the original poster said every time an archivelog is created an event is written to the event log in the same manner as an entry is written to the alert log.

The poster wanted to disable this write to the event log and found an undocumented parameter _disable_ntlog_events. You can test the default value of this parameter with my script check_parameter.sql. An example for my 9iR2 personal edition database is given next:

check_parameter: Release 1.0.0.0.0 - Production on Mon Dec 20 15:27:29 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

PARAMETER TO CHECK [utl_file_dir]: _disable_ntlog_events
CORRECT VALUE [null]: FALSE
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Investigating parameter => _disable_ntlog_events
====================================================================
Name : _disable_ntlog_events
Value : FALSE
Type : BOOLEAN
Is Default : DEFAULT VALUE
Is Session modifiable : FALSE
Is System modifiable : FALSE
Is Modified : FALSE
Is Adjusted : FALSE
Description : Disable logging to NT event log
Update Comment :
-------------------------------------------------------------------------
value is correct

PL/SQL procedure successfully completed.

For updates please visit /tools.htm

SQL>

The poster changed the value of this parameter with an ALTER SYSTEM command and checked it had been changed. He then noted that the events were still being written. His version was 10.1.0.2.0 on Windows XP. Niall Litchfield answered with information that he thought this bug was fixed in 10.1.0.3.

As I said at the start of this entry I found this interesting because it talks about internals, in this case an undocumented or underscore parameter.

I just found this post in Haris Ali's blog entitled http://www.cheblogs.com/roller/page/nimzobenoni/20041218#autotrace_in_sqlplus - (broken link) Autotrace in SQLPLUS this evening.

This is an excellent short description / step-by-step of how to setup autotrace in SQL*Plus. The steps to use autotrace are quite simple. From a security aspect we need to ensure that the user has access to a PLAN_TABLE and also to the PLUSTRACE role. As Haris points out this role is usually granted to PUBLIC.

This is where my interest is. I have written many times before about the risks of allowing users to race their own sessions and create trace files or get execution plans and wait information. Autotrace is useful for developers, consultants etc who may be investigating bugs or issues. Of course access to SQL*Plus is also required (normally not a problem). There is no reason why general users should be able to create trace files, even to create autotrace sessions. Therefore ensure that if you enable autotrace or if its enabled already, then do not grant the PLUSTRACE role to PUBLIC. Grant it only to those users that need it. Also ensure that only the same users that need to have access to the PLAN_TABLE.

Use the script who_can_access.sql to find out which users can access the PLAN_TABLE. Check first who owns the PLAN_TABLE and how many there are in the database:

SQL> col owner for a10
SQL> col object_type for a30
SQL> col object_name for a30
SQL> select owner,object_type,object_name
2 from dba_objects
3 where object_name='PLAN_TABLE';

OWNER OBJECT_TYPE OBJECT_NAME
---------- ------------------------------ ------------------------
SH TABLE PLAN_TABLE

SQL>

Next use who_can_access.sql

who_can_access: Release 1.0.1.0.0 - Production on Sun Dec 19 21:51:30 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF OBJECT TO CHECK [USER_OBJECTS]: PLAN_TABLE
OWNER OF THE OBJECT TO CHECK [USER]: SH
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Checking object => SH.PLAN_TABLE
====================================================================



PL/SQL procedure successfully completed.


For updates please visit /tools.htm

SQL>

In my test database there is just one PLAN_TABLE and no users have been granted access to it. Let's now check which users have been granted the PLUSTRACE role. This time we can use the script who_has_role.sql as follows:

who_has_priv: Release 1.0.1.0.0 - Production on Sun Dec 19 21:53:40 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

ROLE TO CHECK [DBA]: PLUSTRACE
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Investigating Role => PLUSTRACE (PWD = ) which is granted to =>
====================================================================

PL/SQL procedure successfully completed.

For updates please visit /tools.htm

SQL>

Again on my test database no one has been granted the role. If you find users that have been granted access to the table or role review who they are and revoke the access that is not necessary.

I have also written a paper that details the many ways to set trace for your own session, others sessions and also to set trace levels to record bind and wait data. The paper is called "How to set trace for others sessions, for your own session and at instance level"

I just found Edward Stanglers post that is a comment on my post yesterday about a good paper introducing Java used in stored procedures. This is the paper that Mark Rittman found first.

As Ed points out I have currently disabled comments in my blog, I may re-enable them in the future but not at present. I also saw that Andrej Koelewijn has had some troubles with comments in his blog.

So Ed made a post to his own blog to add some information to my yesterday post. Ed made a good point that was alluded to in the original article but not explained is that its possible to create the Java completely in SQL*Plus using the "CREATE REPLACE AND COMPILE" syntax. Ed gives some examples links from Tom's book. he also makes a good point about using Java instead of C external procedures. He also corrects my memory about where I heard that you should use SQL first, then PL/SQL, then Java then C.

Thanks for the update Edward!

I found a nice post by Brian Duff about debugging in JDeveloper. The post is called http://www.orablogs.com/duffblog/archives/000745.html - (broken link) Nice Debugger Feature and talks about the woes of adding print statements to your code to debug it and the fact that you would have to check out the example code he gives if it was part of a team development. He then presents some cleaner code and shows us how to use JDeveloper to debug the same piece of code and also shows us how to use the "Object Preferences" menu item to create more readable variable values when using breakpoints. Brian also shows us how JDeveloper can be used to evaluate expressions in the live debugging environment.

Why am I talking about this short article? - First because I like debuggers and in fact any system tools such as compilers, decompilers..etc that could be used to delve deeper into the inner workings of your code. These are the tools of hackers also so if you want to be on top of securing your software (Oracle in this case) you need to know the tools that hackers may use and also how they work. This will give you an edge.

The other point I want to talk about in this article is Brian’s mention at the start of it of the use of print statements in code to find issues in debugging. Print statements can also be used not just for debugging but also for instrumentation of your code. Oracle themselves do this extensively with the wait interface. All the wait interface really is, is a set of print statements added to various function calls in the Oracle kernel. This method is useful as you can see with the wait interface example as the workings of the kernel can be debugged at run time in a production environment without the use of a debugger. A trace file can be produced that contains a huge amount of data about how the software did its work and conclusions can be made as to what went wrong or how it could have done its stuff better next time.

Contrary to popular belief Oracle are not the only ones to do this (adding instrumentation) to their products. I know of companies who have instrumented the whole of their application product. This was very useful for developers, consultants and customers to be able to set trace at run time to analyse why a particular function behaved as it did or to analyse any particular bug. The instrumentation, like Oracles wait interface could be turned on or off at will.

I can see Brian’s point about developers using print statements instead of a debugger and how this is not an efficient way to debug code but print statements do also have their role in production systems for instrumentation - Oracle have made this case and independently a previous employer of mine were also successful with this technique.

Other companies may use the same techniques to provide application audit facilities, this could be done in C for Pro*C or OCI programs or in PL/SQL for PL/SQL code or forms, or indeed at any level in the application stack. This instrumentation could write records to the file system or to database tables.

Now for a possible downside to using print statements for instrumentation and also we have a security angle. We know of the risks of allowing users to enable Oracle SQL trace or indeed any of the other events that generate trace files in Oracle, particularly dump files of memory in the Oracle kernel. If users can do this and read the produced trace files then it’s highly possible that structural information relating to the Oracle configuration including password hashes or even passwords can be learned as well as application specific details such as program code, SQL or application database structure.

What about instrumentation that provides useful logging and trace information about an application. It is highly feasible that developers, consultants, users and many others can set this trace to enabled and generate trace files independently of the software supplier. It’s also very feasible that they can also gain access to the produced trace or log files and analyse them. Quite often these files will include function flow, SQL statements, data used in the SQL (i.e. bind variables) and even internal data values used. This information would be extremely useful to a hacker.

If your company has instrumented their applications (and I think the number of companies that do do this is not trivial) then you need to ensure that this trace mechanism is protected and that users who are not authorised to turn it on cannot actually turn it on. Also you need to ensure that the trace, log files or even database tables that store the trace data cannot be accessed. Being able to access this type of data, whilst useful for support and maintenance, could break some of the newer legal requirements such as Sarbanes Oxley, GLB or HIPAA.

What if you are using Fine grained access (Row level security - see my two part paper) to control access to critical data and you also use instrumentation in the application source code. It is highly likely that a user could enter a screen and enter critical data, the data would not be visible to anyone else and could not be viewed by others as RLS protects it. But what if the instrumentation (trace) grabbed the values in the screen code or in a PL/SQL package before the database inserts the data then writes that data to a trace file. The data you have protected with RLS could be sat viewable by anyone on a file system in a trace file.

Debuggers are a hacker’s paradise and instrumentation whilst useful, in fact very useful in some cases also needs to be protected and controlled. Access to enable trace needs to be protected. Any above all steps to ensure critical data values are not written to trace files also needs to be taken otherwise features such as Row Level security could be easily bypassed.

I saw an interesting entry in Mark Rittmans weblog entitled "Introduction To Java Stored Procedures" yesterday and decided to take a look as I am always interested in Java and Oracle and PL/SQL - especially from a security perspective. There are not many articles on Java used inside the database and security. This is a subject that needs to be expanded.

Even on a general level I have not seen many really good introductions on how to use Java in stored procedures. The article that Mark has found is the exception; this is a very good introduction article to this subject.

The article is called "Oracle and Java Stored Procedures" and is written by By Michael Klaene. There are two pages, the first is here, and the second here.

The fact that Oracle have added a Java virtual machine into the Oracle kernel has meant that it is now easy t use Java programs within the database and also to use Java to extend the functionality of PL/SQL programs beyond where was possible with just PL/SQL. Of course the same can be done with C and external procedures.

I read somewhere once, it could have been Tom or Steve, I am not sure that if you can do the task in SQL then you should, if not then use PL/SQL and if not then use Java or even C. This is good advice especially from a security perspective as the more extensive the language used, e.g. Java or C, then he more potential for damage there is.

As I said this is a great paper, it covers the four main steps in writing Java to run inside the database, 1) writing the Java classes, 2) compiling and loading, 3) publishing by providing a PL/SQL interface and 4) actually calling the interface PL/SQL procedure to execute the Java.

The article even explains, although briefly the issues of classpaths and the Oracle resolver. Also discussed is the fact that it’s possible to load the class (compiled code) and also the source into the database or just the class or compiled code. The security aspects of this are not discussed.

There are some very clear examples given in this paper and the paper finishes by talking about file IO with Java. This is an area that has to be looked at carefully as it can be easy to expose access to the underlying file system that could allow a hacker to gain control of the machine.

Again this is a very good paper. It would be interesting to see a paper dedicated to secure Java programming in database stored procedures. Maybe I will write it when I have time :)

I have just updated the tools page to add a link to the Helix CD I talked about recently here. This CD is a live Linux distribution that is aimed at incident response and forensics. It doesn't alert the host PC and should be in every Oracle security enthusiast’s toolkit.

I just posted my previous blog entry on read only users and I checked the mailing list its based on (Oracle-l) and found a new post by Gints. This is an excellent post so I decided to post an update here about it as his comments and ideas are important.

Gints reminds us that programmed interfaces such as those described by the poster on oracle-l may not just query the data but also create audit records into some application log or system tables. This could be done to record what data application users are viewing or changing during normal operations or could be used to log connection attempts or when procedures are called or even for performance metrics. He goes on to suggest that the select procedures may possibly do inserts or updates of audit records even just for read access.

He also mentions in a similar vein to me that read only requirements should not be generalised. Access to data should be segregated for insert, update, delete and select and even segregated on data subsets. He even suggests that access to historical and current data may need to be segregated.

Gints finishes on a similar note to me in saying that it a pity this is a late requirement as it would have been better to design this sort of access properly in at the start of a project.

These are some excellent comments on the issues of read only users. I had not remembered about audit records generated at the application level. Good call. If audit is enabled at the table level either through normal audit trail or via table or view level triggers then no matter the access method - either direct or via programmed interface the audit will be created. The user recorded may vary though based on whether the programmed interface is definer or invoker.

Fine grained audit also will not be fooled by a read only user created above the application. Any policies created will still fire no matter the type of access used.

Application based audit though needs to be considered properly when read only users are created that do not access the data through the approved API.

I saw an interesting thread on the ORACLE-L mailing list yesterday about how to create a read only user in Oracle. The poster said in the thread "Read Only User" the application and database had not been designed to accommodate this. She had created a role and granted SELECT access to the role on relevant tables but she was having issues with how to deal with access procedures written in PL/SQL that are presumably used by the application. She identified that she could create wrapper procedures to only expose the read functions or procedures of these packages.

Some of the posters suggested some good ideas, these ranged from create wrapper PL/SQL code, using triggers on each table to disallow any DML access and allow only query access. This, as the original poster suggested would be a big maintenance issue but the code could be generated where wrapper functions could not. Using triggers would allow execute privileges to be simply given on the packages as the triggers would prevent DML from succeeding. The original poster said that there could be performance issues with having a lot of triggers on the OLTP application / database.

Someone else made a good suggestion about using Row Level Security as one policy function can be created to prevent any access other than read access. Again there will be a slight performance hit but not as bad as triggers.

I suggested reading four previous entries in this blog. I said:

"I don't know if these are of use to you or not but I wrote a few entries
in my Oracle security weblog on this subject and similar. These are the
links:

"Creating read only users" -
/weblog/archives/00000009.htm

"creating read only tables" -
/weblog/archives/00000033.htm

"Allowing a user read-only access to stored procedure source code" -
/weblog/archives/00000052.htm

"Another good paper by Howard Rogers on read-only tables" -
/weblog/archives/00000063.htm"

The original poster hit on the real issue when she said:

"Thanks for your links - some useful material here. I like your comment: "The whole process requires
planning and forethought not quick fixes". This is exactly not what I've got here! Management tell
me "I just need to tick a box to make my Excel spreadsheet read-only" and wonder why I can't just do
the same for the database."

This is an interesting and I said in reply:

"Glad you found them useful. I think you have found the universal problem
in security, the fact that management do not appreciate how easy or hard
some things are to achieve in an Oracle environment..:-)

I am not sure if I have said this before in writing, I think I have, I
think Oracle should look seriously at letting customers easily specify a
read only user without resorting to wrapper code or any other non
trivial hacks. I can appreciate that this may not be easy to do but it
sure would be a useful feature. I don't mean just SELECT ANY TABLE but
being able to access the data through the same developed interfaces that
already have been coded."

My view with this issue of creating read only users which the original poster now says is for developers to view data in the production database. If possible it is better to not allow developers access to production systems anyway. I understand that this is often needed to resolve bugs but if so it needs to be done so that it can be disabled after use, either by revoking the privileges granted or by locking the accounts. The best approach is to plan ahead, design these users and roles in advance. If possible access the base data or views rather than go to the trouble of creating wrapper procedures around application code - this simplifies the issue. Most of all remember that access to ALL data is very very rarely needed. So don't grant access to all data. Review each request in advance or create users in advance that can access each logical business area of an application.

Its like buses, you wait hours for one to come and then two come around the corner at the same time. I have just posted a blog entry about the issues with revoking privileges from SYS and DBA and dropping these roles and then went over to Howard Roger’s site to see if he had any new weblog entries and found that he had written about the same subject on December 8th. Howard’s entry is more related to the advice Daniel gave and why its wrong. In my entry I tried to concentrate on the end consensus. Still, Howard’s blog entry Hoist by his own DBA petard... is relevant in this context and is worth a read!

I saw a thread on comp.databases.oracle.server last week entitled http://groups-beta.google.com/group/comp.databases.oracle.server/browse_thread/thread/e9575e9d3bc7d6a0/44605d01b5afd114?q=%22OK+to+revoke+privileges+from+SYS+or+DBA%3F%22&_done=%2Fgroups%3Fq%3D%22OK+to+revoke+privileges+from+SYS+or+DBA%3F%22%26hl%3Den%26lr%3D%26sa%3DN%26tab%3Dwg%26&_doneTitle=Back+to+Search&&d#44605d01b5afd114 - (broken link) OK to revoke privileges from SYS or DBA?.

This thread started by talking about whether it’s OK to revoke privileges from the SYS user and the DBA role. The thread got a little heated in places but the consensus at the end is to not use the SYS user except for things it has to be used for such as creating databases. The consensus is also to not drop the DBA role and to not alter it in anyway, the reasoning being that Oracle use the role in some of the installation script for default users and the features they use could break.

The consensus also seemed to be to not alter the CONNECT or RESOURCE roles and to not drop them for the same reasons as above.

Some suggest altering these roles and some suggest that dropping them is OK and Daniel sited the fact that some ultra secure sites in the USA have dropped DBA, CONNECT and RESOURCE and altered the installation scripts and work fine. This is hard to confirm. I can see the potential issue of Oracle using these roles themselves can make it hard to remove them without losing warranty so don't drop them.

Removing privileges from these roles could also be a support issue for the same reasons. The sound advice is to not grant DBA, CONNECT or RESOURCE to any users in your databases (apart from the default users created by Oracle - even then the default users should be kept to the absolute minimum anyway).

Create your own roles for user to connect to your applications with; do not grant privileges that can be used to create objects. Also for users that can create objects create a separate role and if possible revoke those CREATE PRIVILEGES after use so that the users only have them for the short time they are needed.

In the case of the DBA role, other than the SYS and SYSTEM users do not grant the DBA role to any other admin staff. Segregate the admin duties and create admin roles that have the privileges needed. Do not simply grant all privileges to another role. Use the least privilege principle.

I was reading the pen-test mailing list from security focus last night and found a post in a thread titled "Class on Security Tools". The post that took my eye is a post by Todd. The reason this took my eye is in the second paragraph where he talks about the new trend of using google for hacking. He goes on to say that google is used by hackers and penetration testers to find out huge amounts of data about a site that is to be hacked or about a client that is being audited. Todd also states that there is a tool called Wikto that has google hacking features. He also says that there is a new book about hacking with google.

I searched Amazon for this book and found a book called Google Hacking for Penetration Testers written by Johnny Long. This sounds a very interesting book. The author explores how to use google to find hidden information about a site, break into sites and find ways to access information that is supposed to be secure. The book is very useful for those people who are interested in making sure that their company or site does not leak this information into the Internet. The book will be on my shopping list of security and general books to buy once we get Christmas over with.

The subject of information leakage via websites, newsgroups, mailing lists, even on the internal intranet is something all users of Oracle databases and the data stored in them should b concerned about. I talked about this very subject in a recent blog entry here.

Hacking google or indeed any of the search engines to find hidden data or data that would help a hacker break into your company and steal your data is a real threat and one that should be taken into account when you are devising or revising your company security policies.

I saw yesterday Colins weblog post titled http://www.orablogs.com/cmaxwell/archives/000752.html - (broken link) Securing Binary Data in today's world which looked quite interesting so I went and read the post. Colin talks about the issues of how it is possible to sign and encrypt documents (binary attachments) in web services using JDeveloper. Colin gives links to some good papers and goes on to suggest that until apart from using the simple base64Binary XSD type in webservices there is not much else that can be done. Colin also talks about MTOM and then gives some example code. An interesting discussion, again it is http://www.orablogs.com/cmaxwell/archives/000752.html - (broken link) here.

About one and a half years (July 2003 I think) ago I added a page to my website where people can subscribe to my newsletter and another page to unsubscribe if they wish. I wrote one newsletter that contained a brief introduction to me and my company and what I am trying to achieve, a short bio and even a not very good photo. I will update my new bio page soon, I promise :D. I had planned to publish the newsletter every two or three months and have in it two or three technical articles about Oracle security of course. In the first newsletter I talked about protecting the database from illicit use of SQL*Plus and gave some examples of the use of product user profile and how it can be bypassed, renaming SQL*Plus and a few other ideas in the same vein.

If you check my previous newsletter page you will see that there is still just one newsletter. :(. This is mainly due to lack of time to write something as in-depth as the first newsletter.

I plan to re-launch the newsletter very soon, in the next couple of weeks, again time permitting but with a slightly different format - the main reasoning being that I want to get a newsletter out there as I have a lot of subscribers, I think without counting in the region of 400 and I feel I have a duty to provide something that has been promised. The problem was that I set a high standard in terms of content and length with the first one that is hard in terms of time to follow up on.

So my feeling is that the newsletter will be much shorter than the first one, but much more frequent, at least once a month and maybe more frequent. I plan to have three sections. The first an intro - what’s been happening section - any good relevant new items and pages on the site that have changed etc, maybe prominent news items. The second being a short to medium length article on something relevant to Oracle security. The final section will be a summary of the best entries in the blog since the last newsletter for those that do not read the blog.

The one area I wanted to combine with the newsletter that wasn't planned for the first one was to inform people of when the site has changed, when new content has been added that they might like to look at. Quite a few people have asked me if it’s possible to be made aware of when pages have changed. When I added a lot of new menu items and pages recently I planned a page for people to register to be told of updates. My thoughts since was to create a simple newsletter for this purpose and send it out, then I thought why not simply combine it as a short section in the main newsletter and get it out there more regularly. So that is what I plan to do. Of course you can also see the changed or added pages in the sitemap as well, indicated by the new graphics.

I have had a look for some mailing list software a couple of weekends ago and have read the set-up, features, readmes etc of quite a few and I have settled on one solution. I hope to get this installed and tested soon. Then I will send out the first of the re-vitalised Oracle Security mailing lists. If you have subscribed already then I must apologise for the delay in sending out the list, if not then please do if you wish.

I saw last week some time an entry in the Amis blog written by Alex Nuijten entitled "Oracle Open Source : SQuirrel" - As I am always interested in tools, debuggers, compilers, internals and the like I took a look at this entry.

In the world of Oracle security you cannot simply focus on parameter settings and privileges granted and default users passwords you need to understand the technology, how it works, how it’s installed and also how to delve deeper into it and finally how to efficiently access the database structures and data. To perform Oracle security audits I use a set of custom written SQL and PL/SQL and shell scripts that gather a huge amount of data about the database, the operating system, the software installation and networking applied to Oracle. These scripts give me a good overview of the security state of a database and its accompanied software installation.

Even though I use command line tools almost exclusively I also need to do manual checks and analysis based on what my scripts gather in the first sweep. I am therefore always interested in any tools to analyse the database particularly free ones like this.

This tool is written in Java and allows browsing of database structure, users, privileges etc. Its not as sophisticated as TOAD but then again its free and has ongoing development so could undoubtedly catch up. The SQuirrel too is available here.

I was surfing this evening and found a nice entry in Frank Nimphius's weblog from about a week ago. The entry is entitled http://www.orablogs.com/fnimphius/archives/000730.html - (broken link) J2EE Security: Struts “Shale” proposal does improve web application security.

This is an excellent short article that looks at J2EE security in web applications. Frank notes that current frameworks do not integrate well with the tools needed to build secure applications. He goes on to note that the current implementations of having security in the client, controller and business model is not ideal and multiple non synchronised configuration files or repositories are needed. Frank suggests that JAAS is suitable but doesn't implement end top end security. He talks about JSF and its problems of no application security integration and he goes on to introduce Asegi's security architecture.

Frank discusses Asegi framework and the issues of JAAS and discusses the Struts 2.x 'Shale' proposal. Frank finishes with his views of the possible future of this area. Interesting post, http://www.orablogs.com/fnimphius/archives/000730.html - (broken link) again its here.

I just found the first part of a series of papers written by Arup Nanda - It is entitled http://www.dbazine.com/nanda9.shtml - (broken limk) Oracle Security and Auditing, Part 1: Securing Listener through a Password and as its name suggests its about protecting the listener with a password.

Whilst this is an admirable thing to do the short article doesn't go far enough. The paper gives a thorough description of both methods of using passwords for the listener, the change_password command where the password is set dynamically and also the method by which the password is added to the configuration file listener.ora. The latter method of adding a password in clear text should not be used. This has a number of problems. The first is that the password is in clear text and because of this it can be read by anyone with permissions on the file. The paper does not discuss this fact or the need for file level security. Also this method is weaker in some versions of Oracle as it can be abused and connecting to the listener is still possible without the password. The key is to not use this method but if it is used then the configuration file must be protected at the operating system level. This is also the case with the change_password method as the hash should not be divulged either.

If the hash value is known it can be installed off line on another listener and be brute forced. This brings me to another issue with the listener which was not mentioned. The fact that the listener has no password management features, ageing, locking of the account etc.

I covered in a similar issue in my blog yesterday about listener passwords and the ADMIN_RESTRICTIONS_{listener_name} parameter. This parameter can also be used effectively to prevent changes to the listener configuration through the listener control utility. This is also a worthwhile addition to the things mentioned in Arups paper.

Arup also makes a statement in his article:

"This will make the work of the hacker impossible unless the password is known."

This is not true, the hacker can simply brute force the password if its not known because there are no protections built into the listener to prevent multiple failed login attempts. Also if it’s possible to get the listener password hash then it’s possible to brute force it off line.

This paper is the start of what looks likely to be a good series from Arup who is a good author. This is a good paper that covers its subject well but as I said I think it could have gone further and covered file permissions, password attacks and also restricting dynamic configuration of the listener.

I have added a new sitemap page to my site. A number of people have asked me about the availability of a sitemap over the last year to make it easier to find pages. I also had emails from some users of Konqueror who told me the menus I used do not behave correctly in this browser. I added links in my copyright notice at the bottom of the page sometime back but when I added a lot of new menu items and pages recently I decided to not update the copyright notice further to add links - Its getting too big.

So i decided to hunt around on Sunday night for a free Perl script that could generate a site map for me. This seemed to be more difficult than I expected. I found three candidates, I am sure there are many more out there but these are what I found after an hour or so of searching. The first was a very simple script that unfortunately didn't drill down correctly through the sites directories so I abandoned that one almost immediately. The second was more substantial and was called sitemapper version 1.019. This looked like a good option but proved to be too big a time spinner to get it running. It needs a large amount of modules to be pre-installed and as my ISP didn't have them installed that meant messing with the script to run local versions. I got it running but was not happy with the maintainability of it so i abandoned it.

The final script i downloaded was sitemap.pl - The site seems to have a problem this evening though. This script was much simpler and up and running quickly. I only needed to add configuration information and change the template to match my site. This then generated a map but all the links had the same text. This is because it uses the text held in the title tags. hmmm... so i set about and changed the perl to use the H1 tags instead.

The sitemap is now up and running. Hopefully it will be useful to visitors to find what they need. Its available on the "About" menu or directly here.

I just came across Edward Stanglers latest blog entry that discusses Jonathans article in DBAZine.com. Ed's post is entitled "Lewis On Hidden 10g".

I decided to hop over to DBA Zine to read Jonathans paper as anything that smells like hidden info, undocumented info or similar interests me, especially if Jonathan has written it as he is an excellent author.

Jonathans paper is called "Hidden Benefits of 10g" and discusses some of the lesser known facts about Oracle 10g Release 1. Jonathan starts off with an interesting view point that most people tend to decide to upgrade based on the high profile press details of the features that may be useful. In this paper Jonathan has decided to discuss some of the less well known features but features that are also very useful and should help customers of Oracle decide whether to upgrade or not.

Jonathan talks about optimizer improvements, hash joins and outer join optimizations. He then looks at improvements in index range scans to remove redundant reads of table blocks. Jonathan then talks about the fact that Oracle has now in 10g finally added a proper optimizing compiler for PL/SQL. he then looks at a simple example of single row processing and shows how in 10g R1 it is promoted to array processes and why it could be an issue. He also talks about the fact the optimizer tries much harder to standardise the SQL by massaging the SQL text by upper casing it and removing white space.

One little snippet of interest to me was the note about the optimization level of the compiler that can be changed with an ALTER SESSION command as follows:

alter session set plsql_optimize_level = 1;

This paper reminded me of a paper I read some time back when i was interested in the inner workings of PL/SQL. The paper called http://www.oracle.com/technology/tech/pl_sql/htdocs/new_in_10gr1.htm#faster - (broken link) PL/SQL just got faster is superb and well worth reading for those interested in real technical details. This paper describes the inner workings of PL/SQL, the Virtual machine, P-Code or M-Code and the Diana. It describes in some details the changes to the compiler and also to the native compilation facilities in Oracle. This paper gives more background to Jonathans as it talks about the new optimizing compiler and how it works. This paper is well worth reading for those interested in detail and internals. The paper can be found at the link above as a download via a zip file. You can access the zip directly from this link.

I saw a useful post to the comp.databases.oracle.server the other day entitled http://groups-beta.google.com/group/comp.databases.oracle.server/browse_frm/thread/647d904e952b7783/ec4cc9b652fb8a3d?q=%22restrict+remote+listener+administration%22&_done=%2Fgroups%3Fq%3D%22restrict+remote+listener+administration%22%26&_doneTitle=Back+to+Search&&d#ec4cc9b652fb8a3d - (broken link) restrict remote listener administration where the poster said he had found a way to prevent remote administration of the listener and also remote shutdown of the listener. His suggestion was that instead of using a password for the listener (which he had problems with as it meant changes to the shutdown scripts) he would set the parameter:

ADMIN_RESTRICTIONS_{listener_name}=ON

He wrongly thought that this alone would prevent remote shutdown as a poster informed him :

"admin_restrictions parameter does not prevent from remote shutdown.
It just prevents from modifying the listener.ora file with listener set command."

This parameter is still very useful. I added the following text to the thread;

"You should still set the ADMIN_RESTRICTIONS_{LISTENER_NAME} parameter as
it prevents listener settings being changed via the listener control
utility. You should also still set a listener password of course. If a
listener parameters can be set remotely (or even from inside an
organisation) then it can be possible to use listener commands to hack
the server it runs on. Remember that there are no password management
features for the listener password so the password can be brute forced
so setting ADMIN_RESTRICTIONS provides a valid extra defence.

Set both this parameter and the password."

This is a very useful parameter for security as it prevents changes to the listener configuration with the listener control utility. This parameter should be set as should a password.

I posted a blog entry last week about the many possible techniques that could be used to audit the SQL that is sent from a black box application to the database server. This is where the source code is not available for the application. I posted the entry "Auditing the SQL a black box application submits to the database" where I said, amongst other things:

"I am (almost?) certain OBDC trace can be used as well. I need to investigate this option - assuming ODBC is used of course"

Niall emailed me at the end of last week to let me know that ODBC trace is not useful in grabbing the SQL sent from an application that uses ODBC. Niall told me the following:



Thanks to Niall for the clarification on the ODBC issue.

For reference I also posted a second post on the subject of grabbing and auditing the SQL - This was called "Addendum to yesterdays auditing SQL from black box third party applications"

Finally Niall also made an additional post to http://groups-beta.google.com/group/comp.databases.oracle.server/browse_frm/thread/64968f2adf0e6a7c/71c92ae7a86a2ca7?q=%22Auditing+an+app%27s+SQL+-+How%3F%22&_done=%2Fgroups%3Fq%3D%22Auditing+an+app%27s+SQL+-+How%3F%22%26hl%3Den%26lr%3D%26sa%3DN%26tab%3Dwg%26&_doneTitle=Back+to+Search&&d#71c92ae7a86a2ca7 - (broken link) the original thread on c.d.o.s today about ODBC that said:

"It is incredibly slow, and incredibly verbose. Those who bemoan the 'overhead' of timed_statistics=true or sql_trace ought to try it someday :("

I have just found that someone was posting advertisements and links to some completely off topic subjects in my weblog comments. Each post was full of nonesense and then a link to some gambling website, according to the name. Each post was also made from different IP Addresses so its hard to block these posts in the software I am using. It looks like the poster was working through all of my entries one by one adding comments, completing one every fifteen minutes.

Because of this I have disabled comments for now on my weblog and removed the comments that have been posted by this person. I am sorry I have had to do this, but I do not want spam in my blog or links to dubious web sites.

I found a great news article today about security in the form of "defence in depth". The article has been written by Jon Oltsik and is titled http://news.com.com/Finally,+a+sensible+security+scheme/2010-1071-5486352.html - (broken link) Finally, a sensible security scheme. This article doesn't tell me personally anything new in the details and ideas that it presents but it does bring together a very good combination of ideas and thinking on security. Its well written and can be understand by techies and non-techies alike. This is a great article for anyone who doesn't understand the issues of security in multi-tier / multi-level applications and particularly those that involve databases, particularly Oracle databases.

Jon talks about Visa (the credit card peoples) "defence in depth" approach to security detailed in their Cardholder Information Security Program (CISP) and gives the classic bank security approach as an example. He goes on to suggest that many organisations do not take security of their data seriously as he says they tend to employ security at the outer perimeters and then do not protect the servers or at best if they do then they do not protect applications and in particular the database and data.

Jon thinks that companies will see the light at the end of the security tunnel. As Jon says currently application security is like locking the doors but leaving the windows open. Leaving the data wide open to attack is an example of this thought. Jon feels that the trend will change because of organisations like Visa who have recognised the fact that the data needs to be as secure as the outer perimeter - "defence in depth" - This idea will be recognised by the more observant management - soon - I hope.

This is a great article that makes a great point about securing the corporate data and the fact that major groups like Visa recognise defence in depth - including the data - again its http://news.com.com/Finally,+a+sensible+security+scheme/2010-1071-5486352.html - (broken link) here.

I just found what seems to be Justin’s final post to the OTN at Oracle Open World blog. He has entitled his post http://www.orablogs.com/otn_at_openworld/archives/000733.html - (broken link) Some Thoughts on What DBAs Learned and it covers some good points about grid, saving time by simplifying your job, abstraction and IT stacks.

But, the reason I wanted to mention Justin’s post is because of what he said in his third point. Justin said:

"3. Security, once an also-ran in the list of DBA priorities, is becoming increasingly important. Evaluating your security priorities across the IT stack (not just in the database) is a good start."

This is an important observation and quite obviously something I have believed in for some years. It is heartening to see that security is now becoming much more important not just for DBA's but for all of those people involved in implementing and running their businesses on Oracle software.

I was surfing over at Niall's weblog this morning and read his entry about Oracle Open World and the keynote speech about 10g R2 being released early next year. Niall talks interestingly about the fact that Oracle will be the first database to ship on Windows that supports writing stored procedures in CLR languages rather than Microsoft being first to ship.

The other interesting line for me was the link to the press release for the launch of the Oracle database 10g Release 2 - The press release gives some details of the new features. As I said yesterday the interesting things for me are the new seamless encryption / decryption and the direct memory access for statistics and also some of the high availability features. Again the press release is here.

I saw a post on a thread on Oracle-L last night where the poster said that the 9.2.0.6 patch set is now available as of 8th December 2004. The post is here.

I was browsing Franks blog this evening and found this excellent short post entitled http://www.orablogs.com/fnimphius/archives/000707.html - (broken link) ADF UIX:Simple J2EE Form based authentication example.

This post describes a simple example that Frank has created for a UIX project that uses struts as the controller for JDeveloper 10g. Frank hopes that this simple example will provide help for those using the technologies and techniques. As Frank says at the end "Simple but hopefully helpful".

I just noticed that the keynotes given so far this week at Oracle Open World are available online on OTN in three formats, Real Media, MP3 and PDF. The Interesting ones for me are Chuck Rozwat's, Larrys and Scott McNeallys so far.

If you want to download the text or media then go to OTN to get them. There are links to blogs, photos, demo's etc on the same page.

I just found Justin Kestelyn's blog entry http://www.orablogs.com/otn_at_openworld/archives/000729.html - (broken link) Oracle Database 10g R2 Details about yesterdays keynote given on the new 10g Release 2. This gives a bit more details than the posts made earlier but not any fine detail on the security aspects. Point 2 about "Information Life Cycle Management" sounds very interesting, with data managed at the row level with highly accessed data being held on high performance media and low accessed data being held on low cost media. This sounds an interesting concept, data being stored according to its importance. In a performance, cost perspective this could save money, i.e. pay only for fast media for a subset of data but from a security perspective it could make an easier target for a hacker, get the important data in one place on the fast media. Is the data already sorted and placed for a hacker?

Interesting reading, let's wait for more details.

I was thinking further about the post I made yesterday about how to audit SQL from black box third party applications. I said at the end of the post there are probably other ways to detect the SQL being sent from an application.

Well one area I did not think of yesterday and probably an area that is not of interest to the original poster on c.d.o.s is the possibility of commercial tools that can be used to grab all the SQL emitted from an application or from the other end arriving at the database (depends on where the tool is positioned). I should say that even though there are commercial tools they all do use techniques listed yesterday anyway, such as network packet sniffing.

The tools that come to mind first are Chakra from OR Solutions, Guardium SQL Guard and Entegra from Lumigent Technologies Inc. Links to all of these commercial tools are available on my tools page.

I just noticed via http://www.orablogs.com - (broken link) orablogs that there are two accounts of the 10g Release 2 keynote from Oracle Open World. The first is via the TV screen in the speakers lounge from Mark Rittman which summarises the main new features announced and the second by http://www.orablogs.com/otn_at_openworld/archives/000726.html - (broken link) Justin Kestelyn the editor in chief of OTN also summarises the talk and the main features announced and also the delivery date of mid 2005 to production.

Today at Open World one of the key notes was about the introduction of Oracle 10g Release 2. With Release 2 the main thrust is improving efficiency and reducing the cost of management. Its all about Grid computing and automation and self managing databases. What’s in it for us Oracle security aficionados?

Well the most direct points from the http://www.oracle.com/technology/products/database/oracle10g/pdf/10gr2_highlights.pdf - (broken link) Oracle database 10g Release 2: new feature highlights document are the quotes to say 10g grid provides 24*7 data access whilst being secure. They don't go to say how the data is secure!, lots of 24*7, such as clusterware redundancy and higher availability, integrated tape backup and recovery (These facts are important for data security), fast failover and most interestingly integrated transparent data encryption and key management in the database. This helps to allow customers to protect their data seamlessly without changing applications - they say. This sounds a very interesting addition, one of which I am keep to get my hands on 10gR2 to test and run through its paces. Encryption of data in the database is an interesting problem. There are three main commercial players in this space - see the links on my Oracle Security Tools page. The fact that Oracle say they will support the handling of keys seamlessly in the database is very interesting. I for one would like to know more.

The backup and high availability functions and improvements are also key features for those interested in security. Backups and recovery processes and also high availability strategies should be a key part of any Oracle security policies and procedures.

One other key item of note in the note in the second page that Oracle are now proving statistics collection from the SGA directly from memory. This means that direct SGA access methods are being used. I have some links to papers written by Kyle Hailey and Miladin Modrakovic on my Oracle Internals page that readers may find very interesting to see how Oracle are doing this. I also talked about Direct SGA access in a couple of previous blog entries.

I came across an interesting thread this morning on comp.databases.oracle.server that asked http://groups-beta.google.com/group/comp.databases.oracle.server/browse_frm/thread/64968f2adf0e6a7c/71c92ae7a86a2ca7?tvc=1&q=%22Auditing+an+app%27s+SQL+-+How%3F%22&_done=%2Fgroups%3Fq%3D%22Auditing+an+app%27s+SQL+-+How%3F%22%26hl%3Den%26lr%3D%26sa%3DN%26tab%3Dwg%26&_doneTitle=Back+to+Search&scrollSave=&&d#71c92ae7a86a2ca7 - (broken link) Auditing an app's SQL - How? where the poster asked if its possible top grab the actual SQL sent from a black box third party application he was managing to the database server. He later in the thread told us that an error message is being generated by an INSERT statement and an error number was available.

Howard went on to offer some great advice as usual to grab the SQL from the SGA or by using Log Miner.

I added the following:

"Howard has given some good advice but let me give some other tips. The
first thing is that the error number you list looks like a Windows
error, at least that is the sort of number I see when Windows programs
crash. It could be that the application tool parses the SQL first and
the error is detected before sending the SQL to the server so you may
not find it in the database or on the way to the database.


If you can repeat the problem - I think from inference you can then set
SQL*Net trace on the client that is running the application. An example
of how to do this is in my paper "Detecting SQL Injection in Oracle"
which you can find at /orasec.htm - This
trace will then contain the SQL statement sent to the server from the
application. You can also use SQL trace (depending on how far the SQL
got into the server) - a paper on many ways to set trace is at
/ramblings/how_to_set_trace.htm


Finally if you are a bit more adventurous and you application uses OCI
as its lowest layer then there is a free tool that grabs SQL from the
OCI layer called OCISPY, you can find a link on my tools page at
/tools.htm - There is a Java tool that does
the same for JDBC listed there as well, sorry cannot recall the exact
name at the moment."

Finally Niall gave some good advice that went further to suggest that the issue may be in the client.

This is a good subject and one that comes up from time to time. Quite often third party applications where the course code is not available are used in organisations and its difficult to understand exactly what these applications are doing database wise. This can often be an issue for tuning the application but also often in security contexts where the exact data translations and queries need to be known. Just because an application is a black box does not mean that we cannot work out how it talks to the database, and as such this interests me from a security perspective.

There are a number of possibilities of extracting the SQL generated from an application as have been discussed in this thread. These can be summarised as follows:


As I said, probably there are other ways as well.

I just saw http://www.orablogs.com/otn_at_openworld/archives/000719.html - (broken link) a post by justin kestelyn to the OTN at open world blog that talks about Mary Ann Davidsons guru chat at Oracle Open World today. Justin tells us that the room was standing room only and quite informative.

Mary Ann talked about the reasons for the choice of quarterly patch schedule and then took a series of questions from the floor, predictably the main subject being the problems of patching. Justin makes a great point that the OEM team need to carefully consider a more robust and proper patch management tool in future. I have said this before as well. What we need is a tool that can detect which patches (exactly which patches) have been applied; it should allow multiple patches to be applied on top of each other (or not) and reversed if needed. It should even also be capable of remotely pushing out patches to large numbers of databases across the network (ideally without stopping them - Is that asking too much??) - And i don't mean remotely from an Oracle website in the same manner as Windows OS patches.

I saw http://www.orablogs.com/cmaxwell/archives/000705.html - (broken link) this post on Colin Maxwells weblog today that lets us know that the WS-Security Jars were not included in the new developers preview release of JDeveloper 10.1.3 because the export compliance was not granted in time. :(.

Colin lets interested parties know where the jars can be downloaded from, when they will be available.

The new 6 day hands on "Securing Oracle" training course is to be debuted in San Diegio in April 2005. This is the course that I have written for SANS over the last 9 - 10 months. The details for the San Diego Conference have recently been added to the SANS website. If you follow he link to the track you can find the Securing Oracle track listed.

The main page for the course description gives an introduction to the course and also a statement from me. Details for each of the days are then available from links to the top right of the page. I will list them here for convenience.



This is a comprehensive course on Securing an Oracle database that covers every area of securing an Oracle database from why it could be insecure, how hackers can exploit it and how to plan to secure it, audit it and make it secure.

I will talk again in a bit more details about the structure of the course in another later post, I just wanted to let people know that the course is going to be debuted early next year.

I noticed an interesting post on the Oracle-L mailing list today - The post was made late last week. The first post in the thread by Patrice asks

"Does anyone know if 10g Release 2 will be the final release for 10g?

Will 10g Rel 2 be announced for mid 2005?"

Patrice goes on to say why they need to know in a later post but the post later in the thread that caught my eye was by Martic who says that he knows people within Oracle who have told him that Oracle 10gR2 was released internally to staff and will be released soon to the general public. I have not seen any other mentions of this (although it has to be said until I saw this thread I was not looking for 10gR2 :) ) on mailing lists or newsgroups.

If 10gR2 is really imminent for the rest of us it will be interesting to see if there are any new Oracle security related features or at least what bug fixes are included security wise.

The final post in Edward Stanglers short series of issues with not running catpatch.sql or to be more accurate not running all of the upgrade scripts has come to an end. The final post entitled "Catpatch - concluded" summarises the previous posts in the saga. Ed wanted to emphasis these issues are more prevalent in test and development systems rather than production and that in 10g it may get better!

I was just surfing over at Howards website where i noticed that he has just started a new weblog. Whilst there have not been any Oracle security specific posts so far, Howards blog will be on my list of places to regularly check for new Oracle information and particularly Oracle security info. Howard has written specifically about Oracle security features and functions in the past and Howard’s work is always well written and informative so it is going to be worth checking his blog.

The blog is here and the first post inauspiciously titled "...It's a start " was posted on Friday.

I just found Edward Stanglers next post in the problems with not running catpatch.sql issue entitled "Missing DROP_SEGMENTS from DBA_TAB_MODIFICATIONS". This is part of a feature added in 8i that allows monitoring to be set on a table. When the table is modified significantly (>10%) then the statistics are effectively stale. Then the DBMS_STATS.GATHER_SCHEMA_STATS can be run with "GATHER STALE" to just update those tables that have changed. This is a good performance boost. Ed explains that this data is kept in memory and flushed to a dollar table periodically. The flushing is automatic in 8i and 9i (greater frequency) and can also be done manually. Ed explains that in 9.2.0.5 the DROP_SEGMENTS was added to SYS.MON_MODS$.

This is the subject of Ed's post, the fact that catpatch.sql needs to be run to add this column. Edward then goes on to explain the problems of not running catpatch.sql. Interesting post, again it’s available here.

In his next post in this series Ed talks about missing password files. Edward starts by talking about the fact that you can no longer connect internal in 9i and you can no longer connect just as SYS without AS SYSDBA. He demonstrates that any user granted SYSDBA is connected to the database as SYS.

Then Edward gets onto the issue for this post, that fact that if you have upgraded to 9i then you can no longer connect as sysdba whilst specifying a TNS alias in the connect string. The issue is the subject of his post, a missing password file. Ed covers AS SYSDBA, password files, orapwd and how remote privileged connections work very well. This is a great short article and again it’s available here.

OK, what are encoded or decoded shell codes? - First I had better say what shell code is. Shell code is the instructions (machine instructions) that a hacker sends to a server via a buffer overflow or similar to gain control of the server. This is a common practice in hacking computers. A hacker finds an incorrectly written piece of code in an application that allows you to send incorrect input that in turn ends up with the server running your own machine code rather than the application processing some business function.

Oracle seems to be very susceptible to this kind of attack in recent times. A lot of the alert 68 issues seem to be cases of PL/SQL built in procedures being exploited by sending long strings to them. This kind of attack can be used to send shell code via a PL/SQL function to the database server. So how does a hacker do this? Well part of the long string that the hacker would pass to a vulnerable PL/SQL function or procedure parameter would be shell code. A buffer overflow works because the string passed overflows the end of the buffer assigned to handle it and may go on to overflow a return address of a function on the machines stack (There are also other ways that this kind of overwrite attack can work but let's stick to this one for now). The idea is to get an executable instruction that is supplied by the hacker into the CPU. When the function returns the server will execute the hackers code instead. This kind of attack works because a hacker is able to send machine code to the server, either through a formal program parameter or as other supplied input or possibly via PL/SQL functions or procedures.

Applications are becoming more clever and various filters between the hacker and the server aim to filter out anything that could be machine code. This is where an encoding loop comes in, as it allows the hacker to use valid character sets such as A-Z0-9a-z and then the decoder takes care of making it executable.

http://www.edup.tudelft.nl/~bjwever/whitepaper_shellcode.html - (broken link) I found this paper by Berend-Jan Wever (skylined) that is excellent. It gives some good ideas on how to write a decoder loop and gives some example source code at the end. This paper build on two previous papers, the first by Rix called "Writing ia32 alphanumeric shellcodes" and the second by obscou called http://www.phrack.org/show.php?p=61&a=11 - (broken link) Building IA32 'Unicode-Proof' Shellcodes. It is worth reading these two papers first before skylineds paper.

If you want to understand how a hacker thinks and to understand the lengths that they will go to exploit applications including getting past filters then you need to read these papers. The two by Rix and obscou are superb and well worth reading. The paper by skylined that I found first is also excellent.

I just came across Edwards post made yesterday in his issues of not running catpatch.sql series. This post is entitled Missing SELECT ANY DICTIONARY privilege granted to the DBA role. This post talks about how SELECT ANY DICTIONARY replaced SELECT ANY TABLE in 9i as the privilege to get access to the dollar tables. Edwards post goes on to talk about migrations from 8iR3 to 9i where the SELECT ANY DICTIONARY privilege goes missing if a migration fails.

I posted the other day about a great floppy disk Linux distribution that I found that can be used to rescue a failed system or to use in a forensics capacity on a database server that is known to have been compromised.

Paul Drake made a comment post to this blog entry that i felt was worth mentioning here as a new blog entry.

Paul made us aware of another option. This is called Helix. Helix is a customised distribution of Knoppix Live Linux CD. The Helix CD can be booted using customised different versions of the kernel. It is a bootable live CD and includes tools that are dedicated to Incident response and forensics. The CD has been created specifically so that it does not alter the HOST PC in anyway. This CD is used by SANS in the Track 8: System Forensics, investigation and response course.

The above paragraph is paraphrased from the Helix website for this CD. Go there to see their own details, FAQ, contents and download.

Details of the SANS forensics course can be found here.

I made a post the other day about a list of Oracle security policy checks that I found on DBA Village about a list of policy check pages on Application Security Incs website.

At the time of the post I could not find an index page to make it easy to traverse these pages. Aaron has added a comment to the original post on my weblog that gives us access to a search page.

The link to search on is http://www.appsecinc.com/cgi-bin/show_policy_list.pl?app_type=2&category=6