Oracle Data Masking TEST2 추가내용

1.Data Masking 옵션(무작위 자리수)

   

   

2.해당 Table 컬럼:EMAIL - Unique Index

3.Data Masking 옵션- 무작위 자릿수

 

   

4.변경된 데이터 값 확인

   

5.상태확인

• 기존에 가지고 있는 Trigger 및 Index 그리고 Constraints가 정상적으로 존재하는 것을 확인할 수 있습니다.
• Check sum으로 인하여 Data Masking 후 Unique Index 생성시 해당 열에 대한 중복값이 발생하지 않아 에러 없이 잘 적용이 되는 것을 확인할 수 있습니다.

     

  

   

   

   

   

<file_error msg="지정된 경로를 찾을 수 없습니다." n="3"/> -- ¿¿ ¿¿¿¿¿¿: test -- ¿¿¿¿ ¿¿ ¿¿: 03-9¿ -2012 18:42 ¿¿¿ ¿¿¿ ¿¿ ¿ User is "SYS" COMMIT ALTER SESSION ENABLE PARALLEL DML DROP TABLE "MGMT_DM_TT_48" PURGE ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿¿, ¿¿ ¿¿ ¿ declare adj number:=0; num number:=0; begin select length(count(*)) into adj from (select distinct "EMAIL" from "HR"."EMPLOYEES"); num := adj; adj := greatest(adj - 3, 0); execute immediate 'create table MGMT_DM_TT_48 (orig _val null, new_val null) NOLOGGING PARALLEL as select CAST(null AS VARCHAR2(25)) orig_val, CAST(null AS VARCHAR2(25)) new_val from dual union all select s.orig_val, case when s.subset = 1 then CAST( lpad(trunc(dbms_ random.value(0, power(10, 2 - least(2, greatest('||adj||' - 0, 0))) - 1)), 2 - least(2, greatest('||adj||' - 0, 0)), 0) || lpad(nvl(substr(s.new_num, 1, 3 + least(2, greatest('||adj||' - 0, 0))),0),3 + least(2, greatest('||adj||' - 0, 0)), 0)   AS VARCHAR2(25)) end new_val from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, '||num||', 0) new_num from (select "EMAIL" orig_val, min(rowid) rid, min( case when 1=1 then 1 end ) subset from "HR"."EMPLOYEES" group by "EMAIL")) s where 1=1 and s.orig_val is not null '; DBMS_STATS.GATHER_TABLE_STATS(NULL, '"MGMT_DM_TT_48"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE); end; DROP TRIGGER "HR"."SECURE_EMPLOYEES" DROP TRIGGER "HR"."UPDATE_JOB_HISTORY" DECLARE CURSOR fk_sql IS select refr.owner, refr.table_name, refr.constraint_name from dba_constraints refd, dba_constraints refr where refd.owner = 'HR' and refd.table_name = 'EMPLOYEES' and refr.constraint_ty pe = 'R' and refr.r_owner = refd.owner and refr.r_constraint_name = refd.constraint_name; BEGIN FOR fk IN fk_sql LOOP EXECUTE IMMEDIATE 'ALTER TABLE "' || fk.owner || '"."' || fk.table_name || '" DROP CONSTRAINT " ' || fk.constraint_name || '"'; END LOOP; END; ¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿: CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_47 AS grant_cur INTEGER; BEGIN grant_cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (grant_cur, ' REVOKE REFERENCES ON "HR"."EMPLOYEES" F ROM "OE"', DBMS_SQL.NATIVE); DBMS_SQL.CLOSE_CURSOR (grant_cur); END; BEGIN "HR".MGMT$MASK_GRANT_47; END; DROP procedure "HR".MGMT$MASK_GRANT_47 ¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿: CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_47 AS grant_cur INTEGER; BEGIN grant_cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (grant_cur, ' REVOKE SELECT ON "HR"."EMPLOYEES" FROM "OE"', DBMS_SQL.NATIVE); DBMS_SQL.CLOSE_CURSOR (grant_cur); END; BEGIN "HR".MGMT$MASK_GRANT_47; END; DROP procedure "HR".MGMT$MASK_GRANT_47 DECLARE CURSOR c_sql IS select owner, table_name, constraint_name, constraint_type, generated, index_name from dba_constraints where owner = 'HR' and table_name = 'EMPLOYEES' and constraint_type <> 'R'; BEGIN FOR c I N c_sql LOOP EXECUTE IMMEDIATE 'ALTER TABLE "' || c.owner || '"."' || c.table_name || '" DROP CONSTRAINT "' || c.constraint_name || '"'; END LOOP; END; DROP INDEX "HR"."EMP_DEPARTMENT_IX" DROP INDEX "HR"."EMP_EMAIL_UK" DROP INDEX "HR"."EMP_EMP_ID_PK" DROP INDEX "HR"."EMP_JOB_IX" DROP INDEX "HR"."EMP_MANAGER_IX" DROP INDEX "HR"."EMP_NAME_IX" ALTER TABLE "HR"."EMPLOYEES" RENAME TO "EMPLOYEES$DMASK" CREATE TABLE "HR"."EMPLOYEES" TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING NOCOMPRESS PARALLEL AS SELECT s."EMPLOYEE_ID", s."FIRST_NAME", s."LAST_NAME", c0m48.NEW_VAL "EMAIL", s."PHONE_ NUMBER", s."HIRE_DATE", s."JOB_ID", s."SALARY", s."COMMISSION_PCT", s."MANAGER_ID", s."DEPARTMENT_ID" FROM "HR"."EMPLOYEES$DMASK" s , MGMT_DM_TT_48 c0m48 WHERE sys_op_map_nonnull(s."EMAIL") = sys_op_map_nonnull(c0m48.ORIG_VAL) ALTER TABLE "HR"."EMPLOYEES" NOPARALLEL DROP TABLE "HR"."EMPLOYEES$DMASK" PURGE CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES" ("DEPARTMENT_ID") TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING CREATE UNIQUE INDEX "HR"."EMP_EMAIL_UK" ON "HR"."EMPLOYEES" ("EMAIL") TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID") TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING CREATE INDEX "HR"."EMP_JOB_IX" ON "HR"."EMPLOYEES" ("JOB_ID") TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING CREATE INDEX "HR"."EMP_MANAGER_IX" ON "HR"."EMPLOYEES" ("MANAGER_ID") TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME", "FIRST_NAME") TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING ALTER TABLE "HR"."EMPLOYEES" MODIFY ("EMAIL" CONSTRAINT "EMP_EMAIL_NN" NOT NULL ) ALTER TABLE "HR"."EMPLOYEES" ADD (CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL") ) ALTER TABLE "HR"."EMPLOYEES" ADD (CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID") ) ALTER TABLE "HR"."EMPLOYEES" MODIFY ("HIRE_DATE" CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ) ALTER TABLE "HR"."EMPLOYEES" MODIFY ("JOB_ID" CONSTRAINT "EMP_JOB_NN" NOT NULL ) ALTER TABLE "HR"."EMPLOYEES" MODIFY ("LAST_NAME" CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ) ALTER TABLE "HR"."EMPLOYEES" ADD (CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ) ALTER TABLE "HR"."EMPLOYEES" ADD (CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ) ALTER TABLE "HR"."EMPLOYEES" ADD (CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ) ALTER TABLE "HR"."EMPLOYEES" ADD (CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "HR"."JOBS" ("JOB_ID") ) ¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿: CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_47 AS grant_cur INTEGER; BEGIN grant_cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (grant_cur, ' GRANT REFERENCES ON "HR"."EMPLOYEES" TO "OE"', DBMS_SQL.NATIVE); DBMS_SQL.CLOSE_CURSOR (grant_cur); END; BEGIN "HR".MGMT$MASK_GRANT_47; END; DROP procedure "HR".MGMT$MASK_GRANT_47 ¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿: CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_47 AS grant_cur INTEGER; BEGIN grant_cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (grant_cur, ' GRANT SELECT ON "HR"."EMPLOYEES" TO "OE "', DBMS_SQL.NATIVE); DBMS_SQL.CLOSE_CURSOR (grant_cur); END; BEGIN "HR".MGMT$MASK_GRANT_47; END; DROP procedure "HR".MGMT$MASK_GRANT_47 CREATE OR REPLACE TRIGGER "HR"."SECURE_EMPLOYEES" BEFORE INSERT OR UPDATE OR DELETE ON "HR"."EMPLOYEES" BEGIN secure_dml; END secure_employees; ALTER TRIGGER "HR"."SECURE_EMPLOYEES" DISABLE CREATE OR REPLACE TRIGGER "HR"."UPDATE_JOB_HISTORY" AFTER UPDATE OF "DEPARTMENT_ID", "JOB_ID" ON "HR"."EMPLOYEES" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN add_job_history(:old.employee_id, :old.hire_date, sysdate, :old.job_ id, :old.department_id); END; COMMENT ON COLUMN "HR"."EMPLOYEES"."COMMISSION_PCT" IS 'Commission percentage of the employee; Only employees in sales department elgible for commission percentage' COMMENT ON COLUMN "HR"."EMPLOYEES"."DEPARTMENT_ID" IS 'Department id where employee works; foreign key to department_id column of the departments table' COMMENT ON COLUMN "HR"."EMPLOYEES"."EMAIL" IS 'Email id of the employee' COMMENT ON COLUMN "HR"."EMPLOYEES"."EMPLOYEE_ID" IS 'Primary key of employees table.' COMMENT ON COLUMN "HR"."EMPLOYEES"."FIRST_NAME" IS 'First name of the employee. A not null column.' COMMENT ON COLUMN "HR"."EMPLOYEES"."HIRE_DATE" IS 'Date when the employee started on this job. A not null column.' COMMENT ON COLUMN "HR"."EMPLOYEES"."JOB_ID" IS 'Current job of the employee; foreign key to job_id column of the jobs table. A not null column.' COMMENT ON COLUMN "HR"."EMPLOYEES"."LAST_NAME" IS 'Last name of the employee. A not null column.' COMMENT ON COLUMN "HR"."EMPLOYEES"."MANAGER_ID" IS 'Manager id of the employee; has same domain as manager_id in departments table. Foreign key to employee_id column of employees table. (useful for reflexive joins and CONNECT BY query)' COMMENT ON COLUMN "HR"."EMPLOYEES"."PHONE_NUMBER" IS 'Phone number of the employee; includes country code and area code' COMMENT ON COLUMN "HR"."EMPLOYEES"."SALARY" IS 'Monthly salary of the employee. Must be greater than zero (enforced by constraint emp_salary_min)' COMMENT ON TABLE "HR"."EMPLOYEES" IS 'employees table. Contains 107 rows. References with departments, jobs, job_history tables. Contains a self reference.' BEGIN DBMS_STATS.GATHER_TABLE_STATS('"HR"', '"EMPLOYEES"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE, cascade=>TRUE); END; ¿¿¿ ¿¿¿¿ ¿¿¿¿¿¿¿. ¿¿ ¿¿¿ ¿¿¿¿¿. ¿¿ ¿¿¿ ¿¿¿ ¿¿ ¿ ¿¿ ¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿ ¿¿¿ ¿¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿¿¿ ¿¿ ¿¿ SQL> set ver on