Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "Security is a major force in the new 10g Release 2 database"] [Next entry: "An issue with DBA_REGISTRY"]

10gR2 adds a wrap package procedure, TDE and makes DBMS_OUTPUT output unlimited

I just saw Justin kestelyn's post to his OTN blog on http://www.orablogs.com - (broken link) orablogs this evening titled http://www.orablogs.com/otn/archives/001232.html - (broken link) Oracle Database 10g Release 2 "Sneak Peek". This is a great post as it includes links to several resources on 10gR2 that have just been made available today. This includes technical papers. The paper I particularly noticed first is a paper discussing Oracle Advanced Security that discusses encryption.

Another particularly interesting paper is Arup Nanda's paper Part 1: SQL and PL/SQL Features which is to be part one of a set of papers in the Oracle Database 10g: Top Features for DBAs - Release 2 Features Addendum. This paper is very very interesting for those of us interested in security. The paper includes a description of how Transparent Data Encryption (TDE) will work in 10gR2. Arup also shows that TDE can be used to encrypt the columns in external tables. The most interesting part of this paper for me is the item about the new wrap database package. This is a new function is DBMS_DDL.CREATE_WRAPPED which will allow dynamic PL/SQL to be create wrapped in the database. Arup gives examples. There is also another function DBMS_DDL.WRAP that will output the convert PL/SQL code passed as an argument in a wrapped form. This can then be spooled to a file and created later in the database wrapped.

Arup then goes on to discuss another great new feature, conditional compilation in PL/SQL. This is a feature that I have wished for many times in the past. This feature works like the pre-processor in C. This means that conditional code can be added at compilation time rather than at run time. This is a great addition and Arup gives some examples. A good use for this would be to conditionally compile debug code in to your source to instrument the PL/SQL code as Arup shows.

The final interesting addition in 10gR2 is the fact that the package DBMS_OUTPUT can now have unlimited output and each line of text can now be of any length. I have had this issue many times in the past. The source for this package used to be shipped in earlier version 7 databases so it was possible to see roughly how it worked and implement your own version in a local schema to get around the issue in earlier versions but this is a useful addition to 10gR2.

Have a look at the rest of the links in Justin's post for more information on new additions in 10gR2.