postgresql9.1数据库加解密

概述--如下为postgresql9.1数据库加解密模块配置 --查询版本 psql --version --liunx首先要安装pgcrypto模块，找到pgcrypto文件夹，进入进行编译安装 make USE_PGXS=1 make install --sql command line create extension pgcrypto ; --加密函数 select encrypt(‘1234

--如下为postgresql9.1数据库加解密模块配置
--查询版本
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‘,‘aes‘),‘sql_ASCII‘);
select decrypt(‘\x39c3c665757a0ff973b83fb98cc3d63f‘,‘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_,‘sql_ASCII‘) AS name,ADDR FROM SEC.SBDC_URDJ4VDZJB8AVNDQ;

--SEC
UPDATE SEC.SBDC_URDJ4VDZJB8AVNDQ SET name_=encrypt(name::BYTEA,‘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,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,‘A3‘;

SELECT ENCRYPT(name,‘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,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=1;
------------------------------------------------------------------------------------
--5.
--UNSEC
UPDATE SEC.SBDC_URDJ4VDZJB8AVNDQ SET name=convert_from(decrypt(name_,‘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,
NulLABLE text,
DATA_DEFAulT TEXT,
KEYDATA text,
DATA_SCALE NUMERIC (10,
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,‘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,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,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,‘
||V_table_ID||‘ AS table_ID,‘
||‘‘‘ALTER table ‘||V_SCHEMA_name||‘.‘||V_table_name_NEW||‘ ADD ColUMN ‘||V_ColUMN_name_NEW||‘ BYTEA‘‘ AS sqlTEXT,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,LENGTH(L_ColUMN_AGGREGATE)-1);
L_VIEW_CREATE=‘INSERT INTO SBDC.RUN_sql(ID,‘
||‘‘‘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,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,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||‘‘‘‘‘,‘;
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,LENGTH(L_ColUMNE_MAPPing_ENC_AGGREGAT)-1);
L_TRIGGER_INS=‘INSERT INTO SBDC.RUN_sql(ID,‘
||‘‘‘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)||‘END;‘||CHR(10)||‘$FUNCTION$;‘‘ AS sqlTEXT,NulL AS BATCH_ID‘;
EXECUTE L_TRIGGER_INS;

EXECUTE ‘INSERT INTO SBDC.RUN_sql(ID,‘
||‘‘‘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,NulL AS BATCH_ID‘;

L_ColUMNE_MAPPing_ENC_AGGREGAT=‘‘;
OPEN CUR_Col_ENC_MAPPing;
LOOP
FETCH CUR_Col_ENC_MAPPing INTO V_ColUMN_name,V_IS_ENC;
EXIT WHEN NOT FOUND;
IF V_IS_ENC=0 THEN
L_ColUMNE_MAPPing_ENC_AGGREGAT=L_ColUMNE_MAPPing_ENC_AGGREGAT||V_ColUMN_name||‘=‘||‘NEW.‘||V_ColUMN_name||‘,‘;
ELSE
L_ColUMNE_MAPPing_ENC_AGGREGAT=L_ColUMNE_MAPPing_ENC_AGGREGAT||V_ColUMN_name_NEW||‘=ENCRYPT(NEW.‘||V_ColUMN_name||‘::BYTEA,‘;
END IF;
END LOOP;
CLOSE CUR_Col_ENC_MAPPing;
L_ColUMNE_MAPPing_ENC_AGGREGAT=SUBSTR(L_ColUMNE_MAPPing_ENC_AGGREGAT,LENGTH(L_ColUMNE_MAPPing_ENC_AGGREGAT)-1);

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,LENGTH( L_ColUMN_ISPK_AGGREGATE)-5);

--V_TRIGGER_name_UPD=‘UPD_‘||V_table_name_NEW
V_FUNCTION_name_UPD=REPLACE(V_FUNCTION_name_INS,‘INS_‘,‘UPD_‘);
V_TRIGGER_name_UPD=REPLACE(V_TRIGGER_name_INS,‘UPD_‘);
L_TRIGGER_UPD=‘INSERT INTO SBDC.RUN_sql(ID,‘
||‘‘‘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)||‘END;‘||CHR(10)||‘$FUNCTION$;‘‘ AS sqlTEXT,NulL AS BATCH_ID‘;
EXECUTE L_TRIGGER_UPD;

EXECUTE ‘INSERT INTO SBDC.RUN_sql(ID,‘
||‘‘‘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,NulL AS BATCH_ID‘;

--V_TRIGGER_name_DEL=‘DEL_‘||V_table_name_NEW
V_FUNCTION_name_DEL=REPLACE(V_FUNCTION_name_INS,‘DEL_‘);
V_TRIGGER_name_DEL=REPLACE(V_TRIGGER_name_INS,‘DEL_‘);
L_TRIGGER_DEL=‘INSERT INTO SBDC.RUN_sql(ID,‘
||‘‘‘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)||‘END;‘||CHR(10)||‘$FUNCTION$;‘‘ AS sqlTEXT,NulL AS BATCH_ID‘;
EXECUTE L_TRIGGER_DEL;

EXECUTE ‘INSERT INTO SBDC.RUN_sql(ID,‘
||‘‘‘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,NulL AS BATCH_ID‘;

L_ENC_UPD=‘‘;
OPEN CUR_ENC_UPD;
LOOP
FETCH CUR_ENC_UPD INTO V_ColUMN_name,V_KEYDATA;
EXIT WHEN NOT FOUND;
L_ENC_UPD=L_ENC_UPD||V_ColUMN_name_NEW||‘=ENCRYPT(‘||V_ColUMN_name||‘::BYTEA,‘;
END LOOP;
CLOSE CUR_ENC_UPD;
L_ENC_UPD=SUBSTR(L_ENC_UPD,LENGTH(L_ENC_UPD)-1);
L_ENC_UPD=‘INSERT INTO SBDC.RUN_sql(ID,‘
||‘‘‘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,LENGTH(L_ENC_UPD_NulL)-1);
L_ENC_UPD_NulL=‘INSERT INTO SBDC.RUN_sql(ID,‘
||‘‘‘UPDATE ‘||V_SCHEMA_name||‘.‘||V_table_name_NEW||‘ SET ‘||L_ENC_UPD_NulL||‘‘‘ AS sqlTEXT,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_KEYDATA;
EXIT WHEN NOT FOUND;
L_DEC_UPD=L_DEC_UPD||V_ColUMN_name||‘=convert_from(decrypt(‘||V_ColUMN_name_NEW||‘,‘‘‘‘sql_ASCII‘‘‘‘),‘;
END LOOP;
CLOSE CUR_DEC_UPD;
L_DEC_UPD=SUBSTR(L_DEC_UPD,LENGTH(L_DEC_UPD)-1);
L_DEC_UPD=‘INSERT INTO SBDC.RUN_sql(ID,2 AS FLAG,‘
||‘‘‘UPDATE ‘||V_SCHEMA_name||‘.‘||V_table_name_NEW||‘ SET ‘||L_DEC_UPD||‘‘‘ AS sqlTEXT,NulL AS BATCH_ID‘;
EXECUTE L_DEC_UPD;

L_VIEW_DROP=‘INSERT INTO SBDC.RUN_sql(ID,‘
||‘‘‘DROP VIEW ‘||V_SCHEMA_name||‘.‘||V_table_name||‘‘‘ AS sqlTEXT,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,‘
||‘‘‘DROP FUNCTION ‘||V_SCHEMA_name||‘.‘||V_TRIGGER_name_DEL||‘()‘‘ AS sqlTEXT,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,‘
||‘‘‘DROP FUNCTION ‘||V_SCHEMA_name||‘.‘||V_TRIGGER_name_UPD||‘()‘‘ AS sqlTEXT,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,‘
||‘‘‘DROP FUNCTION ‘||V_SCHEMA_name||‘.‘||V_TRIGGER_name_INS||‘()‘‘ AS sqlTEXT,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,‘
||‘‘‘ALTER table ‘||V_SCHEMA_name||‘.‘||V_table_name_NEW||‘ DROP ColUMN ‘||V_ColUMN_name_NEW||‘‘‘ AS sqlTEXT,NulL AS BATCH_ID‘;
EXECUTE L_Col_DROP;
END LOOP;
CLOSE CUR_Col_DROP;

L_table_name_INSTATE=‘INSERT INTO SBDC.RUN_sql(ID,‘
||‘‘‘ALTER table ‘||V_SCHEMA_name||‘.‘||V_table_name_NEW||‘ REname TO ‘||V_table_name||‘‘‘ AS sqlTEXT,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;

总结

以上是内存溢出为你收集整理的postgresql9.1数据库加解密全部内容，希望文章能够帮你解决postgresql9.1数据库加解密所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。