Thursday, April 12, 2012

Security Features in Oracle 11g You Need to Know

Oracle 11g has some new security features that I like a lot.  Oracle is doing some really good work in the name of security for the products they sell (including Solaris 11).  Here are two of my favorites out of ten major enhancements.

1.  Fine-grained access control on network services in the database. Namely UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR.  For those in the security field this might remind you a little of SELinux or IPtables.

2.  Encryption enhancements.  Oracle has four enhancements to it's crytographic framework.  Oracle is faced with the challenges of speed and security.  Queries must be fast and data must be secure.  Encryption can be very resource intensive for just about any hardware configuration today.  So let's see what they've done to make query responses fast and more secure. 

ACLs give more control to the DBA to control external access to applications in the database.  Like a firewall you can now assign inbound/outbound traffic to certain ports.  The ACL is stored in Oracle's XML database.  You can manage the ACL through the XML or, since this feature is based on PL/SQL packages you can manage it that way too.  By default PL/SQL utility packages are created with EXECUTE privilege which are open to PUBLIC users.  By using ACLs on a networked service to the database you effectively take away the intruder's ability to attack the network because of  generous default settings.  There are two steps to configuration.

1.  Create the ACL and its definitions.  Use the DBMS_NETWORK_ACL_ADMIN.CREATE_ACL stored procedure to create the content.  Here's the basic syntax for the ACL:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl          => 'file_name.xml', 
  description  => 'file description',
  principal    => 'user_or_role',
  is_grant     => TRUE|FALSE, 
  privilege    => 'connect|resolve',
  start_date   => null|timestamp_with_time_zone,
  end_date     => null|timestamp_with_time_zone); 
END;
 
You'll save yourself a lot of ACL management by assigning the privilege to a role rather than individual users.   And if "connect" fails use the "resolve" privilege instead. 

2. Assign the ACL to network hosts.  Use the DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL stored procedure to assign the ACL to hosts. The syntax looks like this:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl         => 'file_name.xml',
  host        => 'network_host', 
  lower_port  => null|port_number,
  upper_port  => null|port_number); 
END;
 
Try to avoid using "null" for port parameters (lower_port to upper_port).  Null means all ports are open and that defeats the purpose of the ACL.  I would recommend using specific port for your lower and upper boundaries.  For example use port 443 for both lower and upper port parameters.  Be specific and narrow your ports down to only those that are required to access the application.  Of course the ports you use in the ACL should also be configured within the local firewall (like iptables) and the network firewall (like Checkpoint) if the application is access externally.

For examples of syntax for both stored procs click here.  That wraps up the benefits of the fine-grained access control feature.  Beware, if your new Oracle 11g application isn't communicating then check the ACL configuration first (then the local firewall, then the network firewall).  The error you receive when ACLs aren't configured is ORA-24247 network access denied by access control list (ACL).

Encryption is one of my favorite topics and also one of the most complex concepts.  In Oracle 11g there are four new ways to encrypt.  SecureFiles, encrypted Dump File Sets, Transparent Data Encryption (TDE) and my pick Transparent Tablespace Encryption.  SecureFiles supports better performance, deduplication and compression and encryption all in one.  SecureFiles is very cool. Encrypted Dump File Sets allows you to do exactly that - encrypt all dump file sets during an export.  I haven't heard about performance for this feature yet.  If you do a lot of dump files to backup/move/migrate data between environments then I would highly recommend also encrypting those file sets.  Data in motion can be open to exposure if it's not protected properly.  TDE is also cool.  It allows you to store your master encryption key in an encrypted wallet and uses this key to encrypt the column keys which also encrypt the columns.  If key management is a challenge for you then you've just doubled your challenge with a master key for your column keys (primary key, foreign key, etc.).  You can also share this master key between databases and instances of RAC and Data Guard. 

There are three steps to Transparent Tablespace Encryption.  Set the master key, open the Oracle wallet and create an encrypted tablespace.  By encrypting the entire tablespace you also encrypt all the data.  Nice! 

1.   Creating the master key is easy.

SQL> ALTER SYSTEM SET ENCRYPTION KEY [certificate_ID] IDENTIFIED BY "password"
 
 
The [certifate_ID] is your PKI private key.   Query V$WALLET for this certificate - you may already have one in the wallet.  The certificate_ID is optional as designated by the [ and ].  "password" is required to create the key.  Make it at least 12 characters long.

2.  Start the database instance first then open the wallet.  The sequence is important here.  And separation of duties suggests you also assign the wallet opening procedure to your security administrator (like me). 

SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY password;
SQL> ALTER DATABASE OPEN;
 
 
Don't use the auto login option for the wallet.  Unless, that is, if your environment doesn't require that level of security.  The wallet must be reopened for database restarts and recovery options.

3.  Encrypt your tablespace!

CREATE TABLESPACE securespace
DATAFILE '/home/user/oradata/secure01.dbf'
SIZE 150M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);
 
There are four symmetric encryption options available for you to use.  3DES168, AES128, AES192 and AES256.  The default is AES128 if you don't specify one.  Remember the larger the encryption bit the better but you may also experience performance issues. 

To verify if your tablespace is encrypted query DBA_TABLESPACES or USER_TABLESPACES. The encrypted column will tell you if your tablespace is encrypted or not.

SQL>  select tablespace_name , encrypted from dba_tablespaces;

TABLESPACE_NAME                 ENC
------------------------------                ---
DWADM_NN_RAP_DATA          YES                                                                                            



No comments:

Post a Comment