oracle: DBMS_CRYPTO实现对指定列的加密解密

加密指定的varchar2类型的列:

test用户的权限:

GRANT EXECUTE ON  SYS.DBMS_CRYPTO TO test;

创建测试表

[oracle@test1 ~]$ sqlplus test/test
sql> create table t1(a int primary key,psw raw(60));

把创建加密函数的语句放在1.sql里

[oracle@test1 ~]$ wrap iname=1.sql
SQL> @1.plb
Function created.
Function created.

利用加密函数插入测试数据：

[oracle@test1 ~]$ sqlplus test/test
SQL> insert into t1 values(4, f_encrypt('12345678'));
1 row created.
SQL> insert into t1 values(1, f_encrypt('这个是测试'));
1 row created.
SQL> insert into t1 values(3, f_encrypt('abcdefg'));
1 row created.
SQL> commit;
Commit complete.

查询数据是加密过的:

SQL> select * from t1;
	 A PSW
---------- ----------------------------------------
	 4 E1C62821F2C416F7FB851C9868959F98
	 1 370499B0617F62D1686B1917728BF6AE
	 3 EDB2F32298D9137F9BE38AF0248F31D2

通过解密函数查询：

SQL> select a,f_decrypted(psw) psw from t1;

	 A PSW
---------- ----------------------------------------
	 4 12345678
         1 这个是测试
	 3 abcdefg