권한 부여 시 ROLE, PRIVILEGE 차이

DB권한관리는 대부분 업무별 ROLE 을 만들어 적절하게 운영 할 것이다.

운영 중 서비스가 추가되면서 타업무의 테이블을 조회하는 경우가 생길 수 있는데 이 때 신규 권한을 부여 하는 방식에 따른 차이를 알아보자.

 

요약 :  - ROLE 부여 시 기존에 접속되어 있던 세션은 적용이 안된다(신규접속 세션부터 적용 됨). - PRIVILEGE 부여 시 기존/신규 세션 모두 적용 됨  - DEFAULT_ROLE 상태 YES 인 경우만 ROLE 적용 됨

 

-- SK2 유저에 ROLE 부여 및 확인

SQL> select sid, serial#, username, status, program, logon_time from v$session where username = 'SK2' ;

       SID    SERIAL# USERNAME    STATUS   PROGRAM      LOGON_TI
---------- ---------- -------------------- -------- ------------------------------------------------ --------
43  6575 SK2    INACTIVE sqlplus@svr1 (TNS V1-V3)      17:18:40


SQL> SELECT * FROM DBA_TAB_PRIVS WHERE table_name LIKE 'DFT%' ;

GRANTEE       OWNER TABLE_NAME      GRANTOR   PRIVILEGE        GRA HIE COMMON   TYPE    INHERITED
-------------------- ---------- -------------------- -------------------- -------------------- --- --- ---------- ------------------------ ----------
DFT_NO      SK  DFT_ROLE_NO      SK    SELECT        NO  NO  NO   TABLE     NO
DFT_YES       SK  DFT_ROLE_YES      SK    SELECT        NO  NO  NO   TABLE     NO

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE grantee = 'SK2' ;

no rows selected

SQL> grant dft_yes, dft_no to sk2 ;

Grant succeeded.

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE grantee = 'SK2' ;

GRANTEE       GRANTED_ROLE   ADMIN_OPTION        DELEGATE_OPTION     DEFAULT_ROLE  COMMON     INHERITED
-------------------- -------------------- -------------------- -------------------- -------------------- ---------- ----------
SK2      DFT_YES   NO        NO     YES   NO     NO
SK2      DFT_NO   NO        NO     NO  NO     NO

 

-- ROLE 부여 전 접속되어 있던 세션으로 SELECT 실패

-- 세션#1 (기존 세션)
SQL> select * from session_privs ;
 
PRIVILEGE
----------------------------------------
CREATE SESSION
 
SQL>  select * from session_roles ;
 
no rows selected
 
SQL> select count(*) from sk.dft_role_yes ;
select count(*) from sk.dft_role_yes
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

 

-- 신규로 접속하여 SELECT 가능

-- 세션#2 (신규 세션)
SQL> select * from session_roles ;

ROLE
--------------------------------------------------------------------------------------------------------------------------------
DFT_YES

SQL> select * from session_privs ;

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> select count(*) from sk.dft_role_yes ;

  COUNT(*)
----------
     72650

 

-- 테이블 조회 특권(PRIVILEGE) 부여

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE grantee = 'SK2' ;

GRANTEE       GRANTED_ROLE   ADMIN_OPTION        DELEGATE_OPTION     DEFAULT_ROLE  COMMON     INHERITED
-------------------- -------------------- -------------------- -------------------- -------------------- ---------- ----------
SK2      DFT_YES   NO        NO     YES   NO     NO
SK2      DFT_NO   NO        NO     NO  NO     NO

SQL> select sid, serial#, username, status, program, logon_time from v$session where username = 'SK2' ;

       SID    SERIAL# USERNAME    STATUS   PROGRAM      LOGON_TI
---------- ---------- -------------------- -------- ------------------------------------------------ --------
20 13124 SK2    INACTIVE sqlplus@svr1 (TNS V1-V3)      17:23:55
43 54619 SK2    INACTIVE sqlplus@svr1 (TNS V1-V3)      17:22:46

SQL> grant select on sk.dft_role_yes to sk2 ;

