Encrypt Your Data Assets

  • Uploaded by: SHAHID FAROOQ
  • 0
  • 0
  • May 2020
  • PDF

This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA


Overview

Download & View Encrypt Your Data Assets as PDF for free.

More details

  • Words: 21,970
  • Pages: 353
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

Related Documents

Encrypt Data In Xml File
November 2019 9
Encrypt > Passwd
July 2020 11
G Encrypt
August 2019 19
Assets
November 2019 29

More Documents from "Business Expert Press"