0.HR User 현재상태

   

   

EMP_DEPT_FK        Referential Integrity        Enabled        No Action        Not Deferrable        Immediate        Validated                        1        DEPARTMENT_ID

EMP_EMAIL_NN        Check        Enabled                Not Deferrable        Immediate        Validated                        1        "EMAIL" IS NOT NULL

EMP_EMAIL_UK        Unique Key        Enabled                Not Deferrable        Immediate        Validated                        1        EMAIL

EMP_EMP_ID_PK        Primary Key        Enabled                Not Deferrable        Immediate        Validated                        1        EMPLOYEE_ID

EMP_HIRE_DATE_NN        Check        Enabled                Not Deferrable        Immediate        Validated                        1        "HIRE_DATE" IS NOT NULL

EMP_JOB_FK        Referential Integrity        Enabled        No Action        Not Deferrable        Immediate        Validated                        1        JOB_ID

EMP_JOB_NN        Check        Enabled                Not Deferrable        Immediate        Validated                        1        "JOB_ID" IS NOT NULL

EMP_LAST_NAME_NN        Check        Enabled                Not Deferrable        Immediate        Validated                        1        "LAST_NAME" IS NOT NULL

EMP_MANAGER_FK        Referential Integrity        Enabled        No Action        Not Deferrable        Immediate        Validated                        1        MANAGER_ID

EMP_SALARY_MIN        Check        Enabled                Not Deferrable        Immediate        Validated                        1        salary > 0

   

   

1.인덱스 없는 컬럼

   

  • 선택한 컬럼명(phone_number) : 해당 컬럼에는 인덱스가 없다.

       

  • 현재 데이터상태

   

   

  • Masking 조건(무작위 숫자 삽입)

       

   

  • 변경된 데이터 현태

       

   

  • 상태확인
  • Constraints/Index 에 아무 이상이 없다.

       

   

   

<전체 실행된 스크립트 내역>

-- ¿¿ ¿¿¿¿¿¿: test

-- ¿¿¿¿ ¿¿ ¿¿: 03-9¿ -2012 13:22

¿¿¿ ¿¿¿ ¿¿ ¿

User is "SYS"

COMMIT

ALTER SESSION ENABLE PARALLEL DML

DROP TABLE "MGMT_DM_TT_4" PURGE

¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿¿, ¿¿ ¿¿ ¿

declare

adj number:=0;

num number:=0;

begin

select length(count(*)) into adj from (select distinct "PHONE_NUMBER" from "HR"."EMPLOYEES");

num := adj;

adj := greatest(adj - 3, 0);

execute immediate 'create table MGMT_DM_TT_4

 

(orig_val null, new_val null) NOLOGGING PARALLEL as

select CAST(null AS VARCHAR2(20)) orig_val, CAST(null AS VARCHAR2(20)) new_val from dual union all

select s.orig_val,

case

when s.subset = 1 then

CAST(

lpad(3 + n

vl(substr(s.new_num, 1, 1), 0), 1, 0)

AS VARCHAR2(20))

end new_val

from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, '||num||', 0) new_num

from (select "PHONE_NUMBER" orig_val, min(rowid) rid, min(

case

when 1=1 then 1

end

) subset

from "HR"."EMPLOYEES" group by "PHONE_NUMBER")) s

where 1=1 and s.orig_val is not null

';

