[제목]
[2025-10-20] Multitenant 환경에서 Common User vs Local User에 대한 실습 (19c에서 테스트)
[테스트
개요]
19c 멀티테넌트에서
Common User vs Local User의 생성/권한/접속 동작을 검증했다.
Common User는 CREATE SESSION을
CONTAINER=ALL로 부여해야 PDB에서 로그인 가능함을 확인했고, 기본 TS 지정 시 **모든 컨테이너에 동일 TS가 존재해야 함(미존재
시 ORA-00959)**도 재현했다.
Local User는 PDB에서 생성·권한 부여 후 정상 접속됨을 확인했으며, 실습 종료 후 두
사용자 모두 원복(DROP) 했다.
[테스트
환경]
OS : Oracle
Linux Server 8.10 (grep ^PRETTY_NAME= /etc/os-release | cut -d= -f2- | tr -d
'"')
OS Kernal : 5.15.0-206.153.7.1.el8uek.x86_64 (uname -r)
Oracle
Version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
(echo -e "set pages 0 lines 200 feedback off heading off\nselect banner from
v\$version where banner like 'Oracle Database%';" | sqlplus -s / as
sysdba)
Oracle Configuration
DB명 : ORA19RS (echo
-e 'set pages 0 feedback off heading off verify off\nselect '\''DB명 : '\''||name
from v$database;' | sqlplus -s / as sysdba) '
PDB명 : ORA19RSP1 (echo -e
"set pages 0 feedback off heading off verify off\nselect 'PDB명 : '||name from
v\$pdbs where name <> 'PDB\$SEED';" | sqlplus -s / as
sysdba)
RAC Node 1
Hostname : ol8ora19rs1
(hostname)
Public IP : 192.168.240.41 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA19RS1 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
RAC Node 2
Hostname :
ol8ora19rs2
Public IP : 192.168.240.42 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA19RS2 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
Patch Info
Grid (opatch lspatches) (grid os
user)
38124772;TOMCAT RELEASE UPDATE 19.0.0.0.0
(38124772)
37962946;OCW RELEASE UPDATE 19.28.0.0.0
(37962946)
37962938;ACFS RELEASE UPDATE 19.28.0.0.0
(37962938)
37960098;Database Release Update : 19.28.0.0.250715
(37960098)
36758186;DBWLM RELEASE UPDATE 19.0.0.0.0
(36758186)
Oracle (opatch lspatches) (oracle os
user)
37962946;OCW RELEASE UPDATE 19.28.0.0.0
(37962946)
37960098;Database Release Update : 19.28.0.0.250715
(37960098)
[내용]
1. common user
(1) common
user는 cdb$root 및 모든 pdb에서 사용가능
(2) common user는 root cdb$root에서만 생성
(3) common user의 username은 C##으로 시작해야 함
(4) common user를 만들 때
container=Current를 사용할 수 없음. container=ALL을 사용하거나 container 키워드를 사용하지 말것
(5)
common user 소유의 객체를 생성하는 것은 권장하지 않음
1-1. common
user 생성
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Mon Oct 20 22:06:42 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show con_name
CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rs1]<SYS@ORA19RS1>$ show pdbs
CON_ID
CON_NAME
OPEN MODE RESTRICTED
---------- ------------------------------
---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA19RSP1
READ WRITE NO
[ol8ora19rs1]<SYS@ORA19RS1>$ create user c##cuser identified by
"oracle";
User created.
Elapsed: 00:00:00.10
사용자를
생성할 때 Default 테이블스페이스를 명시적으로 기재하면 해당 테이블스페이스는 CDB$ROOT 및 PDB들에 모두 같은 이름의
테이블 스페이스가 존재하고 있어야함
--테이블스페이스가 PDB에 존재하지 않으면 다음과 같은 오류가 발생함
[ol8ora19rs1]<SYS@ORA19RS1>$ create user c##user2 identified by
"oracle" default tablespace not_exists_ts;
create user c##user2 identified by
"oracle" default tablespace not_exists_ts
*
ERROR at line 1:
ORA-00959:
tablespace 'NOT_EXISTS_TS' does not exist
Elapsed: 00:00:00.01
1-2.
common user에게 권한을 준 후 CDB와 PDB에 접속 시도
[ol8ora19rs1]<SYS@ORA19RS1>$ grant create session to c##cuser;
Grant succeeded.
Elapsed: 00:00:00.01
--common user로 CDB$ROOT
접속
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ sqlplus c##cuser/oracle
SQL*Plus:
Release 19.0.0.0.0 - Production on Mon Oct 20 22:23:02 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<C##CUSER@ORA19RS1>$ show con_name
CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rs1]<C##CUSER@ORA19RS1>$
--> CDB로는 접속이 제대로 됨
--common user로 PDB 접속 시도
--PDB의 서비스명 찾기 (grid os user)
[+ASM1:grid@ol8ora19rs1][/home/grid]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 20-OCT-2025 22:23:54
Copyright (c) 1991, 2025, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the
LISTENER
------------------------
Alias
LISTENER
Version
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start
Date
20-OCT-2025
22:04:26
Uptime
0 days 0 hr. 19 min. 27 sec
Trace
Level
off
Security
ON: Local OS
Authentication
SNMP
OFF
Listener Parameter File
/u01/app/19c/grid/network/admin/listener.ora
Listener Log
File
/u01/app/oracle/diag/tnslsnr/ol8ora19rs1/listener/alert/log.xml
Listening
Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.240.41)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.240.44)(PORT=1521)))
Services
Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1",
status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1
instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this
service...
Service "+ASM_DATA1" has 1 instance(s).
Instance
"+ASM1", status READY, has 1 handler(s) for this service...
Service
"+ASM_FRA1" has 1 instance(s).
Instance "+ASM1", status READY, has 1
handler(s) for this service...
Service "40bb61da40640933e0632af0a8c05571" has
1 instance(s).
Instance "ORA19RS1", status READY, has 1 handler(s) for
this service...
Service "ORA19RS" has 1 instance(s).
Instance
"ORA19RS1", status READY, has 1 handler(s) for this service...
Service
"ORA19RSXDB" has 1 instance(s).
Instance "ORA19RS1", status READY, has
1 handler(s) for this service...
Service "ora19rsp1" has 1
instance(s).
Instance "ORA19RS1", status READY, has 1 handler(s) for
this service...
The command completed
successfully
--tnsnames.ora 정보 찾기 (oracle os user)
[ORA19RS1:oracle@ol8ora19rs1][/u01/app/oracle/product/19c/db_1/network/admin]$
cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration
File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated
by Oracle configuration tools.
ORA19RS =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = ol8ora19rs-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME =
ORA19RS)
)
)
ORA19RS1P1 =
(DESCRIPTION
=
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.240.41)(PORT
= 1521))
(CONNECT_DATA =
(SERVICE_NAME = ora19rsp1)
)
)
--PDB로 접속 시도
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ sqlplus c##cuser/oracle@ORA19RS1P1
SQL*Plus:
Release 19.0.0.0.0 - Production on Mon Oct 20 22:34:35 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
ERROR:
ORA-01045: user C##CUSER lacks CREATE SESSION privilege; logon
denied
Enter user-name:
--> PDB로는 접속 불가능한 상태임
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as
sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$
ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Mon Oct 20 22:39:15 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show con_name
CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rs1]<SYS@ORA19RS1>$
COL grantee
FOR a15
COL privilege FOR a25
COL common FOR a6
COL con_id FOR
9999
SELECT
grantee
, privilege
,
common
, con_id
FROM
cdb_sys_privs
WHERE grantee = 'C##CUSER';
GRANTEE
PRIVILEGE
COMMON CON_ID
--------------- ------------------------- ------
------
C##CUSER CREATE
SESSION
NO 1
--> CDB에서 common user에게 create session 권한을 줬는데 그 권한을 오로지 CDB에만 가지고 있는것을 알 수 있음
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Mon Oct 20 22:46:06 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show con_name
CON_NAME
------------------------------
CDB$ROOT
--> container=all 을 기재해서 create session 권한을 주기
[ol8ora19rs1]<SYS@ORA19RS1>$ grant create session to c##cuser container=ALL;
Grant succeeded.
Elapsed: 00:00:00.02
COL
grantee FOR a15
COL
privilege FOR a25
COL
common FOR a6
COL
inherited FOR a9
COL admin_option
FOR a12
COL container_name FOR a20
COL
con_id FOR
9999
SELECT
sp.grantee
,
sp.privilege
, sp.common
, sp.inherited
,
sp.admin_option
,
sp.con_id
, vc.name AS container_name
FROM
cdb_sys_privs sp
JOIN v$containers vc
ON
vc.con_id = sp.con_id
WHERE sp.grantee = 'C##CUSER'
ORDER BY
sp.con_id
, CASE sp.common WHEN
'YES' THEN 1 ELSE 2 END
, CASE
sp.inherited WHEN 'YES' THEN 1 ELSE 2 END
;
GRANTEE
PRIVILEGE
COMMON INHERITED ADMIN_OPTION CON_ID CONTAINER_NAME
---------------
------------------------- ------ --------- ------------ ------
--------------------
C##CUSER
CREATE SESSION
YES NO
NO
1 CDB$ROOT
C##CUSER CREATE
SESSION
NO NO
NO
1 CDB$ROOT
C##CUSER CREATE
SESSION
YES YES
NO
3 ORA19RSP1
COMMON =
YES: **루트(CDB$ROOT)에서 CONTAINER=ALL로 ‘공통 부여’**된 권한입니다. 이 권한은 해당 공통 사용자(C##…)에게
모든 PDB에 걸쳐 유효하고, 새로 만들어질 PDB에도 자동 적용됨
COMMON = NO: **현재 컨테이너에만 ‘로컬 부여’**된
권한입니다. CONTAINER=CURRENT(기본값)로 부여될 때 생깁니다. 루트에서도 로컬 부여가 가능하므로, 루트에 대해 로컬(NO) 과
공통(YES) 이 둘 다 존재할 수 있음
--이 상태에서 PDB로 접속
시도
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ sqlplus c##cuser/oracle@ORA19RS1P1
SQL*Plus:
Release 19.0.0.0.0 - Production on Mon Oct 20 22:55:15 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Mon Oct 20 2025 22:23:02 +09:00
Connected
to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.28.0.0.0
[ol8ora19rs1]<C##CUSER@ORA19RS1P1>$
--> 접속 가능함
따라서 common user의 경우 container=ALL 권한을 부여해야 함. 그렇지 않으면 PDBS는 해당 권한을 상속받지 못함
2.
local user
(1) 로컬 사용자는
일반 데이터베이스 사용자로, PDB에서만 생성할 수 있음.
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as
sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$
ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Mon Oct 20 22:59:45 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show con_name
CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rs1]<SYS@ORA19RS1>$ show pdbs
CON_ID
CON_NAME
OPEN MODE RESTRICTED
---------- ------------------------------
---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA19RSP1
READ WRITE NO
[ol8ora19rs1]<SYS@ORA19RS1>$ alter session set
container=ORA19RSP1;
Session altered.
Elapsed: 00:00:00.00
[ol8ora19rs1]<SYS@ORA19RS1>$ create user local_user identified by
"oracle";
User created.
Elapsed: 00:00:00.06
[ol8ora19rs1]<SYS@ORA19RS1>$ grant create session to
local_user;
Grant succeeded.
Elapsed: 00:00:00.00
[ol8ora19rs1]<SYS@ORA19RS1>$ quit
Disconnected from Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version
19.28.0.0.0
--> PDB로 접속 시도
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ sqlplus local_user/oracle@ORA19RS1P1
SQL*Plus:
Release 19.0.0.0.0 - Production on Mon Oct 20 23:01:20 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<LOCAL_USER@ORA19RS1P1>$ quit
--> 접속
성공
Disconnected from Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ sqlplus sys/oracle@ORA19RS1P1 as sysdba
SQL*Plus:
Release 19.0.0.0.0 - Production on Mon Oct 20 23:05:11 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1P1>$
COL container_name FOR a20
COL
grantee FOR a20
COL
privilege FOR a30
COL
common FOR a6
COL
inherited FOR a9
COL admin_option
FOR a12
SELECT
SYS_CONTEXT('USERENV','CON_NAME') AS container_name
, sp.grantee
,
sp.privilege
, sp.common
, sp.inherited
, sp.admin_option
FROM
dba_sys_privs sp
WHERE sp.grantee in ('C##CUSER',
'LOCAL_USER')
ORDER BY sp.common
DESC
, sp.inherited
DESC
,
sp.privilege
;
CONTAINER_NAME
GRANTEE
PRIVILEGE
COMMON INHERITED ADMIN_OPTION
-------------------- --------------------
------------------------------ ------ ---------
------------
ORA19RSP1
C##CUSER
CREATE
SESSION
YES YES
NO
ORA19RSP1
LOCAL_USER CREATE
SESSION
NO NO
NO
--> 해당 PDB내에는 local user의 create session 권한도 생겼음
99. 작업 정리 (원상 복구)
--PDB
정리
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ sqlplus sys/oracle@ORA19RS1P1 as sysdba
SQL*Plus:
Release 19.0.0.0.0 - Production on Mon Oct 20 23:08:24 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
Elapsed: 00:00:00.05
[ol8ora19rs1]<SYS@ORA19RS1P1>$ drop user local_user
cascade;
User dropped.
Elapsed: 00:00:00.53
--CDB 정리
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Mon Oct 20 23:09:36 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show con_name
CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rs1]<SYS@ORA19RS1>$ DROP USER C##CUSER
CASCADE;
User dropped.
Elapsed: 00:00:00.63
[ol8ora19rs1]<SYS@ORA19RS1>$
SELECT
username, common, con_id
FROM cdb_users
WHERE username =
'C##CUSER';
no rows selected
Elapsed:
00:00:00.04