Encrypt Your Data Assets

Encrypt Your Data Assets By Arup Nanda Build a flexible infrastructure to protect sensitive data. John, the lead DBA at Acme Bank, is involved in a very important initiative regarding security and privacy. Jane, the chief information security officer of the company, has outlined Acme's security strategy, and John has identified his team's responsibilities. Enterprise security, Jane explains at a meeting with IT managers at Acme, can be thought of as a series of protective layers. To illustrate her point, Jane uses a nesting doll—a doll with a hollow body inside which another hollow doll is kept, which, in turn, contains another hollow doll, and so on. The last of four or five of these hollow dolls contains some type of prize. Getting to the prize means removing the layers of the dolls one by one, and if the layers can't be removed for some reason, the prize becomes increasingly difficult to get to. To get to corporate information assets, Jane explains, an intruder must also defeat many layers of security. The first layer of defense is the firewall around the whole information infrastructure of the organization, which keeps outsiders from accessing any of the information sources inside the company. However, no organization is an island and firewalls are far from airtight; "holes" or ports are necessary to let legitimate traffic from outside flow in. If an intruder gets past the external firewall, that person will be required to supply a password to access the server or perhaps be asked to provide other authentication credentials such as security certificates. This is the next layer of security. After being authenticated, the legitimate user must be allowed to access only those assets that person is supposed to access. If a user gets into the database but has no authority to see any table, view, or any other data source, the information is still protected. This mechanism is the next layer of security. Jane stresses that it is possible for an intruder to somehow defeat all of the protective measures and get to the enterprise data. From a planning perspective, this possibility must be accepted, analyzed, and accounted for. The only option left for defending against an intruder at this point, the last layer of security, is to alter the data, via a process known as encryption, in such a way that the intruder will not find it useful. Encryption alters data to make it unreadable to all except those who know how to decipher the information.

Database Encryption When John leaves the IT managers' meeting, he immediately calls together his direct reports to talk about his team's encryption strategy and implementation. He presents his take on the encryption strategy to his team, beginning with a brief overview of the encryption process. He presents a simple example in which the account balance value is altered by the addition of a secret one-digit number. If the secret number is 2, for example, and the real balance value is 3467, the encrypted value will be 3469. The real value can be deciphered from the encrypted value by the deduction of the number 2, a process known as decryption, John explains. This logic of adding a specific number to the real data is called the encryption algorithm. Here, the value 2, which is added by the algorithm, is known as the encryption key. Encrypting a value involves passing the original data and the encryption key to the encryption algorithm to create encrypted data, as shown in Figure 1.

Figure 1: Encryption mechanism During decryption, the logic is reversed, producing the original value. Because the same key is used to encrypt and decrypt, this scheme is also known as symmetric encryption. Encryption algorithms are most often in the public domain; hence, the security lies in choosing a difficult-toguess key. If hackers were to guess the 1-digit key, in this example, they would have to take only as many as 10 guesses—a number from 0 to 9. However, if the key were two digits, they would have to take as many as 100 guesses—a number from 0 to 99. The longer the key, John explains, the more difficult it is to guess it.

Oracle-Supplied Packages In Oracle Database 10g, John continues, users can implement these encryption techniques by using functions and procedures available in a built-in package named DBMS_CRYPTO. Another package, DBMS_OBFUSCATION_TOOLKIT, also available in Oracle Database 10g and earlier releases, offers a subset of the functionality provided by DBMS_CRYPTO. But because Acme Bank's systems are built on Oracle Database 10g, the newer package offers more functionality, and Oracle recommends it over the older toolkit, John decides to use the DBMS_CRYPTO package, and no one in the room disagrees. Key Generation. Because the security of the encrypted value depends on how hard it is to guess the key, using an appropriate key is a major step in the encryption process. A key can be any value of data type RAW, but unless it is random enough, an intruder will be able to guess the key. For instance, John warns, the key can't be something such as your pet's name or your date of birth; it must be truly random. "How do you generate such a random key?" asks one junior DBA. John answers that random values can be generated with the built-in package DBMS_RANDOM, but true cryptographically acceptable randomness means using the function RANDOMBYTES in the package DBMS_CRYPTO. This function accepts one parameter of data type BINARY_INTEGER and produces a RAW value of that length. This value can then be used as a key. John demonstrates the usage with a simple PL/SQL routine, shown in Listing 1. Code Listing 1: Encrypting a value 1 2 3 4 5 6 7 8

declare enc_val l_key l_key_len l_mod begin

raw (2000); raw (2000); number := 128; number := dbms_crypto.ENCRYPT_AES128 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5;

9 10 11 12 13 14 15 16 17

l_key := dbms_crypto.randombytes (l_key_len); enc_val := dbms_crypto.encrypt ( UTL_I18N.STRING_TO_RAW ('SECRET', 'AL32UTF8'), l_mod, l_key ); -- The encrypted value enc_val can be used here end;

Explaining the code, John points out that the DBMS_CRYPTO package offers several types of algorithms and associated key lengths for Acme's encryption project (see Table 1).

Table 1: Constants for algorithms in DBMS_CRYPTO The first column in Table 1—Constant Name—shows the constant defined in the package to specify the different algorithms and the key length. For instance, to specify a 128-bit key according to the Advanced Encryption Standard (AES), you use the constant DBMS_CRYPTO.ENCRYPT_AES128, John explains (see line 5 of Listing 1). The longer the key, the less the chance that an intruder will be able to guess it but the more work the server has to do during encryption and decryption. To strike a balance between security and stress on the server, John chooses the middle ground—a 128-bit-key AES algorithm. Next, the type of chaining, which divides the data into chunks to prepare for encryption in block ciphering, is defined, as shown in Listing 1, line 6. The most common format is Cipher Block Chaining (CBC), specified by a constant defined in the DBMS_CRYPTO package as CHAIN_CBC. Other chaining options include Electronic Code Book format (CHAIN_ECB), Cyber Feedback (CHAIN_CFB), and Output Feedback (CHAIN_OFB). Finally, in block ciphering, John explains, the data is usually encrypted in blocks of eight characters. If the length of the input data is not a multiple of eight, you add a character or characters, in a process known as padding. A simple option is to use zeroes as padding. John points out that the constant PAD_ZERO defined in the DBMS_CRYPTO package pads with zeroes but that padding with zeroes is not considered very secure, because a potential intruder might be able to guess that. More-secure padding is based on Public-Key Cryptography Standards # 5 (PCKS#5), specified by the constant PKCS5 in the DBMS_CRYPTO package and demonstrated in Listing 1, line 7. If you're sure, John comments, that the length of the data is already a multiple of the block size, there is no need to pad, and you can specify that by using the constant PAD_NONE. These three parameters—the algorithm with the key length, the chaining method, and the padding method— are all combined and passed to the built-in function ENCRYPT in DBMS_CRYPTO. The ENCRYPT function needs

this input value to be a RAW data type. Listing 1, line 12 converts the input value to a RAW value, which is then passed to the ENCRYPT function. As a means of standardization, John continues, Acme has made a decision to adopt the AES algorithm with 128-bit keys, CBC chaining, and PCKS #5 padding across all applications. Using these values, John builds a simpler function GET_ENC_VAL, shown in Listing 2, that accepts only two parameters—the input value and the key—and returns the encrypted value. Code Listing 2: A simple encryption function 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20*

create or replace function get_enc_val ( p_in in varchar2, p_key in raw ) return raw is l_enc_val raw (2000); l_mod number := dbms_crypto.ENCRYPT_AES128 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5; begin l_enc_val := dbms_crypto.encrypt ( UTL_I18N.STRING_TO_RAW (p_in, 'AL32UTF8'), l_mod, p_key ); return l_enc_val; end;

Decryption "When the time comes to decipher the encoded data, how do we do it?" asks Jill, one of John's developers. John explains that DBMS_CRYPTO has a function for that too: DECRYPT. It accepts the source data to be decrypted; the key used during encryption; and the combined parameter of algorithm, key length, chaining, and padding schemes. The same key and the modifiers used during encryption must be passed along with the value to be decrypted. Because Acme Bank uses a standard algorithm, key length, and padding, John creates a simple function to decrypt encrypted values, as shown in Listing 3. This function accepts only two parameters —the encrypted value and the key—and returns the decrypted value as a VARCHAR2 data type. The conversion from the RAW data type is done in line 20 of Listing 3. Code Listing 3: A simple decryption function 1 2 3 4 5 6 7 8 9 10

create or replace function get_dec_val ( p_in in raw, p_key in raw ) return varchar2 is l_ret varchar2 (2000); l_dec_val raw (2000); l_mod number := dbms_crypto.ENCRYPT_AES128

11 + dbms_crypto.CHAIN_CBC 12 + dbms_crypto.PAD_PKCS5; 13 begin 14 l_dec_val := dbms_crypto.decrypt 15 ( 16 p_in, 17 l_mod, 18 p_key 19 ); 20 l_ret:= UTL_I18N.RAW_TO_CHAR 21 (l_dec_val, 'AL32UTF8'); 22 return l_ret; 23* end;

Key Management With the building blocks in place, John's team is looking for a complete encryption solution built on the DBMS_CRYPTO package. The biggest challenge in encryption, John explains, is not generating keys or using the functions but managing the keys used in the encryption process. Because the same key is used to encrypt and decrypt a value, it must be reliably guarded to protect the data. At the same time, however, the applications and users must have access to the keys to decrypt the values for normal use. The challenge is figuring out where to store the keys and how to make sure they are available to legitimate users only, John explains. He lays out two options for managing keys: 1. Use the same key for all records 2. Use a different key for each record With option 1, John continues, a single key is used to encrypt the value in all the rows. In this case, there are several options for storing the key: •

•

•

In the database—A key table owned by a special non-application owner can be used to store the key. John writes a simple function that merely returns the key as an output parameter. The users receive execute privileges in this procedure, and no user has any privileges on the key table. The function contains several checks and balances to make sure users have the proper privileges to get the key. Because the function is the only source for getting the key, users can be authenticated easily and given access to the key. In the filesystem—Keeping the key in the database protects from most intruders but not from DBAs who may have access to any table. In addition, making sure users making requests are indeed legitimate can be very difficult. Storing the key in a filesystem DBAs don't have access to, even on a different server such as an application server, may be a better idea. However, this also means that if the key is somehow lost because filesystems are damaged, the encrypted data is lost as well, forever. With the user—A third option, John shows, can be to let users keep the key somewhere, such as on a memory stick or a client machine. This way, no one other than legitimate users can access the sensitive data. This is particularly useful in situations such as in data warehouses where encrypted data is sent regularly to users who have the key already. If the data is stolen along the way, the sensitive information is still protected. However, the risk of data loss is highest here, because users are more likely to lose the key.

John proposes the "With the user" single-key solution for a small number of cases, such as publishing summarized content to various users who have received the keys earlier.

The biggest drawback of this approach is the vulnerability of the key to theft. If the key is stolen, all the data in the database is compromised. Therefore, John suggests a different approach to protecting sensitive data in OLTP databases. The database has a table named ACCOUNT_MASTER, where a sensitive data element, the account holder's name, is stored. The column containing the name, ACC_NAME, needs to be encrypted. The primary key of the table is ACCOUNT_NO. Here is what the ACCOUNT_MASTER table looks like: SQL> desc account_master Name ---------ACCOUNT_NO ACC_NAME ACC_TYPE

Null? -------NOT NULL

Type -----------NUMBER VARCHAR2(200) CHAR(1)

John suggests using a different key for each row of the ACCOUNT_MASTER table, which eliminates the risk of databasewide exposure in case of a key theft. He creates a table called ACCOUNT_MASTER_ENC to store the encrypted values of the account name and creates another table to hold the keys used to encrypt the values. These tables—ACCOUNT_MASTER_ENC and ACCOUNT_MASTER_KEYS—look like this: SQL> desc account_master_enc Name -----------ACCOUNT_NO ACC_NAME_ENC

Null? -------NOT NULL

Type -------NUMBER RAW(2000)

SQL> desc account_master_keys Name ---------ACCOUNT_NO KEY

Null? -------NOT NULL NOT NULL

Type --------NUMBER RAW(2000)

Next John creates the view VW_ACCOUNT_MASTER, shown in Listing 4, to join these three tables to get the decrypted value. He points out line 8 in Listing 4, where the value is decrypted with the function GET_DEC_VAL mentioned earlier. Because the function returns a value as a VARCHAR2 data type, it will be shown as a VARCHAR2(2000) column; hence, line 7 has a CAST function to make it a VARCHAR2(20) type. This view, not the table, is what is granted to the other users. John creates a public synonym ACCOUNT_MASTER pointing to the view VW_ACCCOUNT_MASTER, not to the table of the same name. Code Listing 4: View for account master 1 2 3 4 5 6 7 8 9 10 11 12 13

create or replace view vw_account_master as select m.account_no as account_no, m.acc_type as acc_type, cast ( get_dec_val (e.acc_name_enc, k.key) as varchar2(20)) as acc_name from account_master m, account_master_enc e, account_master_keys k

14 where 15 k.account_no = e.account_no 16* and m.account_no = e.account_no;

Because the public synonym ACCOUNT_MASTER points to the view, users can select from the view, but Jill the developer asks how the users will manipulate the data in the table. By using an INSTEAD OF trigger (shown in Listing 5), explains John. This trigger manipulates the data in the table whenever a user inserts or updates the view. The INSTEAD OF trigger fires when the data is either inserted or updated in the view, which, in turn, does an INSERT or UPDATE on the actual tables. While updating, it makes sure the encrypted values are also updated. Code Listing 5: INSTEAD OF trigger on the view 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

create or replace trigger io_vw_acc_master instead of insert or update on vw_account_master for each row declare l_key raw(2000); begin if (inserting) then l_key := dbms_crypto.randombytes (128); insert into account_master (account_no, acc_type, acc_name) values ( :new.account_no, :new.acc_type, :new.acc_name ); insert into account_master_enc (account_no, acc_name_enc) values ( :new.account_no, get_enc_val ( :new.acc_name, l_key ) ); insert into account_master_keys (account_no, key) values ( :new.account_no, l_key ); else select key into l_key from account_master_keys where account_no = :new.account_no; update account_master set acc_name = :new.acc_name, acc_type = :new.acc_type where account_no = :new.account_no; update account_master_enc set acc_name_enc = get_enc_val (:new.acc_name, l_key) where account_no = :new.account_no;

47 end if; 48* end;

Transparent Data Encryption Encryption is a topic that evokes a mixed reaction in many users: interest coupled with a sense of wariness arising from the perceived complexity of key management, which can render the setup ineffective if not done correctly. There is also performance overhead associated with encrypting and decrypting the values, which makes the process a little less palatable to most application architects. As a result, many systems are designed with no encryption at all but with strong perimeter protection instead, such as strong passwords and proper authorization schemes. However, imagine a situation where the entire server is stolen—or even just the disks, so they can be mounted on a server of the same OS and then cleaned of data. Or, consider the case of a rogue DBA who penetrates perimeter protection in the daily course of business and then downloads sensitive customer information. In both cases, the businesses involved, if located in the state of California (and perhaps in other U.S. states shortly), would be legally obligated to notify all affected customers of a security breach. In those rare (but certainly realistic) cases, the authentication scheme is moot. That's why transparent data encryption (TDE) is such a valuable feature for organizations that make security a top priority; it supports encryption while putting the complexity of key management in the hands of the database engine. At the same time, it lets DBAs manage database tables without actually having to see the data. Using TDE in Oracle Database 10g Release 2, you can encrypt one or more columns of a table right out of the box; all you have to do is define the column as encrypted, without writing a single line of code. Remember, encryption requires a key and an algorithm to encrypt an input value. TDE generates a single key for a specific table. Because this approach makes key management simpler but more susceptible to theft, there is another key—a master key—that can be set at the database level. The table key is encrypted with the master key, which is required to obtain the table key. Consequently, the master key as well as the table key are required to decrypt a column. (For a more detailed discussion about encryption generally and the use of supplied packages in Oracle in particular, see my Oracle Magazine column "Encrypt Your Data Assets.") The master key is stored outside the database in a location known as a "wallet"—by default in $ORACLE_BASE/admin/$ORACLE_SID/wallet. Conceptually, it looks like the figure below.

