Oracle Database 11g: The Top New Features for DBAs and Developers by Arup Nanda

Security

Oracle Database 11g delivers a rich new security functionality—from case-sensitive passwords, to Transparent Tablespace Encryption, to Access Control Lists for UTL_TCP/HTTP/SMTP.

Download Oracle Database 11g

Default Passwords

In 2006 OTN pubished a series of articles I authored called "Project Lockdown: A Phased Approach to Securing Your Database Infrastructure", in which I discussed how to address common security challenges such as the use of default passwords by users, and how to scan your database for them

Unfortunately for me, you can throw that specific portion of my article away. Oracle Database 11g now offers a way to quickly identify users with default passwords, implemented in the rather ludicrously simple way of checking a single data dictionary view: DBA_USERS_WITH_DEFPWD. (Note that DBA_ is a standard prefix; it does not contain only DBA users with default passwords.) You can identify these users by issuing:

select *
from dba_users_with_defpwd

USERNAME
------------------------------
DIP
MDSYS
WK_TEST
CTXSYS
OLAPSYS
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA
WMSYS

SQL> alter user scott identified by tiger1;
 
User altered.

SQL> select * from dba_users_with_defpwd;

Case-Sensitive Passwords

In Oracle Database prior to release 11g, user passwords were case insensitive. For example:

SQL> conn scott/tiger
Connected.
SQL> conn scott/TIGER
Connected.

Problem solved; in Oracle Database 11g passwords can be case sensitive as well. While creating the database via DBCA, you will be prompted whether you want to upgrade to the "new security standards," one of which is the case-sensitive password. If you accept, passwords will be recorded in the same case as they were created. Here is the resulting behavior, assuming you have accepted the new standard:

SQL> conn scott/tiger
Connected.
SQL> conn scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Now, some of your apps may not be passing the password in proper case right now. A typical example is a user input form: Many forms accept passwords with no case conversion being performed. However, with Oracle Database 11g, that login may fail unless the user enters the password in case-sensitive format or the developer changes the app to convert to upper or lower case (which may not be possible quickly).

If you wish, however, it is still possible to revert to case insensitivity by altering a system parameter, SEC_CASE_SENSITIVE_LOGON, as shown in the example below.

SQL> conn / as sysdba
Connected.
SQL>  alter system set sec_case_sensitive_logon = false;
 
System altered.
 
SQL> conn scott/TIGER
Connected.	

select username, password, password_versions
from dba_users;

USERNAME                  PASSWORD                       PASSWORD
------------------------- ------------------------------ --------
SYSTEM                                                   10G 11G
SYS                                                      10G 11G
MGMT_VIEW                                                10G 11G

Next, note the column PASSWORD_VERSIONS, which is new in Oracle Database 11g. This column signifies the case sensitivity of the password. The value "10G 11G" signifies that the user was either created in 10g and migrated to 11g or created in 11g directly.

You can enforce, if you wish, the sensitivity of the SYSDBA password as well by entering a new parameter, ignorecase, while creating the password file as shown below:

$ orapwd file=orapwPRODB3 password=abc123 entries=10 ignorecase=n

The possibility of enforcing a case-sensitive password not only makes it more difficult to crack passwords by brute force, but also enables you to meet many more compliance requirements. Even more important, you can enforce the password requirement dynamically without needing a database shutdown, which comes in handy during upgrades and debugging login issues when upgrading legacy apps.

Profiles and Password Verify Function

Remember the password verification function in Oracle Database? Many of you may not be even aware of its existence, let alone use it. The function is a quick and easy way to enforce quality of database passwords—for example, they should contain a certain number of characters, should not be identical to the username, and so on. Perhaps its best feature is that it is built-in; all you have to do is turn it on. More likely than not, you didn't.

In Oracle Database 11g, the password management function has new and improved verification logic. If you examine the password verification file utlpwdmg.sql in $ORACLE_HOME/rdbms/admin, you will notice that the script creates a new password function called verify_fnction_11g. At the end, the script has the following lines:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;

Improved Out-of-Box Auditing

Auditing is another common pain point. Oracle Database includes powerful auditing features that can be used for tracking user activities. Most people, fearing an I/O contention issue, do not take advantage of them. But the truth is that some auditing can be safely turned on with little risk.

