OCP043第十五讲 Database Security

本讲内容主要包括：
1：使用透明数据加密技术(TDE)加密和解密数据
2：数据泵导入导出加密，外部表加密
3：加密rman备份数据
4：使用虚拟专用数据库VPD
一：使用TDE，TDE使用密码学上的透明数据加密技术
方法一：简单使用TDE，但主密钥的密码不能更改，这种方式在密码位置不能使用引号包含
1. [oracle@rhel6 ~]$  mkdir $ORACLE_BASE/admin/$ORACLE_SID/wallet 
2. [oracle@rhel6 ~]$ ls $ORACLE_BASE/admin/$ORACLE_SID/wallet 
3. 
4. SQL> alter system set encryption key identified by oracle123; 
5. System altered. 
6. 
7. [oracle@rhel6 ~]$ ls $ORACLE_BASE/admin/$ORACLE_SID/wallet 
8. ewallet.p12 
9. 
10. SQL> conn hr/hr 
11. Connected. 
12. SQL> create table t04315_a( a number encrypt); 
13. Table created. 
14. 
15. SQL> insert into t04315_a values (1); 
16. 1 row created. 
17. 
18. SQL> commit; 
19. Commit complete 
20. 
21. 
22. SQL> select * from dba_encrypted_columns; 
23. 
24. OWNER TABLE_NAME COLUMN_NAM ENCRYPTION_ALG       SALT 
25. ----- ---------- ---------- -------------------- --------- 
26. HR    T04315_A   A          AES 192 bits key     YES 
27. 
28. SQL> select * from hr.t04315_a; 
29. 
30.          A 
31. ---------- 
32.          1 
33. 
34. SQL> alter system set encryption wallet close; 
35. System altered. 
36. 
37. SQL> select * from hr.t04315_a; 
38. select * from hr.t04315_a 
39.                  * 
40. ERROR at line 1: 
41. ORA-28365: wallet is not open 
42. 
43. 
44. SQL> insert into hr.t04315_a values (2); 
45. insert into hr.t04315_a values (2) 
46.                * 
47. ERROR at line 1: 
48. ORA-28365: wallet is not open
方法二：使用owm程序生成ewallet.p12文件，需要修改sqlnet.ora文件，使用这种方式在需要使用引号将密码包含生成新的主密钥之前，需要取消加密字段，关闭之前的主密钥
1. SQL> alter system set encryption wallet open identified by oracle123; 
2. System altered. 
3. 
4. SQL> alter table hr.t04315_a modify (a decrypt); 
5. Table altered. 
6. 
7. SQL> select * from dba_encrypted_columns; 
8. no rows selected 
9. 
10. 准备目录，修改sqlnet.ora文件 
11. [oracle@rhel6 ~]$ mkdir -p $ORACLE_BASE/wallet/$ORACLE_SID 
12. [oracle@rhel6 ~]$ cat  $ORACLE_HOME/network/admin/sqlnet.ora 
13. ENCRYPTION_WALLET_LOCATION= 
14. (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/wallet/ora10g/))) 
使用OWM工具产生ewallet.p12文件
 
 

 
[oracle@rhel6 ~]$ ls $ORACLE_BASE/wallet/$ORACLE_SID
ewallet.p12
使用密钥进行加密和解密,no salt代表不进行加密填充，索引字段的加密一般要使用no salt
1. SQL> alter system set encryption key identified by "oracle456"; 
2. System altered 
3. 
4. SQL> create table hr.t04315_b (a number encrypt using '3des168' no salt); 
5. Table created. 
6. 
7. SQL> insert into hr.t04315_b values (1); 
8. 1 row created. 
9. 
10. SQL> commit; 
11. Commit complete. 
12. 
13. SQL> alter table hr.t04315_b modify (a encrypt salt); 
14. Table altered. 
15. 
16. SQL> alter table hr.t04315_b modify (a decrypt); 
17. Table altered. 
18. 
19. SQL> alter system set encryption wallet close; 
20. System altered.
方法三：连接wallet，可以实现用户输入"conn /@connectstring"方式登录，注意区别外部用户验证方式，不能通TDE加密共存
准备目录，配置tns串和sqlnet.ora文件
1. [oracle@rhel6 ~]$ mkdir -p $ORACLE_BASE/connectwallet/$ORACLE_SID 
2. [oracle@rhel6 ~]$ tail -n 8 /u01/app/oracle/network/admin/tnsnames.ora  
3. HRUSERS = 
4.   (DESCRIPTION = 
5.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.4)(PORT = 1521)) 
6.     (CONNECT_DATA = 
7.       (SERVER = DEDICATED) 
8.       (SERVICE_NAME = ora10g.766.com) 
9.     ) 
10.   ) 
11.    
12. [oracle@rhel6 ~]$ cat   $ORACLE_HOME/network/admin/sqlnet.ora 
13. SQLNET.WALLET_OVERRIDE=TRUE
14. WALLET_LOCATION= 
15. (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/u01/app/connectwallet/ora10g/)))
使用mkstore命令创建主密码，添加用户凭据
1. [oracle@rhel6 ~]$  mkstore -create -wrl /u01/app/connectwallet/ora10g/ 
2. Enter password:        
3. Enter password again: 
4. [oracle@rhel6 ~]$ ls /u01/app/connectwallet/ora10g/ 
5. cwallet.sso  ewallet.p12 
6. 
7. [oracle@rhel6 ~]$ mkstore -wrl /u01/app/connectwallet/ora10g/ -createCredential HRUSERS hr hr 
8. Enter password:           
9. Create credential oracle.security.client.connect_string1
测试：
1. [oracle@rhel6 ~]$ sqlplus /nolog 
2. SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 10 11:38:45 2011 
3. Copyright (c) 1982, 2005, Oracle.  All rights reserved. 
4. SQL> conn /@HRUSERS 
5. Connected. 
6. SQL> show user; 
7. USER is "HR" 
8. SQL> select count(*) from employees; 
9. 
10.   COUNT(*) 
11. ---------- 
12.        110
二：数据泵导入导出加密，外部表加密  
1.  数据泵导入导出加密
1. [oracle@rhel6 ~]$ cat /u01/app/oracle/network/admin/sqlnet.ora  
2. WALLET_LOCATION= 
3. (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/u01/app/wallet/ora10g/)))  
4. [oracle@rhel6 ~]$ sqlplus /nolog 
5. SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 12 09:10:23 2011 
6. Copyright (c) 1982, 2005, Oracle.  All rights reserved. 
7. SQL> conn /as sysdba 
8. Connected. 
9. SQL> create directory dir01 as '/home/oracle/dir01'; 
10. Directory created. 
11. 
12. SQL> grant read,write on directory dir01 to hr; 
13. Grant succeeded     
14. 
15. SQL> alter system set encryption wallet open identified by "oracle456"; 
16. System altered 
17. 
18. SQL> conn hr/hr 
19. Connected. 
20. SQL> create table t04315_c (id number,name varchar2(20) encrypt); 
21. Table created. 
22. 
23. SQL> insert into t04315_c values (1,'sam'); 
24. 1 row created. 
25. 
26. SQL> commit; 
27. Commit complete 
28. 
29. [oracle@rhel6 ~]$ expdp hr/hr directory=dir01 dumpfile=1.dmp tables=t04315_c encryption_password=oracle123456
30. Export: Release 10.2.0.1.0 - 64bit Production on Friday, 12 August, 2011 9:18:42 
31. Copyright (c) 2003, 2005, Oracle.  All rights reserved. 
32. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production 
33. With the Partitioning, OLAP and Data Mining options 
34. Starting "HR"."SYS_EXPORT_TABLE_01":  hr/******** directory=dir01 dumpfile=1.dmp tables=t04315_c encryption_password=***** 
35. Estimate in progress using BLOCKS method... 
36. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
37. Total estimation using BLOCKS method: 64 KB 
38. Processing object type TABLE_EXPORT/TABLE/TABLE 
39. . . exported "HR"."T04315_C"                             5.273 KB       1 rows 
40. Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded 
41. ****************************************************************************** 
42. Dump file set for HR.SYS_EXPORT_TABLE_01 is: 
43.   /home/oracle/dir01/1.dmp 
44. Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 09:18:55
若不使用encryption_password指定密码，则会提示ORA-39173，加密的数据已经被泄密导出
1. [oracle@rhel6 ~]$ expdp hr/hr directory=dir01 dumpfile=2.dmp tables=t04315_c  
2. Export: Release 10.2.0.1.0 - 64bit Production on Friday, 12 August, 2011 9:19:35 
3. Copyright (c) 2003, 2005, Oracle.  All rights reserved. 
4. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production 
5. With the Partitioning, OLAP and Data Mining options 
6. Starting "HR"."SYS_EXPORT_TABLE_01":  hr/******** directory=dir01 dumpfile=2.dmp tables=t04315_c  
7. Estimate in progress using BLOCKS method... 
8. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
9. Total estimation using BLOCKS method: 64 KB 
10. Processing object type TABLE_EXPORT/TABLE/TABLE 
11. . . exported "HR"."T04315_C"                             5.218 KB       1 rows 
12. ORA-39173: Encrypted data has been stored unencrypted in dump file set. 
13. Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded 
14. ****************************************************************************** 
15. Dump file set for HR.SYS_EXPORT_TABLE_01 is: 
16.   /home/oracle/dir01/2.dmp 
17. Job "HR"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 09:19:40
 
 
 
 
2.外部表加密,只能针对datapump外部表进行加密
1. SQL> create table t04315_ext (a,b encrypt identified by oracle123) 
2.   2  organization external 
3.   3  (type oracle_datapump 
4.   4  default directory dir01 
5.   5  location ('3.dmp')) 
6.   6  reject limit unlimited 
7.   7* as select * from t04315_c; 
8. Table created
三：加密rman备份数据
rman备份的加密分三类：透明模式(钱夹)加密，密码模式加密，双重模式加密；oracle会自动归档主密钥和密码的变更，rman备份加密必须要把compatible参数设置到10.2.0以上
1.透明模式,适用于本地的oracle备份和恢复操作，需要有主密钥，rman备份加密的算法主要有3种
1. SQL> select ALGORITHM_ID,ALGORITHM_NAME from V$RMAN_ENCRYPTION_ALGORITHMS; 
2. ALGORITHM_ID ALGORITHM_ 
3. ------------ ---------- 
4.            1 AES128 
5.            2 AES192 
6.            3 AES256 
7. 
8. RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON; 
9. new RMAN configuration parameters: 
10. CONFIGURE ENCRYPTION FOR DATABASE ON; 
11. new RMAN configuration parameters are successfully stored 
12. 
13. RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256'; 
14. new RMAN configuration parameters: 
15. CONFIGURE ENCRYPTION ALGORITHM 'AES256'; 
16. new RMAN configuration parameters are successfully stored 
17. 
18. RMAN> list backupset; 
19. RMAN> backup tablespace users; 
20. 
21. SQL> conn /as sysdba 
22. Connected. 
23. SQL> alter tablespace users offline immediate; 
24. Tablespace altered. 
25. 
26. SQL> alter tablespace users online; 
27. alter tablespace users online 
28. * 
29. ERROR at line 1: 
30. ORA-01113: file 4 needs media recovery 
31. ORA-01110: data file 4: '/u01/app/oradata/ora10g/users01.dbf' 
32. 
33. SQL> alter system set encryption wallet close; 
34. System altered. 
35. 
36. RMAN> restore tablespace users; 
37. RMAN-00571: =========================================================== 
38. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
39. RMAN-00571: =========================================================== 
40. RMAN-03002: failure of restore command at 08/12/2011 10:12:11 
41. ORA-19870: error reading backup piece /u01/app/flash_recovery_area/ORA10G/backupset/xxxxx 
42. ORA-19913: unable to decrypt backup 
43. ORA-28365: wallet is not open 
44. 
45. SQL> alter system set encryption wallet open identified by "oracle456"; 
46. System altered 
47. 
48. RMAN> restore tablespace users; 
49. RMAN> recover tablespace users; 
50. RMAN> sql "alter tablespace users online"; 
51. sql statement: alter tablespace users online
2.密码模式加密，适用于加密rman备份的异机恢复
1. RMAN> set encryption on identified by a123456 only; 
2. executing command: SET encryption 
3. using target database control file instead of recovery catalog 
4. 
5. RMAN> backup tablespace users; 
6. RMAN> restore tablespace users; 
7. RMAN-00571: =========================================================== 
8. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
9. RMAN-00571: =========================================================== 
10. RMAN-03002: failure of restore command at 08/12/2011 10:20:40 
11. ORA-19870: error reading backup piece /u01/app/flash_recovery_area/ORA10G/backupset/2011_08_12/xxxx 
12. ORA-19913: unable to decrypt backup 
13. 
14. RMAN> set decryption identified by b123456,c123456,a123456; 
15. RMAN> restore tablespace users; 
16. RMAN> recover tablespace users; 
17. RMAN> sql "alter tablespace users online";
3；双重模式，适用于本地和远程加密备份场景，只需要去掉之前密码模式中的only关键字即可，同时需要将主密钥打开，只要有一种条件满足就可以解开rman备份
1. RMAN> set encryption on identified by b123456; 
2. executing command: SET encryption 
3. using target database control file instead of recovery catalog
四：使用虚拟专用数据库VPD
使用VPD，可以实现细粒度的访问控制(FGAC),利用函数自动返回where条件改写用户的sql语句，保护敏感的数据，当用户的查询不涉及敏感数据的时候，返回全部的值
1.创建用户，准备基础数据
1. SQL> create user zhang3 identified by oracle account unlock; 
2. User created. 
3. SQL> create user li4 identified by oracle account unlock; 
4. User created. 
5. SQL> grant resource,connect to zhang3,li4; 
6. Grant succeeded. 
7. SQL> grant select on hr.t04315_vpd to zhang3,li4; 
8. Grant succeeded 
9. 
10. SQL> conn hr/hr 
11. Connected. 
12. SQL> create table t04315_vpd(first_name varchar2(20),salary number(8,2),department_id number); 
13. Table created. 
14. 
15. SQL> insert into t04315_vpd values ('ZHANG3',3000,10); 
16. 1 row created. 
17. 
18. SQL> insert into t04315_vpd values ('LI4',4000,20); 
19. 1 row created. 
20. 
21. SQL> commit; 
22. Commit complete.
2.创建函数
1. SQL> create or replace function func_t04315_vpd 
2.   2  (owner varchar2,objectname varchar2) 
3.   3  return varchar2 
4.   4  is 
5.   5  where_cluase varchar2(4000); 
6.   6  begin 
7.   7  where_cluase := 'first_name=sys_context(''userenv'',''session_user'')'; 
8.   8  return where_cluase; 
9.   9  end; 
10. Function created. 
11. 
12. SQL> select status from dba_objects where object_name='FUNC_T04315_VPD'; 
13. 
14. STATUS 
15. --------------------- 
16. VALID
3.添加策略
1. SQL> BEGIN 
2.   2   dbms_rls.add_policy(object_schema => 'hr', 
3.   3   object_name => 't04315_vpd', 
4.   4   policy_name => 'policy1', 
5.   5   function_schema =>'sys', 
6.   6   policy_function => 'func_t04315_vpd', 
7.   7   statement_types =>'select', 
8.   8   sec_relevant_cols=>'salary'); 
9.   9* END;   
10. PL/SQL procedure successfully completed 
11. 
12. SQL> select object_owner,sel,ins from dba_policies where object_name='T04315_VPD'; 
13. 
14. OBJECT_OWN SEL       INS 
15. ---------- --------- --------- 
16. HR         YES       NO
4：测试，sys用户不受策略影响
1. SQL> conn /as sysdba 
2. Connected. 
3. SQL> select * from hr.t04315_vpd; 
4. 
5. FIRST_NAME     SALARY DEPARTMENT_ID 
6. ---------- ---------- ------------- 
7. ZHANG3           3000            10 
8. LI4              4000            20 
9.          
10. SQL> conn hr/hr 
11. Connected. 
12. SQL> select * from t04315_vpd; 
13. no rows selected 
14.          
15. SQL> conn zhang3/oracle 
16. Connected. 
17. SQL> select * from hr.t04315_vpd; 
18. 
19. FIRST_NAME     SALARY DEPARTMENT_ID 
20. ---------- ---------- ------------- 
21. ZHANG3           3000            10 
22. 
23. SQL> select first_name from hr.t04315_vpd; 
24. 
25. FIRST_NAME 
26. ---------- 
27. ZHANG3 
28. LI4 
29. 
30. SQL> conn li4/oracle 
31. Connected. 
32. SQL> select * from hr.t04315_vpd; 
33. 
34. FIRST_NAME     SALARY DEPARTMENT_ID 
35. ---------- ---------- ------------- 
36. LI4              4000            20 
37. 
38. SQL> select first_name from hr.t04315_vpd; 
39. 
40. FIRST_NAME 
41. ---------- 
42. ZHANG3 
43. LI4
 
本文出自 “月牙天冲” 博客