postgresql9.1数据库加解密

2022-06-05 02:04:17

--如下为postgresql9.1数据库加解密模块配置

--设置schema
psql -U postgres -h localhost
set schema 'sbdc';
--生成日志
\o E:/shanjie/sql/batch.log;
--调用sql脚本
\i E:/shanjie/sql/post.sql;

----

--查询版本
psql --version
--liunx首先要安装pgcrypto模块，找到pgcrypto文件夹，进入进行编译安装
make USE_PGXS=1
make install

--sql command line
create extension pgcrypto ;

--加密函数
select encrypt('123456','aa','aes');
--解密函数
select convert_from(decrypt('\x39c3c665757a0ff973b83fb98cc3d63f','aa','aes'),'SQL_ASCII');
select decrypt('\x39c3c665757a0ff973b83fb98cc3d63f','aa','aes');
-------------------------------------------------------------------------------------------
--java或手工调用
--调用生成加解密sql的过程
select SBDC.GENERATE_SQL(1,'');

--调用加密
select SBDC.PERFORM_ENCRYPT(1);
--调用解密
select SBDC.PERFORM_DECRYPT(1);
-------------------------------------------------------------------------------------------

--如下为加解密模型
CREATE SCHEMA SBDC;
CREATE SCHEMA SEC;

CREATE TABLE SEC.TEST(ID INT,NAME VARCHAR(200),ADDR VARCHAR(200));
ALTER TABLE SEC.TEST ADD CONSTRAINT PK_TEST_ID PRIMARY KEY (ID);

INSERT INTO SEC.TEST SELECT 1,'N1','A1';
INSERT INTO SEC.TEST SELECT 2,'N2','A2';
INSERT INTO SEC.TEST SELECT 3,'N3','A3';

SELECT * FROM SEC.TEST;
SELECT * FROM SEC.SBDC_URDJ4VDZJB8AVNDQ;
-------------------------------------------------------------------------------------------
--1.
ALTER TABLE SEC.TEST RENAME TO SBDC_URDJ4VDZJB8AVNDQ;
ALTER TABLE SEC.SBDC_URDJ4VDZJB8AVNDQ ADD NAME_ BYTEA;
--无ROWID
CREATE VIEW SEC.TEST AS SELECT ID,convert_from(decrypt(NAME_,'aa','aes'),'SQL_ASCII') AS NAME,ADDR FROM SEC.SBDC_URDJ4VDZJB8AVNDQ;

--SEC
UPDATE SEC.SBDC_URDJ4VDZJB8AVNDQ SET NAME_=encrypt(NAME::BYTEA,'aa','aes');
UPDATE SEC.SBDC_URDJ4VDZJB8AVNDQ SET NAME='';
------------------------------------------------------------------------------------
--2.
CREATE OR REPLACE FUNCTION SEC.FINS_A34()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $FUNCTION$
DECLARE
BEGIN
INSERT INTO SEC.SBDC_URDJ4VDZJB8AVNDQ(ID,NAME_,ADDR) VALUES (NEW.ID,ENCRYPT(NEW.NAME::BYTEA,'aa','aes'),NEW.ADDR);
RETURN NULL;
END;
$FUNCTION$;

CREATE TRIGGER INS_SBDC_URDJ4VDZJB8AVNDQ INSTEAD OF INSERT ON SEC.TEST FOR EACH ROW EXECUTE PROCEDURE SEC.FINS_A34();

SELECT * FROM SEC.TEST;
SELECT * FROM SEC.SBDC_URDJ4VDZJB8AVNDQ;
DELETE FROM SEC.TEST T WHERE T.ID=3;
INSERT INTO SEC.TEST(ID,NAME,ADDR) SELECT 3,'N3','A3';

SELECT ENCRYPT(NAME,'aa','aes') FROM SEC.SBDC_URDJ4VDZJB8AVNDQ;

------------------------------------------------------------------------------------
--3.
CREATE OR REPLACE FUNCTION SEC.FUPD_A34()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $FUNCTION$
DECLARE
BEGIN
UPDATE SEC.SBDC_URDJ4VDZJB8AVNDQ SET ID=NEW.ID ,NAME_=ENCRYPT(NEW.NAME::BYTEA,'aa','aes') ,ADDR=NEW.ADDR WHERE ID=OLD.ID;
RETURN NULL;
END;
$FUNCTION$;

