Oracle 11g Secure File Features with Oracle UCM

Tuesday, April 22nd, 2008 | Content Management, Database, Oracle, Secure File | David Roe

Last week I and several others talked about Oracle’s announcement of their new product Universal Online Archive. While UOA should be a pretty cool application, many of the features mentioned are actually available now in the 11g database and can easily be leveraged by Oracle UCM.

So what are these features?

Oracle 11g has a ton of new features, but the ones most interesting from a content repository standpoint are the new BLOB storage capabilities called SecureFiles. What they allow you to do is create a BLOB field in a table, but instead of just saving the file in the database table, you can now:

Compress it
Encrypt it
Deduplicate it (ok that doesn’t make sense..but we’ll cover it soon)

Those are some pretty cool features, ones which are great from a just a database perspective, but become very pretty handy if you’re talking about a content repository. The fact that UCM has traditionally stored it’s content on the filesystem has always been a selling point for me; small databases, better performance, what would I gain by storing my content in a database? Bex Huff I think actually answers this best:

For the longest time, the folks at Stellent balked at using the database for file storage. Using the filesystem made much more sense because of performance reasons, which made up for the additional complexity of the architecture. However, if the user has 11g, there really is no better option than storing content items in the database.

There it is…No better option than storing content in the database? We have to give that a go.

Giving it a go

There are a couple prerequisites required before we can store UCM content in it’s database:

Minimum UCM version is 10gr3
UCM must be running on a 11g database
A JDBC Filestore provider must be configured in UCM, here’s a link to the installation PDF

Step 1 – Verify if Secure File Support

The 11g database enabled SecureFile support by default, but if you want to make sure it’s working you can run the following commands as SYSDBA:

SQL>show parameter COMPATIBLE; NAME TYPE VALUE ------------------------------------ ----------- ------------- compatible string 11.1.0.0.0

The database must have a version setting higher than 11.0.0.0.0

SQL> show parameter db_securefile NAME TYPE VALUE ------------------------------------ ----------- -------------- db_securefile string PERMITTED

There are a couple different values that will work for db_securefile, PERMITTED is the default, ALWAYS or FORCE should work as well. If you need to change this value, run:
ALTER SYSTEM SET db_securefile = 'PERMITTED';

Step 2 – Drop and Recreate the BFILEDATA Column

The FILESTORAGE table created by the file store provider is configured for BASICFILE support, which unfortunately will not allow us to take advantage of any of the new BLOB features. Also unfortunate is that it appears the only way to enable SecureFiles is to drop and recreate the BLOB column.

Since I’m just using my local laptop, this isn’t really a big deal for me as my table is empty. Where I am going with this is that in these next few steps you could loose data if you’ve already stored it in the FILESTORAGE table…so be careful. Take a back up and if you’re using JDBC storage move you data somewhere else first.

As the UCM user drop the existing BFILEDATA BLOB column:

alter table FILESTORAGE drop column BFILEDATA;

Now add the column back only this time using the SECUREFILE Store As identifier:

alter table FILESTORAGE add ("BFILEDATA" BLOB) LOB ("BFILEDATA") STORE AS SECUREFILE filestorageblob( TABLESPACE "UCM_SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT));

So what we’ve done is removed the old BLOB field(which was originally configured as a BASICFILE and replaced it with a BLOB configured for SecureFile support. Even though that’s set, we still aren’t taking advantage of new features. That will require a couple modifications to the column.

Compression

SecureFile compression comes in a couple different flavors; None, Medium or High which correspond to the level of compression applied to the field. SecureFile compression is completely separate from any table or index compression in place already, so if you compress the BLOB it’s just the BLOB nothing else. You’ll want to think about performance some when setting compression, as there should be an increase in latency and also CPU utilization when enabled.

Compression can be added with any of the following:

alter table FILESTORAGE modify LOB("BFILEDATA") ( COMPRESS HIGH );

alter table FILESTORAGE modify LOB("BFILEDATA") ( COMPRESS MEDIUM );

Or removed with this one

alter table FILESTORAGE modify LOB("BFILEDATA") ( NOCOMPRESS );

Encryption

SecureFile encryption adds an additional layer of security to your database by encrypting your BLOB files while they reside in the Oracle data file. The encryption is designed to prevent unauthorized access to the BLOB in case someone gets their paws on your data files. I have to admit that this one seems like a little bit overkill considering that like compression, encryption will cause a cpu and latency hit. I guess I just don’t think that many data files are getting hacked, but I could be wrong.

Enabling encryption requires adding a new folder named wallet to the database’s admin folder.

/oracle/product/database/admin/wallet

Then log in to the database as sysdba and run:

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "[your password here]";

Reconnect back with your UCM account and update the BLOB:

Add encryption
ALTER TABLE FILESTORAGE MODIFY LOB("BFILEDATA") (ENCRYPT USING 'AES256');
Remove it
ALTER TABLE FILESTORAGE MODIFY LOB("BFILEDATA") (DECRYPT);

Deduplication

Deduplication may be the most clever feature included in SecureFiles. Each file checked in to a BLOB field is compared against the others, if the new file is a duplicate, only a pointer to the original is stored. Of course you as the user never knows the difference. Query either row and the file is returned.

The thing that is really cool about deduplication from a UCM perspective is that very often UCM actually stores duplicate files. In addition to user’s checking in the same file twice, quite a few file formats are simply copied over to the web layout folder. If you’re running both your vault and weblayout from the JDBC Filestore provider, even if the file type is configured to be copied out to the weblayout it will only be stored once in the database.

Adding the deduplication is pretty easy:

Enable deduplication
ALTER TABLE FILESTORAGE MODIFY LOB("BFILEDATA") ( DEDUPLICATE );

Disable it

ALTER TABLE FILESTORAGE MODIFY LOB("BFILEDATA") ( KEEP_DUPLICATES );

That’s it

As you can see there are quite a few options when it comes to configuring SecureFiles in general, but the integration with UCM is actually pretty simple. I ran through a couple test check ins while writing this post(some of the items being images that went though the refinery) all of which went though smoothly, though a little slower than a standard filesystem check in. I plan on leaving my local server with this configuration for some time, so we’ll see how it goes.