oracle数据透明加密技术

来源：互联网发布：cg资源网源码编辑：程序博客网时间：2024/05/07 02:47

[oracle@] mkdir -p /opt/oracle/product/10g/network/admin/encryption_wallet

[oracle@hadoop-m1 encryption_wallet]$ more /opt/oracle/product/10g/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /opt/oracle/product/10g/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
#SQLNET.AUTHENTICATION_SERVICES= (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/product/10g/network/admin/encryption_wallet/)))

SQL> alter system set encryption key authenticated by "heizistudio";

System altered.

SQL> alter system set encryption wallet close identified by "heizistudio";
alter system set encryption wallet close identified by "heizistudio"
                                         *
ERROR at line 1:
ORA-28364: invalid wallet operation

SQL> SQL> alter system set encryption wallet close;

System altered.

SQL> alter system set wallet open identified by "heizistudio";

System altered.

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

创建表

-------------------------------------------------------------
SQL> conn scott/tiger
Connected.
SQL>

SQL> desc emp;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPNO                                     NOT NULL NUMBER(4)
ENAME                                              VARCHAR2(10)
JOB                                                VARCHAR2(9)
MGR                                                NUMBER(4)
HIREDATE                                           DATE
SAL                                                NUMBER(7,2)
COMM                                               NUMBER(7,2)
DEPTNO                                             NUMBER(2)

SQL> create table t5(EMPNO NUMBER(4),ENAME VARCHAR2(10) encrypt);

Table created.

SQL> insert into t5 select EMPNO,ENAME from emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba

SQL> SQL> select * from scott.t5;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

SQL> select EMPNO from scott.t5;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

14 rows selected.

SQL> select ENAME from scott.t5;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

SQL> select * from dba_encrypted_columns;

OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL
------------------------------ ------------------------------ ------------------------------ ----------------------------- ---
SCOTT T5 ENAME AES 192 bits key YES

SQL> alter system set encryption wallet close;

System altered.

SQL> select ENAME from scott.t5;
select ENAME from scott.t5
*
ERROR at line 1:
ORA-28365: wallet is not open

SQL> select EMPNO from scott.t5;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

14 rows selected.

SQL> alter system set encryption wallet close;

System altered.

SQL> alter system set wallet open;
alter system set wallet open
*
ERROR at line 1:

SQL>shutdown immediate

SQL>startup

SQL> alter system set encryption wallet open identified by "heizistudio"; ---关闭wallet后迫不得己重启数据库

System altered.

SQL> select * from scott.t5;

14 rows selected.

SQL> conn scott/tiger
Connected.

SQL> select * from t5;

14 rows selected.

SQL> create index a1 on scott.t5(ENAME);
create index a1 on scott.t5(ENAME)
*
ERROR at line 1:
ORA-28338: cannot encrypt indexed column(s) with salt

SQL> alter table scott.t5 modify ENAME VARCHAR2(10) ENCRYPT no salt;

Table altered.

SQL> create index a1 on scott.t5(ENAME);

Index created.

在加密列时，存在两个选项：Salt和No Salt。
Salt在加密前对数据增加随即字符串，增加破解的难度，使得同样的字符串加密结果不同；而对于NO Salt，则同样字符串可以获得同样的加密输出，其安全性相对略低。

在加密列上，如果使用Salt方式，则不能创建索引,Salt加密和索引两种属性互斥，不能同时设置。当使用缺省Salt方式加密时，此时允许对于加密列创建索引

参考文献

http://www.eygle.com/archives/2011/09/oracle_transparent_data_encryption.html

http://www.2cto.com/database/201109/104248.html