After TDE is configured—or more specifically the wallet and the master key are configured—you can use it to protect data values. To encrypt a column of a table, you would use the following SQL: create table accounts ( acc_no number not first_name varchar2(30) not last_name varchar2(30) not SSN varchar2(9) acc_type varchar2(1) not folio_id number sub_acc_type varchar2(30), acc_open_dt date not acc_mod_dt date, acc_mgr_id number )

null, null, null, null,

ENCRYPT USING 'AES128', ENCRYPT USING 'AES128',

null,

Here you have used TDE on the columns SSN and FOLIO_ID, which are now stored in encrypted manner on the table itself. However, when a user selects from the table, she sees the data in clear text because the decryption is performed during retrieval. If the disks are stolen, the information contained in the table segments remain encrypted. The thief needs the table key to see the encrypted value, but to get that he needs the master key, which is externally stored and hence unavailable. Note the clauses after the columns SSN and FOLIO_ID, which specify ENCRYPT using the 128-bit Advanced Encryption Standard. To set the wallet password, use the command:

alter system set encryption key authenticated BY "topSecret";

This command creates the wallet, if not created already, and then sets the password to "topSecret" (case sensitive). Then you can start using encryption in column definitions during table creation and modification. Encrypting External Tables In the above example, I used a hash table to encrypt columns. You can also use TDE on external tables. For instance, if you want to generate a dump file containing the data from ACCOUNTS for shipping to a different location, you can use the simple ENCRYPT clause.

create table account_ext organization external ( type oracle_datapump default directory dump_dir location ('accounts_1_ext.dmp', 'accounts_2_ext.dmp', 'accounts_3_ext.dmp', 'accounts_4_ext.dmp') ) parallel 4 as select ACC_NO, FIRST_NAME, LAST_NAME, SSN ENCRYPT IDENTIFIED BY "topSecret", ACC_TYPE, FOLIO_ID ENCRYPT IDENTIFIED BY "topSecret", SUB_ACC_TYPE, ACC_OPEN_DT, ACC_MOD_DT from accounts;

In the files accounts_*_ext.dmp, the values of SSN and FOLIO_ID will not be clear text, but encrypted. If you want to use these files as external tables, you have to supply the password as topSecret to read the files. As you can see here, TDE is a highly desirable complement to (not a substitute for) access control.

Query XML in SQL XML has long been a de-facto standard for datatype of many applications involving large character content. Recently it has also become a storage layout for other applications, not limited to large content only. Oracle has provided XML integration with the database since Oracle9i Database. In that release, you could query XML content using many different methods. In Oracle Database 10g Release 2, new XQuery and XMLTable functions make it even easier to query XML contents. (Note: A thorough discussion of the XQuery specification is beyond the bounds of this article; for background, read the Oracle Magazine article "XQuery: A New Way to Search.") XQuery First, let's examine the simpler of the two methods: XQuery. Here's an example: SQL> xquery 2 for $var1 in (1,2,3,4,5,6,7,8,9) 3 let $var2 := $var1 + 1 4 where $var2 < 6 5 order by $var2 descending 6 return $var2 7 / Result Sequence -----------------5 4 3 2

The new SQL command xquery indicates an XQuery command. Note the command carefully: The new syntax simulates the FOR ... IN ... inline view introduced in Oracle9i Database. The general structure of an XQuery is described by the acronym FLOWR (pronounced "flower"), which stands for FOR, LET, ORDER BY, WHERE and RETURN. In the above example, we see that the line 2

defines the source of the data, which is a series of numbers from 1 to 9. This could be any source—a bunch of scalar values or an element of an XML data, specified by the FOR clause. The line also specifies a variable to go hold these values (var1). In line 3, another variable var2 holds the value of var1 added with 1, specified with the LET clause. For all these values returned, we are interested in only those below 6, which is specified by the clause WHERE. Then we sort the result set on the var2 value in a descending manner, shown as ORDER BY clause in line 6. Finally the values are returned to the user with the RETURN clause. If you were to compare the syntax to the regular SQL syntax, RETURN, FOR, WHERE, and ORDER BY would be analogous to SELECT, FROM, WHERE, and ORDER BY. The LET clause has no SQL analogy but it's something specified in the other clauses. Let's look at a practical example of this powerful new tool in action. First, create a table to hold the communication details with an account holder. create table acc_comm_log ( acc_no number, comm_details xmltype );

Now, insert some records into it.

insert into acc_comm_log values ( 1, xmltype( ' EMAIL 3/11/2005 Dear Mr Smith ') ) / insert into acc_comm_log values ( 2, xmltype( ' LETTER 3/12/2005 Dear Mr Jackson ') ); insert into acc_comm_log values ( 3, xmltype( ' PHONE 3/10/2005 Dear Ms Potter ') );

Now you can see what records are in the table: SQL> l 1 select acc_no, 2 XMLQuery(

3 'for $i in /CommRecord 4 where $i/CommType != "EMAIL" 5 order by $i/CommType 6 return $i/CommDate' 7 passing by value COMM_DETAILS 8 returning content) XDetails 9 from acc_comm_log 10 / ACC_NO ---------1 2 3

XDETAILS -----------------------------3/12/2005 3/10/2005

XMLTable

The other function, XMLTable, has a similar purpose but returns the columns as if it were a regular SQL query. Here it is in action. 1

select t.column_value 2 from acc_comm_log a, 3 xmltable ( 4 'for $root in $date 5 where $root/CommRecord/CommType!="EMAIL" 6 return $root/CommRecord/CommDate/text()' 7 passing a.comm_details as "date" 8* ) t SQL> / COLUMN_VALUE --------------------3/12/2005 3/10/2005

This example illustrates how you can use regular SQL statements against an XML table returned by the XML query. The queries follow the very structured FLOWR pattern for specifying commands. XQuery versus XMLTable Now that you have seen the two ways you can use XML in a regular SQL query, let's see where you should use each one and under what circumstances. The first method, XQuery, allows you to to get the data in an XMLType, which can be manipulated as XML in any program or application that supports it. In the example you saw, the resultant output of account data is in XML format and you can use any tool, not necessarily relational, to manipulate and display that data. The second method, XMLTable, combines the functionality of regular SQL and XML. The resultant output of the account data is not XML, but relational. Note that the source in both cases is XML, but XQuery presents the data in XML format using XMLType whereas XMLTable presents it as a relational table, which can be manipulated as a regular table. This functionality may work best for existing programs which expect a table, while bringing the power of XML into the mix. XML is quite useful where the exact structure of the data is not well known in advance. In the example above, the communication records are different based on the mode. If it's email, then the attributes could be email address of the recipient, return address, any carbon copies (cc:, bcc:, and so on), the text of the message and so on. If it's a phone call, the attributes are phone number called, the type of number (home, work, cell, and so on), the person answered, the voicemail left, and so on. If you were to design a table that holds all possible types of attributes, it would span across many columns and may become tedious to read. However, if you just have one column as XMLType, then you can cram everything there but still retain the unique attributes of the

communication type. The query can still use a simple SQL interface, making application development a breeze. For more information about Oracle's implementation of XQuery, visit the Oracle XQuery page on OTN.

Enhanced COMMIT When a session commits, the redo log buffer is flushed to the online redo logs on disk. This process ensures that transactions can be replayed from the redo logs if necessary when recovery is performed on the database. Sometimes, however, you may want to trade-off the guaranteed ability to recover for better performance. With Oracle Database 10g Release 2, you now have control over how the redo stream is written to the online log files. You can control this behavior while issuing the commit statement itself, or simply make change the default behavior of the database. Let's see how the commit statement works. After a transaction, when you issue COMMIT, you can have an additional clause: COMMIT WRITE where the is what

influences the redo stream. The option WAIT is the default behavior. For instance,

you can issue: COMMIT WRITE WAIT;

This command has the same effect as COMMIT itself. The commit does not get the control back to the user until the redo stream is written to the online redo log files. If you don't want it to wait, you could issue: COMMIT WRITE NOWAIT;

In this case, the control immediately returns to the session, even before the redo streams are written to the online redo logs. When a commit is issued, the Log Writer process writes the redo stream to the online redo logs. If you are making a series of transactions, such as in a batch processing environment, you may not want it to commit so frequently. Of course, the best course of action is to change the application to reduce the number of commits; but that may be easier said than done. In that case, you could simply issue the following commit statement: COMMIT WRITE BATCH;

This command will make the commit write the redo streams to the log file in batches, instead of at each commit. You can use this technique to reduce log-buffer flushing in a frequent-commit environment. If you want to write the log buffer immediately, you would issue: COMMIT WRITE IMMEDIATE;

If you want a specific commit behavior to be the default for a database, you could issue the following statement. ALTER SYSTEM SET COMMIT_WRITE = NOWAIT;

This command will make this behavior the default across the database. You can also make it at session level: ALTER SESSION SET COMMIT_WORK = NOWAIT;

As with any parameter, the parameter behaves the setting at the system level, if set. If there is a setting at the session level, the session level setting takes precedence and finally the clause after the COMMIT statement, if given, takes precedence. This option is not available for distributed transactions. Catch the Error and Move On: Error Logging Clause Suppose you are trying to insert the records of the table ACCOUNTS_NY to the table ACCOUNTS. The table ACCOUNTS has a primary key on ACC_NO column. It's possible that some rows in ACCOUNTS_NY may violate that primary key. Try using a conventional insert statement: SQL> insert into accounts 2 select * from accounts_ny; insert into accounts * ERROR at line 1: ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated

None of the records from the table ACCOUNTS_NY has been loaded. Now, try the same with error logging turned on. First, you need to create a table to hold the records rejected by the DML statement. Call that table ERR_ACCOUNTS.

exec dbms_errlog.CREATE_ERROR_LOG ('ACCOUNTS','ERR_ACCOUNTS')

Next, execute the earlier statement with the error-logging clause. SQL> 2 3 4 5

insert into accounts select * from accounts_ny log errors into err_accounts reject limit 200 /

6 rows created.

Note that the table ACCOUNTS_NY contains 10 rows yet only six rows were inserted; the other four rows were rejected due to some error. To find out what it was, query the ERR_ACCOUNTS table. SQL> select ORA_ERR_NUMBER$, ORA_ERR_MESG$, ACC_NO 2 from err_accounts; ORA_ERR_NUMBER$ ORA_ERR_MESG$ ACC_NO --------------- -------------------------------------------------- -----1 ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi 9997 olated 1 ORA-00001: unique constraint (ARUP.PK_ACCOUNTS)vi 9998 olated 1 ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi 9999 olated 1 ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi 10000 olated

Note the columns ORA_ERR_NUMBER$, which show the Oracle error number encountered during the DML statement execution, and the ORA_ERR_MESG$, which shows the error message. In this case you can see that four records were rejected because they violated the primary key constraint PK_ACCOUNTS. The table also captures all the column of table ACCOUNTS, including the column ACC_NO. Looking at the rejected records, note that these account numbers already exist in the table; hence the records were rejected with ORA00001 error. Without the error-logging clause, the whole statement would have failed, with no records rejected. Through this clause, only the invalid records were rejected; all others were successful.

Protect the Code at Source: WRAP Package PL/SQL program units often contain very sensitive and confidential information about company procedures and trade secrets, which makes them a protected entity group, similar to tables. To prevent unauthorized viewing of the source code, the programs are often obfuscated using the wrap command line utility. You can invoke wrap only after the PL/SQL script is created; the utility creates a wrapped file from the input clear text. However, in some cases you may want to generate the wrapper dynamically inside PL/SQL code. In such a case, the wrap utility can't be invoked because no source file exists yet. Thankfully, Oracle Database 10g Release 2 provides a supplied package that you can use to create the code in a wrapped format. This package complements, not replaces, the wrap utility. The latter is still appropriate in cases where you want to wrap a large number of source files quickly using a command line option. For instance, imagine that you want to create the simple procedure p1 in wrapped format. create or replace procedure p1 as begin null; end;

Inside the PL/SQL unit, you can create it dynamically but in wrapped format with: begin dbms_ddl.create_wrapped ('create or replace procedure p1 as begin null; end;') end; /

Now you want to confirm the wrapped procedure. You can select the source text from the dictionary. SQL> select text from user_source where name = 'P1';

TEXT ----------------------------------------------------------------procedure p1 wrapped a000000 369 abcd abcd ...and so on ... The first line, procedure p1 wrapped, is confirmation that the procedure was created in wrapped manner. If you get the DDL of the procedure with the DBMS_METADATA.GET_DDL() function, you will still see the source

as wrapped. Sometimes you may have a slightly different requirement; you may decide to generate the PL/SQL code but not create the procedure, for example. In that case, you may save it in a file or table to be executed later. But because the above approach creates the procedure, it won't work here. Rather, you need to call another function in the package: SQL> select dbms_ddl.wrap 2 ('create or replace procedure p1 as begin null; end;') 3 from dual 4 / DBMS_DDL.WRAP('CREATEORREPLACEPROCEDUREP1ASBEGINNULL;END;') ---------------------------------------------------------------------create or replace procedure p1 wrapped a000000 369 abcd abcd ... and so on ... The output of the WRAP function is the wrapped output of the PL/SQL code passed as a parameter. This

parameter can be stored in a flat file or a table and executed later. This comes in handy in situations where you generate the code to be deployed elsewhere and the security of the code cannot compromised in any way. The above approach works fine as long as you can pass the entire text of the stored code as a varchar2 datatype, which is limited to 32K. If the PL/SQL code exceeds 32K, you have to use a slightly different method: accept a collection variable as the input. Here you can use a supplied datatype: varchar2s in the package DBMS_SQL. This is a collection datatype (TABLE OF VARCHAR2), with each element of the table accepting up to 32K of text; you can extend it to as many elements as necessary. Suppose, for example, that you have to wrap a very long procedure called myproc, which is defined as follows: create or replace procedure myproc as l_key VARCHAR2(200); begin l_key := 'ARUPNANDA'; end;

Of course, this is not a very long procedure at all; but for demonstration purposes assume it is. To create it as wrapped, you would execute the following PL/SQL block: 1 declare 2 l_input_code dbms_sql.varchar2s; 3 begin 4 l_input_code (1) := 'Array to hold the MYPROC'; 5 l_input_code (2) := 'create or replace procedure myproc as '; 6 l_input_code (3) := ' l_key VARCHAR2(200);'; 7 l_input_code (4) := 'begin '; 8 l_input_code (5) := ' l_key := ''ARUPNANDA'';'; 9 l_input_code (6) := 'end;'; 10 l_input_code (7) := 'the end'; 11 sys.dbms_ddl.create_wrapped ( 12 ddl => l_input_code,

13 14 15 ); 16* end;

lb ub

=> 2, => 6

