[펌]DBMS_RLS: 행단위 보안 설정

이 강좌의 원문은 "Keeping Information Private with VPD" 입니다. ,
dbakorea.pe.kr의 강명규님께서 번역하여 작성하신 강좌를 일부 보충, 수정해서 올렸습니다.
 
 
오라클의 RLS(Row Level Security)기능은 사용자에게 VPD(Virtual Private Database)를 제공해 줍니다.
RLS는 8i부터 지원되며, 행 레벨까지 접근권한을 지정할 수 있게 해줍니다.
 
  쉽게 말해서 DBMS_RLS를 설정하고 쿼리를 던지면 자동으로 설정된 WHERE절이 추가되어 실행되며,
테이블 데이타 Row의 Access를 제한 할 수 있는 기능을 말합니다.

아래 강좌에서는 4개의 오라클 사용자(bank,secman, lion, lara)가 생성됩니다.
- bank 계정은 실제 원본 데이터의 소유 계정입니다.
- secman 계정은 row level security를 설정하고, 관리하기 위한 securiy 계정입니다.
- lion,lara 계정의 bank의 데이터를 조작할 수 있는 계정입니다.
   이 두 계정은 secman계정에 access_policy에 등록되어 있는 권한 데이터에 의해서 bank 계정의 데이터를 조작할 수 있는 권한을 제한 받습니다.

일반적인 오라클 객체 권한은 객체 단위로 조작 권한을 제어 하지만 RLS(Row Level Security)기능은 테이블 데이터의 Row 레벨까지 권한을 제어할 수 있습니다.

Oracle이 9i와 10g로  업그레이드 되면서 VPD(Virtual Private Database) 기능이 매우 강력한 기능으로 발전되었지만, 이 강좌에서는 아주 간단한 Row Level Security의 예제를 실습 하겠습니다.

 

1. 테스트를 위한 기본적인 환경 설정

☞ SYSDBA권한으로 접속해서 실행 합니다. 

-- SYSDBA 권한으로 접속 합니다.
SQL>CONN / AS SYSDBA
  
-- bank 유저 생성 및 권한부여.
SQL>CREATE USER bank IDENTIFIED BY bank
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP;
 
SQL>GRANT CONNECT, RESOURCE TO bank;
 
 
-- secman 유저 생성 및 권한부여.
SQL>CREATE USER secman IDENTIFIED BY secman
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP;
 
SQL>GRANT CONNECT, RESOURCE TO SECMAN;
 
 
-- lion 유저 생성 및 권한부여.
SQL>CREATE USER lion IDENTIFIED BY lion
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP;
 
SQL>GRANT CONNECT, RESOURCE TO LION;
 
 
-- lara 유저 생성 및 권한부여
SQL>CREATE USER lara IDENTIFIED BY lara
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP;
  
SQL>GRANT CONNECT, RESOURCE TO LARA;
  
 
☞ bank유저에 접속해서 실행 합니다. (bank 유저는 실제 데이터 소유자 입니다.)
 
SQL>conn bank/bank
 
-- 테스트용 테이블 생성
SQL>CREATE TABLE customers(
    cust_id   NUMBER  NOT NULL,
    cust_name VARCHAR(10)  NOT NULL);
 
-- 테스트용 테이블 생성
SQL>CREATE TABLE accounts(
    acct_id  NUMBER       NOT NULL,
    cust_id  NUMBER       NOT NULL,
    balance  NUMBER(15,2));

 -- 테스트 데이터 등록
INSERT INTO customers VALUES(123, '강명규');
INSERT INTO customers VALUES(456, '여운형');
INSERT INTO accounts VALUES (101, 123, 10000000);
INSERT INTO accounts VALUES (102, 123, 15000000);
INSERT INTO accounts VALUES (201, 456, 10000000);
INSERT INTO accounts VALUES (202, 456, 10000000);
COMMIT;
 
 
☞ secman유저에 접속해서 실행 합니다.
(secman 유저는 row level security를 관리하는 유저입니다.)
 
SQL>conn secman/secman
 
-- 접근 관리 테이블 생성
SQL>CREATE TABLE access_policy(
    am_name      VARCHAR(20) NOT NULL,
    cust_id      NUMBER      NOT NULL,
    access_type  CHAR(1)     NOT NULL ); --S,I,D,U
 
-- 접근을 제어하는 샘플 데이터 등록
INSERT INTO access_policy VALUES('LION', 123, 'I');
INSERT INTO access_policy VALUES('LION', 123, 'D');
INSERT INTO access_policy VALUES('LION', 123, 'U');
INSERT INTO access_policy VALUES('LION', 123, 'S');
INSERT INTO access_policy VALUES('LION', 456, 'S');
INSERT INTO access_policy VALUES('LARA', 456, 'I');
INSERT INTO access_policy VALUES('LARA', 456, 'D');
INSERT INTO access_policy VALUES('LARA', 456, 'U');
INSERT INTO access_policy VALUES('LARA', 456, 'S');
COMMIT;

 

2. 정책함수(Policy Function) 생성

사용자, 테이블명을 인자로 주면 질의에 추가될 WHERE절을 리턴하는 함수를 생성합니다..
 

☞ secman유저에 접속해서 실행 합니다.
 
SQL>conn secman/secman
 
-- SELECT용 정책함수 생성
SQL>CREATE OR REPLACE FUNCTION get_sel_cust_id(
    p_schema IN VARCHAR2,
    p_table IN VARCHAR2
)
    RETURN VARCHAR2
AS
    l_retstr VARCHAR2(2000);
BEGIN
    IF(p_schema = USER) THEN
        l_retstr := null;
    -- 소유자 계정이 아닐 경우 access_policy을 조회해서 IN 조건절을 추가해서 생성 합니다.
    ELSE
        FOR cust_rec IN (SELECT cust_id FROM access_policy WHERE am_name = USER AND access_type='S')
        LOOP
            l_retstr := l_retstr || ',' || cust_rec.cust_id;
        END LOOP;
        l_retstr := LTRIM(l_retstr, ',');
        l_retstr := 'CUST_ID IN (' || l_retstr || ')';
    END IF;
    RETURN l_retstr;
END;
/
 
 
-- INSERT, UPDATE, DELETE용 정책함수 생성
-- (실제 환경에서는 각각에 대해서 함수를 정의하는 것이 좋습니다)
SQL>CREATE OR REPLACE FUNCTION get_iud_cust_id(
    p_schema in varchar2,
    p_table in varchar2
)
    return varchar2
AS
    l_retstr varchar2(2000);
BEGIN
    IF(p_schema = USER) THEN
        l_retstr := null;
    -- 소유자 계정이 아닐 경우 access_policy을 조회해서 IN 조건절을 추가해서 생성 합니다.
    ELSE
        FOR cust_rec IN (SELECT cust_id FROM access_policy WHERE am_name = USER AND access_type IN ('I','U','D'))
        LOOP
            l_retstr := l_retstr || ',' || cust_rec.cust_id;
        END LOOP;
        l_retstr := LTRIM(l_retstr, ',');
        l_retstr := 'CUST_ID IN (' || l_retstr || ')';
    END IF;
    RETURN l_retstr;
END;
/

 

3. 정책(Policy)의 정의

정책은 DBMS_RLS패키지를 이용하여 정의할 수 있습니다.
secman에게 DBMS_RLS에 대한 실행권한을 부여하고, secman에서 정책을 설정 합니다.
 
☞ SYSDBA권한으로 접속해서 실행 합니다.
 
-- SYSDBA 권한으로 접속 합니다.
SQL>CONN / AS SYSDBA
 
-- 권한 부여
SQL>GRANT EXECUTE ON DBMS_RLS TO SECMAN;
 
 
 
☞ secman유저에 접속해서 정책을 정의 합니다.
 
SQL>conn secman/secman
 
-- CUST_SEL_POLICY 정책 생성
BEGIN
DBMS_RLS.ADD_POLICY(
    OBJECT_SCHEMA   => 'BANK',
    OBJECT_NAME     => 'CUSTOMERS',
    POLICY_NAME     => 'CUST_SEL_POLICY',
    FUNCTION_SCHEMA => 'SECMAN',
    POLICY_FUNCTION => 'GET_SEL_CUST_ID',
    UPDATE_CHECK    => TRUE
);
END;
/
 
-- CUST_IUD_POLICY 정책 생성
BEGIN
DBMS_RLS.ADD_POLICY(
    OBJECT_SCHEMA   => 'BANK',
    OBJECT_NAME     => 'CUSTOMERS',
    POLICY_NAME     => 'CUST_IUD_POLICY',
    FUNCTION_SCHEMA => 'SECMAN',
    POLICY_FUNCTION => 'GET_IUD_CUST_ID',
    STATEMENT_TYPES => 'INSERT,UPDATE,DELETE',
    UPDATE_CHECK    => TRUE
);
END;
/
 
 ※ 지정한 정책 제거