Examples include CREATE SESSION, which writes a record when a session starts and then updates the record when it ends. This audit has minimal impact on I/O but provides powerful benefits.

In Oracle Database 11g, two simple changes have been made to provide an even more powerful auditing solution. First, the database parameter audit_trail is now set to DB by default, not NONE, as it was in previous versions. This allows you to turn on auditing on any object, statement, or privilege without recycling the database.

The second change is more statements have been placed under audit by default. Here is the list:

ALTER SYSTEM
SYSTEM AUDIT
CREATE SESSION
CREATE USER
ALTER USER
DROP USER
ROLE
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
CREATE PUBLIC DATABASE LINK
GRANT ANY ROLE
ALTER DATABASE
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
ALTER PROFILE
DROP PROFILE
GRANT ANY PRIVILEGE
CREATE ANY LIBRARY
EXEMPT ACCESS POLICY
GRANT ANY OBJECT PRIVILEGE
CREATE ANY JOB
CREATE EXTERNAL JOB

These two changes create some powerful auditing capabilities out of the box. Of course, they are just database parameters and audit settings; if you want, you can turn them off easily. But if you look at the list of statements, you may actually find them worth auditing, even in development databases. You may want to fine tune them, however. (For example, in data warehouses, users create and drop a lot of temporary tables so auditing CREATE/DROP TABLE might flood the audit trail.)

Caution: When you upgrade to Oracle Database 11g, auditing is turned on by default for the above-mentioned statements. Thus audit trails will be written to the table AUD$ in the SYSTEM tablespace, which may fill up quickly. Watch this space closely.

Transparent Tablespace Encryption

Encryption is getting more and more attention these days, thanks to myriad new laws and regulations. You need to encrypt data somehow but the big question is, how?

For those still on Oracle Database 10g Release 1 and previous releases, the DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT toolkits let you build your own encryption framework. In Oracle Database 10g Release 2, this framework is baked in via the great Transparent Data Encryption feature.

Transparent Data Encryption lets you encrypt specific columns, which is adequate for most requirements. However, performance can be an issue with this feature (or any other encryption solution for that matter): Index range scan cannot be applied to encrypted columns, which creates a major drag on performance.

This is where Transparent Tablespace Encryption in Oracle Database 11g really shines. When the tablespace is declared encrypted any data on the tablespace (including transportable tablespaces, backups, and so on), not just tables individually declared as such, is encrypted. But during index scanning, the scanning occurs in memory where the data is unencrypted, therefore causing no performance impact.

Excited yet? Let's see how it is done. The encryption procedure is identical to that of Transparent Data Encryption: You need to create a wallet where the master encryption key is stored. If you don't have Transparent Data Encryption set up already, you will need to create the wallet and the key.

First, create the file location for the wallet; the default location is $ORACLE_BASE/admin//wallet. The wallet subdirectory does not exist by default; you need to create it. So, in my example, this directory will be /home/oracle/app/admin/PRODB3/wallet.

Next, create the encryption key in the wallet by issuing the following statement:

alter system set encryption key identified by "abcd1234!";

$ cd /home/oracle/app/admin/PRODB3/wallet
$ ls
ewallet.p12

alter system set wallet open identified by "abcd1234!"

Now create the tablespace:

create tablespace secure1
datafile '/home/oracle/oradata/PRODB3/secure1_01.dbf'
size 1M
encryption using 'AES128' 
default storage (encrypt) 
/

Now that the tablespace is created, you can create tables as you would in a regular tablespace.

create table secure_trans
tablespace secure1
as
select * from trans
where rownum < 201
/

create table secure_res
tablespace secure1
as
select * from res
where rownum < 201
/

Now update a text field in the tables so that you can search inside the datafile for that:

update secure_trans set comments = 'Transaction Comments';
update insecure_trans set comments = 'Transaction Comments';
commit;

alter tablespace secure1 offline;
alter tablespace secure1 online;
alter tablespace insecure1 offline;
alter tablespace insecure1 online;

$ strings insecure1_01.dbf | grep Transaction
Transaction Comments
...

$ strings secure1_01.dbf | grep Transaction
$

