TDE 加密列和加密表空间

一、概念

Transparent Data Encryption(TDE) enables you to encrypt sensitive data, such as credit card numbers, stored in tables and tablespaces. Encrypted data is transparently decrypted for a database user or application that has access to data. TDE helps protect data stored on media in the event that the storage media or data file gets stolen.

TDE可以对数据库中的敏感数据加密，基于表或是表空间加密。

已经加密的数据对数据库使用者或是应用程序都是透明的，自动解密。

如果存储介质或是数据文件被偷，TDE可以保护数据不被还原。

Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored. 

To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files. oracle提供TDE技术来保护数据文件，保护存储在数据文件中的敏感数据。

To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database. 为了防止未认证的解密，TDE将加密密钥存储在数据库外部。

Database users and applications do not need to manage key storage or create auxiliary tables, views, and triggers. An application that processes sensitive data can use TDE to provide strong data encryption with little or no change to the application.

数据库用户和应用程序不必产生额外的表，视图或是触发器来管理密钥。处理敏感数据的应用程序使用TDE提供增强的数据加密，而不用对应用程序做任何的修改。

Use TDE to protect confidential data, such as credit card and social security numbers, stored in table columns. You can also use TDE to encrypt entire tablespaces.

TDE可以加密列，也可以加密整个表空间。

二、Benefits of Using Transparent Data Encryption

Transparent Data Encryption (TDE) has the following advantages:

As a security administrator, you can be sure that sensitive data is safe in case the storage media or data file gets stolen. 防止敏感数据被偷
Implementing TDE helps you address security-related regulatory compliance issues.
You do not need to create triggers or views to decrypt data for the authorized user or application. Data from tables is transparently decrypted for the database user and application. TDE不需要额外的维护，对用户透明
Database users and applications need not be aware of the fact that the data they are accessing is stored in encrypted form. Data is transparently decrypted for the database users and applications. 数据透明
Applications need not be modified to handle encrypted data. Data encryption and decryption is managed by the database. 应用程序不参与加密和解密，加密和解密由数据库完成
Key management operations are automated. The user or application does not need to manage encryption keys.  加密密钥自动产生，不需要用户管理。只需要做好备份，不要丢失即可。

三、 Types of Transparent Data Encryption

Transparent Data Encryption (TDE) column encryption enables you to encrypt sensitive data stored in select table columns. TDE tablespace encryption enables you to encrypt all data stored in a tablespace.

Both TDE column encryption and TDE tablespace encryption use a two-tiered, key-based architecture. Even if the encrypted data is retrieved, it cannot be understood until authorized decryption occurs, which is automatic for users authorized to access the table.

The following sections discuss TDE column encryption and TDE tablespace encryption:

TDE Column Encryption
TDE Tablespace Encryption

3.1 TDE Column Encryption

TDE column encryption is used to protect confidential data, such as credit card and social security numbers, stored in table columns. TDE column encryption uses the two-tiered, key-based architecture to transparently encrypt and decrypt sensitive table columns. The TDE master encryption key is stored in an external security module, which can be an Oracle wallet or Hardware Security Module (HSM). This master encryption key is used to encrypt the table key, which in turn is used to encrypt and decrypt data in the table column.

the master encryption key is stored in an external security module that is outside the database and accessible only to the security administrator. For this external security module, Oracle uses an Oracle wallet or Hardware Security Module (HSM), as described in this chapter. Storing the master encryption key in this way prevents its unauthorized use.

Using an external security module (wallet/HSM) separates ordinary program functions from encryption operations, making it possible to divide duties between database administrators and security administrators. Security is enhanced because the wallet password can be unknown to the database administrator, requiring the security administrator to provide the password.

When a table contains encrypted columns, a single table key is used regardless of the number of encrypted columns. The table keys for all tables are encrypted with the database server master encryption key and stored in a dictionary table in the database. No keys are stored in the clear.

3.2 TDE Tablespace Encryption

TDE tablespace encryption enables you to encrypt an entire tablespace. All objects created in the encrypted tablespace are automatically encrypted. TDE tablespace encryption is useful if you want to secure sensitive data in tables. You do not need to perform a granular analysis of each table column to determine the columns that need encryption.

In addition, TDE tablespace encryption takes advantage of bulk encryption and caching to provide enhanced performance. While the actual performance impact on applications can vary, the performance overhead is roughly estimated to be in between 5% and 8%.

TDE tablespace encryption is a good alternative to TDE column encryption if your tables contain sensitive data in multiple columns, or if you want to protect the entire table and not just individual columns.