DBMS_STATS.GATHER_TABLE_STATS(NULL, '"MGMT_DM_TT_4"', 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_3 AS

grant_cur INTEGER;

BEGIN

grant_cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE (grant_cur, ' REVOKE REFERENCES ON "HR"."EMPLOYEES" FR

OM "OE"', DBMS_SQL.NATIVE);

DBMS_SQL.CLOSE_CURSOR (grant_cur);

END;

BEGIN "HR".MGMT$MASK_GRANT_3; END;

DROP procedure "HR".MGMT$MASK_GRANT_3

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_3 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_3; END;

DROP procedure "HR".MGMT$MASK_GRANT_3

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", s."EMAIL", c0m4.NEW_VAL "PHONE_N

UMBER", 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_4 c0m4 WHERE sys_op_map_nonnull(s."PHONE_NUMBER") = sys_op_map_nonnull(c0m4.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_3 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_3; END;

DROP procedure "HR".MGMT$MASK_GRANT_3

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_3 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_3; END;

DROP procedure "HR".MGMT$MASK_GRANT_3

ALTER TABLE "HR"."DEPARTMENTS" ADD (CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") )

ALTER TABLE "HR"."JOB_HISTORY" ADD (CONSTRAINT "JHIST_EMP_FK" FOREIGN KEY ("EMPLOYEE_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") )

ALTER TABLE "OE"."CUSTOMERS" ADD (CONSTRAINT "CUSTOMERS_ACCOUNT_MANAGER_FK" FOREIGN KEY ("ACCOUNT_MGR_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ON DELETE SET NULL)

ALTER TABLE "OE"."ORDERS" ADD (CONSTRAINT "ORDERS_SALES_REP_FK" FOREIGN KEY ("SALES_REP_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ON DELETE SET NULL)

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;

ALTER VIEW "HR"."EMP_DETAILS_VIEW" COMPILE

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

   

2.No Unique Index

2-1 중복된 값 없도록 Data Masking

   

  • 해당컬럼 선택(First_name) : no unique index

   

   

  • 현재 데이터 상태

   

  • Data Masking 설정(섞기): 해당 열의 값들끼리 섞어버림

  • 변경된 데이터

   

   

   

  • 현재상태 확인
    • Constraints/Index 에 아무 이상이 없다.

   

   

   

<실행된 스크립트내역>

-- ¿¿ ¿¿¿¿¿¿: test

-- ¿¿¿¿ ¿¿ ¿¿: 03-9¿ -2012 13:30

¿¿¿ ¿¿¿ ¿¿ ¿

User is "SYS"

COMMIT

ALTER SESSION ENABLE PARALLEL DML

DROP TABLE "MGMT_DM_TT_7" PURGE

¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿¿, ¿¿ ¿¿ ¿

declare

adj number:=0;

num number:=0;

begin

select length(count(*)) into adj from (select distinct "FIRST_NAME" from "HR"."EMPLOYEES");

num := adj;

adj := greatest(adj - 2, 0);

execute immediate 'create table MGMT_DM_TT_7

(

orig_val null, new_val null) NOLOGGING PARALLEL as

select CAST(null AS VARCHAR2(20)) orig_val, CAST(null AS VARCHAR2(20)) new_val from dual union all

select s.orig_val,

case

when s.subset = 1 then

CAST(

a0.new_val

 

AS VARCHAR2(20))

end new_val

from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, '||num||', 0) new_num

from (select "FIRST_NAME" orig_val, min(rowid) rid, min(

case

when 1=1 then 1

end

 

) subset

from "HR"."EMPLOYEES" group by "FIRST_NAME")) s

,(select rownum rn, new_val from (select distinct "FIRST_NAME" new_val from "HR"."EMPLOYEES" where 1=1 order by dbms_random.value)) a0

where 1=1 and s.orig_val is not null

 

and s.rn = a0.rn

';

DBMS_STATS.GATHER_TABLE_STATS(NULL, '"MGMT_DM_TT_7"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);

end;

DROP TRIGGER "HR"."UPDATE_JOB_HISTORY"

DROP TRIGGER "HR"."SECURE_EMPLOYEES"

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_6 AS

grant_cur INTEGER;

BEGIN

grant_cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE (grant_cur, ' REVOKE REFERENCES ON "HR"."EMPLOYEES" FR

OM "OE"', DBMS_SQL.NATIVE);

DBMS_SQL.CLOSE_CURSOR (grant_cur);

END;

BEGIN "HR".MGMT$MASK_GRANT_6; END;

DROP procedure "HR".MGMT$MASK_GRANT_6

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_6 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_6; END;

DROP procedure "HR".MGMT$MASK_GRANT_6

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", c0m7.NEW_VAL "FIRST_NAME", s."LAST_NAME", s."EMAIL", s."PHONE_N

UMBER", 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_7 c0m7 WHERE sys_op_map_nonnull(s."FIRST_NAME") = sys_op_map_nonnull(c0m7.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_6 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_6; END;

DROP procedure "HR".MGMT$MASK_GRANT_6

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_6 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_6; END;

DROP procedure "HR".MGMT$MASK_GRANT_6

ALTER TABLE "HR"."DEPARTMENTS" ADD (CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") )

ALTER TABLE "HR"."JOB_HISTORY" ADD (CONSTRAINT "JHIST_EMP_FK" FOREIGN KEY ("EMPLOYEE_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") )

ALTER TABLE "OE"."CUSTOMERS" ADD (CONSTRAINT "CUSTOMERS_ACCOUNT_MANAGER_FK" FOREIGN KEY ("ACCOUNT_MGR_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ON DELETE SET NULL)

ALTER TABLE "OE"."ORDERS" ADD (CONSTRAINT "ORDERS_SALES_REP_FK" FOREIGN KEY ("SALES_REP_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ON DELETE SET NULL)

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;

ALTER VIEW "HR"."EMP_DETAILS_VIEW" COMPILE

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

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

   

   

2-2 중복된 값이 있도록 Data Masking

   

  • 컬럼 선택(LAST_NAME): no Unique index

       

       

  • 현재 데이터 상태확인

       

  • Data Masking 옵션 선택(무작위 문자열): 2자리의 무작위로 문자열 삽입

   

  • 변경된 데이터 확인

   

  • 상태 확인
  • 중복된 값이 있어도 no Uinque index이기 때문에 Constraints/Index에 이상 없음

       

   

   

<수행 스크립트 확인>

<file_error msg="지정된 경로를 찾을 수 없습니다." n="3"/>

-- ¿¿ ¿¿¿¿¿¿: test

-- ¿¿¿¿ ¿¿ ¿¿: 03-9¿ -2012 14:19

¿¿¿ ¿¿¿ ¿¿ ¿

User is "SYS"

COMMIT

ALTER SESSION ENABLE PARALLEL DML

DROP TABLE "MGMT_DM_TT_42" PURGE

¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿¿, ¿¿ ¿¿ ¿

declare

adj number:=0;

num number:=0;

begin

select length(count(*)) into adj from (select distinct "LAST_NAME" from "HR"."EMPLOYEES");

num := adj;

adj := greatest(adj - 1, 0);

execute immediate 'create table MGMT_DM_TT_42

(

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(lower(d

bms_random.string(''l'', trunc(dbms_random.value(1, 1 - least(1, greatest('||adj||' - 0, 0)))))), 1 - least(1, greatest('||adj||' - 0, 0)),''a'')

|| mgmt$mask_randomencode(nvl(substr(s.new_num, 1, 1 + least(1, greatest('||adj||' - 0, 0))), ''0''),

1 + least(1, greatest('||adj||' - 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 "LAST_NAME" orig_val, min(rowid) rid, min(

case

   

when 1=1 then 1

end

) subset

from "HR"."EMPLOYEES" group by "LAST_NAME")) s

where 1=1 and s.orig_val is not null

';

DBMS_STATS.GATHER_TABLE_STATS(NULL, '"MGMT_DM_TT_42"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_S

IZE, degree=>DBMS_STATS.DEFAULT_DEGREE);

end;

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_41 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_41; END;

DROP procedure "HR".MGMT$MASK_GRANT_41

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_41 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_41; END;

DROP procedure "HR".MGMT$MASK_GRANT_41

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", c0m42.NEW_VAL "LAST_NAME", s."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_42 c0m42 WHERE sys_op_map_nonnull(s."LAST_NAME") = sys_op_map_nonnull(c0m42.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_41 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_41; END;

DROP procedure "HR".MGMT$MASK_GRANT_41

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_41 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_41; END;

DROP procedure "HR".MGMT$MASK_GRANT_41

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

   

3.Yes Unique Index

3-1 중복 값이 없는 Data Masking

   

  • 대상 컬럼(EMAIL) : Unique Index

       

       

  • 원본 데이터 확인

   

  • Data Masking 옵션선택(해당 열의 Data를 섞는다.)

   

  • Data Masking 후 변경된 데이터

   

  • 상태확인
  • 중복된 값이 없이 Data Masking 되어서 기존의 Index와 Constraints가 유지되는 것을 확인할 수 있습니다,

       

   

   

<수행 스크립트 확인>

-- ¿¿ ¿¿¿¿¿¿: test

-- ¿¿¿¿ ¿¿ ¿¿: 03-9¿ -2012 14:26

¿¿¿ ¿¿¿ ¿¿ ¿

User is "SYS"

COMMIT

ALTER SESSION ENABLE PARALLEL DML

DROP TABLE "MGMT_DM_TT_45" 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_45

(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(

a0.new_val

 

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

,(select rownum rn, new_val from (select distinct "EMAIL" new_val from "HR"."EMPLOYEES" where 1=1 order by dbms_random.value)) a0

where 1=1 and s.orig_val is not null

and s.rn = a0.rn

';

DBMS_STATS.GATHER_TABLE_STATS(NULL, '"MGMT_DM_TT_45"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);

end;

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_44 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_44; END;

DROP procedure "HR".MGMT$MASK_GRANT_44

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_44 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_44; END;

DROP procedure "HR".MGMT$MASK_GRANT_44

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", c0m45.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_45 c0m45 WHERE sys_op_map_nonnull(s."EMAIL") = sys_op_map_nonnull(c0m45.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_44 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_44; END;

DROP procedure "HR".MGMT$MASK_GRANT_44

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_44 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_44; END;

DROP procedure "HR".MGMT$MASK_GRANT_44

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

   

=================================================

   

3-2 중복 값이 있는 Data Masking

   

  • Data Masking 대상 컬럼(EMAIL) : Unique Index

   

   

  • 현재 Data 상태 확인

   

   

   

  • Data Masking 옵션 선택(부분 문자열): 4번째 칸까지의 데이터 출력

       

  • Data Masking 후 변경된 내용

       

   

   

  • 상태확인
    • 부분 Masking이 되면서 중복된 값이 발생되어 Unique Index를 생성하지 못하고 해당 데이터 변경만 이루어졌음.
    • 아래 실행 스크립트 로그를 확인하게 되면 해당 에러가 발생하면서 Index및 Constraints가 깨지는 것을 확인 할 수 있습니다.

         

   

   

   

<수행 스크립트 확인>

-- ¿¿ ¿¿¿¿¿¿: test

-- ¿¿¿¿ ¿¿ ¿¿: 03-9¿ -2012 13:48

¿¿¿ ¿¿¿ ¿¿ ¿

User is "SYS"

COMMIT

ALTER SESSION ENABLE PARALLEL DML

DROP TABLE "MGMT_DM_TT_21" 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_21

(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(

substr(s.orig_va

l, 4, 4)

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_21"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);

end;

DROP TRIGGER "HR"."UPDATE_JOB_HISTORY"

DROP TRIGGER "HR"."SECURE_EMPLOYEES"

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_20 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_20; END;

DROP procedure "HR".MGMT$MASK_GRANT_20

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_20 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_20; END;

DROP procedure "HR".MGMT$MASK_GRANT_20

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", c0m21.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_21 c0m21 WHERE sys_op_map_nonnull(s."EMAIL") = sys_op_map_nonnull(c0m21.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

¿¿¿ ¿¿¿¿ ¿ ¿¿ ¿¿

ORA-01452:

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

errorExitOraError!

¿¿¿ ¿¿¿¿ ¿¿¿¿¿¿¿. ¿¿ ¿¿¿ ¿¿¿¿¿.

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿

¿¿¿ ¿¿¿¿ ¿¿¿¿ ¿¿¿

¿¿¿¿ ¿¿ ¿¿

SQL> set ver on

   

   

결론

  • DATA Masking 시에 적용하는 옵션에 따라서 변경되는 데이터의 열에 중복되는 값이 있느냐, 없느냐에 따라서 Unique index를 포함하고 있는 대상 컬럼일 경우, 다른 곳의 컬럼에 있는 index 및 Constraints까지도 재생성 하지 못하게 되는 것을 확인할 수 있습니다.

   

  • Data Masking 시에 특히 Unique index를 포함하고 있는 컬럼의 경우, 중복된 데이터 값이 나오지 않도록 Masking 옵션을 신중하게 선택하여 적용할 필요가 있을 것으로 판단됩니다.

     

   

블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,