greenplum加密

--如下为greenplum5.0数据库加解密
--加密函数
select encrypt('123456','aa','aes');
--加解密函数
select convert_from(decrypt('\x39c3c665757a0ff973b83fb98cc3d63f','aa','aes'),'SQL_ASCII');
select decrypt('\x39c3c665757a0ff973b83fb98cc3d63f','aa','aes');

--4.3中在表中调用加密函数***************************
select encrypt(t.name,'aa','aes') from sec.sbdc_urdj4vdzjb8avndq t;
[Err] ERROR: function encrypt(character varying, unknown, unknown) does not exist
LINE 2: select encrypt(t.name,'aa','aes') from sec.sbdc_urdj4vdzjb8a...
HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-------------------------------------------------------------------------------------------

--java或手工调用
--调用生成加解密sql的过程
select sbdc.generate_sql(1);

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

--查询sql
select * from sbdc.run_sql;

--查询表
SELECT * FROM sec.test;
-------------------------------------------------------------------------------------------

--如下为加解密模型
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 greenplum4.3中无法调用encrypt(NAME::BYTEA,'aa','aes')
UPDATE SEC.SBDC_URDJ4VDZJB8AVNDQ SET NAME_=encrypt(NAME::BYTEA,'aa','aes');
UPDATE SEC.SBDC_URDJ4VDZJB8AVNDQ SET NAME='';
------------------------------------------------------------------------------------
--2.
--如何删除rule
--drop RULE ruleins_a34;

CREATE OR REPLACE RULE rule_ins_a34 AS
ON insert TO sec.test
DO INSTEAD
INSERT INTO sec.SBDC_URDJ4VDZJB8AVNDQ(ID,NAME_,ADDR) VALUES (NEW.ID,ENCRYPT(NEW.NAME::BYTEA,'aa','aes'),NEW.ADDR);

SELECT * FROM SEC.TEST;
SELECT * FROM SEC.SBDC_URDJ4VDZJB8AVNDQ;
insert into SEC.test select 4,'n4','a4'

------------------------------------------------------------------------------------
--3.
CREATE OR REPLACE RULE rule_upd_a34 AS
ON update TO sec.test
DO INSTEAD
UPDATE sec.SBDC_URDJ4VDZJB8AVNDQ SET ID=NEW.ID ,NAME_=ENCRYPT(NEW.NAME::BYTEA,'aa','aes') ,ADDR=NEW.ADDR WHERE ID=OLD.ID;

SELECT * FROM SEC.TEST;
SELECT * FROM SEC.SBDC_URDJ4VDZJB8AVNDQ;
UPDATE SEC.TEST SET NAME='A444' WHERE ID=4;
------------------------------------------------------------------------------------
--4.
CREATE OR REPLACE RULE rule_del_a34 AS
ON delete TO sec.test
DO INSTEAD
DELETE FROM sec.SBDC_URDJ4VDZJB8AVNDQ WHERE ID=OLD.ID;

SELECT * FROM SEC.TEST;
SELECT * FROM SEC.SBDC_URDJ4VDZJB8AVNDQ;
DELETE FROM SEC.TEST T WHERE T.ID=4;
------------------------------------------------------------------------------------
--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.DBCO