DBMS_REDACT 패키지를 사용하기 위해서는 Oracle Advanced Security 옵션을 사용해야 한다. 해당 부분은 라이센스가 필요하다.
시나리오
SCOTT의 Customer 테이블에 대하여 viewer라는 유저에게는 데이터의 일부만 볼 수 있는 정책을 구현
1. scott유저에게 DBMS_REDACT 패키지를 사용할 수 있는 권한 부여
|
-- run by SYS
GRANT EXECUTE ON sys.dbms_redact TO scott; |
2. Scott 유저에서 테이블 생성 및 정책 적용
|
SCOTT 유저 |
VIEWER 유저 |
|
DROP TABLE customer purge ;
CREATE TABLE customer ( id number , name varchar2(30) , jumin varchar2(30) , phone varchar2(30) , email varchar2(50), cardno number ) ;
INSERT INTO customer VALUES ( 1 , '홍길동' , '123456-1234567' , '010-1234-5678' ,'hong@naver.com' , 123456789 ) ; INSERT INTO customer VALUES ( 2 , '이말년' , '654321-7654321' , '010-9876-5678' ,'malyear@naver.com', 123455555 ) ; COMMIT ;
SELECT * FROM customer ;
1 홍길동 123456-1234567 010-1234-5678 hong@naver.com 123456789 2 이말년 654321-7654321 010-9876-5678 malyear@naver.com 123455555
GRANT SELECT ON customer TO viewer ;
|
|
|
|
SELECT * FROM scott.customer ;
1 홍길동 123456-1234567 010-1234-5678 hong@naver.com 123456789 2 이말년 654321-7654321 010-9876-5678 malyear@naver.com 123455555 |
|
cardno 칼럼에 데이터 값이 다 안보이게 하는 정책 적용
BEGIN DBMS_REDACT.add_policy( object_schema => 'scott', object_name => 'customer', column_name => 'cardno', policy_name => 'redact_scott_customer', function_type => DBMS_REDACT.full, expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') IN (''VIEWER'')') ; END ; |
SELECT * FROM scott.customer ;
1 홍길동 123456-1234567 010-1234-5678 hong@naver.com 0 2 이말년 654321-7654321 010-9876-5678 malyear@naver.com 0 |
|
cardno 칼럼에 값 중 1~5자리까지의 숫자를 9로 변경 하도록 정책 수정
BEGIN DBMS_REDACT.alter_policy( object_schema => 'scott', object_name => 'customer', column_name => 'cardno', policy_name => 'redact_scott_customer', action => DBMS_REDACT.modify_column, function_type => DBMS_REDACT.partial, function_parameters => '9,1,5' , expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') IN (''VIEWER'')') ; END ; |
SELECT * FROM scott.customer ;
1 홍길동 123456-1234567 010-1234-5678 hong@naver.com 999996789 2 이말년 654321-7654321 010-9876-5678 malyear@naver.com 999995555 |
|
주민번호 칼럼에 마스킹 적용
BEGIN DBMS_REDACT.alter_policy( object_schema => 'scott', object_name => 'customer', column_name => 'jumin', policy_name => 'redact_scott_customer', action => DBMS_REDACT.add_column, function_type => DBMS_REDACT.partial, function_parameters => 'VVVVVVFVVVVVVV,VVVVVV-VVVVVVV,#,8,14' , expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') IN (''VIEWER'')') ; END ; |
SELECT * FROM scott.customer ;
1 홍길동 123456-1###### 010-1234-5678 hong@naver.com 999996789 2 이말년 654321-7###### 010-9876-5678 malyear@naver.com 999995555 |
|
이메일 칼럼의 아이디 부분 데이터를 마스킹 처리
BEGIN DBMS_REDACT.alter_policy( object_schema => 'scott', object_name => 'customer', column_name => 'email', policy_name => 'redact_scott_customer', action => DBMS_REDACT.add_column, function_type => DBMS_REDACT.REGEXP, regexp_pattern => DBMS_REDACT.RE_PATTERN_EMAIL_ADDRESS, regexp_replace_string => DBMS_REDACT.RE_REDACT_EMAIL_NAME, regexp_position => DBMS_REDACT.RE_BEGINNING, regexp_occurrence => DBMS_REDACT.RE_ALL, expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') IN (''VIEWER'')') ; END ; |
SELECT * FROM scott.customer ;
1 홍길동 123456-1###### 010-1234-5678 xxxx@naver.com 999996789 2 이말년 654321-7###### 010-9876-5678 xxxx@naver.com 999995555 |
|
cardno에 적용 되었던 정책만 삭제
BEGIN DBMS_REDACT.alter_policy( object_schema => 'scott', object_name => 'customer', column_name => 'cardno', policy_name => 'redact_scott_customer', action => DBMS_REDACT.modify_column, function_type => DBMS_REDACT.NONE) ; END ; |
SELECT * FROM scott.customer ;
1 홍길동 123456-1###### 010-1234-5678 xxxx@naver.com 123456789 2 이말년 654321-7###### 010-9876-5678 xxxx@naver.com 123455555 |
3. 적용되어 있는 정책 확인
|
-- run by sys
SELECT * FROM REDACTION_POLICIES ;
OBJECT_OWNER OBJECT_NAME POLICY_NAME EXPRESSION ENABLE POLICY_DESCRIPTION ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------- ---------------------------------------- SCOTT CUSTOMER redact_scott_customer SYS_CONTEXT('USERENV','SESSION_USER') IN YES
1 row
SELECT * FROM REDACTION_COLUMNS ;
OBJECT_OWNER OBJECT_NAME COLUMN_NAME FUNCTION_TYPE FUNCTION_PARAMETERS REGEXP_PATTERN REGEXP_REPLACE_STRING REGEXP_POSITION REGEXP_OCCURRENCE REGEXP_MATCH_PARAMETER COLUMN_DESCRIPTION ---------------------------------------- ---------------------------------------- ---------------------------------------- --------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- --------------- ----------------- ---------------------- ---------------------------------------- SCOTT CUSTOMER JUMIN PARTIAL REDACTION VVVVVVFVVVVVVV,VVVVVV-VVVVVVV,#,8,14 0 0 SCOTT CUSTOMER EMAIL REGEXP REDACTION ([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+\.[A- xxxx@\2 1 0 SCOTT CUSTOMER CARDNO NO REDACTION 0 0
3 rows |
4. 정책 삭제
|
-- run by scott BEGIN DBMS_REDACT.drop_policy( object_schema => 'scott', object_name => 'customer', policy_name => 'redact_scott_customer') ; END ; |