SafeNet DataSecure 암호화 SQL 샘플 – 뿡뿡너구리의 블로그

// 컬럼 암호화

CREATE SEQUENCE "ESVALI_TECH"."ING_SEQ_2" NOCACHE

lock TABLE "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707" IN EXCLUSIVE MODE

ALTER TABLE "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707" ADD ing_row_id INTEGER

UPDATE "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707"

SET ing_row_id="ESVALI_TECH".ing_seq_2.NEXTVAL
WHERE ROWID=ROWID

CREATE UNIQUE INDEX "ESVALI_TECH".ing_idx_2
ON "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707"
( ing_row_id )

LOCK TABLE "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707" IN EXCLUSIVE MODE

ALTER TABLE "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707" ADD ( "ISSUE_NEW" RAW(528) )

ALTER TABLE "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707" RENAME TO "TB_TECH_HISTORY_HASU0707_NEW"

CREATE OR replace VIEW "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_IDV" AS
SELECT "INDEX",
       "DATE",
       "CUSTOMER",
       "PRODUCT",
       "VERSION",
       "ISSUE",
       "PROCESS",
       "ETC",
       "ING_ROW_ID"
FROM   "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_NEW"

CREATE OR replace VIEW "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707" AS
SELECT "INDEX" ,
       "DATE" ,
       "CUSTOMER" ,
       "PRODUCT" ,
       "VERSION" ,
       "ISSUE" ,
       "PROCESS" ,
       "ETC"
FROM   "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_IDV"

CREATE OR replace TRIGGER "ESVALI_TECH"."TB_TECH_HISTORY_HASU0_INS_TRIG" instead OF

INSERT ON "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_IDV" REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE check_22 VARCHAR2

       (
              512
       );BEGIN
  SELECT :NEW."ISSUE"
  INTO   check_22
  FROM   dual;

  INSERT INTO "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_NEW"
              (
                          "INDEX",
                          "DATE",
                          "CUSTOMER",
                          "PRODUCT",
                          "VERSION",
                          "ISSUE",
                          "ISSUE_NEW",
                          "PROCESS",
                          "ETC",
                          "ING_ROW_ID"
              )
              VALUES
              (
                          :NEW."INDEX",
                          :NEW."DATE",
                          :NEW."CUSTOMER",
                          :NEW."PRODUCT",
                          :NEW."VERSION",
                          :NEW."ISSUE",
                          ingrian.ingfastencryptvarcharbyname(:NEW."ISSUE", 'ESVALI_TECH', 'TB_TECH_HISTORY_HASU0707:3', 'ISSUE', 'C31ECDE409E93E3B401F362BC7B0ADE1'),
                          :NEW."PROCESS",
                          :NEW."ETC",
                          "ESVALI_TECH".ing_seq_2.NEXTVAL
              );

END;

CREATE OR replace TRIGGER "ESVALI_TECH"."TB_TECH_HISTORY_HASU0_UPD_TRIG" instead OF
UPDATE
ON "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_IDV" REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE check_22 VARCHAR2(512);

BEGIN
  SELECT :NEW."ISSUE"
  INTO   check_22
  FROM   dual;

  UPDATE "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_NEW"
  SET    "INDEX" = :NEW."INDEX" ,
         "DATE" = :NEW."DATE" ,
         "CUSTOMER" = :NEW."CUSTOMER" ,
         "PRODUCT" = :NEW."PRODUCT" ,
         "VERSION" = :NEW."VERSION" ,
         "ISSUE" = :NEW."ISSUE" ,
         "ISSUE_NEW" = decode (:NEW."ISSUE" ,
                               :OLD."ISSUE" , "ISSUE_NEW" ,
                               ingrian.ingfastencryptvarcharbyname(:NEW."ISSUE", 'ESVALI_TECH', 'TB_TECH_HISTORY_HASU0707:3', 'ISSUE', 'C31ECDE409E93E3B401F362BC7B0ADE1') ) ,
         "PROCESS" = :NEW."PROCESS" ,
         "ETC" = :NEW."ETC"
  WHERE  ing_row_id = :OLD.ing_row_id;

END;
SELECT   ing_row_id ,
         ingrian.ingstringtoraw("ISSUE")
FROM     "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_NEW"
WHERE    (
                  issue IS NOT NULL )
ORDER BY ing_row_id
UPDATE "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_NEW"
SET    "ISSUE_NEW" = ?
WHERE  ing_row_id = ?
CREATE OR replace VIEW "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_IDV" AS
SELECT "INDEX",
       "DATE",
       "CUSTOMER",
       "PRODUCT",
       "VERSION",
       ingrian.ingfastdecryptop("ISSUE_NEW", 'ESVALI_TECH', 'TB_TECH_HISTORY_HASU0707_NEW:3', 'ISSUE_NEW', 'C31ECDE409E93E3B401F362BC7B0ADE1')"ISSUE",
       "PROCESS",
       "ETC",
       "ING_ROW_ID"
FROM   "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_NEW"

CREATE OR replace TRIGGER "ESVALI_TECH"."TB_TECH_HISTORY_HASU0_INS_TRIG" instead OF
INSERT
ON "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_IDV" REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE check_22 VARCHAR2
       (
              512
       );

BEGIN
  SELECT :NEW."ISSUE"
  INTO   check_22
  FROM   dual;

  INSERT INTO "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_NEW"
              (
                          "INDEX",
                          "DATE",
                          "CUSTOMER",
                          "PRODUCT",
                          "VERSION",
                          "ISSUE",
                          "ISSUE_NEW",
                          "PROCESS",
                          "ETC",
                          "ING_ROW_ID"
              )
              VALUES
              (
                          :NEW."INDEX",
                          :NEW."DATE",
                          :NEW."CUSTOMER",
                          :NEW."PRODUCT",
                          :NEW."VERSION",
                          ' ',
                          ingrian.ingfastencryptvarcharbyname(:NEW."ISSUE", 'ESVALI_TECH', 'TB_TECH_HISTORY_HASU0707:3', 'ISSUE', 'C31ECDE409E93E3B401F362BC7B0ADE1'),
                          :NEW."PROCESS",
                          :NEW."ETC",
                          "ESVALI_TECH".ing_seq_2.NEXTVAL
              );

END;

GRANT SELECT ON "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707" TO "INGRIAN"

GRANT SELECT ON "ESVALI_TECH"."TB_TECH_HISTORY_HASU0707_NEW" TO ingrian

// 무효 데이터 정리

SELECT ing_row_id
FROM   "ESVALI_TECH"."tb_tech_history_hasu0707_new"
ORDER  BY ing_row_id

UPDATE "ESVALI_TECH"."tb_tech_history_hasu0707_new"
SET    "issue" = ' '
WHERE  ing_row_id = ?