Oracle Data Masking Constraints/Index TEST1

1. IT Story/DB 2013. 4. 30. 23:01

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"

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)