select hotel_id, sum(amt)
from secure_trans t, secure_res r
where t.res_id = r.res_id
group by hotel_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       14      0.01       0.01          4          6          0         186
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.01       0.01          4          6          0         186
 
Rows     Row Source Operation
-------  ---------------------------------------------------
    186  HASH GROUP BY (cr=6 pr=4 pw=4 time=5 us cost=8 size=10400 card=200)
    200   HASH JOIN  (cr=6 pr=4 pw=4 time=45 us cost=7 size=10400 card=200)
    200    TABLE ACCESS FULL SECURE_TRANS (cr=3 pr=2 pw=2 time=8 us cost=3 size=5200 card=200)
    200    TABLE ACCESS FULL SECURE_RES (cr=3 pr=2 pw=2 time=9 us cost=3 size=5200 card=200)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       14      0.00       0.01          4          6          0         186
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.01       0.01          4          6          0         186
 
Rows     Row Source Operation
-------  ---------------------------------------------------
    186  HASH GROUP BY (cr=6 pr=4 pw=4 time=4 us cost=8 size=10400 card=200)
    200   HASH JOIN  (cr=6 pr=4 pw=4 time=46 us cost=7 size=10400 card=200)
    200    TABLE ACCESS FULL INSECURE_TRANS (cr=3 pr=2 pw=2 time=8 us cost=3 size=5200 card=200)
    200    TABLE ACCESS FULL INSECURE_RES (cr=3 pr=2 pw=2 time=9 us cost=3 size=5200 card=200)

The view DBA_TABLESPACES has a new column, ENCRYPTED, to show if a tablespace is encrypted. In addition, a new view, V$ ENCRYPTED_TABLESPACES, shows what type of encryption is enabled for the tablespace.

SQL> desc v$encrypted_tablespaces
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 TS#                                                NUMBER
 ENCRYPTIONALG                                      VARCHAR2(7)
 ENCRYPTEDTS                                        VARCHAR2(3)
 
SQL> select * from v$encrypted_tablespaces;
 
       TS# ENCRYPT ENC
---------- ------- ---
         5 AES128  YES

SQL> desc v$tablespace
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 TS#                                                NUMBER
 NAME                                               VARCHAR2(30)
 INCLUDED_IN_DATABASE_BACKUP                        VARCHAR2(3)
 BIGFILE                                            VARCHAR2(3)
 FLASHBACK_ON                                       VARCHAR2(3)
 ENCRYPT_IN_BACKUP                                  VARCHAR2(3)

As you can see, Transparent Tablespace Encryption solves two problems in a rather elegant way: It encrypts data at rest on disks without affecting performance because the data management occurs inside the SGA.

Encryption of Data Pump Dumpfiles

Oracle Database 10g introduced one of the most powerful features for data movement: Data Pump, the successor to the original export/import tool. Apart from being just plain faster, Data Pump provided many benefits such as parallelizing the process and remapping the tablespace. In Oracle Database 11g, it also helps secure dumpfiles via a new parameter called ENCRYPTION.

A dumpfile is outside the database and realm of database security and contains potentially sensitive data. In today's security conscious environments, they present a unique set of problems. In some really security conscious environments, DBAs resort to encrypting dumpfiles through third-party utilities after exporting the data—not a very convenient approach if you do a lot of exporting.

First, let's see how a typical dumpfile might be vulnerable. Suppose you have a table called TRANS, which contains a column called COMMENTS. The value in the column is "Transaction Comments". If you export this table normally:

$ expdp scott/tiger tables=trans dumpfile=insec.dmp directory=tmp_dir

$ strings /tmp/insec.dmp | grep Transaction

Now perform the export with the new parameter, ENCRYPTION. You also have to specify what type of algorithm to use. We'll use the AES 128-bit algorithm.

$ expdp scott/tiger tables=trans dumpfile=sec.dmp directory=
   tmp_dir encryption=data_only encryption_algorithm=aes128
 
Export: Release 11.1.0.5.0 - Beta on Sunday, 22 July, 2007 18:17:30
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.5.0 - Beta
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  
   '/******** AS SYSDBA' tables=scott.insecure_trans dumpfile=
    sec.dmp directory=tmp_dir encryption=data_only encryption_algorithm=aes128 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TRANS"                             16.82 KB     200 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /tmp/sec.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 18:17:44