CREATE TRIGGER UPD_SBDC_URDJ4VDZJB8AVNDQ INSTEAD OF UPDATE ON SEC.TEST FOR EACH ROW EXECUTE PROCEDURE SEC.FUPD_A34();

SELECT * FROM SEC.TEST;
SELECT * FROM SEC.SBDC_URDJ4VDZJB8AVNDQ;
UPDATE SEC.TEST SET NAME='A333' WHERE ID=3;
------------------------------------------------------------------------------------
--4.
CREATE OR REPLACE FUNCTION SEC.FDEL_A34()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $FUNCTION$
DECLARE
BEGIN
DELETE FROM SEC.SBDC_URDJ4VDZJB8AVNDQ WHERE ID=OLD.ID;
RETURN NULL;
END;
$FUNCTION$;

CREATE TRIGGER DEL_SBDC_URDJ4VDZJB8AVNDQ INSTEAD OF DELETE ON SEC.TEST FOR EACH ROW EXECUTE PROCEDURE SEC.FDEL_A34();

SELECT * FROM SEC.TEST;
SELECT * FROM SEC.SBDC_URDJ4VDZJB8AVNDQ;
DELETE FROM SEC.TEST T WHERE T.ID=3;
------------------------------------------------------------------------------------
--5.
--UNSEC
UPDATE SEC.SBDC_URDJ4VDZJB8AVNDQ SET NAME=convert_from(decrypt(NAME_,'aa','aes'),'SQL_ASCII');

DROP VIEW SEC.TEST;
DROP FUNCTION SEC.FINS_A34();
DROP FUNCTION SEC.FUPD_A34();
DROP FUNCTION SEC.FDEL_A34();
ALTER TABLE SEC.SBDC_URDJ4VDZJB8AVNDQ DROP NAME_ CASCADE;
ALTER TABLE SEC.SBDC_URDJ4VDZJB8AVNDQ RENAME TO TEST;

-------------------------------------------------------------------------------------
--如下为加解密
DROP SCHEMA SBDC CASCADE;
CREATE SCHEMA SBDC;

CREATE SEQUENCE SBDC.SEC_SQLID INCREMENT BY 1 MINVALUE 1 START WITH 1 CACHE 20;

CREATE TABLE SBDC.DBTABLE(
TABLE_ID INTEGER,
SCHEMA_NAME VARCHAR (200),
TABLE_NAME VARCHAR (200),
TABLE_NAME_NEW VARCHAR (200)) WITHOUT OIDS
TABLESPACE PG_DEFAULT;
alter table SBDC.DBTABLE add constraint PK_DBTABLE primary key (TABLE_ID) using index tablespace PG_DEFAULT;

CREATE TABLE SBDC.DBCOLUMN(
TABLE_ID INTEGER,
COLUMN_ID INTEGER,
KEYID INTEGER,
COLUMN_NAME VARCHAR (200)) WITHOUT OIDS
TABLESPACE PG_DEFAULT;
alter table SBDC.DBCOLUMN add constraint PK_DBCOLUMN primary key (COLUMN_ID) using index tablespace PG_DEFAULT;

CREATE TABLE SBDC.COLUMNKEY(
COLUMN_ID INTEGER,
KEYDATA VARCHAR (200),
KEYID INTEGER) WITHOUT OIDS
TABLESPACE PG_DEFAULT;
alter table SBDC.COLUMNKEY add constraint PK_COLUMNKEY primary key (KEYID) using index tablespace PG_DEFAULT;

create table SBDC.RUN_SQL
(
id INTEGER not null,
TABLE_ID INTEGER,
flag INTEGER,
expire INTEGER default 0,
genetime VARCHAR(40),
isexe INTEGER default 0,
sqltext TEXT,
sqltype INTEGER,
batch_id INTEGER
)
tablespace PG_DEFAULT;
alter table SBDC.RUN_SQL add constraint PK_RUN_SQL primary key (ID) using index tablespace PG_DEFAULT;

