OCM Tips – Create Encrypted Tablespace
In the first section of the OCM exam (Server Configuration), there is a topic “1.3. Create and manage temporary, permanent, and undo tablespaces”. I have provided a very detailed information in the book on this topic and covered the following subjects:
• Create Permanent tablespace
• Create Compressed Tablespace
• Create Encrypted Tablespace
• Create Temporary Tablespace
• Create temporary tablespace group
• Specify Nonstandard block size for tablespace
• Modify Tablespace (OFFLINE, ONLINE, READ-ONLY, ALTER TABLESPACE, SHRINK, RENAME, DROP)
• Managing UNDO tablespace
In this newsletter I will show you how to create an encrypted tablespace.
In the beginning of each topic of the exam a reference from the documentation or the metalink note is provided. Check the following documentation to get more information on creating an encrypted tablespace during the exam:
Database Advanced Security Administrator’s Guide -> 8 Securing Stored Data Using Transparent
Data Encryption -> 8.2.5 Encrypting Entire Tablespaces
Any permanent tablespace can be encrypted to protect sensitive data. Tablespace encryption is completely transparent to your applications, and hence no application modification is required.
It’s not possible to specify encryption for undo and temporary tablespaces, as there is no need to do so, because data from the encrypted tablespace is automatically encrypted even when written to the online redo log files, undo tablespace and temporary tablespace to maximize the security.
Before attempting to create an encrypted tablespace, a wallet must be created to hold the encryption key, otherwise the following error will pop up “ORA-28365: wallet is not open”.
For this, create a wallet folder and change sql.net ora file as follows:
[oracle@ocm11g ~]$ cd $ORACLE_HOME [oracle@ocm11g db_1]$ mkdir wallet [oracle@ocm11g db_1]$ cd wallet/ [oracle@ocm11g wallet]$ pwd /home/oracle/product/11.2.0/db_1/wallet [oracle@ocm11g wallet]$ cd $ORACLE_HOME/network/admin [oracle@ocm11g wallet]$ vi sqlnet.ora ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=file) (METHOD_DATA= (DIRECTORY=/home/oracle/product/11.2.0/db_1/wallet)))
Enter the following command to protect the Oracle wallet:
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “mywallet”; System altered. SQL>
The following error will pop up if you run the above command without changing sqlnet.ora file:
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “mywallet”; ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “mywallet” * ERROR at line 1: ORA-28368: cannot auto-create wallet SQL>
Now, create encrypted tablespace as follows:
SQL> CREATE TABLESPACE encrypted_tbs DATAFILE ‘/home/oracle/oradata/encrypted_tbs01.dbf’ SIZE 100M ENCRYPTION USING ‘AES256’ DEFAULT STORAGE(ENCRYPT); Tablespace created. SQL>
After creating an encrypted tablespace, check V$ENCRYPTED_TABLESPACES view:
SQL> SELECT tbs.name, tbs_en.encryptionalg encr_algorithm FROM v$tablespace tbs, v$encrypted_tablespaces tbs_en WHERE tbs.ts# = tbs_en.ts#; NAME ENCR_ALGORITHM ------------------------------ --------- ENCRYPTED_TBS AES256
If you want to get more information on creating and managing tablespaces, get my book from the following link: