[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;
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> conn scott/tiger
Connected.
SQL> select * from 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> 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