In Oracle Database 10g Release 2, Oracle introduced the new transparent data encryption (TDE) feature, which lets you easily encrypt a column’s data in a table.The encryption is “transparent” because the Oracle database takes care of all the encryption and decryption details, with no need for you to manage any tables or triggers to decrypt data.Now, in Oracle Database 11g, you can encrypt an entire tablespace by simply using a pair of special clauses during table space creation.
Oracle has been gradually improving its encryption capabilities over the years.In Oracle 8i,Oracle introduced the DBMS_ OBFUSCATION_ TOOLKITpackage and in the Oracle 10.1 release introduced the DBMS_CRYPTOpackage to facilitate encryption.Both the toolkit and the DBMS_CRYPTOpackage required the appli cation to manage the encryption keys and call the API to perform the necessary encryption/decryption operations.
Prior to Oracle Database 11g,encryption was limited to a column or multiple columns of a table. When entering data in an encrypted column,Oracle trans parent data encryption automatically encrypts the data.When the data is selected out of the table, it is automatically decrypted.Oracle Database 11g adds the functionality to encrypt a complete table space.This breaks the boundary of encryption at the column.Tablespace encr yption also relies on encryption keys in a wallet outside the database.
When you encrypt a column,you have to be concerned about performance implications.The higher the level of encryption, the more CPU intensive the encryption and decryption function.Oracle supports up to AES 256-bitencryption.
Tablespace encryption eliminates the need for granular analysis of applications to determine which columns are candidates for encryption. You can use tablespace encryption to encrypt entire tables and associated indexes by moving the objects into an encrypted tablespace.The data from the encrypted tablespace is transparently decrypted at runtime.
Tablespace encryption will also encrypt objects that are stored as LOBsincluding BLOBs and CLOBs.However, if you create a table with a BFILE,it will encrypt all other columns except for the BFILE since BFILEs are stored outside the database.Similar to BFILEs,external tablesare not encrypted.
Certain situations in column-level encryption prohibit its use.For example,you cannot encrypt a column that participates in an index scan.When you encrypt a column(s) for a table,there are limitations on certain queries. For example,in Oracle Database10g,you cannot encrypt a column if it is part of a foreign key or used in another database.Encrypting the entire tablespace re moves some of these restrictions.The following are additional considerations to usingtablespace-level encryption:
Creating the Oracle Wallet
An Oracle Wallet is a container to store authentication and signing creden tials.The tablespace encryption feature relies on the Oracle Wallet to store and protect the master key used in the encryption.There are two kinds of Oracle Wallets—encryption wallets and auto-open wallets.You must manually open an encryption wallet after database start-up, whereas the auto-open wallet automatically opens upon database start-up.The encryption wallet is commonly recommended for tablespace encryption. However, if you are dealing with unattended Data Guard environments, the automatic opening of the wallet comes in handy.
The Oracle Wallet, named ewallet.p12 under both Windows and Unix/Linux-based systems,is a file in your directory system.The location where Oracle stores this file is operating system specific.However, you can specify a nondefault location by using the parameter encryption_wallet_location in the sqlnet.ora file,as shown here:ENCRYPTION_WALLET_LOCATION =
To use TDE,you must have the ALTER SYSTEM privilege as well as a password for an Oracle Wallet. If you do not have an Oracle Wallet,you must create one and then add a master key to it.You can create the Oracle Wallet in several ways:
TDE requires an Oracle Wallet encryption key be established and opened. Because the wallet is the essence of TDE, we will take you on a little tour to create a key and open it for use for the database.The easiest way to create a wallet is by using the ALTER SYSTEM command, which is the method we use here:SQL>alter system set encryption key identified by "clidba123"; System altered.
The alter system statement shown here both creates the wallet if it does not already exist and adds a master key to it.You must replace the password with your own password for the wallet.The ALTER SYSTEM statement you issued in the previous example works in the following way:
Since we created an encryption wallet,we must re-open the wallet after database start-up.To open the wallet (ewallet.p12) after database start-up, you can issue the following command:alter system set wallet open identified by "clidba123";
Please remember that since the wallet was created with the double quotes around it, it needs to be opened with double quotes. Otherwise, you will receive the ORA- 28353 (“failed to open wallet”) error.To verify that a wallet is open, you can query the V$ENCRYPTION_WALLET view:SQL>select wrl_parameter, status from v$encryption_wallet
Encrypted Tablespace Caveats
There are several caveats to encrypted tablespaces.You cannot encrypt an existing tablespace. You can encrypt a new tablespace only at creation time.If you want to encrypt an existing table space, you must move the objects into a newly created encrypted tablespace using the various options such as CTAS, ALTER TABLE MOVE,exp/imp,online data redefinition, or expdp/impdp.Table space-level encryption makes it transparent to even developers.Deve lopers do not have to be concerned about how to encrypt a column or what is required to maintain a high-performance data encryption environment.
If you are upgrading from Oracle Database 10g Release 2 to Oracle Database 11g and have set the compatibility mode to 11.1.0, you need to reissue the ALTER SYSTEM SET ENCRYPTION KEY command to create a master encryption key for tablespace encryption.This command will re-create the standard TDE master key and overwrite the existing one if one already exists.It also creates a new tablespace master encryption key.If the tablespace key already exists in the wallet folder,the ALTER SYSTEM SET ENCRYPTION KEY command will not rekey it.
Additional restrictions apply to encrypted tablespaces:
Encrypting a Tablespace
Once you create the Oracle Wallet,creating an encrypted tablespace is a breeze.The tablespace creation statement for an encrypted tablespace has the following syntax:create tablespace <tbsp_name> encryption default storage(encrypt)
The encryption clause in the second line does not actually encrypt the tablespace.You merely provide the encryption properties by setting values for the keyword encryption.You may use the using clause to specify the name of the encryption algorithm you want to use.The supported encryption algorithms are as follows:
You can use the default algorithm of AES128,in which case you can omit the using clause altogether.The encrypt keyword is passed to the storage clause in the third line that performs the actual encryption of the tablespace.In the following sections,we review how to encrypt a tablespace.
The following is an example showing how to create a simple encrypted table space that uses the default AES 128-bit encryption. Since you do not have to specify the default encryption level, you do not specify the using clause for the encryption clause in the third line.SQL> create tablespace docs_encrypted_default
Next,we will create an encrypted tablespace with the AES 192-bit encryption .Notice the keywords encryption using 'AES192' default storage (encrypt) in the following example:SQL> create tablespace docs_d_e
Now all objects created in the docs_d_e tablespace will be encrypted.The beauty behind encrypted tablespaces is that the encryption consideration just becomes an object placement consideration.By designing an encrypted table space,the DBAs can place objects into the encrypted tablespace and be confi dent that the data is encrypted.Furthermore, both DBAs and data architects can collaborate and place objects into the encrypted tablespaces during the design phase.
Upon creating the encrypted tablespace,you can query the DBA_TABLESPACES view to review the encryption state. Oracle provides a new column called ENCRYPTED in the DBA_TABLESPACES view to reveal whether a tablespace is enc rypted.In the following query, we are identifying the encryption attributes for tablespaces that start with the name DOC:1 select tablespace_name,encrypted
Another provided view called V$ENCRYPTED_TABLESPACES reveals the encryption attributes of encrypted tablespaces:SQL> desc V$ENCRYPTED_TABLESPACES
You can join this view with the V$TABLESPACE view based on the ts# column to determine which tablespace is encrypted and which encryption algorithm is being used.The following query produces output of all encrypted tablespaces including the encryption algorithm information:SQL> select vt.name,vet.encryptionalg, vet.encryptedts
Oracle 11g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 11g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 11g Tutorial
Installing, Upgrading, And Managing Change
Database Diagnosability And Failure Repair
Backup And Recovery
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.