$ cat /tmp/sec.dmp | grep Transaction
$

"But wait," you may ask, "Doesn't encryption require a key? Where did we pass the key to be used? Better yet, when decrypting, how will decryption occur without a key?"

The answer is very simple: The key will be from the wallet that was used in Transparent Tablespace Encryption you saw earlier. You do not need to use Transparent Tablespace Encryption feature to use the Data Pump encryption, but you need to follow the steps to create the wallet. Needless to say, the wallet must be open during the encryption and decryption processes.

If you are familiar with the Data Pump tool, you might recall that a similar feature is implemented thorough the parameter ENCRYPTION_PASSWORD. And now you may be wondering what's different now.

Good question. The 10g version encrypts the columns that were put under Transparent Data Encryption, not the whole dumpfile. If you do not use Transparent Data Encryption, you have no dumpfile encryption. In the 11g version of the tool, you can encrypt a dumpfile regardless of whether Transparent Data Encryption is used. This allows you greater flexibility and makes some practical sense. You may not want to encrypt data in the database for several reasons, performance and added security being the most obvious. But you do want to encrypt the data when it is outside the realm of the database. In that case the power of Data Pump encryption can't be beat.

Access Control Lists for UTL_TCP/HTTP/SMTP

You are probably familiar with the packages UTL_TCP, UTL_HTTP and UTL_SMTP; they enable communication between servers outside the realm of the database. For instance, utl_tcp is used to establish TCP/IP communication between two hosts, not through database links. Similarly, utl_http is used to make an http request from a Web server and utl_smtp is used to make SMTP mail calls between hosts.

These powerful tools are used occasionally by developers—for instance, utl_smtp to send emails from within the database, utl_http to pull Web pages that can be massaged inside a PL/SQL program, and so on. However, these tools present huge security risks. Using utl_tcp, a database user can go to any other machine approachable from that host, without even going to the system prompt. This was the modus operandi of the Voyager worm that rattled the Oracle user community just a year ago.

To address this risk, many experts advise to revoke the privilege of "execute from public" on these packages. In my Project Lockdown series, I recommended that approach as well. But what if the developers want to execute these packages for legitimate reasons?

Oracle Database 11g has a novel solution: You can grant the execute privilege package to anyone but control which resources they can call. For instance, utl_tcp can be limited to calling only a few IP addresses, a mechanism known as an Access Control List (ACL). If the host is on the ACL, the user can use in utl_tcp; a mere execute privilege on utl_tcp is not enough. Therefore it is impossible for a rogue process to overtake the utl_tcp package and make connections illegitimately.

Let's see how it works. First, create an ACL:

begin
        dbms_network_acl_admin.create_acl (
                acl             => 'utlpkg.xml',
                description     => 'Normal Access',
                principal       => 'CONNECT',
                is_grant        => TRUE,
                privilege       => 'connect',
                start_date      => null,
                end_date        => null
        );
end;

After the creation, you can check to make sure the ACL was added:

SELECT any_path
FROM resource_view
WHERE any_path like '/sys/acls/%.xml';

ANY_PATH
----------------------------------------------------------------------------
/sys/acls/ANONYMOUS/ANONYMOUS3553d2be53ca40e040a8c0680777c_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f93feb8dde040a8c068075b7_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f944b8dde040a8c068075b7_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f948b8dde040a8c068075b7_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f94cb8dde040a8c068075b7_acl.xml
/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/ro_all_acl.xml
/sys/acls/ro_anonymous_acl.xml
/sys/acls/utlpkg.xml

begin
  dbms_network_acl_admin.add_privilege ( 
  acl 		=> 'utlpkg.xml',
  principal 	=> 'SCOTT',
  is_grant 	=> TRUE, 
  privilege 	=> 'connect', 
  start_date 	=> null, 
  end_date 	=> null); 
end;

begin
  dbms_network_acl_admin.assign_acl (
  acl => 'utlpkg.xml',
  host => 'www.proligence.com',
  lower_port => 22,
  upper_port => 55);
end;

SQL> grant execute on utl_http to scott
  2  /
 
Grant succeeded.
 
