[2025-05-29] GRANT READ PRIVILEGE (Oracle 12cR1 new feature) (12cR2에서 테스트)
[실습
환경]
OS : Oracle
Linux Server 7.9 (Linux rdb01d 5.4.17-2102.201.3.el7uek.x86_64)
DB : Oracle
Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
RAC1 :
rdb01d(192.168.0.31) : DRDB1
RAC2 : rdb01d(192.168.0.32) :
DRDB2
db_unique_name : DRDB
오라클 12cR1에 새로운 권한 GRANT READ 권한이 도입되었음.
GRANT READ
권한의 기능은 GRANT SELECT 권한의 기능과 거의 동일하지만 차이점이 있음
GRANT READ 권한은 사용자가
일반적인 SELECT 권한을 무시하고 테이블을 잠그는 것을 제한함
두 PRIVILEGE의 차이를 비교하는 테스트를 진행함
1. 실습 테이블
생성
[DRDB1:oracle@rdb01d][/home/oracle]$ alias st
alias st='rlwrap sqlplus tuner/oracle'
--> tuner는 dba role을 가지고 있는 상태임
[DRDB1:oracle@rdb01d][/home/oracle]$ st
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 9 22:48:47 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Thu May 29 2025 19:48:13 +09:00
Connected
to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production
[2025-06-09:22:48:47][rdb01d]<TUNER@DRDB1> CREATE TABLE TUNER.TB_TEST_20250609(TEST_NO VARCHAR2(10), TEST_NM VARCHAR2(100), CONSTRAINT PK_TB_TEST_20250609 PRIMARY KEY (TEST_NO));
Table created.
[2025-06-09:22:48:47][rdb01d]<TUNER@DRDB1>
INSERT INTO
TUNER.TB_TEST_20250609 (TEST_NO, TEST_NM) VALUES ('001',
'TEST_NAME_1');
INSERT INTO TUNER.TB_TEST_20250609 (TEST_NO, TEST_NM) VALUES
('002', 'TEST_NAME_2');
INSERT INTO TUNER.TB_TEST_20250609 (TEST_NO, TEST_NM)
VALUES ('003', 'TEST_NAME_3');
INSERT INTO TUNER.TB_TEST_20250609 (TEST_NO,
TEST_NM) VALUES ('004', 'TEST_NAME_4');
INSERT INTO TUNER.TB_TEST_20250609
(TEST_NO, TEST_NM) VALUES ('005', 'TEST_NAME_5');
INSERT INTO
TUNER.TB_TEST_20250609 (TEST_NO, TEST_NM) VALUES ('006',
'TEST_NAME_6');
INSERT INTO TUNER.TB_TEST_20250609 (TEST_NO, TEST_NM) VALUES
('007', 'TEST_NAME_7');
INSERT INTO TUNER.TB_TEST_20250609 (TEST_NO, TEST_NM)
VALUES ('008', 'TEST_NAME_8');
INSERT INTO TUNER.TB_TEST_20250609 (TEST_NO,
TEST_NM) VALUES ('009', 'TEST_NAME_9');
INSERT INTO TUNER.TB_TEST_20250609
(TEST_NO, TEST_NM) VALUES ('010', 'TEST_NAME_10');
[2025-06-09:22:48:47][rdb01d]<TUNER@DRDB1> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
2. 유저 생성
[2025-06-09:22:48:47][rdb01d]<TUNER@DRDB1> CREATE USER TEST_USER IDENTIFIED BY oracle;
User created.
Elapsed: 00:00:01.72
[2025-06-09:22:48:47][rdb01d]<TUNER@DRDB1> grant create session to TEST_USER;
Grant succeeded.
Elapsed: 00:00:00.65
3.
TEST_user 유저에게 TUNER.TB_TEST_20250609 테이블에 대한 select 권한 주기
[2025-06-09:22:48:47][rdb01d]<TUNER@DRDB1> grant SELECT ON TUNER.TB_TEST_20250609 to TEST_USER;
Grant succeeded.
Elapsed: 00:00:00.38
4.
TEST_user 계정으로 접속 한 후 TUNER.TB_TEST_20250609 테이블에 대한 select for update
[DRDB1:oracle@rdb01d][/home/oracle]$ rlwrap sqlplus TEST_user/oracle
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 9 23:15:56 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon Jun 09 2025 23:13:38 +09:00
Connected
to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production
[2025-06-09:23:15:56][rdb01d]<TEST_USER@DRDB1>
[2025-06-09:23:15:56][rdb01d]<TEST_USER@DRDB1> show user;
USER is "TEST_USER"
col test_no
for a20
col test_nm for a30
select * from TUNER.TB_TEST_20250609 for
update;
TEST_NO
TEST_NM
--------------------
------------------------------
001
TEST_NAME_1
002
TEST_NAME_2
003
TEST_NAME_3
004
TEST_NAME_4
005
TEST_NAME_5
006
TEST_NAME_6
007
TEST_NAME_7
008
TEST_NAME_8
009
TEST_NAME_9
010
TEST_NAME_10
10 rows selected.
Elapsed: 00:00:00.01
5. 또다른 세션
열어서 tuner(TUNER.TB_TEST_20250609 테이블의 owner) 계정으로 접속 한 후 update
시도
[2025-06-09:23:20:17][rdb01d]<TUNER@DRDB1> update TUNER.TB_TEST_20250609 set test_nm = 'init' where test_no =
'001';
--> 락 대기하게 됨
GRANT select 권한은 select for update 시 레코드에 lock을 잡는 것을 알 수 있음
6. 또다른 세션 또
열어서 락 모니터링
[2025-06-09:23:24:28][rdb01d]<SYS@DRDB1>
SELECT
S.INST_ID
,
S.BLOCKING_SESSION
,
S.SID
, S.SERIAL#
,
S.SECONDS_IN_WAIT
FROM GV$SESSION S
WHERE BLOCKING_SESSION IS
NOT NULL;
INST_ID
BLOCKING_SESSION SID
SERIAL# SECONDS_IN_WAIT
---------- ---------------- ---------- ----------
---------------
1
275
406
6726
322
--> 사용자 TEST_USER가
TUNER.TB_TEST_20250609 테이블에 대한 GRANT SELECT 권한만 가지고 있음에도 불구하고 테이블을 잠글 수 있다는 것을
확인함
--select한 세션을 종료 quit로 종료
시킴
[2025-06-09:23:15:56][rdb01d]<TEST_USER@DRDB1> quit
--update한 세션은
롤백해줌
[2025-06-09:23:20:17][rdb01d]<TUNER@DRDB1> rollback;
7. 다시
새로운 유저를 생성 후 GRANT read 권한 주기
[DRDB1:oracle@rdb01d][/home/oracle]$ st
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 9 23:34:09 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon Jun 09 2025 23:20:17 +09:00
Connected
to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production
[2025-06-09:23:34:09][rdb01d]<TUNER@DRDB1>
[2025-06-09:23:20:17][rdb01d]<TUNER@DRDB1> CREATE USER TEST_USER2 IDENTIFIED BY oracle;
User created.
Elapsed: 00:00:00.06
[2025-06-09:23:20:17][rdb01d]<TUNER@DRDB1> grant create session to TEST_USER2;
Grant succeeded.
Elapsed:
00:00:00.03
[2025-06-09:23:20:17][rdb01d]<TUNER@DRDB1> grant read ON TUNER.TB_TEST_20250609 to TEST_USER2;
8.
TEST_USER2 계정으로 접속 한 후 TUNER.TB_TEST_20250609 테이블에 대한 select for
update
DRDB1:oracle@rdb01d][/home/oracle]$ rlwrap sqlplus TEST_USER2/oracle
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 9 23:15:56 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon Jun 09 2025 23:13:38 +09:00
Connected
to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production
[2025-06-09:23:15:56][rdb01d]<TEST_USER2@DRDB1>
[2025-06-09:23:15:56][rdb01d]<TEST_USER2@DRDB1> show user;
USER is
"TEST_USER2"
[DRDB1:oracle@rdb01d][/home/oracle]$ rlwrap sqlplus TEST_USER2/oracle
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 9 23:35:42 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
[2025-06-09:23:35:42][rdb01d]<TEST_USER2@DRDB1> select count(*) as cnt from TUNER.TB_TEST_20250609;
CNT
----------
10
1 row selected.
Elapsed: 00:00:00.01
col test_no
for a20
col test_nm for a30
select * from TUNER.TB_TEST_20250609 for
update;
ERROR at
line 1:
ORA-01031: insufficient privileges
Elapsed: 00:00:00.01
--> read 권한은 for udpate를 못함
--> grant select any와
유사하게 read select any 권한을 줄 수 있음
9.
실습 테이블 정리 (원상 복구)
[2025-06-09:23:34:09][rdb01d]<TUNER@DRDB1> drop table TUNER.TB_TEST_20250609
purge;
[2025-06-09:23:34:09][rdb01d]<TUNER@DRDB1> drop user TEST_USER
cascade;
[2025-06-09:23:34:09][rdb01d]<TUNER@DRDB1> drop user TEST_USER2 cascade;