EXEC DBMS_RLS.DROP_POLICY('BANK','CUSTOMERS','CUST_SEL_POLICY');
 
※ DBMS_RLS패키지의 프로시저들
 - ADD_POLICY : Adds a fine-grained access control policy to a
 - DROP_POLICY :  Drops a fine-grained access control policy from a table or view.
 - REFRESH_POLICY : Causes all the cached statements associated with the policy to be reparsed.
 - ENABLE_POLICY : Enables or disables a fine-grained access control policy.
 - CREATE_POLICY_GROUP : Creates a policy group.
 - ADD_GROUPED_POLICY : Adds a policy associated with a policy group.
 - ADD_POLICY_CONTEXT : Adds the context for the active application.
 - DELETE_POLICY_GROUP : Deletes a policy group.
 - DROP_GROUPED_POLICY : Drops a policy associated with a policy group.
 - DROP_POLICY_CONTEXT : Drops a driving context from the object so that it will have one less driving context.
 - ENABLE__GROUPED_POLICY : Enables or disables a row-level group security policy.
 - REFRESH_GROUPED_POLICY : Reparses the SQL statements associated with a refreshed policy.

 

4. 테스트 확인

(원문에서 빠진 내용입니다. dbakorea.pe.kr에 강명규씨가 작성) 
우선 lion, lara에게 bank.customers에 접근할 수 있는 권한을 부여 합니다.
 
☞ bank유저에 접속해서 lion,lara 유저에게 권한을 부여 합니다.
 
SQL>conn bank/bank
 
SQL>GRANT SELECT,INSERT,UPDATE,DELETE ON CUSTOMERS TO LION,LARA;
 
 
 
다음은 각 계정에서의 질의 결과를 테스트 해봅니다.
 
☞ bank유저에 접속해서 테스트
SQL> conn bank/bank

-- bank는 customers객체(테이블)의 소유자이므로 모든 데이터 조회가능
SQL> select * from customers;
 
   CUST_ID CUST_NAME
---------- --------------------
       123 강명규
       123 여운형
 
 
☞ lara유저에 접속해서 테스트
 
 - lara는 정책에 의해 'WHERE cust_id IN (456)'이 질의에 추가되어 실행 됩니다.

 - 즉, 다음과 같이 질의가 rewritten 됩니다.
  SELECT * FROM bank.customers;
  → SELECT * FROM (SELECT * FROM bank.customers) WHERE cust_id IN (456);
 
SQL> conn lara/lara
SQL> SELECT * FROM bank.customers;
 
   CUST_ID CUST_NAME
---------- --------------------
       456 여운형
 
-- 위에 결과와 같이 정책에 의해서 1개의 행만 조회 됩니다.
 
-- UPDATE 테스트
SQL> UPDATE bank.customers SET cust_name='김규식';
 
1 행이 갱신되었습니다.
 
-- 마찬가지로 정책에 의해 1개행만 갱신 됩니다.
 
 
-- lara 유저에게 정책이 부여되지 않은 '123' 계좌에 대해서 INSERT 를 해봅니다.
-- 123 계좌는 access_policy 테이블에서 lara 유저에게 등록되어 있지 않습니다.
SQL> INSERT INTO bank.customers VALUES(123,'조소앙');
INSERT INTO bank.customers VALUES(123,'조소앙');
                 *
1행에 오류:
ORA-28115: 검색 옵션으로 된 정책이 위반입니다
 
-- 위와 같이 정책위반으로 데이타 조작을 할 수가 없습니다.
 
 
☞ lion유저에 접속해서 테스트
 
SQL> conn lion/lion
연결되었습니다.
 
-- lion 유저는 현재 조회 권한이 '123','456' 계좌에 대해서 부여되어 있습니다.
SQL> SELECT * FROM bank.customers;
 
   CUST_ID CUST_NAME
---------- --------------------
       123 강명규
       456 김규식
 
-- 하지만 '456' 계좌에 대해서는 INSERT,UPDATE,DELETE권한이 부여되어 있지 않습니다.
SQL> INSERT INTO bank.customers VALUES(456,'조소앙');
INSERT INTO bank.customers VALUES(456,'조소앙')
                 *
1행에 오류:
ORA-28115: 검색 옵션으로 된 정책이 위반입니다
 
위에 예제와 같이 access_policy 테이블에 등록된 데이타를 기준으로 정책 관리를 하게 됩니다.