20190503-每天定时从oracle数据库中取出表字段数据并加密

一、创建用户、数据链

--以sys用户，dba身份，登录
create tablespace jzjg_wg datafile 'E:\oradata\JZJJORA\jzjj_wg1.DBF' SIZE 5000M;
create user jzjg_wg identified by jzjg_wg2019 default tablespace jzjg_wg;
grant dba to jzjg_wg;

--tzq用户，narmal登录
--create database link tzq_jicheng connect to rm identified by rm using '10.74.132.85/racdb'
create database link jzjj_wg connect to trff_app identified by 1 using '10.74.132.188/orcl'

--测试是否能够正常连接
select * from user_tables@jzjj_wg

二、在用户jzjg_wg下创建函数对字符进行加密解密。

1、加密。

create or replace function encrypt_des(p_text varchar2, p_key varchar2) return varchar2 is
v_text varchar2(4000);
v_enc varchar2(4000);
raw_input RAW(128) ;
key_input RAW(128) ;
decrypted_raw RAW(2048);
begin
v_text := rpad( p_text, (trunc(length(p_text)/8)+1)*8, chr(0));
raw_input := UTL_RAW.CAST_TO_RAW(v_text);
key_input := UTL_RAW.CAST_TO_RAW(p_key);
dbms_obfuscation_toolkit.DESEncrypt(input => raw_input,key => key_input,encrypted_data =>decrypted_raw);
v_enc := rawtohex(decrypted_raw);
--dbms_output.put_line(v_enc);
return v_enc;
end;

2、解密

create or replace function decrypt_des(p_text varchar2,p_key varchar2) return varchar2 is v_text varchar2(2000);
begin
dbms_obfuscation_toolkit.DESDECRYPT(input_string => UTL_RAW.CAST_TO_varchar2(p_text),key_string =>p_key, decrypted_string=> v_text);
v_text := rtrim(v_text,chr(0));
--dbms_output.put_line(v_text);
return v_text;
end;

3、测试加密解密功能。

select encrypt_des('18772637634', 'tzq12345') from dual;
80223CE4389D8BC1C866FDA7BF4811A5

select decrypt_des('80223CE4389D8BC1C866FDA7BF4811A5', 'tzq12345') from dual;
18772637634

三、创建存储过程。删除已有表，重新从目标库抽取数据并生成表，并加密数据。

1、创建判断表是否存在并删除的存储过程。

create or replace procedure droptable_ifexists(
p_table in varchar2
) is
v_count number(10);
begin
  select count(*) into v_count from user_tables where table_name=upper(p_table);
  if v_count>0 then
    execute immediate 'drop table '||p_table||' purge';
  end if;
end droptable_ifexists;

2、创建主存储过程。调用加密函数（encrypt_des）及删除表的存储过程（droptable_ifexists）。

create or replace procedure jzjj_trff_app_vehicle
as
begin
droptable_ifexists('VEHICLE_WG');
execute immediate 
'CREATE TABLE VEHICLE_WG AS
select t.hpzl,t.hphm,t.sjhm,sysdate create_date from vehicle@jzjj_wg.regress.rdbms.dev.us.oracle.com t
where t.hpzl=''02'' and t.sjhm is not null';
droptable_ifexists('VEHICLE_JM');
execute immediate 
'CREATE TABLE VEHICLE_JM AS
select t.hpzl,t.hphm,encrypt_des(t.sjhm, ''tzq12345'') sjhm_jm,create_date from VEHICLE_WG t';
droptable_ifexists('VEHICLE_WG');
end jzjj_trff_app_vehicle;

3、测试存储过程是否能正常运行。

call jzjj_trff_app_vehicle();

四、创建job，每天定时执行存储过程。实现每天定时抽取数据并加密。

创建job。每天执行。

DECLARE tzqtest number;
begin
  dbms_job.submit(tzqtest,'jzjj_trff_app_vehicle;',sysdate,'sysdate+1');
  commit;
end;

附：
a、查询job

select * from user_jobs t

b、修改job运行时间间隔

begin
  dbms_job.interval(3,'sysdate+sysdate+3/1440');--3分钟执行一次
  commit;
end;

c、立即运行job。

begin
  dbms_job.run(3);
  commit;
end;

d、删除job

begin
  dbms_job.remove(2);
  commit;
end;