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.
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).
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”).
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.
Back in the “tablespace” section I checked the “Encryption” check box.
Always interested in the manual stuff, before I clicked on the OK-button, I checked “Show SQL”.
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.
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…
So I clicked on the “Thresholds” link and was presented with the following screen.
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.
Setting both thresholds on value “disabled” gave me the following SQL statements.
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.
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.