TDE tablespace encryption encrypts all data stored in an encrypted tablespace. This includes internal large objects (LOBs) such as BLOBs and CLOBs. TDE tablespace encryption does not encrypt data that is stored outside the tablespace. For example, BFILE data is not encrypted as it is stored outside the database. If you create a table with a BFILE column in an encrypted tablespace, then this particular column will not be encrypted. However, SecureFile LOBs are supported from Oracle Database 11g Release 1 (11.1).

All data in an encrypted tablespace is stored in encrypted format on the disk. Data is transparently decrypted for an authorized user having the necessary privileges to view or modify the data. A database user or application does not need to know if the data in a particular table is encrypted on the disk. In the event that the data files on a disk or backup media gets stolen, the data is not compromised.

TDE tablespace encryption uses the two-tiered, key-based architecture to transparently encrypt (and decrypt) tablespaces. The TDE master key is stored in an external security module (Oracle Wallet or HSM). This TDE master key is used to encrypt the TDE tablespace encryption key, which in turn is used to encrypt and decrypt data in the tablespace.

Note:

The encrypted data is protected during operations like JOIN and SORT. This means that the data is safe when it is moved to temporary tablespaces. Data in undo and redo logs is also protected.

TDE tablespace encryption also allows index range scans on data in encrypted tablespaces. This is not possible with TDE column encryption.

Oracle Database 11g Release 2 (11.2) implements the following enhancements to TDE tablespace encryption:

1、A unified master encryption key is used for both TDE column encryption and TDE tablespace encryption.
2、You can reset the unified master encryption key. This provides enhanced security and helps meet security and compliance requirements

四、Using Transparent Data Encryption

4.1 启用TDE

4.1.1 Specifying a Wallet Location for Transparent Data Encryption 修改sqlnet.ora

[oracle@localhost admin]$ cd /u01/app/oracle/product/11.2.0.3/db_1/network/admin/ 
[oracle@localhost admin]$ cat sqlnet.ora 
ENCRYPTION_WALLET_LOCATION = (SOURCE =(METHOD = FILE)(METHOD_DATA =(DIRECTORY =/u01/app/wallet)))

4.1.2 Setting and Resetting the Master Encryption Key 设置加密主密钥

[oracle@localhost admin]$ sqlplus sys/oracle@prod1 as sysdba 
SQL> alter system set encryption key identified by "ZAQ12WSX"; 
目录/u01/app/wallet下产生一个文件：

[oracle@localhost wallet]$ pwd 
/u01/app/wallet 
[oracle@localhost wallet]$ ll 
total 8 
-rw-r--r-- 1 oracle asmadmin 1573 Jul 30 15:53 ewallet.p12

4.1.3  Opening and Closing the Encrypted Wallet  打开钱包

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "ZAQ12WSX"

SQL> ALTER SYSTEM SET ENCRYPTION WALLET close IDENTIFIED BY "ZAQ12WSX"

4.2.1 加密列

关闭钱包后，不可以查看加密列

但是可以查看非加密列。

SQL> select FIRST_NAME,LAST_NAME from employee;

FIRST_NAME

--------------------------------------------------------------------------------

LAST_NAME

--------------------------------------------------------------------------------

tom

green

重新打开wallet，可以查看加密列：

SQL> ALTER SYSTEM SET ENCRYPTION WALLET open IDENTIFIED BY "ZAQ12WSX";

System altered.

SQL> select * from employee;

FIRST_NAME

--------------------------------------------------------------------------------

LAST_NAME

--------------------------------------------------------------------------------

     EMPID SALARY

---------- ----------

tom

green

      7788 12000

数据库重启后，需要open wallet：

SQL> startup

ORACLE instance started.

Total System Global Area 2505338880 bytes

Fixed Size 2230952 bytes

Variable Size 1543505240 bytes

Database Buffers 939524096 bytes

Redo Buffers 20078592 bytes

Database mounted.

Database opened.

SQL> select * from vpm2.employee;

select * from vpm2.employee

                   *

ERROR at line 1:

ORA-28365: wallet is not open

SQL> conn vpm2/oracle

Connected.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET open IDENTIFIED BY "ZAQ12WSX";

System altered.

SQL> select * from employee;

FIRST_NAME

--------------------------------------------------------------------------------

LAST_NAME

--------------------------------------------------------------------------------

     EMPID SALARY

---------- ----------

tom

green

      7788 12000

4.2.2 加密列上创建索引

SQL> CREATE TABLE employee (

  2 first_name VARCHAR2(10),

  3 last_name VARCHAR2(10),

  4 empID NUMBER ENCRYPT,

  5 salary NUMBER(6) ENCRYPT USING '3DES168'

  6 );

Table created.

SQL> insert into employee values('tom','green',4500,10000);

1 row created.

SQL> commit;

Commit complete.

--

1）加密列的属性必须为no salt才可以创建索引

