[ORACLE] ROLE 권한 부여 시 기존 세션은 적용 안됨(신규 세션부터 적용됨)

오라클에서 권한 부여 시 role로 권한 부여를 했을 때 

여전히 안된다는 연락을 받은 적이 한 번쯤은 있을 겁니다.

DB 접속 시 해당 유저가 소유하고 있는 role을 체크하기 때문인데요

권한 부여 시 특권(privilege)과 ROLE의 차이 테스트입니다.

테스트 테이블 생성

 
> CREATE TABLE sk.tab1 AS SELECT level c1 FROM dual CONNECT BY LEVEL <= 100 
100 row(s) modified.

> SELECT count(*) FROM sk.tab1 
COUNT(*)|
--------+
     100|
1 row(s) fetched.


-- USR1 접속 후 테이블 조회
SQL> conn usr1
Enter password:
Connected.
SQL> select count(*) from sk.tab1 ;
select count(*) from sk.tab1
                        *
ERROR at line 1:
ORA-00942: table or view does not exist


-- USR2 접속 후 테이블 조회
SQL> conn usr2
Enter password:
Connected.
SQL>
SQL>
SQL> select count(*) from sk.tab1 ;
select count(*) from sk.tab1
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

테이블 조회 권한 부여

 
-- role 에 조회권한 부여
> CREATE ROLE rl_sel 
0 row(s) modified.

> GRANT SELECT ON sk.tab1 TO rl_sel 
0 row(s) modified.

-- role 권한 USR1에 부여
> GRANT rl_sel TO usr1 

0 row(s) modified.

-- 테이블 조회 권한 USR2에 부여
> GRANT SELECT ON sk.tab1 TO usr2 

0 row(s) modified.

-- 테이블 조회 권한 확인
> SELECT * FROM dba_tab_privs WHERE table_name = 'TAB1' 

GRANTEE|OWNER|TABLE_NAME|GRANTOR|PRIVILEGE|GRANTABLE|HIERARCHY|COMMON|TYPE |INHERITED|
-------+-----+----------+-------+---------+---------+---------+------+-----+---------+
USR2   |SK   |TAB1      |SK     |SELECT   |NO       |NO       |NO    |TABLE|NO       |
RL_SEL |SK   |TAB1      |SK     |SELECT   |NO       |NO       |NO    |TABLE|NO       |

2 row(s) fetched.

-- role 부여된 계정 확인
> SELECT * FROM dba_role_privs WHERE GRANTED_ROLE = 'RL_SEL' 

GRANTEE|GRANTED_ROLE|ADMIN_OPTION|DELEGATE_OPTION|DEFAULT_ROLE|COMMON|INHERITED|
-------+------------+------------+---------------+------------+------+---------+
SYSTEM |RL_SEL      |YES         |NO             |YES         |NO    |NO       |
USR1   |RL_SEL      |NO          |NO             |YES         |NO    |NO       |

2 row(s) fetched.

권한 부여 후 기존에 접속되어 있던 세션에서 테이블 조회 테스트

 
-- USR1 실패
SQL> select count(*) from sk.tab1 ;
select count(*) from sk.tab1
                        *
ERROR at line 1:
ORA-00942: table or view does not exist


-- USR2 성공
SQL> select count(*) from sk.tab1 ;

  COUNT(*)
----------
       100

SQL>

USR1 신규 세션 접속 후 조회 테스트

 
-- USR1 신규 접속
[ora19ent@svr ~]$ sqlplus usr1

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 16 03:05:18 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Mon Sep 16 2024 03:01:36 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

-- 조회 성공
SQL> select count(*) from sk.tab1 ;

  COUNT(*)
----------
       100

SQL>