Grant succeeded.

SQL> SELECT * FROM DBA_TAB_PRIVS WHERE table_name LIKE 'DFT%' ;

GRANTEE       OWNER TABLE_NAME      GRANTOR   PRIVILEGE        GRA HIE COMMON   TYPE    INHERITED
-------------------- ---------- -------------------- -------------------- -------------------- --- --- ---------- ------------------------ ----------
DFT_NO      SK  DFT_ROLE_NO      SK    SELECT        NO  NO  NO   TABLE     NO
SK2      SK  DFT_ROLE_YES      SK    SELECT        NO  NO  NO   TABLE     NO
DFT_YES       SK  DFT_ROLE_YES      SK    SELECT        NO  NO  NO   TABLE     NO

SQL>

 

-- 기존 세션에서 SELECT 가능

-- 세션#1 (기존 세션)
SQL> select count(*) from sk.dft_role_yes ;

  COUNT(*)
----------
     72650

SQL>

 

• Default Role이란
  세션을 열때 활성화되는 role
  default role을 제외한 role은 모두 비활성화

-- DEFAULT_ROLE='YES' 상태일때 권한 적용이 되는데 'NO'인 경우가(role 생성 시 패스워드 적용 등으로 인한) 있다. 해당 상황에 따른 확인 및 변경 방법

-- DEFAULT_ROLE 상태 확인
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE grantee = 'SK2' ;

GRANTEE       GRANTED_ROLE   ADMIN_OPTION        DELEGATE_OPTION     DEFAULT_ROLE  COMMON     INHERITED
-------------------- -------------------- -------------------- -------------------- -------------------- ---------- ----------
SK2      DFT_YES   NO        NO     YES   NO     NO
SK2      DFT_NO   NO        NO     NO  NO     NO

-- ROLE PASSWORD 설정 확인
SQL> SELECT ROLE, PASSWORD_REQUIRED FROM DBA_ROLES WHERE ROLE LIKE 'DFT%' ;

ROLE      PASSWORD
-------------------- --------
DFT_NO      YES
DFT_YES       NO

-- PASSWORD 제거
SQL> ALTER ROLE DFT_NO NOT IDENTIFIED ;

Role altered.

SQL> SELECT ROLE, PASSWORD_REQUIRED FROM DBA_ROLES WHERE ROLE LIKE 'DFT%' ;

ROLE      PASSWORD
-------------------- --------
DFT_NO      NO
DFT_YES       NO

-- DEFAULT_ROLE 상태 확인
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE grantee = 'SK2' ;

GRANTEE       GRANTED_ROLE   ADMIN_OPTION        DELEGATE_OPTION     DEFAULT_ROLE  COMMON     INHERITED
-------------------- -------------------- -------------------- -------------------- -------------------- ---------- ----------
SK2      DFT_YES   NO        NO     YES   NO     NO
SK2      DFT_NO   NO        NO     YES   NO     NO

-- DEFAULT_ROLE 한개만 선택 또는 모두다 선택
SQL> ALTER USER sk2 DEFAULT ROLE DFT_YES ;

User altered.

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE grantee = 'SK2' ;

GRANTEE       GRANTED_ROLE   ADMIN_OPTION        DELEGATE_OPTION     DEFAULT_ROLE  COMMON     INHERITED
-------------------- -------------------- -------------------- -------------------- -------------------- ---------- ----------
SK2      DFT_YES   NO        NO     YES   NO     NO
SK2      DFT_NO   NO        NO     NO  NO     NO

SQL> ALTER USER sk2 DEFAULT ROLE ALL ;

User altered.

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE grantee = 'SK2' ;

GRANTEE       GRANTED_ROLE   ADMIN_OPTION        DELEGATE_OPTION     DEFAULT_ROLE  COMMON     INHERITED
-------------------- -------------------- -------------------- -------------------- -------------------- ---------- ----------
SK2      DFT_YES   NO        NO     YES   NO     NO
SK2      DFT_NO   NO        NO     YES   NO     NO

SQL>