DBConsole 11g (part 3) – “Transparent Tablespace Encryption and Alerting”

Triggered a little bit by a post of Christo Kutrovsky from The Pythian Group, this post will be about one of the new features on tablespace level: Transparent Tablespace Encryption

Until now you were able to encrypt a column or table. When a user inserts data into an encrypted column, transparent data encryption automatically encrypts the data. When users select the column, the data is automatically decrypted. After the selection, the data is re-encrypted.

Transparent Tablespace Encryption

Oracle 11g adds now the possibility to encrypt a complete tablespace, instead of only a column or a table. Tablespace encryption makes use of encryption keys in a wallet outside the database.

Creation of encryption keys and control of security can be handled via the Oracle Enterprise Manager DBConsole.

 

Via a login into the local DBConsole, clicking on the server section, you can find under “Security” the URL for “Transparent Data Encryption”. Clicking this link the following page is shown.

DBConsole 11g (part 3) - "Transparent Tablespace Encryption and Alerting" 011

I created a new wallet to store keys in a directory I created called c:\oracle\admin\BETA\WALLET.

I manually added the parameter ENCRYPTION_WALLET_LOCATION in my sqlnet.ora file

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\Oracle\admin\BETA\WALLET)))

Be aware that the wallet is closed after a restart of the database…

To manually create and open the master you issue the following command:

connect / as sysdba
alter system set key identified by "[Your Password Goes Here]";

To open the wallet (ewallet.p12)after database startup, you can issue the following command:

alter system set wallet open identified by "[Your Password]";

Now you can create a secured tablespace via the tablespaces overview page (via the “server” section and then under “storage” or direct via the URL on the “Transparent Data Encryption” page).

DBConsole 11g (part 3) - "Transparent Tablespace Encryption and Alerting" 021

Click on the tablespaces page on the “create” button.

In the general tab enter (at least) a name for the tablespace (“SECURED”) and add a datafile (“secure_01.dbf”).

DBConsole 11g (part 3) - "Transparent Tablespace Encryption and Alerting" 03

Now click now on “Encryption Options”. In this screen you can choose for an Encryption Algorithm. I didn’t change anything and used the default value “AES192” and then clicked continue.

DBConsole 11g (part 3) - "Transparent Tablespace Encryption and Alerting" 04

Back in the “tablespace” section I checked the “Encryption” check box.

DBConsole 11g (part 3) - "Transparent Tablespace Encryption and Alerting" 05

Always interested in the manual stuff, before I clicked on the OK-button, I checked “Show SQL”.

DBConsole 11g (part 3) - "Transparent Tablespace Encryption and Alerting" 06

The interesting addition here is, of course, the string

ENCRYPTION USING 'AES192' DEFAULT STORAGE (ENCRYPT)

Clicking back in the “Tablespace” section on the OK-button created the enabled encryption tablespace SECURED.

DBConsole 11g (part 3) - "Transparent Tablespace Encryption and Alerting" 07

Easy.

DBMS_SERVER_ALERT

When now zooming into specific tablespace level, in my case I zoomed in on the just created “SECURED” tablespace, I noticed that this page has 3 tabs in “edit” mode. Two of them I knew, the other one was new for me…

DBConsole 11g (part 3) - "Transparent Tablespace Encryption and Alerting" 08

So I clicked on the “Thresholds” link and was presented with the following screen.

DBConsole 11g (part 3) - "Transparent Tablespace Encryption and Alerting" 09

Default values checked are the “Use Database Default Thresholds” for “Space Used (%)” and “Free Space (MB)”. I altered both thresholds, so that would enable me to see the SQL statements.

DBConsole 11g (part 3) - "Transparent Tablespace Encryption and Alerting" 10

Setting both thresholds on value “disabled” gave me the following SQL statements.

DBConsole 11g (part 3) - "Transparent Tablespace Encryption and Alerting" 11

As you could see in the thresholds section there is also a button to change default database behavior. Clicking on the “Modify” button gave me the following page.

DBConsole 11g (part 3) - "Transparent Tablespace Encryption and Alerting" 12

So what does this DBMS_SERVER_ALERT do?

In the (beta) documentation it is explained as follows:

Oracle 11g can create Server Generated Alerts, for example for “physical reads per second” or the ones shown before (space used / free space). The can be based thresholds or triggered by an event, for example “snapshot to old”. When an event or a threshold is triggered:

An alert message is sent to the predefined persistent queue ALERT_QUE owned by the user SYS. Oracle Enterprise Manager reads this queue and provides notifications about outstanding server alerts, and sometimes suggests actions for correcting the problem.

The alerts are displayed on the Enterprise Manager Database Home page and can be configured to send email or pager notifications to selected administrators. If an alert cannot be written to the alert queue, a message about the alert is written to the Oracle Database alert log.

Background processes periodically flush the data to the Automatic Workload Repository to capture a history of metric values. The alert history table and ALERT_QUE are purged automatically by the system at regular intervals.

Via DBMS_SERVER_ALERT.SET_THRESHOLD (as seen in the screenshots) you are manually able to set or disable these thresholds.

The DBMS_SERVER_ALERT package is described in the database as:

SQL> desc DBMS_SERVER_ALERT
FUNCTION EXPAND_MESSAGE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 USER_LANGUAGE                  VARCHAR2                IN
 MESSAGE_ID                     NUMBER                  IN
 ARGUMENT_1                     VARCHAR2                IN
 ARGUMENT_2                     VARCHAR2                IN
 ARGUMENT_3                     VARCHAR2                IN
 ARGUMENT_4                     VARCHAR2                IN
 ARGUMENT_5                     VARCHAR2                IN
PROCEDURE GET_THRESHOLD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 METRICS_ID                     BINARY_INTEGER          IN
 WARNING_OPERATOR               BINARY_INTEGER          OUT
 WARNING_VALUE                  VARCHAR2                OUT
 CRITICAL_OPERATOR              BINARY_INTEGER          OUT
 CRITICAL_VALUE                 VARCHAR2                OUT
 OBSERVATION_PERIOD             BINARY_INTEGER          OUT
 CONSECUTIVE_OCCURRENCES        BINARY_INTEGER          OUT
 INSTANCE_NAME                  VARCHAR2                IN
 OBJECT_TYPE                    BINARY_INTEGER          IN
 OBJECT_NAME                    VARCHAR2                IN
PROCEDURE SET_THRESHOLD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 METRICS_ID                     BINARY_INTEGER          IN
 WARNING_OPERATOR               BINARY_INTEGER          IN
 WARNING_VALUE                  VARCHAR2                IN
 CRITICAL_OPERATOR              BINARY_INTEGER          IN
 CRITICAL_VALUE                 VARCHAR2                IN
 OBSERVATION_PERIOD             BINARY_INTEGER          IN
 CONSECUTIVE_OCCURRENCES        BINARY_INTEGER          IN
 INSTANCE_NAME                  VARCHAR2                IN
 OBJECT_TYPE                    BINARY_INTEGER          IN
 OBJECT_NAME                    VARCHAR2                IN
FUNCTION VIEW_THRESHOLDS RETURNS THRESHOLD_TYPE_SET

Using the VIEW_THRESHOLDS function shows:

SQL> select DBMS_SERVER_ALERT.VIEW_THRESHOLDS() from dual;
DBMS_SERVER_ALERT.VIEW_THRESHOLDS()(OBJECT_TYPE, OBJECT_NAME, METRICS_ID, INSTAN
--------------------------------------------------------------------------------
THRESHOLD_TYPE_SET(THRESHOLD_TYPE(5, NULL, 9001, 'database_wide', 1, 3, '0', 3,
'0', 1, 1, 2147483647), THRESHOLD_TYPE(1, NULL, 2018, 'beta4', 1, 4, '100', NULL
, NULL, 1, 2, 0), THRESHOLD_TYPE(1, NULL, 2119, 'beta4', 1, 0, '90', 0, '97', 1,
 3, 0), THRESHOLD_TYPE(1, NULL, 2104, 'beta4', 1, 0, '1200', NULL, NULL, 1, 3, 0
), THRESHOLD_TYPE(9, NULL, 4000, 'beta4', 1, 0, '0', NULL, NULL, 1, 15, 0), THRE
SHOLD_TYPE(4, 'Administrative', 1000, 'beta4', 1, 0, '10', NULL, NULL, 1, 3, 416
6625743), THRESHOLD_TYPE(4, 'Administrative', 1001, 'beta4', 1, 0, '30', NULL, N
ULL, 1, 3, 4166625743), THRESHOLD_TYPE(4, 'Application', 1000, 'beta4', 1, 0, '1
0', NULL, NULL, 1, 3, 4217450380), THRESHOLD_TYPE(4, 'Application', 1001, 'beta4
', 1, 0, '30', NULL, NULL, 1, 3, 4217450380), THRESHOLD_TYPE(4, 'Cluster', 1000,
 'beta4', 1, 0, '30', NULL, NULL, 1, 3, 3871361733), THRESHOLD_TYPE(4, 'Cluster'
, 1001, 'beta4', 1, 0, '50', NULL, NULL, 1, 3, 3871361733), THRESHOLD_TYPE(4, 'C
ommit', 1000, 'beta4', 1, 0, '30', NULL, NULL, 1, 3, 3386400367), THRESHOLD_TYPE
(4, 'Commit', 1001, 'beta4', 1, 0, '50', NULL, NULL, 1, 3, 3386400367), THRESHOL
D_TYPE(4, 'Concurrency', 1000, 'beta4', 1, 0, '10', NULL, NULL, 1, 3, 3875070507
), THRESHOLD_TYPE(4, 'Concurrency', 1001, 'beta4', 1, 0, '30', NULL, NULL, 1, 3,
 3875070507), THRESHOLD_TYPE(4, 'Configuration', 1000, 'beta4', 1, 0, '10', NULL
, NULL, 1, 3, 3290255840), THRESHOLD_TYPE(4, 'Configuration', 1001, 'beta4', 1,
0, '30', NULL, NULL, 1, 3, 3290255840), THRESHOLD_TYPE(4, 'Network', 1000, 'beta
4', 1, 0, '10', NULL, NULL, 1, 3, 2000153315), THRESHOLD_TYPE(4, 'Network', 1001
, 'beta4', 1, 0, '30', NULL, NULL, 1, 3, 2000153315), THRESHOLD_TYPE(4, 'Other',
 1000, 'beta4', 1, 0, '10', NULL, NULL, 1, 3, 1893977003), THRESHOLD_TYPE(4, 'Ot
her', 1001, 'beta4', 1, 0, '30', NULL, NULL, 1, 3, 1893977003), THRESHOLD_TYPE(5
, NULL, 9000, 'database_wide', 1, 4, '85', 4, '97', 1, 1, 2147483647))

Checking default values in DBA_THRESHOLDS shows

SQL> desc DBA_THRESHOLDS
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 METRICS_NAME                                       VARCHAR2(64)
 WARNING_OPERATOR                                   VARCHAR2(12)
 WARNING_VALUE                                      VARCHAR2(256)
 CRITICAL_OPERATOR                                  VARCHAR2(12)
 CRITICAL_VALUE                                     VARCHAR2(256)
 OBSERVATION_PERIOD                                 NUMBER
 CONSECUTIVE_OCCURRENCES                            NUMBER
 INSTANCE_NAME                                      VARCHAR2(16)
 OBJECT_TYPE                                        VARCHAR2(64)
 OBJECT_NAME                                        VARCHAR2(513)
 STATUS                                             VARCHAR2(7)
SQL> set pages 5000
SQL> set lines 80
SQL>
SQL> col metrics_name for a35
SQL> col warning_value for a7
SQL> col critical_value for a8
SQL> col consecutive_occurrences for 999
SQL>
SQL> SELECT metrics_name
  2  ,      warning_value
  3  ,      critical_value
  4  ,      consecutive_occurrences
  5  FROM   DBA_THRESHOLDS
  6  where  instance_name='database_wide'
  7  and    status='VALID'
  8  ;
METRICS_NAME                        WARNING CRITICAL CONSECUTIVE_OCCURRENCES
----------------------------------- ------- -------- -----------------------
Tablespace Bytes Space Usage        0       0                              1
Tablespace Space Usage              85      97                             1
2 rows selected.

Conclusion

Oracle 11g now allows us to encrypt on tablespace level. I think this is a very welcome asset, one that DBA people will like: it will make Transparent Encryption easier to maintain. Be aware that the alerting mechanism, via DBMS_SERVER_ALERT, has more features then described in this small post. Applying them can make life easier. In general, features come with a cost and you will have to decide if they are appropriate for your situation. For example, the encryption and decryption process will spend CPU time.

Disclaimer

The information demonstrated and shared here is based on Oracle beta software. The following is intended to outline Oracle’s general product direction.  It is intended for information purposes only, and may not be incorporated into any contract.  It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.  The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.