Here we have defined a variable, l_input_code, to hold the input clear text code. In lines 4 through 10, we have populated the lines with the code we are going to wrap. In this example, for the same of simplicity, I have used very small lines. In reality, you may be forced to use quite long lines, up to 32KB in size. Similarly, I have used only 7 elements in the array; in reality you may be use several to fit the entire code. Lines 11 through 15 show how I have called the procedure to create the procedure as wrapped. I have passed the collection as a parameter DDL, in line 12. But, take a pause here—I have assigned a comment as the first element of the array, perhaps for documentation. It's not a valid syntax, however. Similarly, I assigned another comment to the last element (7) of the array, again not a valid syntax for creating a procedure. To let the wrapping work on only the valid lines, I have specified the lowest (2) and highest elements (6) of the collection that stores our code in lines 13 and 14. The parameter LB shows the lower bound of the array, which is 2 in our example, and HB, the higher bound (6). As you can see, using this approach, you can now create any sized procedure in wrapped format from within your PL/SQL code.

Conditional Compilation in PL/SQL: Write Once, Execute Many Many of you have worked with the C language, which supports the concept of compiler directives. In C programs, depending on the version of the compiler involved, the value of certain variables may differ. In Oracle Database 10g Release 2, PL/SQL has a similar feature: pre-processor directives can now be provided that are evaluated during compilation, not runtime. For example, let's create a very simple function that returns a string. 1 create or replace function myfunc 2 return varchar2 3 as 4 begin 5 $if $$ppval $then 6 return 'PPVAL was TRUE'; 7 $else 8 return 'PPVAL was FALSE'; 9 $end 10* end;

Note line 5, where you have used the pre-processor directives to evaluate the variable ppval. Because ppval is a pre-processor variable, not a normal PL/SQL one, you have specified it using the $$ notation. Also, to let the compiler know that it has to process the lines during compilation only, you have specified the evaluations with the special $ notation, e.g. $if instead of if. Now, compile this function with different values of the variable ppval. SQL> alter session set plsql_ccflags = 'PPVAL:TRUE'; Session altered.

Now compile the function and execute it. SQL> alter function myfunc compile; Function altered. SQL> select myfunc from dual; MYFUNC ------------------------------------PPVAL was TRUE The value of ppval was set to false during the

execute the function.

compilation. Now, change the value of the variable and re-

SQL> alter session set plsql_ccflags = 'PPVAL:FALSE';

Session altered. SQL> select myfunc from dual; MYFUNC --------------------------------------------------------PPVAL was TRUE Here although the value of ppval is FALSE in the session, the function does

not take it; rather it takes the value

set during the compilation. Now, recompile the function and execute it. SQL> alter function myfunc compile; Function altered. SQL> select myfunc from dual; MYFUNC --------------------------------------------------PPVAL was FALSE During the compilation, the value of ppval was FALSE, and that

is what was returned. So, how can you use this feature? There are several possibilities—for example, you can use it as a debug flag to display additional messages or you can write a program that compiles differently based on platform. Because the evaluation is done during compilation and not during runtime, runtime efficiency is greatly enhanced. The above example works fine when you have the same pre-processor flag that is referenced in all functions to be compiled. But what if you have different flag for each code? For instance, function calculate_interest may have the flag ACTIVE_STATUS_ONLY set to TRUE while function apply_interest may have flag FOREIGN_ACCOUNTS set to FALSE. To compile these with the appropriate flags you can issue: alter function calculate_interest compile plsql_ccflags = 'ACTIVE_STATUS_ONLY:TRUE' reuse settings; alter function apply_interest compile plsql_ccflags = FOREIGN_ACCOUNTS:TRUE' reuse settings; Note that there is no session level setting. The clause reuse settings

ensures the same compiler directives are used when the functions are recompiled later. Let's examine another variation of this new feature. In addition to the definition of a conditional variable, you can also check a static constant of a package in the conditional compilation. For example, suppose you want to control the debugging output of a PL/SQL procedure based on a Boolean packaged constant. First you create the package as create or replace package debug_pkg is debug_flag constant boolean := FALSE; end; The debug_flag is the constant that determines the

code inside the package as follows:

conditional logic in the code. You can now embed the

create or replace procedure myproc as begin $if debug_pkg.debug_flag $then dbms_output.put_line ('Debug=T'); $else dbms_output.put_line ('Debug=F'); $end end;

Note that the packaged constant is referenced directly without any $ sign. In this case, there is no need to set any session- or system-level conditional compilation parameters. While the function is compiled, you do not need to pass any additional clause either. To see how this works, execute: SQL> exec myproc Debug=F

Because the value of debug_pkg.debug_flag is FALSE now, the execution of the procedure returned "F" as expected. Now, change the constant value: create or replace package debug_pkg is debug_flag constant boolean := TRUE; end;

Then, execute the procedure again: SQL> exec myproc Debug=T

The procedure picked up the value of the constant to show "T," as expected. Note a very important difference here—you did not need to recompile the procedure; the change to the constant was picked up automatically!

Unlimited DBMS Output Remember the dreaded error that resembles the following lines?

ERROR at line 1: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 32 ORA-06512: at "SYS.DBMS_OUTPUT", line 97 ORA-06512: at "SYS.DBMS_OUTPUT", line 112 ORA-06512: at line 2

It is due to the fact that the maximum possible characters handled by the supplied package dbms_output used to be 1 million bytes. In Oracle Database 10g Release 2,, that restriction has been lifted: The maximum output can now be as much as required. You can set it to "unlimited" by simply issuing set serveroutput on

The above statement produces the following result: SQL> show serveroutput serveroutput ON size 2000 format WORD_WRAPPED

Note how the default value of the maximum size of the output used to be 2,000. In Oracle Database 10g Release 2, the command shows the following result: SQL> show serveroutput serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED The default value is UNLIMITED.

Another inconvenience was the maximum size of a line displayed by dbms_output. The following is a typical error message for lines longer than 255 bytes. ERROR at line 1: ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line ORA-06512: at "SYS.DBMS_OUTPUT", line 35 ORA-06512: at "SYS.DBMS_OUTPUT", line 115 ORA-06512: at line 2

In Oracle Database 10g Release 2, the lines can be of any length. In Part 2, I'll cover manageability features.

DBMS_CRYPTO: Example In the comments from my previous post, Tyler and Tom both mentioned the fact that you should never store sensitive data as plain-text in the database. I used a bad example unfortunately, but Tom did mention the DBMS_CRYPTO package which I have used before. Since I have used it and published a working example to the OTN forums, I figured I'd put it here as well. The goal is the encrypt a SSN and store the encrypted string (RAW) in the database. In conjunction with VPD, you should be able to easily control who can see the plain-text version of the SSN. To do that you will need to create another function which decrypts the encrypted SSN. This is a pretty basic example, but it should help to get you started. You can read more about the DBMS_CRYPTO package in the docs.

CREATE OR REPLACE PACKAGE BODY p_encrypt AS --DO NOT FORGET TO WRAP THIS BEFORE LOADING INTO DATABASE --IF IT IS NOT WRAPPED, THE KEY WILL BE EXPOSED --THE WRAP UTILITY IS LOCATED IN THE \BIN DIRECTORY (WRAP.EXE) G_KEY RAW(32) := UTL_I18N.STRING_TO_RAW( 'some_random_string_stuff_goes_here', 'AL32UTF8' ); FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW IS l_ssn RAW(32) := UTL_I18N.STRING_TO_RAW( p_ssn, 'AL32UTF8' ); l_encrypted RAW(32); BEGIN NULL; l_encrypted := dbms_crypto.encrypt ( src => l_ssn, typ => DBMS_CRYPTO.DES_CBC_PKCS5, key => G_KEY ); RETURN l_encrypted; END encrypt_ssn; FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2 IS l_decrypted RAW(32); BEGIN

l_decrypted := dbms_crypto.decrypt ( src => p_ssn, typ => DBMS_CRYPTO.DES_CBC_PKCS5, key => G_KEY ); RETURN UTL_I18N.RAW_TO_CHAR( l_decrypted, 'AL32UTF8' ); END decrypt_ssn; END p_encrypt; / show errors I mention it in the comments of the code, but do not forget to wrap your PL/SQL before you load it, otherwise someone will be able to easily see your salt/key.

Source Code with detail Examples for DBMS_CRYPTO

/* || || || || || || || || || || || || || || || */

Oracle 10g PL/SQL and SQL Enhancements - Listing 1 Contains examples of new and improved Oracle 10g PL/SQL features, including: - Encryption and decryption with DBMS_CRYPTO - Improved database monitoring with DBMS_MONITOR and DBMS_SERVER_ALERT Author: Jim Czuprynski Usage Notes: This script is provided to demonstrate various features of Oracle 10g's new and improved PL/SQL supplied packages DataPump features and should be carefully proofread before executing it against any existing Oracle database to insure that no potential damage can occur.

------ Listing 1.1: Using DBMS_CRYPTO to encrypt and decrypt data ----CREATE OR REPLACE PACKAGE hr.pkg_sensitive_data /* || Describes public interface for sample functions to encrypt and decrypt || character and numeric data using DBMS_CRYPTO methods. */ IS FUNCTION encryptor ( input_string IN VARCHAR2 ) RETURN RAW; FUNCTION decryptor ( INPUT_STRING ) RETURN VARCHAR2;

IN

VARCHAR2

END pkg_sensitive_data; / CREATE OR REPLACE PACKAGE BODY pkg_sensitive_data /* || Implements public methods for sample functions to encrypt and decrypt || character and numeric data using DBMS_CRYPTO methods. */ IS SQLERRMSG VARCHAR2(255); SQLERRCDE NUMBER; ------ Defined Encryption Methods: -- DES_CBC_NONE: Data Encryption Standard Block Cipher, Cipher Block ' -chaining, no padding -- SH1_ECB_ZERO: Secure Hash Algorithm, Electronic Codebook Cipher -chaining, pad with zeroes -----

DES_CBC_NONE CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_NONE; SH1_ECB_ZERO CONSTANT PLS_INTEGER := DBMS_CRYPTO.HASH_SH1 + DBMS_CRYPTO.CHAIN_ECB + DBMS_CRYPTO.PAD_ZERO; FUNCTION encryptor ( input_string IN VARCHAR2 ) RETURN RAW IS -- Local variables seed VARCHAR2(64) := 'a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0'; converted_seed RAW(64); converted_string RAW(64); encrypted_string RAW(64); BEGIN -- Convert incoming string and supplied seed to RAW datatype using the -- new UTLI18N package to convert the string to the AL32UTF8 character -- set converted_string := UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8'); converted_seed := UTL_I18N.STRING_TO_RAW(seed, 'AL32UTF8'); -- Encrypt the RAW value using the ENCRYPT function and the appropriate -- encryption type encrypted_string := DBMS_CRYPTO.ENCRYPT( src => converted_string ,typ => SH1_ECB_ZERO ,key => converted_seed ,iv => NULL); RETURN encrypted_string; EXCEPTION WHEN OTHERS THEN SQLERRMSG := SQLERRM; SQLERRCDE := SQLCODE; RETURN NULL; END encryptor; FUNCTION decryptor ( input_string IN VARCHAR2 ) RETURN VARCHAR2 IS -- Local variables converted_string VARCHAR2(64); seed VARCHAR2(64) := 'a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0'; converted_seed RAW(64); decrypted_string VARCHAR2(64); BEGIN -- Convert incoming string and supplied seed to RAW datatype using the -- new UTLI18N package to convert the string to the AL32UTF8 character -- set converted_string := UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8'); converted_seed := UTL_I18N.STRING_TO_RAW(seed, 'AL32UTF8'); -- Encrypt the RAW value using the ENCRYPT function and the appropriate

-- encryption type decrypted_string := DBMS_CRYPTO.DECRYPT( src => input_string ,typ => SH1_ECB_ZERO ,key => converted_seed ,iv => NULL); -- Convert incoming string to RAW datatype, using the UTLI18N package -- to convert the string to the AL32UTF8 character set converted_string := UTL_I18N.RAW_TO_CHAR(decrypted_string, 'AL32UTF8'); RETURN converted_string; EXCEPTION WHEN OTHERS THEN SQLERRMSG := SQLERRM; SQLERRCDE := SQLCODE; RETURN NULL; END decryptor; END pkg_sensitive_data; / ------ Listing 1.2: Demonstrate usage of DBMS_CRYPTO ------ Create a table for storing sensitive information CREATE TABLE hr.emp_secured_data ( employee_id NUMBER PRIMARY KEY ,ssn VARCHAR2(64) NOT NULL ,credit_card_nbr VARCHAR2(64) NOT NULL ); -- Load some test data and encrypt it with ENCRYPTOR function INSERT INTO hr.emp_secured_data (employee_id,ssn,credit_card_nbr) VALUES ( 301 ,HR.pkg_sensitive_data.encryptor('345678901') ,HR.pkg_sensitive_data.encryptor('372812345630100') ); INSERT INTO hr.emp_secured_data (employee_id,ssn,credit_card_nbr) VALUES ( 302 ,HR.pkg_sensitive_data.encryptor('456789012') ,HR.pkg_sensitive_data.encryptor('372812345630200') ); INSERT INTO hr.emp_secured_data (employee_id,ssn,credit_card_nbr) VALUES ( 303 ,HR.pkg_sensitive_data.encryptor('789012345') ,HR.pkg_sensitive_data.encryptor('372812345630300') ); COMMIT; -- Read sensitive values SQL> COL employee_id SQL> COL dcr_ssn SQL> COL dcr_ccn

from table using DECRYPTOR function FORMAT 9999999 HEADING 'EmpID' FORMAT A9 HEADING 'SocSecNbr' FORMAT A16 HEADING 'Credit Card'

SQL> SELECT 2 employee_id 3 ,HR.pkg_sensitive_data.decryptor(ssn) dcr_ssn 4 ,HR.pkg_sensitive_data.decryptor(credit_card_nbr) dcr_ccn 5 FROM HR.emp_secured_data; EmpID -------301 302 303

SocSecNbr --------345678901 456789012 789012345

Credit Card ---------------372812345630100 372812345630200 372812345630300

DBMS_CRYPTO

Syntaxes

DBMS_CRYPTO provides an interface to encrypt and decrypt stored data, and can be used in conjunction with PL/SQL programs running network communications. It provides support for several industry-standard encryption and hashing algorithms, including the Advanced Encryption Standard (AES) encryption algorithm. AES has been approved by the National Institute of Standards and Technology (NIST) to replace the Data Encryption Standard (DES). See Also:

Oracle Database Security Guide for further information about using this package and about encrypting data in general.

This chapter contains the following topics: • o o o o o o o •

Using the DBMS_CRYPTO Subprograms Overview Security Model Types Algorithms Restrictions Exceptions Operational Notes Summary of DBMS_CRYPTO Subprograms

Using the DBMS_CRYPTO Subprograms • • • • • • •

Overview Security Model Types Algorithms Restrictions Exceptions Operational Notes

Overview DBMS_CRYPTO contains basic cryptographic functions and procedures. To use this package correctly and securely, a general level of security expertise is assumed. The DBMS_CRYPTO package enables encryption and decryption for common Oracle datatypes, including RAW and large objects (LOBs), such as images and sound. Specifically, it supports BLOBs and CLOBs. In addition, it provides Globalization Support for encrypting data across different database character sets.

The following cryptographic algorithms are supported: • • • •

Data Encryption Standard (DES), Triple DES (3DES, 2-key and 3-key) Advanced Encryption Standard (AES) MD5, MD4, and SHA-1 cryptographic hashes MD5 and SHA-1 Message Authentication Code (MAC) Block cipher modifiers are also provided with DBMS_CRYPTO. You can choose from several padding options, including PKCS (Public Key Cryptographic Standard) #5, and from four block cipher chaining modes, including Cipher Block Chaining (CBC). Table 24-1 lists the DBMS_CRYPTO package features in comparison to the other PL/SQL encryption package, the DBMS_OBFUSCATION_TOOLKIT. Table 24-1 DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT Feature Comparison DBMS_OBFUSCATION_TOOLKI T

Package Feature

DBMS_CRYPTO

Cryptographic algorithms

DES, 3DES, AES, RC4, 3DES_2KEY

DES, 3DES

Padding forms

PKCS5, zeroes

none supported

Block cipher chaining modes

CBC, CFB, ECB, OFB

CBC

Cryptographic hash algorithms

MD5, SHA-1, MD4

MD5

Keyed hash (MAC) algorithms

HMAC_MD5, HMAC_SH1

none supported

Cryptographic pseudo-random number generator

RAW, NUMBER, BINARY_INTEGER

RAW, VARCHAR2

Database types

RAW, CLOB, BLOB

RAW, VARCHAR2

DBMS_CRYPTO is intended to replace the DBMS_OBFUSCATION_TOOLKIT, providing greater ease of use and support for a range of algorithms to accommodate new and existing systems. Specifically, 3DES_2KEY and MD4 are provided for backward compatibility. It is not recommended that you use these algorithms because they do not provide the same level of security as provided by 3DES, AES, MD5, or SHA-1.

Security Model Oracle Database installs this package in the SYS schema. You can then grant package access to existing users and roles as needed.

Types Parameters for the DBMS_CRYPTO subprograms use these datatypes: Table 24-2 DBMS_CRYPTO Datatypes Type

Description

BLOB

A source or destination binary LOB

CLOB

A source or destination character LOB (excluding NCLOB)

PLS_INTEGER Specifies a cryptographic algorithm type (used with BLOB, CLOB, and RAW datatypes)

A source or destination RAW buffer

RAW Algorithms

The following cryptographic algorithms, modifiers, and cipher suites are predefined in this package. Table 24-3 DBMS_CRYPTO Cryptographic Hash Functions Name

Description

HASH_MD4

Produces a 128-bit hash, or message digest of the input message

HASH_MD5

Also produces a 128-bit hash, but is more complex than MD4

HASH_SH1

Secure Hash Algorithm (SHA). Produces a 160-bit hash.

Table 24-4 DBMS_CRYPTO MAC (Message Authentication Code) Functions Name

Description

HMAC_MD5Foot 1 Same as MD5 hash function, except it requires a secret key to verify the hash value. HMAC_SH1Footref Same as SHA hash function, except it requires a secret key to verify the 1 hash value.

Footnote 1

Complies with IETF RFC 2104 standard

Table 24-5 DBMS_CRYPTO Encryption Algorithms Name

Description

ENCRYPT_DES

Data Encryption Standard. Block cipher. Uses key length of 56

Name

Description

bits.

ENCRYPT_3DES_2KEY Data Encryption Standard. Block cipher. Operates on a block 3 times with 2 keys. Effective key length of 112 bits.

ENCRYPT_3DES

Data Encryption Standard. Block cipher. Operates on a block 3 times.

ENCRYPT_AES128

Advanced Encryption Standard. Block cipher. Uses 128-bit key size.

ENCRYPT_AES192

Advanced Encryption Standard. Block cipher. Uses 192-bit key size.

ENCRYPT_AES256

Advanced Encryption Standard. Block cipher. Uses 256-bit key size.

ENCRYPT_RC4

Stream cipher. Uses a secret, randomly generated key unique to each session.

Table 24-6 DBMS_CRYPTO Block Cipher Suites Name

Description

DES_CBC_PKCS5

ENCRYPT_DESFoot 1 + CHAIN_CBCFoot 2 + PAD_PKCS5Foot 3

DES3_CBC_PKCS5

ENCRYPT_3DESFootref 1 + CHAIN_CBCFootref 2 + PAD_PKCS5Footref 3

Footnote 1

See Table 24-5, "DBMS_CRYPTO Encryption Algorithms"

Footnote 2

See Table 24-7, "DBMS_CRYPTO Block Cipher Chaining Modifiers"

Footnote 3

See Table 24-8, "DBMS_CRYPTO Block Cipher Padding Modifiers"

Table 24-7 DBMS_CRYPTO Block Cipher Chaining Modifiers Name

Description

CHAIN_ECB Electronic Codebook. Encrypts each plaintext block independently. CHAIN_CBC Cipher Block Chaining. Plaintext is XORed with the previous ciphertext block before it is encrypted.

CHAIN_CFB Cipher-Feedback. Enables encrypting units of data smaller than the block size.

CHAIN_OFB Output-Feedback. Enables running a block cipher as a synchronous stream cipher. Similar to CFB, except that n bits of the previous output block are moved into the right-most positions of the data queue waiting to be encrypted.

Table 24-8 DBMS_CRYPTO Block Cipher Padding Modifiers Name

Description

PAD_PKCS5 Provides padding which complies with the PKCS #5: Password-Based Cryptography Standard

PAD_NONE

Provides option to specify no padding. Caller must ensure that blocksize is correct, else the package returns an error.

PAD_ZERO

Provides padding consisting of zeroes.

Restrictions The VARCHAR2 datatype is not directly supported by DBMS_CRYPTO. Before you can perform cryptographic operations on data of the type VARCHAR2, you must convert it to the uniform database character set AL32UTF8, and then convert it to the RAW datatype. After performing these conversions, you can then encrypt it with the DBMS_CRYPTO package. See Also: "Conversion Rules" for information about converting datatypes.

Exceptions Table 24-9 lists exceptions that have been defined for DBMS_CRYPTO. Table 24-9 DBMS_CRYPTO Exceptions Exception

Code

Description

CipherSuiteInvalid 28827 The specified cipher suite is not defined. CipherSuiteNull

28829 No value has been specified for the cipher suite to be

KeyNull

28239 The encryption key has not been specified or contains a

KeyBadSize

28234 DES keys: Specified key size is too short. DES keys must

used.

NULL value.

be at least 8 bytes (64 bits).

AES keys: Specified key size is not supported. AES keys must be 128, 192, or 256 bits in length.

DoubleEncryption

28233 Source data was previously encrypted.

Operational Notes •

When to Use Encrypt and Decrypt Procedures or Functions

• • •

When to Use Hash or Message Authentication Code (MAC) Functions About Generating and Storing Encryption Keys Conversion Rules

When to Use Encrypt and Decrypt Procedures or Functions This package includes both ENCRYPT and DECRYPT procedures and functions. The procedures are used to encrypt or decrypt LOB datatypes (overloaded for CLOB and BLOB datatypes). In contrast, the ENCRYPT and DECRYPT functions are used to encrypt and decrypt RAW datatypes. Data of type VARCHAR2 must be converted to RAW before you can use DBMS_CRYPTO functions to encrypt it.

When to Use Hash or Message Authentication Code (MAC) Functions This package includes two different types of one-way hash functions: the HASH function and the MAC function. Hash functions operate on an arbitrary-length input message, and return a fixed-length hash value. One-way hash functions work in one direction only. It is easy to compute a hash value from an input message, but it is extremely difficult to generate an input message that hashes to a particular value. Note that hash values should be at least 128 bits in length to be considered secure. You can use hash values to verify whether data has been altered. For example, before storing data, Laurel runs DBMS_CRYPTO.HASH against the stored data to create a hash value. When she retrieves the stored data at a later date, she can again run the hash function against it, using the same algorithm. If the second hash value is identical to the first one, then the data has not been altered. Hash values are similar to "file fingerprints" and are used to ensure data integrity. The HASH function included with DBMS_CRYPTO, is a one-way hash function that you can use to generate a hash value from either RAW or LOB data. The MAC function is also a one-way hash function, but with the addition of a secret key. It works the same way as the DBMS_CRYPTO.HASH function, except only someone with the key can verify the hash value. MACs can be used to authenticate files between users. They can also be used by a single user to determine if her files have been altered, perhaps by a virus. A user could compute the MAC of his files and store that value in a table. If the user did not use a MAC function, then the virus could compute the new hash value after infection and replace the table entry. A virus cannot do that with a MAC because the virus does not know the key.

About Generating and Storing Encryption Keys The DBMS_CRYPTO package can generate random material for encryption keys, but it does not provide a mechanism for maintaining them. Application developers must take care to ensure that the encryption keys used with this package are securely generated and stored. Also note that the encryption and decryption operations performed by

DBMS_CRYPTO occur on the server, not on the client. Consequently, if the key is sent over the connection between the client and the server, the connection must be protected by using network encryption. Otherwise, the key is vulnerable to capture over the wire.

Although DBMS_CRYPTO cannot generate keys on its own, it does provide tools you can use to aid in key generation. For example, you can use the RANDOMBYTES function to generate random material for keys. (Calls to the RANDOMBYTES function behave like calls to the DESGETKEY and DES3GETKEY functions of the DBMS_OBFUSCATION_TOOLKIT package.) When generating encryption keys for DES, it is important to remember that some numbers are considered weak and semiweak keys. Keys are considered weak or semiweak when the pattern of the algorithm combines with the pattern of the initial key value to produce ciphertext that is more susceptible to cryptanalysis. To avoid this, filter out the known weak DES keys. Lists of the known weak and semiweak DES keys are available on several public Internet sites. See Also: • • •

Oracle Database Advanced Security Administrator's Guide for information about configuring network encryption and SSL. "Key Management" for a full discussion about securely storing encryption keys "RANDOMBYTES Function"

Conversion Rules To convert VARCHAR2 to RAW, use the UTL_I18N.STRING_TO_RAW function to perform the following steps: 1. Convert VARCHAR2 in the current database character set to VARCHAR2 in the AL32UTF8 database character. 2. Convert VARCHAR2 in the AL32UTF8 database character set to RAW. •

Syntax example: UTL_I18N.STRING_TO_RAW (string, 'AL32UTF8');

To convert RAW to VARCHAR2, use the UTL_I18N.RAW_TO_CHAR function to perform the following steps: 1. Convert RAW to VARCHAR2 in the AL32UTF8 database character set. 2. Convert VARCHAR2 in the AL32UTF8 database character set to VARCHAR2 in the database character set you wish to use. •

Syntax example: UTL_I18N.RAW_TO_CHAR (data, 'AL32UTF8');

See Also: Chapter 170, "UTL_I18N" for information about using the

UTL_I18N PL/SQL package.

•

If you want to store encrypted data of the RAW datatype in a VARCHAR2 database column, then use RAWTOHEX or UTL_ENCODE.BASE64_ENCODE to make it suitable for VARCHAR2 storage. These functions expand data size by 2 and 4/3, respectively.

Examples The following listing shows PL/SQL block encrypting and decrypting pre-defined 'input_string' using 256-bit AES algorithm with Cipher Block Chaining and PKCS#5 compliant padding. DECLARE input_string output_string encrypted_raw decrypted_raw num_key_bytes key_bytes_raw encryption_type

VARCHAR2 (200) := 'Secret Message'; VARCHAR2 (200); RAW (2000); -- stores encrypted binary text RAW (2000); -- stores decrypted binary text NUMBER := 256/8; -- key length 256 bits (32 bytes) RAW (32); -- stores 256-bit encryption key PLS_INTEGER := -- total encryption type DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;

BEGIN DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string); key_bytes_raw := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes); encrypted_raw := DBMS_CRYPTO.ENCRYPT ( src => UTL_I18N.STRING_TO_RAW (input_string, 'AL32UTF8'), typ => encryption_type, key => key_bytes_raw ); -- The encrypted value "encrypted_raw" can be used here

decrypted_raw := DBMS_CRYPTO.DECRYPT ( src => encrypted_raw, typ => encryption_type, key => key_bytes_raw ); output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8'); DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string); END;

Summary of DBMS_CRYPTO Subprograms Table 24-10 DBMS_CRYPTO Package Subprograms Subprogram

Description

DECRYPT Function

Decrypts RAW data using a stream or block cipher with a user supplied key and optional IV (initialization vector)

DECRYPT

Decrypts LOB data using a stream or block cipher with a user

Subprogram

Description

Procedures

supplied key and optional IV

ENCRYPT Function

Encrypts RAW data using a stream or block cipher with a user supplied key and optional IV

ENCRYPT Procedures

Encrypts LOB data using a stream or block cipher with a user supplied key and optional IV

HASH Function

Applies one of the supported cryptographic hash algorithms (MD4, MD5, or SHA-1) to data

MAC Function

Applies Message Authentication Code algorithms (MD5 or SHA-1) to data to provide keyed message protection

RANDOMBYTES Function

Returns a RAW value containing a cryptographically secure pseudorandom sequence of bytes, and can be used to generate random material for encryption keys

RANDOMINTEGER Function

Returns a random BINARY_INTEGER

RANDOMNUMBER Function

Returns a random 128-bit integer of the NUMBER datatype

DECRYPT Function This function decrypts RAW data using a stream or block cipher with a user supplied key and optional IV (initialization vector). Syntax DBMS_CRYPTO.DECRYPT( src IN RAW, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL) RETURN RAW;

Pragmas pragma restrict_references(decrypt,WNDS,RNDS,WNPS,RNPS);

Parameters Table 24-11 DECRYPT Function Parameters Parameter Name

Description

src

RAW data to be decrypted.

typ

Stream or block cipher type and modifiers to be used.

Parameter Name

Description

key

Key to be used for decryption.

iv

Optional initialization vector for block ciphers. Default is NULL.

Usage Notes •

To retrieve original plaintext data, DECRYPT must be called with the same cipher, modifiers, key, and IV that was used to encrypt the data originally. See Also: "Usage Notes" for the ENCRYPT function for additional information about the ciphers and modifiers available with this package.

•

If VARCHAR2 data is converted to RAW before encryption, then it must be converted back to the appropriate database character set by using the UTL_I18N package. See Also: "Conversion Rules" for a discussion of the

VARCHAR2 to RAW conversion process.

DECRYPT Procedures These procedures decrypt LOB data using a stream or block cipher with a user supplied key and optional IV (initialization vector). Syntax DBMS_CRYPTO.DECRYPT( dst IN OUT NOCOPY src IN typ IN key IN iv IN

BLOB, BLOB, PLS_INTEGER, RAW, RAW DEFAULT NULL);

DBMS_CRYPT.DECRYPT( dst IN OUT NOCOPY src IN typ IN key IN iv IN

CLOB CHARACTER SET ANY_CS, BLOB, PLS_INTEGER, RAW, RAW DEFAULT NULL);

Pragmas pragma restrict_references(decrypt,WNDS,RNDS,WNPS,RNPS);

Parameters

Table 24-12 DECRYPT Procedure Parameters Parameter Name

Description

dst

LOB locator of output data. The value in the output LOB will be overwritten.

src

LOB locator of input data.

typ

Stream or block cipher type and modifiers to be used.

key

Key to be used for decryption.

iv

Optional initialization vector for block ciphers. Default is all zeroes.

ENCRYPT Function This function encrypts RAW data using a stream or block cipher with a user supplied key and optional IV (initialization vector). Syntax DBMS_CRYPTO.ENCRYPT( src IN RAW, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL) RETURN RAW;

Pragmas pragma restrict_references(encrypt,WNDS,RNDS,WNPS,RNPS);

Parameters Table 24-13 ENCRYPT Function Parameters Parameter Name

Description

src

RAW data to be encrypted.

typ

Stream or block cipher type and modifiers to be used.

key

Encryption key to be used for encrypting data.

iv

Optional initialization vector for block ciphers. Default is NULL.

Usage Notes •

Block ciphers may be modified with chaining and padding type modifiers. The chaining and padding type modifiers are added to the block cipher to produce a

•

• • •

cipher suite. Cipher Block Chaining (CBC) is the most commonly used chaining type, and PKCS #5 is the recommended padding type. See Table 24-7 and Table 24-8 for block cipher chaining and padding modifier constants that have been defined for this package. To improve readability, you can define your own package-level constants to represent the cipher suites you use for encryption and decryption. For example, the following example defines a cipher suite that uses DES, cipher block chaining mode, and no padding: DES_CBC_NONE CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_NONE;

See Table 24-6 for the block cipher suites already defined as constants for this package. •

To encrypt VARCHAR2 data, it should first be converted to the AL32UTF8 character set. See Also: "Conversion Rules" for a discussion of the conversion process.

•

Stream ciphers, such as RC4, are not recommended for stored data encryption.

ENCRYPT Procedures These procedures encrypt LOB data using a stream or block cipher with a user supplied key and optional IV (initialization vector). Syntax DBMS_CRYPTO.ENCRYPT( dst IN OUT NOCOPY src IN typ IN key IN iv IN

BLOB, BLOB, PLS_INTEGER, RAW, RAW DEFAULT NULL);

DBMS_CRYPTO.ENCRYPT( dst IN OUT NOCOPY src IN typ IN key IN iv IN

BLOB, CLOB CHARACTER SET ANY_CS, PLS_INTEGER, RAW, RAW DEFAULT NULL);

Pragmas pragma restrict_references(encrypt,WNDS,RNDS,WNPS,RNPS);

Parameters

Table 24-14 ENCRYPT Procedure Parameters Parameter Name

Description

dst

LOB locator of output data. The value in the output LOB will be overwritten.

src

LOB locator of input data.

typ

Stream or block cipher type and modifiers to be used.

key

Encryption key to be used for encrypting data.

iv

Optional initialization vector for block ciphers. Default is NULL.

Usage Notes See "Conversion Rules" for usage notes about using the ENCRYPT procedure.

HASH Function A one-way hash function takes a variable-length input string, the data, and converts it to a fixed-length (generally smaller) output string called a hash value. The hash value serves as a unique identifier (like a fingerprint) of the input data. You can use the hash value to verify whether data has been changed or not. Note that a one-way hash function is a hash function that works in one direction. It is easy to compute a hash value from the input data, but it is hard to generate data that hashes to a particular value. Consequently, one-way hash functions work well to ensure data integrity. Refer to "When to Use Hash or Message Authentication Code (MAC) Functions" for more information about using one-way hash functions. This function applies to data one of the supported cryptographic hash algorithms listed in Table 24-3. Syntax DBMS_CRYPTO.Hash ( src IN RAW, typ IN PLS_INTEGER) RETURN RAW; DBMS_CRYPTO.Hash ( src IN BLOB, typ IN PLS_INTEGER) RETURN RAW; DBMS_CRYPTO.Hash ( src IN CLOB CHARACTER SET ANY_CS, typ IN PLS_INTEGER) RETURN RAW;

Pragmas pragma restrict_references(hash,WNDS,RNDS,WNPS,RNPS);

Parameters Table 24-15 HASH Function Parameters Parameter Name

Description

src

The source data to be hashed.

typ

The hash algorithm to be used.

Usage Note Oracle recommends that you use the SHA-1 (Secure Hash Algorithm), specified with the constant, HASH_SH1, because it is more resistant to brute-force attacks than MD4 or MD5. If you must use a Message Digest algorithm, then MD5 provides greater security than MD4.

MAC Function A Message Authentication Code, or MAC, is a key-dependent one-way hash function. MACs have the same properties as the one-way hash function described in "HASH Function", but they also include a key. Only someone with the identical key can verify the hash. Also refer to "When to Use Hash or Message Authentication Code (MAC) Functions" for more information about using MACs. This function applies MAC algorithms to data to provide keyed message protection. See Table 24-4 for a list of MAC algorithms that have been defined for this package. Syntax DBMS_CRYPTO.MAC ( src IN RAW, typ IN PLS_INTEGER, key IN RAW) RETURN RAW; DBMS_CRYPTO.MAC ( src IN BLOB, typ IN PLS_INTEGER key IN RAW) RETURN RAW; DBMS_CRYPTO.MAC ( src IN CLOB CHARACTER SET ANY_CS, typ IN PLS_INTEGER key IN RAW) RETURN RAW;

Pragmas pragma restrict_references(mac,WNDS,RNDS,WNPS,RNPS);

Parameters Table 24-16 MAC Function Parameters Parameter Name

Description

src

Source data to which MAC algorithms are to be applied.

typ

MAC algorithm to be used.

key

Key to be used for MAC algorithm.

RANDOMBYTES Function This function returns a RAW value containing a cryptographically secure pseudo-random sequence of bytes, which can be used to generate random material for encryption keys. The RANDOMBYTES function is based on the RSA X9.31 PRNG (Pseudo-Random Number Generator), and it draws its entropy (seed) from the sqlnet.ora file parameter SQLNET.CRYPTO_SEED. Syntax DBMS_CRYPTO.RANDOMBYTES ( number_bytes IN POSITIVE) RETURN RAW;

Pragmas pragma restrict_references(randombytes,WNDS,RNDS,WNPS,RNPS);

Parameters Table 24-17 RANDOMBYTES Function Parameter Parameter Name

Description

number_bytes

The number of pseudo-random bytes to be generated.

Usage Note • • •

The number_bytes value should not exceed the maximum length of a RAW variable. The SQLNET.CRYPTO_SEED parameter can be set by entering 10 to 70 random characters with the following syntax in the sqlnet.ora file: SQLNET.CRYPTO_SEED = <10 to 70 random characters>

See Also: Oracle Database Advanced Security Administrator's Guide for more information about the SQLNET.CRYPTO_SEED parameter and its use.

RANDOMINTEGER Function This function returns an integer in the complete range available for the Oracle BINARY_INTEGER datatype. Syntax DBMS_CRYPTO.RANDOMINTEGER RETURN BINARY_INTEGER;

Pragmas pragma restrict_references(randominteger,WNDS,RNDS,WNPS,RNPS);

RANDOMNUMBER Function This function returns an integer in the Oracle NUMBER datatype in the range of [0..2**128-1]. Syntax DBMS_CRYPTO.RANDOMNUMBER RETURN NUMBER;

Pragmas pragma restrict_references(randomnumber,WNDS,RNDS,WNPS,RNPS);

Transparent Data Encryption Four easy steps Step- 1: Setup Wallet and Master Key When a database is initially prepared for TDE, the master key is created with this simple command: SQL> alter system set encryption key identified by "password";

This command, potentially issued by another person apart from the DBA, creates a master key and either puts the key into an existing wallet, or creates a new wallet, when it doesn't exist. It is issued only once. If the wallet is lost, the command does not re-create the master key (even if the identical password is used), it creates a new, different master key. By default, the Oracle Wallet stores a history of retired master keys, which enables you to change them and still be able to decrypt data which was encrypted under an old master key. The fact that the case sensitive wallet password might be unknown to the DBA provides separation of duty, since the DBA might be able to restart the database, but the wallet is closed and needs to be manually opened by a 'Security DBA', who needs to know the wallet password. The command to open the wallet is: SQL> alter system set encryption wallet open identified by "password";

Step- 2: Identify columns with sensitive data Identify columns with sensitive data The minimum requirements about what data needs to be encrypted can be obtained by studying legal documents (California Senate Bill 1386 and other Breach Notification Laws) or private "standards" (PCIDSS). Credit card numbers, social security numbers and other "Personally Identifiable Information" (PII) will fall under this category. Another need for encryption is defined by your own internal security policies — trade secrets, research results, employee salary and bonuses.

Step- 3: Review constraints The following data types can be encrypted using TDE: varchar2 number binary_float timestamp char

nvarchar2 date binary_double raw nchar

Each table has it's own encryption key (which is stored in the database data dictionary and encrypted with the external master key). Encrypted columns can no longer be used as foreign keys. To maintain performance, TDE column encryption allows indexes for equality searches: SQL> select last_name from customers where credit_card = 4716082825579654;

to be built over encrypted columns in the base table. Other indexes (for range scans) are not suppurted.

Step-4 : Encrypt existing and new data Change the definitions of the columns found in step two with this simple command: SQL> alter table modify ( encrypt [using (enc-alg)] ['nomac'] [no salt]);

All existing data in the column specified will now be encrypted. During the update of the table, read access is still possible; in case DML commands are necessary, online redefinition can be used. From now on, all data that is added to the encrypted column is automatically encrypted when it is written to the database file, and decrypted when it is selected by an authorized user who passed all access control mechanisms in place: System and object privileges granted directly, through database roles or secure application roles, access control policies implemented with Virtual Private Database or Oracle Label Security, and finally Oracle Database Vault. The new 'nomac' parameter is available from 10.2.0.4.

Transparent Data Encryption: Experience from the Trenches by Steve Bobrowski One user's experience implementing Oracle TDE reveals some helpful advice about the best approach to encrypting existing data. Published February 2009 Introduced as part of the Oracle Advanced Security Option (Release 10.2 and greater), Oracle Database Transparent Data Encryption (TDE) allows you to selectively encrypt sensitive database data that's maintained in the underlying data files of a database, as well as all downstream file components such as online redo logs, archived redo logs, and database backups. The basic goal of TDE is to protect the sensitive data found in these raw operating system files from prying eyes should a nefarious person gain access to a disk or backup tape and then try to either restore the database or scan the raw operating system files for data such as personally identifiable information or credit card information. I've implemented TDE several times as part of my consulting practice. But until one of my most recent engagements, I had always used TDE either to encrypt a new column of an existing table or a column that was part of a brand new table. Working with TDE in both of these cases is straightforward because the target columns are empty, so there's not much risk involved due to the lack of data and existing application dependencies. My most recent experience implementing TDE was different. I was helping a large company encrypt an existing column in a table that already had more than one million rows. There was also a mission-critical application that depended on the column, so, as you can imagine, there were a lot of important things to ponder before diving in. After searching the Internet for people's experiences in similar situations, I found only a few good resources to help me. This article is a digest of what I learned going through the process of using TDE to encrypt existing data. My hope is that what I learned will help you expedite a similar experience should you try to use TDE with existing column data. Please note that this article is not an introduction to TDE; if you would like to learn the fundamentals of implementing TDE, refer to the documentation (or see this tutorial).

Identifying Possible Restrictions The initial thing that I did when researching my client's system was to look for data model characteristics related to the target column that would prohibit us from encrypting the column altogether, or things about the column that might negatively impact existing operations. This research included looking for column indexes and integrity constraints. As the Oracle documentation clearly states, there are a number of restrictions to understand when you want to encrypt a column that has indexes. Oracle does not permit the encryption of a column that has a bitmap index, which was not germane in our situation. However, the target column did have a couple of normal (B-tree) indexes. While Oracle does permit the encryption of a column that has a normal index, Oracle does prohibit

"salted" encryption of an indexed column. Salting strengthens the security of repetitious data by adding a random string to the data before encryption, thus making it more difficult for thieves to use pattern-matching recognition techniques to decipher encrypted data. In summary, after this initial analysis we were left with a situation where we could encrypt the column, but not with salt. I could have stopped here with the analysis of the column's indexes, but the next question I wanted to answer was, "Are the indexes in place being used?" My thought process was this: if the indexes were not useful, I'd get rid of them and relieve the system of the overhead necessary to maintain the index entries, especially considering the extra burden of encryption. To judge whether the indexes were useful, I used Oracle Database's index monitoring feature. I found that, indeed, the indexes were being used, so we would have to maintain them going forward. Next, I looked to see if the target column was involved in a referential integrity constraint. Because each table has it's own encryption key, Oracle does not permit you to use TDE to encrypt columns involved in a foreign key relationship. In our situation, the target column was not involved in a referential integrity constraint.

Assessing Performance Overhead One of the first questions that my client asked was, "How will TDE affect the general performance of my application?" The Oracle documentation has a small section that discusses, in general terms, how TDE will affect the performance of dependent applications. But my customer wanted some concrete statistics to give them an idea of how TDE would affect a time-critical data load process that occurred every day. To address my client's needs, I calculated the average number of rows inserted into the target table every day during the time-critical process. Then, I created a similar test table and indexes in the client's identical sandbox environment, and measured how much time it took to insert the same number of rows both before and after encrypting the target column. The difference between these elapsed times gave us a reasonably good idea of the "performance penalty" of encrypting the column data during the process. Listing 1 is an example of how I did this using SQL*Plus. SQL> CONNECT system Enter password: Connected. SQL> -- Configure Oracle-Managed (Data) Files SQL> ALTER SYSTEM 2 SET db_create_file_dest = '/data01/oracle/' 3 SCOPE = MEMORY; System altered. SQL> -- Create two new tablespaces for the demo, SQL> -- one for data segments, one for index segments SQL> CREATE TABLESPACE data_001 2 DATAFILE SIZE 1G; Tablespace created. SQL> CREATE TABLESPACE indx_001 2 DATAFILE SIZE 500M; Tablespace created. SQL> -- Create a user for the demo

SQL> CREATE USER app_001 IDENTIFIED BY app 2 DEFAULT TABLESPACE data_001 3 TEMPORARY TABLESPACE temp 4 QUOTA UNLIMITED ON data_001 5 QUOTA UNLIMITED ON indx_001; User created. SQL> GRANT CREATE SESSION, CREATE TABLE TO app_001; Grant succeeded. SQL> -- Work as the demo user SQL> CONNECT app_001/app; Connected. SQL> -- Create the demo table in the default tablespace SQL> CREATE TABLE app_001.transactions ( 2 trans_id INTEGER 3 CONSTRAINT transactions_pk PRIMARY KEY 4 USING INDEX TABLESPACE indx_001, 5 credit_card INTEGER NOT NULL 6 ); Table created. SQL> -- Create an index in the INDX_001 tablespace SQL> CREATE INDEX app_001.transactions_ndx1 2 ON app_001.transactions(credit_card) 3 TABLESPACE indx_001; Index created. SQL> -- Time how long it takes to load data in the clear SQL> SET TIMING ON; SQL> BEGIN 2 -- AMEX 3 FOR i IN 1 .. 100000 LOOP 4 INSERT INTO app_001.transactions(trans_id, credit_card) 5 VALUES ( 6 i, 7 '34' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) 8 ); 9 END LOOP; 10 COMMIT; 11 -- VISA 12 FOR i IN 100001 .. 400000 LOOP 13 INSERT INTO app_001.transactions(trans_id, credit_card) 14 VALUES ( 15 i, 16 '4' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>999999999999999)) 17 ); 18 END LOOP; 19 COMMIT; 20 -- MASTERCARD 21 FOR i IN 400001 .. 500000 LOOP 22 INSERT INTO app_001.transactions(trans_id, credit_card) 23 VALUES ( 24 i, 25 '54' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) 26 );

27 28 29 30

END LOOP; COMMIT; END; /

PL/SQL procedure successfully completed. Elapsed: 00:00:56.14 SQL> SET TIMING OFF; SQL> -- Remove existing synthetic data SQL> TRUNCATE TABLE app_001.transactions; Table truncated. SQL> -- Enable encryption of the credit card column SQL> ALTER TABLE app_001.transactions 2 MODIFY (credit_card ENCRYPT NO SALT); Table altered. SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

-- Time how long it takes to load encrypted data SET TIMING ON; BEGIN -- AMEX FOR i IN 1 .. 100000 LOOP INSERT INTO app_001.transactions(trans_id, credit_card) VALUES ( i, '34' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) ); END LOOP; COMMIT; -- VISA FOR i IN 100001 .. 400000 LOOP INSERT INTO app_001.transactions(trans_id, credit_card) VALUES ( i, '4' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>999999999999999)) ); END LOOP; COMMIT; -- MASTERCARD FOR i IN 400001 .. 500000 LOOP INSERT INTO app_001.transactions(trans_id, credit_card) VALUES ( i, '54' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) ); END LOOP; COMMIT; END; /

PL/SQL procedure successfully completed. Elapsed: 00:01:16.31 SQL> SET TIMING OFF;

Listing 1 Using a sandbox environment that is identical to your production environment, a simple comparison of the time it takes to load a representative data set with and without column encryption enabled will give you a good expectation of how column encryption will affect the performance of your production system.

As with all performance tests, I suspect that the performance penalty of column encryption will vary from system to system depending on the usual variables (CPUs, average load, etc.). In Listing 1, you notice that the calculated performance penalty would be 36% (((56.14-76.31)/56.14)*100); however, using the experimental evidence that we collected in my client's system, our expectation was about an 11% increase in elapsed time for the data load process, which was right on the money when we went into production with TDE. In this example, I've focused on estimating the performance penalty of data encryption for a data load process with indexes in place. If your system has different types of critical processes, such as a demanding report generation cycle, I advise you employ your sandbox environment to compare how long the process takes before and after data encryption. The section "Identifying Potential Query Plan Changes" later in this article discusses unique considerations for queries and data encryption.

Dealing with Outages and Maintenance Windows Another valid concern of my client was what, if any, production application outage would be necessary while encrypting the existing column data in a table of approximately one million rows. My initial thought was that, theoretically, no outage would be necessary—after all, the Oracle documentation states that what happens when encrypting an existing column's data is an essentially a multi-row update of the entire table. Without giving it much thought, I didn't see why concurrent new row insertions into the table and existing row updates wouldn't be able to proceed. And as I mumbled that familiar Oracle mantra "readers don't block writers, writers don't block readers," I certainly didn't expect queries to be affected by the column encryption. But I've been doing this DBA job long enough to know that it's important to test your theories and avoid unexpected problems when you finally make an actual change to a production system. And lo and behold, I discovered issues when I tested the application itself against the sandbox database during the column encryption. Most important, I found that the ongoing encryption slowed the response time of some queries enough that the application experienced response timeouts. These timeouts, in turn, caused disconnects, which then caused subsequent transactions to fail, and then it got real messy from there—I'll spare you the details. Suffice it to say, after the tests, I knew that an outage was certainly justified. But the next question was how long would the production application need to be offline? Would we be able to encrypt the column during the normal two-hour maintenance window scheduled every weekend, or would more downtime be required? To figure this out, I simply measured how long it took to encrypt the column in the sandbox environment, considering that it had identical server hardware and data set as the production environment. I found that it took a little more than an hour to finish the column encryption. Frankly, I was shocked at how long it took, considering that I did simulated test encryption runs on a laptop that took less than five minutes with similar data. But what mattered is what happened with the antiquated server hardware that was going to be used when we encrypted the column in the production database system. Knowing that we needed some more time to carry out other tasks during the normal maintenance window, I decided that I must find ways to decrease the hour that it took to encrypt the column. My first instinct was to drop the two indexes that included the target column. That way, Oracle would only have to encrypt the column data in the table itself, after which I could efficiently rebuild the indexes without the overhead of logging. After some new tests, I dropped the time necessary to encrypt the column and related indexes from 70 minutes (indexes in place during encryption) to just 20 minutes (indexes rebuilt after encrypting the column). Listing 2 is an example of the tests that I used to come to my conclusions (continuing from where we left off in Listing 1). Again, note that the timings in the listing are from a test system used to write this article, not the actual system that my client was using.

SQL> -- Remove existing synthetic data SQL> TRUNCATE TABLE app_001.transactions; Table truncated. SQL> -- Disable encryption of the credit card column SQL> ALTER TABLE app_001.transactions 2 MODIFY (credit_card DECRYPT); Table altered. SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

-- Load new synthetic data BEGIN -- AMEX FOR i IN 1 .. 100000 LOOP INSERT INTO app_001.transactions(trans_id, credit_card) VALUES ( i, '34' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) ); END LOOP; COMMIT; -- VISA FOR i IN 100001 .. 400000 LOOP INSERT INTO app_001.transactions(trans_id, credit_card) VALUES ( i, '4' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>999999999999999)) ); END LOOP; COMMIT; -- MASTERCARD FOR i IN 400001 .. 500000 LOOP INSERT INTO app_001.transactions(trans_id, credit_card) VALUES ( i, '54' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) ); END LOOP; COMMIT; END; /

PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> 2

-- Time how long it takes to encrypt credit card data -- with corresponding indexes in place SET TIMING ON; ALTER TABLE app_001.transactions MODIFY (credit_card ENCRYPT NO SALT);

Table altered. Elapsed: 00:02:27.18 SQL> SET TIMING OFF; SQL> -- Remove existing synthetic data SQL> TRUNCATE TABLE app_001.transactions; Table truncated.

SQL> -- Drop all indexes that correspond to the credit card column SQL> DROP INDEX app_001.transactions_ndx1; Index dropped. SQL> -- Disable encryption of the credit card column SQL> ALTER TABLE app_001.transactions 2 MODIFY (credit_card DECRYPT); Table altered. SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

-- Load new synthetic data BEGIN -- AMEX FOR i IN 1 .. 100000 LOOP INSERT INTO app_001.transactions(trans_id, credit_card) VALUES ( i, '34' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) ); END LOOP; COMMIT; -- VISA FOR i IN 100001 .. 400000 LOOP INSERT INTO app_001.transactions(trans_id, credit_card) VALUES ( i, '4' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>999999999999999)) ); END LOOP; COMMIT; -- MASTERCARD FOR i IN 400001 .. 500000 LOOP INSERT INTO app_001.transactions(trans_id, credit_card) VALUES ( i, '54' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) ); END LOOP; COMMIT; END; /

PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> 2

-- Time how long it takes to: -- 1. Encrypt credit card data without corresponding indexes in place -- 2. Recreate corresponding indexes SET TIMING ON; ALTER TABLE app_001.transactions MODIFY (credit_card ENCRYPT NO SALT);

Table altered. Elapsed: 00:01:15.48 SQL> CREATE INDEX app_001.transactions_ndx1 2 ON app_001.transactions(credit_card) 3 TABLESPACE indx_001 4 PARALLEL 2 5 NOLOGGING;

Index created. Elapsed: 00:00:02.98 SQL> SET TIMING OFF;

Listing 2 To expedite the process of encrypting existing data, simply drop the underlying indexes of a column before you encrypt it and then rebuild the indexes afterward. Note: The CREATE INDEX statements are already known in this article's simulated environment. In a realworld setting, consider using Oracle Database's DBMS_METADATA utility package to generate CREATE INDEX statements that you can use to recreate the indexes after data encryption is complete. In summary, the new strategy of rebuilding the indexes after column encryption left much more time to deal with the most challenging problem in the entire process, as the next section explains.

Removing Ghost Copies of Unencrypted Data Oracle and underlying host operating systems use optimized algorithms to update data in data blocks with the goal being to minimize performance-sapping disk I/O. In the specific case of encrypting existing column data, one thing that commonly happens is that Oracle writes encrypted column data to new data blocks and simply marks the space consumed by former unencrypted values as unused. In other words, Oracle makes no attempt to clean out the older unencrypted data. As long as the system in question experiences a continual amount of update activity, you can be reasonably sure that Oracle will eventually overwrite the older unencrypted data when reusing block space. But considering that my client was preparing for a regulatory compliance audit, we had to be sure that the unencrypted sensitive data was immediately wiped clean after the encryption process. After doing some research, I found an FAQ at the Oracle Technology Network Website as well as a blog post that confirm this specific issue and give some basic thoughts on how to address it. The general idea is to move all segments with previously unencrypted data to a new tablespace (and data file), and then use an operating system utility to shred the old data file. But this synopsis makes the entire process sound simpler than it really is. The truth is that you'll most likely need to move many segments along with the segments that contain sensitive data before you can safely drop the old tablespace and shred its data files. To automate this possibly laborious, somewhat error-prone process, I put together some scripts to help me build the DDL statements that are necessary to get everything done. I should give Tom Kyte a tip of the hat, as some of the work here is a modified query of something that I found at the Asktom site. Listing 3 shows you an example of the entire process that I used. Enter password: Connected. SQL> -- Create new tablespaces for data and index segments SQL> CREATE TABLESPACE data_002 DATAFILE SIZE 1G; Tablespace created. SQL> CREATE TABLESPACE indx_002 DATAFILE SIZE 500M; Tablespace created. SQL> -- Generate a script to move existing segments to new tablespaces SQL> COL ORDER_COL1 NOPRINT; SQL> COL ORDER_COL2 NOPRINT;

SQL> SET HEADING OFF; SQL> SET VERIFY OFF; SQL> SET ECHO OFF; SQL> SELECT DECODE( segment_type, 'TABLE' , segment_name, table_name ) order_col1, 2 DECODE( segment_type, 'TABLE', 1, 2 ) order_col2, 3 'ALTER ' || segment_type || ' ' || LOWER(owner) || '.' || LOWER(segment_name) || 4 DECODE( segment_type, 'TABLE', ' MOVE ', ' REBUILD ' ) || 5 'TABLESPACE ' || LOWER(DECODE( segment_type, 'TABLE' , '&&NEW_DATA_TBS' , '&&NEW_INDX_TBS' )) || ';' 6 FROM dba_segments, 7 (SELECT table_name, index_name FROM dba_indexes WHERE tablespace_name = UPPER('&&OLD_INDX_TBS')) 8 WHERE segment_type in ( 'TABLE', 'INDEX' ) 9 AND segment_name = index_name (+) 10 AND tablespace_name IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS')) 11 AND owner = UPPER('&&OWNER') 12 ORDER BY 1, 2; Enter value for new_data_tbs: data_002 Enter value for new_indx_tbs: indx_002 Enter value for old_indx_tbs: indx_001 Enter value for old_data_tbs: data_001 Enter value for owner: app_001 ALTER TABLE app_001.transactions MOVE TABLESPACE data_002; ALTER INDEX app_001.transactions_pk REBUILD TABLESPACE indx_002; ALTER INDEX app_001.transactions_ndx1 REBUILD TABLESPACE indx_002; SQL> SQL> SQL> SQL> SQL>

SET HEADING ON; SET VERIFY ON; SET ECHO ON; -- execute script output ALTER TABLE app_001.transactions MOVE TABLESPACE data_002;

Table altered. SQL> ALTER INDEX app_001.transactions_pk REBUILD TABLESPACE indx_002; Index altered. SQL> ALTER INDEX app_001.transactions_ndx1 REBUILD TABLESPACE indx_002; Index altered. SQL> -- Check for any unusable indexes SQL> SELECT owner, index_name, tablespace_name 2 FROM dba_indexes 3 WHERE STATUS = 'UNUSABLE'; no rows selected SQL> -- Gather new schema stats SQL> EXEC dbms_stats.gather_schema_stats('app_001'); PL/SQL procedure successfully completed. SQL> -- Check for remaining segments in old tablespaces SQL> SELECT distinct owner 2 FROM dba_segments 3 WHERE tablespace_name IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS'));

old new

3: 3:

WHERE tablespace_name IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS')) WHERE tablespace_name IN (UPPER('data_001'), UPPER('indx_001'))

no rows selected SQL> -- Check for users assigned to old tablespaces SQL> SELECT username, default_tablespace FROM dba_users 2 WHERE default_tablespace IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS')); old 2: WHERE default_tablespace IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS')) new 2: WHERE default_tablespace IN (UPPER('data_001'), UPPER('indx_001')) USERNAME DEFAULT_TABLESPACE ------------------------------ -----------------------------APP_001 DATA_001 SQL> -- Assign new default tablespaces for users, as necessary SQL> ALTER USER app_001 2 DEFAULT TABLESPACE data_002; User altered. SQL> SQL> SQL> SQL> SQL> 2 3 old new

-- List the data file names of old tablespaces COL tablespace_name FOR A15; COL file_name FOR A70; SET LINES 100; SELECT tablespace_name, file_name FROM dba_data_files WHERE tablespace_name IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS')); 3: WHERE tablespace_name IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS')) 3: WHERE tablespace_name IN (UPPER('data_001'), UPPER('indx_001'))

TABLESPACE_NAME --------------DATA_001 INDX_001

FILE_NAME ---------------------------------------------------------------------/data01/oracle/db001/datafile/o1_mf_data_001_4m081w7m_.dbf /data01/oracle/db001/datafile/o1_mf_indx_001_4m082l4q_.dbf

SQL> -- Drop old tablespaces, but keep data files in place SQL> DROP TABLESPACE data_001 2 INCLUDING CONTENTS KEEP DATAFILES; Tablespace dropped. SQL> DROP TABLESPACE indx_001 2 INCLUDING CONTENTS KEEP DATAFILES; Tablespace dropped. SQL> -- Shred/remove old data files SQL> HOST shred -n 200 -z -u /data01/oracle/db001/datafile/o1_mf_data_001_4m081w7m_.dbf SQL> HOST shred -n 200 -z -u /data01/oracle/db001/datafile/o1_mf_indx_001_4m082l4q_.dbf

Listing 3 Removing leftover unencrypted ghost copies of sensitive data requires a series of steps to address a number of dependencies. The last step in Listing 3 is operating system specific. In this example, I demonstrate use of the Linux/Unix shred utility. Other utilities that you might want to research are the Linux/Unix wipe, scrub, and srm programs.

Preparing for Potential Rekey Operations

My client also wanted to be prepared for future situations that might justify a rekey operation. Rekeying existing data means that you re-encrypt existing data with a new key. You might do this when you suspect that someone has gained access to the existing table keys and can potentially decrypt sensitive data outside of your control. My steps for a completing a rekey operation are similar to the steps for originally encrypting existing data: drop indexes on the target column, rekey the column's data, and then rebuild dropped indexes. Optionally, if you are concerned about ghost copies that correspond to the data encrypted with the previous key, you will have to repeat the process of moving segments to a new tablespace, dropping the old tablespace, and scrubbing the old tablespace's data files. Note: In my experience, PCI auditors are happy with re-keying only the master encryption key, because this does not require any data access, and the PCI standard does not include recommendations for a 2-tier key architecture like Oracle's. Re-keying the master encryption key should be sufficient from a PCI compliance perspective, and PCI auditors cannot force companies that have billions of rows to shut their business down for a couple days just for re-keying.

Identifying Potential Query Plan Changes The Oracle documentation, several articles, and some forum posts that I read mention mostly general along with some specific information about possible changes that can occur to existing query execution plans after you encrypt a column's data. In general, you have to pay attention to what happens to the execution of SQL statements when you encrypt an indexed column as opposed to a non-indexed column. When Oracle encrypts an indexed column, Oracle also encrypts corresponding index values. If you think about this for a moment, it's clear that equality predicates targeting indexed data should continue to make use of indexes; however, the random nature of encrypted values makes range scans of encrypted indexes cost prohibitive because of the way that index values are sorted in an index. Listing 4 demonstrates these basic, well-documented scenarios. SQL> CONNECT app_001 Enter password: Connected. SQL> -- Create a plan table SQL> @?/rdbms/admin/utlxplan.sql; Table created. SQL> -- Disable encryption of the credit card column SQL> ALTER TABLE app_001.transactions 2 MODIFY (credit_card DECRYPT); Table altered. SQL> -- Ensure schema stats are current SQL> EXEC dbms_stats.gather_schema_stats('app_001'); PL/SQL procedure successfully completed. SQL> -- Display some representative data SQL> COL credit_card FOR 9999999999999999; SQL> SELECT * FROM app_001.transactions 2 WHERE rownum < 5; TRANS_ID CREDIT_CARD ---------- -----------------

389 390 391 392 SQL> SQL> SQL> SQL> SQL> 2

3469681098409570 3441050723354352 3485598407754404 3485458104610650

-- Enable tracing and explain plan output SET AUTOTRACE ON EXPLAIN; -- Demonstrate an equality predicate targeting the -- encrypted column SELECT * FROM app_001.transactions WHERE credit_card = 3485458104610650;

TRANS_ID CREDIT_CARD ---------- ----------------392 3485458104610650 Execution Plan ---------------------------------------------------------Plan hash value: 32329967 -----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 14 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TRANSACTIONS | 1 | 14 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TRANSACTIONS_NDX1 | 1 | | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - access("CREDIT_CARD"=3485458104610650) SQL> -- Demonstrate a range predicate targeting the SQL> -- encrypted column SQL> SELECT * FROM app_001.transactions 2 WHERE credit_card BETWEEN 3499990000000000 AND 3499999999999999; TRANS_ID CREDIT_CARD ---------- ----------------4629 3499990987277941 18597 3499993250694089 13364 3499996558049599 79326 3499996616476145 60420 3499997873591732 24392 3499998608513414 97433 3499999831086288 72183 3499999977925392 8 rows selected.

Execution Plan ---------------------------------------------------------Plan hash value: 32329967 -----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 42 | 6 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TRANSACTIONS | 3 | 42 | 6 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TRANSACTIONS_NDX1 | 3 | | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - access("CREDIT_CARD">=3499990000000000 AND "CREDIT_CARD"<=3499999999999999) SQL> SQL> SQL> SQL> 2

-- Disable tracing and explain plan output SET AUTOTRACE OFF; -- Encrypt the column (and indexes) ALTER TABLE app_001.transactions MODIFY (credit_card ENCRYPT NO SALT);

Table altered. SQL> -- Ensure schema stats are current SQL> EXEC dbms_stats.gather_schema_stats('app_001'); PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> 2

-- Enable tracing and explain plan output SET AUTOTRACE ON EXPLAIN; -- Rerun previous queries, compare execution plans SELECT * FROM app_001.transactions WHERE credit_card = 3485458104610650;

TRANS_ID CREDIT_CARD ---------- ----------------392 3485458104610650 Execution Plan ---------------------------------------------------------Plan hash value: 32329967 -----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 14 | 5 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TRANSACTIONS | 1 | 14 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TRANSACTIONS_NDX1 | 1 | | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - access("CREDIT_CARD"=3485458104610650) SQL> SELECT * FROM app_001.transactions 2 WHERE credit_card BETWEEN 3499990000000000 AND 3499999999999999; TRANS_ID CREDIT_CARD ---------- ----------------60420 3499997873591732 4629 3499990987277941 18597 3499993250694089 13364 3499996558049599 24392 3499998608513414 79326 3499996616476145 72183 3499999977925392 97433 3499999831086288 8 rows selected. Execution Plan ---------------------------------------------------------Plan hash value: 1321366336 ---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1250 | 17500 | 914 (2)| 00:00:11 | |* 1 | TABLE ACCESS FULL| TRANSACTIONS | 1250 | 17500 | 914 (2)| 00:00:11 | ---------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------1 - filter(INTERNAL_FUNCTION("CREDIT_CARD")>=3499990000000000 AND INTERNAL_FUNCTION("CREDIT_CARD")<=3499999999999999) SQL> -- Disable tracing and explain plan output SQL> SET AUTOTRACE OFF;

Listing 4 Carefully identify SQL statements that reference encrypted column data, and then compare the execution plans of these statements before and after encryption to see if there are any changes. I also wanted to know if the overhead of encryption could change plan costs and the optimizer's choices, even in cases where the documentation suggests that it should not. To make sure I knew exactly what would happen to the mission-critical application when we went into production, I did some extra work in the sandbox environment. First, I collected a list of the Top SQL statements (CPU, Gets, and I/O) from various Automatic Workload Repository (AWR) snapshots. Then, I compared the query execution plans of each SQL statement before and after the column was encrypted. My research turned up a complex query that used a predicate with several equality-based conditions, one of which targeted the column that was going to be encrypted. To my

surprise, the execution plan for this query changed after encrypting the column. Unfortunately, I wasn't able to duplicate these results for this article in my test lab, and I'm still not completely sure why the query plan changed. But the reason I mention this scenario here is to point out that it is always a good idea to research the execution plans of a production application's key queries in a test environment before making a change to a production system. If I had assumed that none of the top queries would change, we would have made the change to the production system and had to scramble for a resolution with the heat on. The lesson here is that you should always test things out before making changes, despite what you read in documentation and other sources.

Conclusion Using Oracle's TDE feature to encrypt columns in new tables, tables without any data, or new columns in existing tables is trivial because there aren't any existing dependencies that you need to worry about. In contrast, encrypting existing column data requires careful research and testing in your sandbox environment before implementing your plan in a live production system due to the many dependencies that might be affected by encryption. Steve Bobrowski has been using Oracle Database since Version 5. He is a former Oracle employee; the founder of The Database Domain (dbdomain.com); and the author of five Oracle Press books, including the Hands-On Oracle Database 10g Express Edition series. Most recently, Steve has served as the CTO of SaaS and as a SaaS consultant for several large companies such as Computer Sciences Corporation, BEA Systems, and Salesforce.com.

Transparent Data Encryption (TDE) Oracle11g Frequently Asked Questions Questions Transparent Data Encryption 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15.

Can anyone who has been given authorization to the application decrypt data? What is the overhead associated with TDE? What are the encryption algorithms that can be used with TDE? Is it possible to use 3rd party encryptions algorithms in place of the ones provided by TDE? Can I use TDE on columns used in foreign key constraints? Can columns that are used for joins be encrypted? Can indexed columns be encrypted? What data types and data lengths does column-level TDE support? Does the data stay encrypted on the network? Does the database memory (SGA) contain clear-text or encrypted data? How do I know which data to encrypt? Where is the data that needs to be encrypted? With Oracle Database 11gR1, shall I use column-level TDE or Tablespace Encryption? How is TDE different from the encryption toolkit Oracle already provides? How is TDE licensed?

Wallet Management 16. 17. 18. 19. 20. 21. 22.

What is a Wallet? How is the TDE wallet protected? Can I use Oracle Wallet Manager (OWM) to create the encryption wallet and master key for TDE? Can I change the wallet password? How do I create an auto-open wallet? What is the default location in which Oracle Wallet Manager looks for a wallet? How do I prevent the Oracle TDE wallet from being backed up on the same tape as the RMAN database backups when using Oracle Secure Backup? 23. Best practices for wallet backup

Cross-component Integration 24. 25. 26. 27. 28. 29. 30. 31.

What is Oracle Secure Backup (OSB)? Can I encrypt backups sent to disk using Oracle Secure Backup? Do transportable tablespaces work with tablespace encryption? Does compression work with TDE? Does TDE work with Data Guard and Oracle Streams? Which packaged applications are certified with Transparent Data Encryption? Are there other database features that column-level TDE does not work with? Can I use SQL*Loader with direct path to load data into a table with encrypted columns?

Best Practices 32. Why can I sometimes still see clear-text data after encrypting sensitive information with column-level TDE?

33. 34. 35. 36.

How do I change the encryption keys? How do I quickly encrypt columns in very large tables with billions of rows? Can I re-key the master key for encrypted tablespaces? How do I migrate the content from clear text application tablespaces to encrypted tablespaces?

Hardware Security Modules (HSM) 37. Can all master keys be stored in an HSM device? 38. How do I use both column-level TDE with hardware keys and tablespace encryption with software wallet keys at the same time? 39. Does the master key leave the HSM device?

Answers 1. Can anyone who has been given authorization to the application decrypt data? Yes, TDE is designed to provide customers the ability to transparently apply encryption within the database without impacting existing applications. Returning data in encrypted format would break most existing applications. TDE provides the benefit of encryption without the overhead associated with traditional database encryption solutions that typically require database triggers and views. Oracle Database Vault can be used protect application data from the DBA and other powerful users as well as implementing robust controls on access to the database and application.

2. What is the overhead associated with TDE? Tablespace encryption (available with 11gR1) has no additional storage overhead; the performance overhead percentage is estimated to be in the low single digits. Overhead associated with column-level TDE falls into two areas: storage and performance.

o

The performance overhead associated with encrypting or decrypting a common attribute such as a credit card number column with column-level TDE is estimated to be around 5%. When an index is built on an encrypted column, the index will be built using the cipher text. If a TDE encrypted column is indexed and referenced in a SQL statement, Oracle will transparently encrypt the value used in the SQL statement and perform an index lookup using the cipher text. In real-world scenarios, only very few columns are encryption candidates, sometimes only 0.3% of all application columns; if all indexes are re-build the way they were originally designed before column-level TDE was applied, customers reported a negligible performance impact. It is recommended that customers, who plan to use column-level TDE, upgarde to Oracle Database 10gR2 10.2.0.4. or Oracle Database 11gR1 11.1.0.7, since both include changes that reduce the performance overhead: Storage overhead associated with column-level TDE can be significant due to an additional 20 byte integrity check associated with each encrypted value. In addition, TDE will pad out encrypted values to 16 bytes, so if a value required 9 bytes of storage, encrypting this value would require an additional 7 bytes of storage. Finally, if 'SALT' is specified on the encrypted value, an additional 16 bytes is required. In summary, encrypting a single column in a table will require between 36 and 51 bytes of additional storage per row. This number is important for storage planning, but the DBAs or developers don't have to expand the columns for column-level TDE; the expansion is done transparently by TDE when a column is marked 'encrypted'. Users can limit the amount of additional storage by choosing the 'no salt' option, and/or the new 'nomac' option (available from 10.2.0.4 and 11.1.0.7), which eliminates the additional CPU cycles and storage of the 20 byte hash value for each encrypted field.

3. What are the encryption algorithms that can be used with TDE? TDE supports 3DES168, AES128 (default for tablespace encryption), AES192 (default for column-level TDE), and AES256.

4. Is it possible to use 3rd party encryptions algorithms in place of the ones provided by TDE?

No, it is not possible to plug-

in other encrypti on algorith

ms. Oracle provides encrypti

on algorith ms that are

broadly accepte d, and will add

new algorith ms as they

become availabl e. Should

you need another encrypti

on algorith m currently

not availabl e in TDE,

please contact your Oracle

Sales represe ntative. 5. Can I use TDE on columns used in foreign key constraints?

TDE doesn't support encrypti

ng columns with foreign

key constrai nts. This is due to

the fact that individua l tables

have their own unique

encrypti on key. The following

query lists all occurren ces of RI

constrai nts in your

databas e: SQL> select A.owner, A.table_name, A.column_name, A.constraint_name from dba_cons_columns A, dba_constraints B where A.table_name = B.table_name and B.constraint_type = 'R';

6. Can columns that are used for joins be encrypted?

Yes. Joining

tables is transpar ent to users and

applicati ons, even if the columns

for the join condition are encrypte

d. 7. Can indexed columns be encrypted?

Tablespa ce encrypti

on supports all indexes transpare

ntly. For columnlevel TDE, the

index needs to be a normal Btree

index, used for equality searches. In case of

a composit e, functionbased

index, the encrypte d column cannot be the

one that was used for the function. When

encryptin ga column with an existing

index, it is recomme nded to first

extract the index definition with dbms_m

etadata.g et_ddl, then drop the index, encrypt

the column with the 'no salt' option,

and rebuild the index. 8. What data types and data lengths does column-level TDE support?

For tablespa ce encryptio n, there

are no limitations in terms of supported

data types; the following data types can

be encrypted using column-

level TDE: varchar2 (< 3933 characters) char (< 1933 characters) number binary_float timestamp SecureFile (11gR1 and later)

nvarchar2 (< 1967 characters) nchar (< 967 characters) raw binary_double date

9. Does the data stay encrypted on the network?

Data encrypted

with TDE is decrypted when it passes

through the SQL layer. Thus if this data

goes on the network, it is the clear-text

data. However, the data can be encrypted

using Oracle's network encryption solution,

which is included with TDE in the Oracle

Advanced Security option. Oracle's network

encryption solution can encrypt all data

traveling to and from a database over

SQL*Net. 10.Does the database memory (SGA) contain clear-text or encrypted data?

With columnlevel TDE, encrypted

data remains encrypted inside the SGA, but with

tablespace encryption, data is already decrypted in the

SGA. 11.How do I know which data to encrypt?

If you have to comply to the PCIDSS

standard, then credit card numbers (a.k.a. Primary

Account Number, or PAN) need to be stored encrypted.

The need to comply to the almost ubiquitous Breach

Notification Laws (for example CA SB 1386, and similar laws

in 43 more US states), adds first name, last name, driver

license number and other PII to your list. In late 2007, CA

AB 1950 added medical and health insurance

information to PII data. Additionally , your industry specific

privacy and security standards may require encryption

of certain stored assets, plus your own core business

assets (such as research results in the pharmaceu

tical industry, results of oil field exploration, financial

contracts, or the personal details of informants in law

enforceme nt) may be worth encrypting to safeguard

this information on the storage medium. In the health

care industry, the privacy of patient data, health

records and X-ray images is of the highest importance

. Most Xray images are stored following the DICOM standard,

which intentionall y includes PII information into the

image meta data, making image and patient data readily

available to an intruder if not properly protected through

encryption. 12.Where is the data that needs to be encrypted?

This is the most difficult task ahead of a security

team or team of DBAs when using columnlevel TDE:

If you run applications that were developed in-house, chances are

you can locate tables with sensitive information by talking to

your developers. It is more difficult when you run

packaged software applications . Since privacy and security

requirement s are different for each of the deployment s of these

applications , vendors themselves cannot readily determine

what to encrypt. If PCI compliance is the goal, and the

column names of the application tables are named

similar to 'CREDIT_C ARD' or 'ACCOUNT _NUMBER', they are

easy to find using Oracle's rich metadata repository.

More complex is the hunt for sensitive data when column

names are not descriptive about their content; the only method

of finding sensitive content is the search for patterns: Social

Security Numbers always look like 'aaa-bbcccc', but Credit Card

Numbers are less consistent: They have 13 or 16 digits, and

are not always grouped by 4 digits. If you need to encrypt

columns that have characteristi cs which are not supported

by columnlevel Transparent Data Encryption (in terms of

indexes, data types, or foreign keys), or if it is not possible to

locate columns that store sensitive data in application

tables, Tablespace Encryption, a new feature of Transparent

Data Encryption in Oracle Database 11gR1, is your best

choice. 13.With Oracle Database 11gR1, shall I use column-level TDE or Tablespace Encryption?

Use tablespace encryption if any of the

following is true: You cannot find all columns with 1.

sensitive content Data type and/or data length of sensitive column is 2.

not supported by columnlevel TDE Sensitive column is 3.

used as foreign key Applications perform range scans over indexed, 4.

encrypted columns You need index types other than B-tree over 5.

encrypted columns 2. How is TDE different from the encryption toolkit Oracle already provides?

Oracle introduced an encryption package

('dbms_obfus cation_toolkit' ) with Oracle8i. In Oracle 10g Release 1, the new

'dbms_crypto ' package was introduced. These APIs can be used to manually

encrypt data within the database. However, the application must manage the

encryption keys and perform required encryption and decryption

operations by calling the API. As opposed to dbms_obfusc ation_toolkit

and dbms_crypto, both columnlevel TDE (from 10gR2) and tablespace

encryption (from 11gR1) don't require changes to the application, are

transparent to the end users, and provide automated key management

. 3. How is TDE licensed?

TDE is part of the Oracle Advanced Security Option, which also includes

Network Encryption and Strong Authentication . It is available for the Oracle Enterprise

Edition. 4. What is the Oracle Wallet?

A wallet is a container that is used to store authentication and signing credentials,

including passwords, the TDE master key, PKI private keys, certificates, and trusted certificates

needed by SSL. With TDE, wallets are used on the server to protect the TDE master key. With the

exception of Diffie-Hellman, Oracle requires entities that communicate over SSL to have a wallet

containing an X.509 version 3 certificate, private key, and list of trusted certificates.

Oracle provides two different types of wallets: encryption wallet and auto-open wallet. The

encryption wallet (filename ewallet.p12) is the one recommended for TDE. It needs to be

opened manually after database startup and prior to TDE encrypted data being accessed. If

the Wallet is not opened, the database will return an error when TDE protected data is queried. The

auto-open wallet (filename cwallet.sso) opens automatically when a database is

started; hence it can be used for unattended Data Guard (10gR2: physical standby only; 11gR1:

physical and logical standby) environments where encrypted columns are shipped to

secondary sites. Do not delete the encryption wallet after creating an auto-open wallet, since

otherwise master encryption key re-key operations would fail. 5. How is the TDE wallet protected?

Access to the wallet should be limited to the 'oracle' user, using proper directory and file level permissions. In

addition, the password (that encrypts the wallet) should contain a minimum of 8 alphanumeric characters.

Wallet passwords can be changed using Oracle Wallet Manager. Changing the wallet password doesn't effect

the TDE master key (they are independent). Even though the 'root' user has access to the wallet file, if she does not

know the wallet password, she has no access to the master key(s). 6. Can I use Oracle Wallet Manager (OWM) to create the encryption wallet and master key for TDE?

If you create an encryption wallet with Oracle

Wallet Manager, it does not contain the master key required by TDE. Only the SQL command: creates a wallet (if it doesn't SQL> alter system set encryption key identified by "wallet_password"

already exist in the location specified in the local sqlnet.ora file) and adds the TDE master key to it. In Oracle 11gR1, TDE and other

security features have been migrated to Enterprise Manager Database Control, thus enabling the wallet and the

master key to be generated using the Web-based GUI of Enterprise Manager. Also new in 11gR1, the master key for column-level

TDE can be created and stored in an HSM device such that the master key never leaves the HSM device in clear text. Starting with

Oracle Database 11gR1 11.1.0.7, the master key for tablespace encryption can also be stored in an HSM device, but re-keying the tablespace

encryption master key is not supported. The syntax to create a new key in an HSM device is: where 'userID:HSM_passwor SQL> alter system set encryption key identified by "user-ID:HSM_password"

d' are the credentials the database uses to log into the HSM device. 7. Can I change the wallet password?

Yes, the wallet password can be changed with Oracle Wallet

Manager (OWM). Changing the wallet password does not change the encryption master key — they are independent. In Oracle 11gR1 11.1.0.7, orapki has been

enhanced to allow wallet password changes from the command line: $ orapki wallet change_pwd -wallet <wallet_location> -oldpwd -newpwd

8. How do I create an auto-open wallet?

A passwordprotected, encrypted wallet for the TDE master key might not be the

right solution when database availability needs to be maintained without human intervention ('lights-out' operation); an autoopen wallet does not require a wallet password after a database came up,

so encrypted values are available to authorized users and applications An auto-open wallet ('cwallet.sso') needs to be created from an existing encryption wallet ('ewallet.p12'), so that the master key

is transferred to the new wallet. You can either open the encryption wallet in Oracle Wallet Manager (OWM), check the 'Auto Login' check box, then select 'Save' to write the auto-open wallet to

disk, or, using the command-line tool 'orapki': In both cases the user will be prompted for the wallet password. Keep the encryption wallet; it may be needed for master $ orapki wallet create -wallet <wallet_location> -auto_login

key re-key operations later, and potentially contains a list of retired master keys. 9. What is the default location in which Oracle Wallet Manager looks for a wallet?

For Linux and UNIX based systems, it's in which usually translates to /etc/ORACLE/WALLETS/

/etc/ORACLE/WALLETS/oracle

Since '/etc' is owned by root, root needs to create the subdirectories and later change ownership to user 'oracle', group 'oinstall': # cd /etc # mkdir -pv ORACLE/WALLETS/oracle # chown -R oracle:oinstall ORACLE

10.How do I prevent the Oracle TDE wallet from being backed up on the same tape as the RMAN database backups when using Oracle Secure Backup?

RMAN only adds database files, redologs etc. to the backup

file, and thus there is no risk of the encryption wallet or the auto-open wallet becoming part of a database backup. Oracle Secure Backup (OSB) uses datasets to define which operating system files to add to a backup. OSB automatically excludes auto-open wallets ('cwallet.sso').

Encryption wallets ('ewallet.p12') are NOT automatically excluded; you need to use the exclude dataset statement to specify what files to skip during a backup: Detailed instructions about the EXCLUDE keyword. exclude name *.p12

11.Best practices for wallet backup

Backup the Oracle wallet right after creating it, and each time it's content changes, for example due to a master key re-key operation. Always store the wallet (encrypted or autoopen) away from your database backups. 12.What is Oracle Secure Backup (OSB)?

Oracle Secure Backup provides an optimized, highly efficient tape backup solution for the Oracle Database. OSB can store data on tape in

encrypted form, providing protection against theft of backup tapes. 13.Can I encrypt backups sent to disk using Oracle Secure Backup?

No, however, Oracle RMAN can be used in conjunction with Oracle Advanced Security to encrypt database backups sent to disk. This requires a license of the Oracle Advanced Security Option. 14.Do transportable tablespaces work with tablespace encryption?

Yes, but it requires that the wallet containing the master key is copied to the secondary database. If the tablespace is moved and the master key is not available, the secondary database will return an error when the data in the tablespace is accessed.

15.Does compression work with TDE?

Customers using TDE tablespace encryption get the full benefit of compression because compression is applied before the data blocks are encrypted. Customers using TDE column level encryption will get the full benefit of compression on table columns that are not encrypted. Individual table columns that are encrypted using TDE will have a much lower level of compression because the encryption takes place in the SQL layer before the advanced compression process. 16.Does TDE work with Data Guard and Oracle Streams?

When TDE is used with Data Guard physical standby (10gR2 and later), encrypted data remains encrypted in the log files during shipping to the secondary database(s), so Oracle Network Encryption is optional. The master key needs to be present on the secondary site only when it is either in Read Only mode or after a failover, but not for applying the redo. When TDE is used with Data Guard logical standby (11gR1), the master key needs to be present and open at the secondary site for SQL Apply to decrypt the data that it reads from the log files. The same master key is also used to optionally encrypt the incoming data while it is written to the Logical Standby database. Encrypted data remains encrypted in log files and during transit when the log files

are shipped to the secondary database; Oracle Network Encryption is optional. When column-level TDE is used with Streams in 11gR1, data is transmitted between active databases in clear text to allow data transformation (character sets, database versions, platforms, etc.). When the receiving side cannot be reached and data needs to be stored temporarily, encrypted columns are stored encrypted on disk. Streams in database versions prior to 11gR1 treat encrypted columns as 'unsupported data types' and skip these tables. 17.Which packaged applications are certified with Transparent Data Encryption? For column-level TDE: o

o

o

o

SAP (customers and partners can refer to note 974876):  SAP 640 kernel, for example: SAP R/3 4.7 Enterprise, NW2004, ECC5.0, BW 3.5, etc.  SAP 700 kernel and later, for example: ERP2005, NW2004s, ECC6.0, BI 7.0, etc. Oracle E-Business Suite:  Available for E-Business Suite 11.5.9 with Consolidated Update 2 or higher (Details) PeopleSoft  PeopleSoft Applications can be protected by TDE; PeopleTools before 8.50 are not aware of encrypted columns, so the view 'dba_encrypted_columns' should be compared before and after an upgrade from, for example, 8.46 to 8.48. Siebel  Siebel Applications can be protected by TDE.

Other packaged applications are in the process of being certified. Contact your Oracle representative today for details. For tablespace encryption, no certification is needed. Tests showed performance degradation of 5 to 9% for normal transactions. 14.Are there database features that column-level TDE does not work with?

Tablespace encryption encrypts all content stored in that tablespace and does not conflict with any other database feature. Column-level TDE encrypts and decrypts data transparently when it passes through the SQL layer. Some features of Oracle bypass the SQL layer, and hence cannot benefit from column-level TDE:

o o o o o

Materialized View Logs Sync. and async CDC (change data capture) Transportable Tablespaces LOBs (SecureFiles are supported from 11gR1) Streams (supported from 11gR1)

15.Can I use SQL*Loader with direct path to load data into a table with encrypted columns? Yes, you can. When the target table contains encrypted columns, the data will be encrypted upon loading the data. Here is a simple example on how to use SQL*Loader with direct path. Simply modify one column in ulcase6.sql from

sal number(7,2), to

sal number(7,2) encrypt, and use the correct syntax for SQL*Loader:

$ sqlldr USERID=scott/tiger CONTROL=ulcase6.ctl LOG=ulcase6.log DIRECT=TRUE

16.Why can I sometimes still see clear-text data after encrypting sensitive information with column-level TDE? This is no different from finding the data still on the disk even after a table is dropped, or a file is deleted. During the lifetime of a table, data may become fragmented, re-arranged, sorted, copied and moved within the table space; this leaves 'ghost copies' of your data within the database file. When encrypting an existing column, only the most recent v' alid' copy is encrypted, leaving behind older clear-text versions in ghost copies. If the data file holding the table space is directly accessed bypassing the access controls of the database (for example with an hex - editor), old clear text values might be visible for some time, until those blocks are overwrit ten by the database. To minimize this risk, please follow these recommendations:

1. 2. 3. 4. 5. 6.

Create a new tablespace in a new data file (CREAT E TABLESPACE ... )

Encrypt the clear text values in the original tablespace and data file (ALTER TABLE ... ENCRYPT )

Repeat 2.) for all tables that contain encrypted columns

Move all tables from the original tablespace into the new data file (ALTER TABLE ....MOVE... )

Drop the original table space (DROP TABLESPACE ). Do not use the 'and datafi les' parameter; Oracle recommends to use stronger methods for OS – level operations, see 6.)

Use 'shred' or other OS commands for your platform to delete the old data file on the OS level.

The 6th step is recommended to lower the probability of being able to find ghost copies of the database file, generated by either the operating system, or storage firmware.

2. How do I change the encryption keys?

Neither master key nor tablespa

ce keys for tablesp ace

encrypti on in 11gR1 can be

re-keyed directly, but you can

move content from one encrypte

d tablespa ce to a new

encrypte d tablespa ce,

where the content is

encrypte d with a new

tablespa ce key. Column -level

TDE uses a two tier key

mechani sm. When TDE is

applied to an existing applicati

on table column, a new table

key is created and stored in

the Oracle data dictionar

y. The table keys are encrypte

d using the TDE master key. The

master key is generate d when

TDE is initialize d and stored

outside the databas e in an

Oracle Wallet or an HSM device

(with Oracle 11gR1). Both the

master key and column keys can

be indepen dently changed

based on compan y

security policies. Oracle recomm

ends backing up the wallet

before and after each master

key change. 3. How do I quickly encrypt columns in very large tables with billions of rows?

Encrypti ng

columns in an existing table is an

'update' operatio n and allows Read

access, but no DML operatio ns, on

that table. With billions of rows,

this window of limited availabilit y can

last several hours. But with Online

table redefiniti on, the table is locked in

the exclusive mode only during a

very small window that is independ

ent of the size of the table and complexi

ty of the redefiniti on, and that is complete

ly transpar ent to users. 4. Can I re-key the master key for encrypted tablespaces?

No; in the current database release (11.1.0.7)

, it is not possible to re-key the master

key for tablespac e encryptio n. The

workarou nd is to create a new encrypte

d tablespac e, export the whole database

(or schema) with Data Pump (expdp),

because exporting a tablespac e only

exports the tables; use Data Pump

(impdp) to move the dump file into the new

tablespac e, and delete the old encrypte

d tablespac e. 5. How do I migrate the content from clear text application tablespaces to encrypted tablespaces?

o

Extract the DDL that was used to create

the original applicati on tablespa

ces using 'dbms_m etadata. get_ddl'

and save the output as a SQL script.

o

Add 'ENCRY PTION DEFAUL T

STORA GE(ENC RYPT)' to each 'create

tablespa ce' statemen t (the SIZE

paramet er does not need to be changed,

since tablespa ce encryptio n does

not increase storage requirem ents).

o

Export either the whole database

, or the schema that owns the applicati

on tablespa ces, with Data

o

Pump (expdp). Drop the applicati on

tablespa ces 'with contents and datafiles'

o

. Run the SQL script to create

encrypte d applicati on tablespa

ce with otherwis e unchang ed

o

characte ristics. Import the dumpfile

with Data Pump (impdp). 17.Can all master encryption keys be stored in an HSM device?

Oracle 11gR1 allows full key managem

ent for column encryption master keys in

HSM devices; master keys for tablespac

e encryption in Oracle 11gR1 11.1.0.7

can be created and stored, but not

rotated (rekeyed), in HSM devices.

HSMs cannot be used for tablespac e

encryption master keys in 11.1.0.6.

Customer s who are already using TDE on

columns with 10gR2 and do not plan

to use tablespac e encryption , can use

the 'migrate' syntax of Oracle 11gR1 to

transpare ntly generate a new master

key for their encrypted columns in an HSM

device. The existing key in the software

wallet is no longer needed, except for importing

data from files or backups that were encrypted

with old master key(s). 11.1.0.6: For

customers who are already using TDE on

columns with 10gR2, and want to store

the master key for columnlevel TDE

in an HSM device and want to use tablespac

e encryption in 11.1.0.6 at the same

time, need to generate a new master

key in their 11gR1 database prior to

migrating. This updates the existing

column master key, and generates a new

tablespac e master key. The next step is to

migrate the column encryption key from

wallet to HSM. If customers plan to

use tablespac e encryption , they

need to maintain the software based

TDE wallet, since it contains the

master key for tablespac e encryption

. 18.How do I use both column-level TDE with hardware keys and tablespace encryption with software wallet keys at the same time?

Only the master key for column-

level TDE can be fully managed in an HSM

device (create, store, rotate, destroy);

With Oracle 11.1.0.7, the master key for

tablespace encyption can be created and

stored, but not rotated, in an HSM device. If

you want to use both columnlevel TDE

(with an HSMbased master key) and

tablespace encryption (with a walletbased

master key), the command to open the HSM

wallet: needs to open both the HSM wallet and SQL> alter system set encryption wallet open identified by "userID:HSM_password"

the software wallet. Since both wallets

need to be open, users can either generate

an autoopen software wallet to use for

tablespace encryption (keep the encryption wallet; it

may be needed for master key re-key operations

later, and potentially contains a list of retired

master keys), or the password for the

software wallet can be changed to "user-

ID:HSM_p assword", using Oracle Wallet

Manager. Additionall y, the '(DIRECT ORY=/....)'

string in sqlnet.ora needs to point to the

software wallet, even though 'METHOD

=FILE' has been changed to 'METHOD

=HSM'. 19.Does the Master key leave the HSM device?

Never. In HSM mode, the database sends the table keys to the HSM device for decryption; communication between database and HSM device is always secure.