'oracle data masking'에 해당되는 글 2건

2013.04.30 Oracle Data Masking TEST2 추가내용
2013.04.30 Oracle Data Masking Constraints/Index TEST1

Oracle Data Masking TEST2 추가내용

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

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

'1. IT Story > DB' 카테고리의 다른 글

Oracle Database Appliance(X5-2) (0)	2015.07.30
Function 수행횟수 확인 TEST (0)	2015.07.30
DECODE Function Test (0)	2015.07.29
RAC& ASM to Single& Filesystem Clone DB Create (0)	2015.07.29
Oracle Data Masking Constraints/Index TEST1 (0)	2013.04.30
Oracle SQL Performance Analyzer (EM만 사용) (0)	2013.04.30
Oracle Datapump를 이용한 Expdp/Impdp백업!! (0)	2013.04.15
Active DataGuard 11gR2 DGMGRL (0)	2013.04.09

운명을바꾸는자

IT와 함께 살아가는 삶

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"

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)