SQL> conn scott/tiger
Connected.
SQL> select utl_http.request('http://www.proligence.com') from dual;
select utl_http.request('http://www.proligence.com') from dual
       *
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1

Now, add another rule to allow the communication:

  1  begin
  2    dbms_network_acl_admin.assign_acl (
  3    acl => 'utlpkg.xml',
  4    host => 'www.proligence.com',
  5    lower_port => 1,
  6    upper_port => 10000);
  7* end;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> conn scott/tiger
Connected.
SQL> select utl_http.request('http://www.proligence.com') from dual;
 
UTL_HTTP.REQUEST('HTTP://WWW.PROLIGENCE.COM')
--------------------------------------------------------------------------------
</iframe><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<HTML><HEAD><TITLE>Proligence Home</TITLE>
<META http-equiv=Content-Language content=en-us>
...

To find out the details of the ACL, query the view DBA_NETWORK_ACLS:

select host, lower_port, upper_port, acl, aclid
from dba_network_acls 
where ACL='/sys/acls/utlpkg.xml';

HOST
---------------------------------------
LOWER_PORT UPPER_PORT
---------- ----------
ACL
---------------------------------------
ACLID
--------------------------------
prolin3.proligence.com
        80        100
/sys/acls/utlpkg.xml
35D4278980DE6407E040A8C0680037D6
... and so on ...

Data Masking

In many organizations staging or QA databases are refreshed from time to time from the production database, allowing developers to run pre-production code against the database prior to going production. However, this procedure is a potential security problem -- when data is refreshed from the production to the QA database to keep them in synch, sensitive data elements may be exposed. To address that risk, many organizations alter sensitive data to some meaningless value in the non-production database, a practice known as data masking. For instance, you may want to update the Social Security Number with a random 9 character number in staging to mask the real SSNs.

But that's the easy part; making the change could be tough—you have to write the SQL script, make sure the process is restartable, take care not to overwhelm the system, and so on. Well, why not let Oracle take care of that while importing the data? In Oracle Database 11g, Data Pump has a new parameter, remap_data, for changing data during the import process.

First create a remap function:

create or replace package pkg_mask
as
   function fn_mask_ssn (p_in varchar2) return varchar2;
end;
/
create or replace package body pkg_mask
as
   function fn_mask_ssn (p_in varchar2)
   return varchar2
   is
   begin
      return lpad (
         round(dbms_random.value (001000000,999999999)),
      9,0);
   end;
end;
/

SQL> select * from accounts;
 
    ACC_NO ACC_NAME                       ACC_SSN
---------- ------------------------------ ---------
         1 John Smith                     123456789
         2 Jane Doe                       234567890

$ expdp scott/tiger tables=scott.accounts dumpfile=
   accounts.dmp directory=tmp_dirremap_data=accounts.acc_ssn:pkg_mask.fn_mask_ssn

[<SchemaName>.]<TableName>.<ColumnName>:[<SchemaName>.]<PackageName>.<FunctionName>

Now, you can import the table into the non-prod database. After the import is complete, if you check the values of the table:

SQL>  select * from accounts;
 
    ACC_NO ACC_NAME                       ACC_SSN
---------- ------------------------------ ---------
         1 John Smith                     645270861
         2 Jane Doe                       538996590

If you have already exported the table without this remap_data parameter, the values in the dumpfile are already the accurate value. You can mask them while importing, again by using the same magic parameter.

$ impdp scott/tiger dumpfile=accounts.dmp remap_data=
   accounts.acc_ssn:pkg_mask.fn_mask_ssn directory=tmp_dir tables=accounts

create or replace package pkg_mask
as
        function fn_mask_ssn (p_in varchar2) return varchar2;
end;
/
create or replace package body pkg_mask
as
        function fn_mask_ssn (p_in varchar2)
        return varchar2
        is
        begin
                return  
                'XXXXX'||
                substr (p_in,6,4);
        end;
end;
/

Other Notables

In previous versions most security operations are made through a tool called Oracle Security Manager. In Oracle Database 11g, Oracle Enterprise Manager contains all the tools to perform these tasks. Here is a screenshot of the Server tab of the Database Homepage. Note the section titled Security at the lower right hand side.