2）加密列只能创建b-tree索引

3）b-tree索引不能使用range scan索引扫描

SQL> create index idx_empID on employee(empID);

create index idx_empID on employee(empID)

                                   *

ERROR at line 1:

ORA-28338: Column(s) cannot be both indexed and encrypted with salt

SQL> alter table employee modify (empID ENCRYPT no salt);

Table altered.

SQL> create index idx_empID on employee(empID);

Index created.

4.2.3 Restrictions on Using TDE Column Encryption

TDE column encryption encrypts and decrypts data at the SQL layer. Oracle Database utilities and features that bypass the SQL layer cannot leverage the services provided by TDE column encryption. Do not use TDE column encryption with the following database features:

• Index types other than B-tree 只能创建b-tree索引

• Range scan search through an index  索引不可以范围查询

• External large objects (BFILE) 外部对象上不能使用加密列

• Synchronous Change Data Capture

• Transportable Tablespaces

• Original import/export utilities

4.2.4 加密列算法

TDE also enables you to specify a nondefault encryption algorithm. You can choose from one of the following algorithms:

• 3DES168

• AES128

• AES192 (default)

• AES256

4.3 加密表空间

4.3.1 创建加密表空间

SQL> CREATE TABLESPACE securespace DATAFILE '+DATA_VPM/prod1/datafile/secure01.dbf' SIZE 50M ENCRYPTION USING '3DES168' DEFAULT STORAGE(ENCRYPT);

SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;

TABLESPACE_NAME ENCRYPTED

------------------------------ ---------

SYSTEM NO

SYSAUX NO

UNDOTBS1 NO

TEMP NO

USERS NO

EXAMPLE NO

TSPOSMMCH NO

TSPOSMMCH_IDX NO

LOGMNRTS NO

TMP01 NO

TMP02 NO

TABLESPACE_NAME ENCRYPTED

------------------------------ ---------

TBS01 NO

SECURESPACE YES

4.3.2、加密算法

 can have one of the following values:

• 3DES168

• AES128

• AES192

• AES256

The key lengths are included in the names of the algorithms themselves. If no encryption algorithm is specified, the default encryption algorithm is used. The default encryption algorithm is AES128.

4.3.3 Restrictions On Using TDE Tablespace Encryption  加密表空间的使用限制

TDE tablespace encryption encrypts/decrypts data during read/write operations,  加密表空间发生在数据存储的时候，存储在表空间文件上的数据已经加密

as opposed to TDE column encryption, which encrypts/decrypts data at the SQL layer. --TDE加密列是发生在SQL层，由sql调用一个内不能算法INTERNAL_FUNCTION(来加密

This means that most restrictions that apply to TDE column encryption, such as data type restrictions and index type restrictions, are not applicable to TDE tablespace encryption.

The following list includes the restrictions that apply to TDE tablespace encryption:

1) External Large Objects ( BFILE s) cannot be encrypted using TDE tablespace encryption. This is because these files reside outside the database. 外部文件不能使用表空间加密
2) Original import/export utilities are not supported. Use the Oracle Data Pump utility instead. imp/exp不能使用加密表空间，使用能够数据泵来代替

4.3.4  加密表空间上的表使用索引

4.4.4.1 sys用户下的对象不能建在加密的表空间上面。

SQL> create table tt(id int encrypt) TABLESPACE SECURESPACE;

create table tt(id int encrypt) TABLESPACE SECURESPACE

                *

ERROR at line 1:

ORA-28336: cannot encrypt SYS owned objects

4.4.4.2 只能创建b-tree索引

不能创建bitmap索引

SQL> create bitmap index idx_bi on tt(id);

create bitmap index idx_bi on tt(id)

                                 *

ERROR at line 1:

ORA-28337: the specified index may not be defined on an encrypted column

不能创建函数索引：

SQL> create index idx_fun on tt(upper(id));

create index idx_fun on tt(upper(id))

                                 *

ERROR at line 1:

ORA-28337: the specified index may not be defined on an encrypted column

4.4 比较加密列和加密表空间

1、加密方式：

表空间加密 是放生在数据存储的时候，也就是存储在文件上的数据已经被加密；字段

加密发生在SQL层，由SQL调用一个算法对数据进行加密处理。

可以使用logminer挖掘加密列的sql加密：（执行sql的时候，关闭钱包，才可以挖掘到加密的sql，不然是未加密的）

2、加密的限制，比如：

– 索引类型 （加密列和加密表空间都只能创建b-tree索引）

– 都需要 no salt创建索引

– 外部大对象（bfiles)都不可以

– exp/imp不行，需要用expdp/impdp

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28194062/viewspace-1757565/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28194062/viewspace-1757565/