CREATE TABLE SBDC.COLUMN_STG(
TABLE_ID INTEGER,
COLUMN_ID NUMERIC (10,0),
COLUMN_NAME text,
COLUMN_NAME_NEW text,
DATA_TYPE text,
DATA_LENGTH NUMERIC (10,0),
NULLABLE text,
DATA_DEFAULT TEXT,
KEYDATA text,
DATA_SCALE NUMERIC (10,0),
KEYID INTEGER,
IS_ENC INTEGER,
ISPK INTEGER)
TABLESPACE PG_DEFAULT;

--初始化数据,数据字典中为小写
delete from SBDC.DBTABLE;
delete from SBDC.DBCOLUMN;
delete from SBDC.COLUMNKEY;
commit;
INSERT INTO SBDC.DBTABLE SELECT 1,'sec','test','';
INSERT INTO SBDC.DBCOLUMN SELECT 1,1,1,'name';
INSERT INTO SBDC.COLUMNKEY SELECT 1,'key',1;

SELECT * FROM SBDC.DBTABLE;
SELECT * FROM SBDC.DBCOLUMN;
SELECT * FROM SBDC.COLUMNKEY;

----------------
CREATE OR REPLACE FUNCTION sbdc.random_string(integer)
RETURNS text AS
$BODY$
select array_to_string(array(select substring(
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
from (ceil(random()*62))::int for 1) from generate_series(1,$1)),'');
$BODY$
LANGUAGE sql;

CREATE OR REPLACE FUNCTION sbdc.generate_sql(
v_table_id integer
)
RETURNS record AS
$BODY$
DECLARE
VINDEX INTEGER;
V_SCHEMA_NAME VARCHAR(200);
V_TABLE_NAME VARCHAR(200);
V_TABLE_NAME_NEW VARCHAR(200);
V_COLUMN_NAME SBDC.COLUMN_STG.COLUMN_NAME%TYPE;
V_COLUMN_NAME_NEW SBDC.COLUMN_STG.COLUMN_NAME_NEW%TYPE;
V_IS_ENC SBDC.COLUMN_STG.IS_ENC%TYPE;
V_VIEW_NAME VARCHAR(200);
V_FUNCTION_NAME_INS VARCHAR(200);
V_FUNCTION_NAME_UPD VARCHAR(200);
V_FUNCTION_NAME_DEL VARCHAR(200);
V_TRIGGER_NAME_INS VARCHAR(200);
V_TRIGGER_NAME_UPD VARCHAR(200);
V_TRIGGER_NAME_DEL VARCHAR(200);
V_KEYDATA VARCHAR (200);
L_ENC_RENAME TEXT;
L_ENC_ADD TEXT;
L_COLUMN_AGGREGATE TEXT DEFAULT '';
L_COLUMNE_MAPPING_AGGREGAT TEXT DEFAULT '';
L_COLUMNE_MAPPING_ENC_AGGREGAT TEXT DEFAULT '';
L_COLUMN_ISPK_AGGREGATE TEXT DEFAULT '';
L_COLUMN_ISPK_AGGREGATE1 TEXT DEFAULT '';
L_VIEW_CREATE TEXT;
L_TRIGGER_INS TEXT;
L_TRIGGER_UPD TEXT;
L_TRIGGER_DEL TEXT;
L_ENC_UPD TEXT;
L_ENC_UPD_NULL TEXT;
L_DEC_UPD TEXT;
L_TRIGGER_DEL_DROP TEXT;
L_TRIGGER_UPD_DROP TEXT;
L_TRIGGER_INS_DROP TEXT;
L_VIEW_DROP TEXT;
L_COL_DROP TEXT;
L_TABLE_NAME_INSTATE TEXT;
CUR_COL_ISENC CURSOR FOR SELECT COLUMN_NAME_NEW FROM SBDC.COLUMN_STG T WHERE T.TABLE_ID=V_TABLE_ID AND T.IS_ENC=1;
CUR_COL CURSOR FOR SELECT CASE WHEN IS_ENC=0 THEN COLUMN_NAME ELSE 'convert_from(decrypt('||COLUMN_NAME_NEW||',''''key'''',''''aes''''),''''SQL_ASCII'''') as '||COLUMN_NAME||'' END AS COLUMN_NAME_MAPPING FROM SBDC.COLUMN_STG T WHERE T.TABLE_ID=V_TABLE_ID order by column_id;
CUR_COL_MAPPING CURSOR FOR SELECT COLUMN_NAME_NEW FROM SBDC.COLUMN_STG T WHERE T.TABLE_ID=V_TABLE_ID ORDER BY T.COLUMN_ID;
CUR_COL_ENC_MAPPING CURSOR FOR SELECT COLUMN_NAME,COLUMN_NAME_NEW,KEYDATA,IS_ENC FROM SBDC.COLUMN_STG T WHERE T.TABLE_ID=V_TABLE_ID ORDER BY T.COLUMN_ID;
CUR_ISPK CURSOR FOR SELECT COLUMN_NAME FROM SBDC.COLUMN_STG T WHERE T.ISPK=1 AND T.TABLE_ID=V_TABLE_ID;
CUR_ISPK1 CURSOR FOR SELECT COLUMN_NAME FROM SBDC.COLUMN_STG T WHERE T.ISPK=1 AND T.TABLE_ID=V_TABLE_ID;
CUR_ENC_UPD CURSOR FOR SELECT COLUMN_NAME,COLUMN_NAME_NEW,KEYDATA FROM SBDC.COLUMN_STG T WHERE T.TABLE_ID=V_TABLE_ID AND T.IS_ENC=1;
CUR_ENC_UPD_NULL CURSOR FOR SELECT COLUMN_NAME FROM SBDC.COLUMN_STG T WHERE T.TABLE_ID=V_TABLE_ID AND T.IS_ENC=1;
CUR_DEC_UPD CURSOR FOR SELECT COLUMN_NAME,COLUMN_NAME_NEW,KEYDATA FROM SBDC.COLUMN_STG T WHERE T.TABLE_ID=V_TABLE_ID AND T.IS_ENC=1;
CUR_COL_DROP CURSOR FOR SELECT COLUMN_NAME_NEW FROM SBDC.COLUMN_STG T WHERE T.TABLE_ID=V_TABLE_ID AND T.IS_ENC=1;
BEGIN
DELETE FROM SBDC.RUN_SQL T WHERE T.TABLE_ID=V_TABLE_ID OR T.TABLE_ID=11;
SELECT T.SCHEMA_NAME,T.TABLE_NAME INTO V_SCHEMA_NAME,V_TABLE_NAME FROM SBDC.DBTABLE T WHERE T.TABLE_ID=V_TABLE_ID;

delete from SBDC.COLUMN_STG;

INSERT INTO SBDC.COLUMN_STG
SELECT T1.TABLE_ID,T1.attnum as COLUMN_ID,T1.attname as COLUMN_NAME,CASE WHEN (T4.KEYDATA IS NULL) THEN T1.attname ELSE T1.attname||'_' END AS COLUMN_NAME_NEW,
T1.typname as DATA_TYPE,T1.typlen as DATA_LENGTH,T1.typnotnull as NULLABLE,T1.typdefault as DATA_DEFAULT,T4.KEYDATA,null as DATA_SCALE,T4.KEYID,
CASE WHEN (T4.KEYDATA IS NULL) THEN 0 ELSE 1 END AS IS_ENC,
CASE WHEN t1.contype IS NOT NULL THEN 1 ELSE 0 END AS ISPK
from
(SELECT T2.TABLE_ID,a7.contype,a5.relname as table_name,a1.atttypid,a1.attnum,a1.attname attname,
a6.typname,a6.typlen,a6.typnotnull,a6.typdefault,
CASE WHEN a6.typname IS NOT NULL THEN 1 ELSE 0 END AS ISPK
from
pg_attribute a1
left join pg_class a5 on a5.oid=a1.attrelid
left join pg_type a6 on a6.oid=a1.atttypid
left join (select contype,conrelid,unnest(conkey) conkey from pg_constraint where contype='p') a7 on a7.conrelid=a5.oid and a7.conkey=a1.attnum
left join SBDC.DBTABLE T2 on t2.table_name=a5.relname
where a1.atttypid>0 and a1.attnum>0) t1
LEFT JOIN SBDC.DBCOLUMN T3 ON T3.COLUMN_NAME=T1.attname and T1.TABLE_ID=T3.TABLE_ID
LEFT JOIN SBDC.COLUMNKEY T4 ON T4.KEYID=T3.KEYID
where
T1.TABLE_NAME=V_TABLE_NAME
order by t1.attnum;

V_TABLE_NAME_NEW='SBDC_'||sbdc.random_string(16);
L_ENC_RENAME='INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||',1 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''ALTER TABLE '||V_SCHEMA_NAME||'.'||V_TABLE_NAME||' RENAME TO '||V_TABLE_NAME_NEW||''' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_ENC_RENAME;

OPEN CUR_COL_ISENC;
LOOP
FETCH CUR_COL_ISENC INTO V_COLUMN_NAME_NEW;
EXIT WHEN NOT FOUND;
L_ENC_ADD= 'INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,1 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''ALTER TABLE '||V_SCHEMA_NAME||'.'||V_TABLE_NAME_NEW||' ADD COLUMN '||V_COLUMN_NAME_NEW||' BYTEA'' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_ENC_ADD;
END LOOP;
CLOSE CUR_COL_ISENC;

L_COLUMN_AGGREGATE='';
VINDEX:=1;
OPEN CUR_COL;
LOOP
FETCH CUR_COL INTO V_COLUMN_NAME;
EXIT WHEN NOT FOUND;
L_COLUMN_AGGREGATE=L_COLUMN_AGGREGATE||V_COLUMN_NAME||',';
VINDEX:=VINDEX+1;
END LOOP;
CLOSE CUR_COL;
L_COLUMN_AGGREGATE=SUBSTR(L_COLUMN_AGGREGATE,1,LENGTH(L_COLUMN_AGGREGATE)-1);
L_VIEW_CREATE='INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,1 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''CREATE OR REPLACE VIEW '||V_SCHEMA_NAME||'.'||V_TABLE_NAME||' AS SELECT '||L_COLUMN_AGGREGATE||' FROM '||V_SCHEMA_NAME||'.'||V_TABLE_NAME_NEW||''' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_VIEW_CREATE;

L_COLUMNE_MAPPING_AGGREGAT='';
OPEN CUR_COL_MAPPING;
LOOP
FETCH CUR_COL_MAPPING INTO V_COLUMN_NAME_NEW;
EXIT WHEN NOT FOUND;
IF V_IS_ENC=0 THEN
L_COLUMNE_MAPPING_AGGREGAT=L_COLUMNE_MAPPING_AGGREGAT||V_COLUMN_NAME||',';
ELSE
L_COLUMNE_MAPPING_AGGREGAT=L_COLUMNE_MAPPING_AGGREGAT||V_COLUMN_NAME_NEW||',';
END IF;
END LOOP;
CLOSE CUR_COL_MAPPING;
L_COLUMNE_MAPPING_AGGREGAT=SUBSTR(L_COLUMNE_MAPPING_AGGREGAT,1,LENGTH(L_COLUMNE_MAPPING_AGGREGAT)-1);

L_COLUMNE_MAPPING_ENC_AGGREGAT='';
OPEN CUR_COL_ENC_MAPPING;
LOOP
FETCH CUR_COL_ENC_MAPPING INTO V_COLUMN_NAME,V_COLUMN_NAME_NEW,V_KEYDATA,V_IS_ENC;
EXIT WHEN NOT FOUND;
IF V_IS_ENC=0 THEN
L_COLUMNE_MAPPING_ENC_AGGREGAT=L_COLUMNE_MAPPING_ENC_AGGREGAT||'NEW.'||V_COLUMN_NAME||',';
ELSE
L_COLUMNE_MAPPING_ENC_AGGREGAT=L_COLUMNE_MAPPING_ENC_AGGREGAT||'ENCRYPT(NEW.'||V_COLUMN_NAME||'::BYTEA,'''''||V_KEYDATA||''''',''''aes''''),';
END IF;
END LOOP;
CLOSE CUR_COL_ENC_MAPPING;
V_FUNCTION_NAME_INS='INS_'||V_TABLE_NAME;
V_TRIGGER_NAME_INS='INS_'||V_TABLE_NAME_NEW;
L_COLUMNE_MAPPING_ENC_AGGREGAT=SUBSTR(L_COLUMNE_MAPPING_ENC_AGGREGAT,1,LENGTH(L_COLUMNE_MAPPING_ENC_AGGREGAT)-1);
L_TRIGGER_INS='INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,1 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''CREATE OR REPLACE FUNCTION '||V_SCHEMA_NAME||'.'||V_FUNCTION_NAME_INS||'()'||CHR(10)||' RETURNS TRIGGER'||CHR(10)||' LANGUAGE PLPGSQL'||CHR(10)
||'AS $FUNCTION$'||CHR(10)||'DECLARE'||CHR(10)||'BEGIN '||CHR(10)||' INSERT INTO '||V_SCHEMA_NAME||'.'||V_TABLE_NAME_NEW||'('||L_COLUMNE_MAPPING_AGGREGAT||') VALUES ('||L_COLUMNE_MAPPING_ENC_AGGREGAT||');'||CHR(10)||'RETURN NULL;'||CHR(10)||'END;'||CHR(10)||'$FUNCTION$;'' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_TRIGGER_INS;

EXECUTE 'INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,1 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''CREATE TRIGGER '||V_TRIGGER_NAME_INS||' INSTEAD OF INSERT ON '||V_SCHEMA_NAME||'.'||V_TABLE_NAME||' FOR EACH ROW EXECUTE PROCEDURE SEC.'||V_FUNCTION_NAME_INS||'()'' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';

L_COLUMN_ISPK_AGGREGATE='';
OPEN CUR_ISPK;
LOOP
FETCH CUR_ISPK INTO V_COLUMN_NAME;
EXIT WHEN NOT FOUND;
L_COLUMN_ISPK_AGGREGATE=L_COLUMN_ISPK_AGGREGATE||V_COLUMN_NAME||'='||'OLD.'||V_COLUMN_NAME||' AND ';
END LOOP;
CLOSE CUR_ISPK;
L_COLUMN_ISPK_AGGREGATE=SUBSTR( L_COLUMN_ISPK_AGGREGATE,1,LENGTH( L_COLUMN_ISPK_AGGREGATE)-5);

V_FUNCTION_NAME_UPD=REPLACE(V_FUNCTION_NAME_INS,'INS_','UPD_');
V_TRIGGER_NAME_UPD=REPLACE(V_TRIGGER_NAME_INS,'INS_','UPD_');
L_TRIGGER_UPD='INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,1 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''CREATE OR REPLACE FUNCTION '||V_SCHEMA_NAME||'.'||V_FUNCTION_NAME_UPD||'()'||CHR(10)||' RETURNS TRIGGER'||CHR(10)||' LANGUAGE PLPGSQL'||CHR(10)
||'AS $FUNCTION$'||CHR(10)||'DECLARE'||CHR(10)||'BEGIN '||CHR(10)||' UPDATE '||V_SCHEMA_NAME||'.'||V_TABLE_NAME_NEW||' SET '||L_COLUMNE_MAPPING_ENC_AGGREGAT||' WHERE '||L_COLUMN_ISPK_AGGREGATE||';'||CHR(10)||'RETURN NULL;'||CHR(10)||'END;'||CHR(10)||'$FUNCTION$;'' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_TRIGGER_UPD;

EXECUTE 'INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,1 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''CREATE TRIGGER '||V_TRIGGER_NAME_UPD||' INSTEAD OF UPDATE ON '||V_SCHEMA_NAME||'.'||V_TABLE_NAME||' FOR EACH ROW EXECUTE PROCEDURE SEC.'||V_FUNCTION_NAME_UPD||'()'' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';

V_FUNCTION_NAME_DEL=REPLACE(V_FUNCTION_NAME_INS,'INS_','DEL_');
V_TRIGGER_NAME_DEL=REPLACE(V_TRIGGER_NAME_INS,'INS_','DEL_');
L_TRIGGER_DEL='INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,1 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''CREATE OR REPLACE FUNCTION '||V_SCHEMA_NAME||'.'||V_FUNCTION_NAME_DEL||'()'||CHR(10)||' RETURNS TRIGGER'||CHR(10)||' LANGUAGE PLPGSQL'||CHR(10)
||'AS $FUNCTION$'||CHR(10)||'DECLARE'||CHR(10)||'BEGIN '||CHR(10)||' DELETE FROM '||V_SCHEMA_NAME||'.'||V_TABLE_NAME_NEW||' WHERE '||L_COLUMN_ISPK_AGGREGATE||';'||CHR(10)||'RETURN NULL;'||CHR(10)||'END;'||CHR(10)||'$FUNCTION$;'' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_TRIGGER_DEL;

EXECUTE 'INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,1 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''CREATE TRIGGER '||V_TRIGGER_NAME_DEL||' INSTEAD OF DELETE ON '||V_SCHEMA_NAME||'.'||V_TABLE_NAME||' FOR EACH ROW EXECUTE PROCEDURE SEC.'||V_FUNCTION_NAME_DEL||'()'' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';

L_ENC_UPD='';
OPEN CUR_ENC_UPD;
LOOP
FETCH CUR_ENC_UPD INTO V_COLUMN_NAME,V_COLUMN_NAME_NEW,V_KEYDATA;
EXIT WHEN NOT FOUND;
L_ENC_UPD=L_ENC_UPD||V_COLUMN_NAME_NEW||'=ENCRYPT('||V_COLUMN_NAME||'::BYTEA,'''''||V_KEYDATA||''''',''''aes''''),';
END LOOP;
CLOSE CUR_ENC_UPD;
L_ENC_UPD=SUBSTR(L_ENC_UPD,1,LENGTH(L_ENC_UPD)-1);
L_ENC_UPD='INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,1 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''UPDATE '||V_SCHEMA_NAME||'.'||V_TABLE_NAME_NEW||' SET '||L_ENC_UPD||''' AS SQLTEXT,
1 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_ENC_UPD;

L_ENC_UPD_NULL='';
OPEN CUR_ENC_UPD_NULL;
LOOP
FETCH CUR_ENC_UPD_NULL INTO V_COLUMN_NAME;
EXIT WHEN NOT FOUND;
L_ENC_UPD_NULL=L_ENC_UPD_NULL||V_COLUMN_NAME||'=NULL,';
END LOOP;
CLOSE CUR_ENC_UPD_NULL;
L_ENC_UPD_NULL=SUBSTR(L_ENC_UPD_NULL,1,LENGTH(L_ENC_UPD_NULL)-1);
L_ENC_UPD_NULL='INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,1 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''UPDATE '||V_SCHEMA_NAME||'.'||V_TABLE_NAME_NEW||' SET '||L_ENC_UPD_NULL||''' AS SQLTEXT,
1 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_ENC_UPD_NULL;

L_DEC_UPD='';
OPEN CUR_DEC_UPD;
LOOP
FETCH CUR_DEC_UPD INTO V_COLUMN_NAME,V_COLUMN_NAME_NEW,V_KEYDATA;
EXIT WHEN NOT FOUND;
L_DEC_UPD=L_DEC_UPD||V_COLUMN_NAME||'=convert_from(decrypt('||V_COLUMN_NAME_NEW||','''''||V_KEYDATA||''''',''''aes''''),''''SQL_ASCII''''),';
END LOOP;
CLOSE CUR_DEC_UPD;
L_DEC_UPD=SUBSTR(L_DEC_UPD,1,LENGTH(L_DEC_UPD)-1);
L_DEC_UPD='INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,2 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''UPDATE '||V_SCHEMA_NAME||'.'||V_TABLE_NAME_NEW||' SET '||L_DEC_UPD||''' AS SQLTEXT,
1 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_DEC_UPD;

L_VIEW_DROP='INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,2 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''DROP VIEW '||V_SCHEMA_NAME||'.'||V_TABLE_NAME||''' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_VIEW_DROP;

V_TRIGGER_NAME_DEL='DEL_'||V_TABLE_NAME;
L_TRIGGER_DEL_DROP='INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,2 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''DROP FUNCTION '||V_SCHEMA_NAME||'.'||V_TRIGGER_NAME_DEL||'()'' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_TRIGGER_DEL_DROP;

V_TRIGGER_NAME_UPD='UPD_'||V_TABLE_NAME;
L_TRIGGER_UPD_DROP='INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,2 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''DROP FUNCTION '||V_SCHEMA_NAME||'.'||V_TRIGGER_NAME_UPD||'()'' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_TRIGGER_UPD_DROP;

V_TRIGGER_NAME_INS='INS_'||V_TABLE_NAME;
L_TRIGGER_INS_DROP='INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,2 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''DROP FUNCTION '||V_SCHEMA_NAME||'.'||V_TRIGGER_NAME_INS||'()'' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_TRIGGER_INS_DROP;

L_COL_DROP='';
OPEN CUR_COL_DROP;
LOOP
FETCH CUR_COL_DROP INTO V_COLUMN_NAME_NEW;
EXIT WHEN NOT FOUND;
L_COL_DROP= 'INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||' AS TABLE_ID,2 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''ALTER TABLE '||V_SCHEMA_NAME||'.'||V_TABLE_NAME_NEW||' DROP COLUMN '||V_COLUMN_NAME_NEW||''' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_COL_DROP;
END LOOP;
CLOSE CUR_COL_DROP;

L_TABLE_NAME_INSTATE='INSERT INTO SBDC.RUN_SQL(ID,TABLE_ID,FLAG,EXPIRE,GENETIME,ISEXE,SQLTEXT,SQLTYPE,BATCH_ID) SELECT NEXTVAL(''SBDC.SEC_SQLID''),'
||V_TABLE_ID||',2 AS FLAG,NULL AS EXPIRE,now() AS GENETIME,NULL AS ISEXE,'
||'''ALTER TABLE '||V_SCHEMA_NAME||'.'||V_TABLE_NAME_NEW||' RENAME TO '||V_TABLE_NAME||''' AS SQLTEXT,
0 AS SQLTYPE,NULL AS BATCH_ID';
EXECUTE L_TABLE_NAME_INSTATE;

RETURN null;
END;
$BODY$
LANGUAGE plpgsql;

----------------
CREATE OR REPLACE FUNCTION sbdc.perform_encrypt(v_table_id integer)
RETURNS record AS
$BODY$
declare
L_ENC_DDL TEXT DEFAULT '';
L_ENC_DML TEXT DEFAULT '';
CUR_ENC_DDL CURSOR FOR SELECT SQLTEXT FROM SBDC.RUN_SQL T WHERE TABLE_ID=V_TABLE_ID AND T.FLAG=1 AND T.SQLTYPE=0 ORDER BY ID;
CUR_ENC_DML CURSOR FOR SELECT SQLTEXT FROM SBDC.RUN_SQL T WHERE TABLE_ID=V_TABLE_ID AND T.FLAG=1 AND T.SQLTYPE=1 ORDER BY ID;
BEGIN
BEGIN
PERFORM SBDC.GENERATE_SQL(V_TABLE_ID);
END;

OPEN CUR_ENC_DDL;
LOOP
FETCH CUR_ENC_DDL INTO L_ENC_DDL;
EXIT WHEN NOT FOUND;
EXECUTE L_ENC_DDL;
END LOOP;
CLOSE CUR_ENC_DDL;

OPEN CUR_ENC_DML;
LOOP
FETCH CUR_ENC_DML INTO L_ENC_DML;
EXIT WHEN NOT FOUND;
EXECUTE L_ENC_DML;
END LOOP;
CLOSE CUR_ENC_DML;
RETURN null;
END;
$BODY$
LANGUAGE plpgsql;

----------------
CREATE OR REPLACE FUNCTION sbdc.perform_decrypt(v_table_id integer)
RETURNS record AS
$BODY$
DECLARE
L_DEC_DDL TEXT DEFAULT '';
L_DEC_DML TEXT DEFAULT '';
CUR_DEC_DML CURSOR FOR SELECT SQLTEXT FROM SBDC.RUN_SQL T WHERE TABLE_ID=V_TABLE_ID AND T.FLAG=2 AND T.SQLTYPE=1 ORDER BY ID;
CUR_DEC_DDL CURSOR FOR SELECT SQLTEXT FROM SBDC.RUN_SQL T WHERE TABLE_ID=V_TABLE_ID AND T.FLAG=2 AND T.SQLTYPE=0 ORDER BY ID;
BEGIN
OPEN CUR_DEC_DML;
LOOP
FETCH CUR_DEC_DML INTO L_DEC_DML;
EXIT WHEN NOT FOUND;
EXECUTE L_DEC_DML;
END LOOP;
CLOSE CUR_DEC_DML;

OPEN CUR_DEC_DDL;
LOOP
FETCH CUR_DEC_DDL INTO L_DEC_DDL;
EXIT WHEN NOT FOUND;
EXECUTE L_DEC_DDL;
END LOOP;
CLOSE CUR_DEC_DDL;
RETURN null;
END;
$BODY$
LANGUAGE plpgsql;

码农公寓

相关文章