[Oracle 12c] Multitenant 컨테이너 / Pluggable 데이터베이스 관리

haisins

7년 ago

Multitenant 컨테이너/Pluggable 데이터베이스 관리

Multitenant 컨테이너/Pluggable 데이터베이스 관리에 대해 살펴보도록 하겠습니다. Multitenant 아키텍쳐라고 하더라도 기존 오라클 데이터베이스와 사용 방법이 크게 다르지 않습니다. 이번에는 Multitenant 아키텍쳐에서 기존 관리 방식과는 약간의 차이가 있는 인스턴스 파라미터 관리, 유저 관리, 권한 관리를 살펴 보겠습니다.

인스턴스 파라미터 관리

Pluggable 데이터베이스의 인스턴스 파라미터 변경 영향을 “OPTIMIZER_USE_SQL_PLAN_BASELINES”를 실습으로 알아 보겠습니다.

SYS 유저로 Root 컨테이너 데이터베이스에 접속하여 인스턴스 파라미터 “OPTIMIZER_USE_SQL_PLAN_BASELINES”의 Pluggable 데이터베이스에서의 변경 가능 여부를 v$parameter 뷰에서 확인합니다. “is_pdb_modifiable” 칼럼 값을 통해 PDB 레벨에서도 변경 가능한 인스턴스 파라미터 임을 알 수 있습니다.

[oracle@New-Features-12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 25 00:45:45 2015

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

SQL> show con_name

CON_NAME

——————————

CDB$ROOT

SQL> col name for a40

SQL> col value for a10

SQL> select name,value,ispdb_modifiable from v$parameter where name = ‘optimizer_use_sql_plan_baselines’;

NAME VALUE ISPDB

—————————————- ———- —–

optimizer_use_sql_plan_baselines TRUE TRUE

이번에는 [CDB1] 내 [PDB2]에 접속해서 OPTIMIZER_USE_SQL_PLAN_BASELINES 속성을 확인합니다. Root 컨테이너와 동일하게 설정되어 있음을 알 수 있습니다.

SQL> connect sys/oracle_4U@pdb2 as sysdba

Connected.

SQL> show con_name

CON_NAME

——————————

PDB2

SQL> show parameter optimizer_use_sql_plan_baselines

NAME TYPE VALUE

———————————— ———– ——————————

optimizer_use_sql_plan_baselines boolean TRUE

[PDB2]에 접속한 상태에서 OPTIMIZER_USE_SQL_PLAN_BASELINES 를 FALSE로 변경하고 이를 확인해 보겠습니다.

SQL> alter system set optimizer_use_sql_plan_baselines=false scope=both;

System altered.

SQL> show parameter optimizer_use_sql_plan_baselines

NAME TYPE VALUE

———————————— ———– ——————————

optimizer_use_sql_plan_baselines boolean FALSE

[CDB1] 내 또 다른 PDB인 [PDB3] 에서는 [PDB2]에서 변경한 인스턴스 파라미터 OPTIMIZER_USE_SQL_PLAN_BASELINES 속성이 어떻게 적용되어 있는 지 확인합니다. PDB 레벨에서 변경 가능한 인스턴스 파라미터의 경우, CDB 내 PDB들 간에 서로 다른 값이 설정 될 수 있음을 알 수 있습니다.

SQL> connect sys/oracle_4U@pdb3 as sysdba

Connected.

SQL> show con_name

CON_NAME

——————————

PDB3

SQL> show parameter optimizer_use_sql_plan_baselines

NAME TYPE VALUE

———————————— ———– ——————————

optimizer_use_sql_plan_baselines boolean TRUE

이번에는 [CDB1]를 재기동하여 OPTIMIZER_USE_SQL_PLAN_BASELINES 설정 값을 전체적으로 확인해 보겠습니다. [CDB1]에 접속해서 확인합니다. CDB 레벨에서는 TRUE, [PDB2]에서는 FALSE로 설정되어 있음을 알 수 있습니다.

SQL> connect / as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 6325010432 bytes

Fixed Size 2938448 bytes

Variable Size 2919238064 bytes

Database Buffers 2113929216 bytes

Redo Buffers 13836288 bytes

In-Memory Area 1275068416 bytes

Database mounted.

Database opened.

SQL> col value for a20

SQL> select con_id,value from v$system_parameter where name = ‘optimizer_use_sql_plan_baselines’;

CON_ID VALUE

———- ——————–

0 TRUE

4 FALSE

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Common/Local 유저 관리

Multitenant 환경에서 유저 관리를 실습을 통해 살펴 보겠습니다. 우선 Root 컨테이너에 접속하여 모든 Common, Local 유저 현황을 확인해 보겠습니다.

[oracle@New-Features-12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 25 01:11:02 2015

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

SQL> set lines 100

SQL> set pages 300

SQL> col username for a50

SQL> select username,common,con_id from cdb_users;

USERNAME COM CON_ID

—————————————- — ———-

APEX_LISTENER YES 1

ORACLE_OCM YES 1

OJVMSYS YES 1

FLOWS_FILES YES 7

DVF YES 7

MDSYS YES 7

C##DBAAS_MONITOR YES 5

SYSTEM YES 5

SYS YES 5

251 rows selected.

이번에는 SYSTEM 유저를 딕셔너리 뷰에서 확인해 보겠습니다. 각 컨테이너별로 SYSTEM 유저가 있는 것을 알 수 있습니다.

SQL> select username,common,con_id from cdb_users where username=’SYSTEM’ order by 3;

USERNAME COM CON_ID

—————————————- — ———-

SYSTEM YES 1

SYSTEM YES 3

SYSTEM YES 4

SYSTEM YES 5

SYSTEM YES 6

SYSTEM YES 7

6 rows selected.

[CDB1]에 생성된 모든 Common 유저를 확인해 봅니다.

SQL> select distinct username from cdb_users where common=’YES’;

USERNAME

—————————————-

MDSYS

SYSTEM

SYS

SPATIAL_CSW_ADMIN_USR

ORACLE_OCM

ORDSYS

XDB

LBACSYS

SI_INFORMTN_SCHEMA

39 rows selected.

[CDB1]에 생성된 유저 중 Common 유저가 아닌 유저를 조회해 보겠습니다.

SQL> select username,con_id from cdb_users where common=’NO’ order by 2;

USERNAME CON_ID

—————————————- ———-

DBAAS_MONITOR 3

PM 3

HR 3

SCOTT 3

IX 3

BI 3

PDBADMIN 3

SH 3

OE 3

PDB_ADMIN 4

PDB2_ADMIN 4

PDB_ADMIN 5

PDB3_ADMIN 5

PDB3_ADMIN 6

PDB_ADMIN 6

PDB2_ADMIN 7

PDB_ADMIN 7

17 rows selected.

이번에는 [CDB1]에 Common 유저를 생성하고 이를 확인해 봅니다.

SQL> create user c##u identified by x container=all;

User created.

SQL> select username,common,con_id from cdb_users where username=’C##U’ order by 3;

USERNAME COM CON_ID

——————– — ———-

C##U YES 1

C##U YES 3

C##U YES 4

C##U YES 5

C##U YES 6

C##U YES 7

6 rows selected.

Common 유저 [C##U]에 CREATE SESSION 권한을 부여한 뒤, [CDB1]과 [CDB1] 내 각 PDB에 접속해 봅니다.

SQL> grant create session to c##u container=all;

Grant succeeded.

SQL> connect c##u/x@pdb2

Connected.

SQL> connect c##u/x@pdb3

Connected.

SQL> connect c##u/x@cdb1

Connected.

CONTAINER = ALL 옵션은 Root 컨테이너에서만, CONTAINER = CURRENT 옵션은 PDB레벨에서만 설정 가능합니다.

이를 확인하기 위해 Root 컨테이너에서 Local 유저를 만들어 보겠습니다. 이 경우 ORA-65049 에러를 리턴하게 됩니다. Root 컨테이너에서는 Local 유저가 생성이 되지 않는 것을 알 수 있습니다.

SQL> connect / as sysdba

Connected.

SQL> show con_name

CON_NAME

——————————

CDB$ROOT

SQL> create user lu identified by x container=current;

create user lu identified by x container=current

ERROR at line 1:

ORA-65049: creation of local user or role is not allowed in CDB$ROOT

[PDB2]에 접속해서 Common 유저를 만들어 보겠습니다. 이번에는 ORA-65050 에러를 리턴합니다. PDB 레벨에서는 Common 유저를 생성할 수 없다는 것을 알 수 있습니다.

SQL> connect sys/oracle_4U@pdb2 as sysdba

Connected.

SQL> create user c##u_pdb2 identified by x container=all;

create user c##u_pdb2 identified by x container=all

ERROR at line 1:

ORA-65050: Common DDLs only allowed in CDB$ROOT

그렇다면 이번에는 [PDB2]에 접속한 상태에서 Local 유저를 만들고, 딕셔너리 뷰에서 이를 확인해 보겠습니다. CREATE SESSION 권한도 부여합니다.

SQL> show con_name

CON_NAME

——————————

PDB2

SQL> create user lu identified by x container=current;

User created.

SQL> select username,common,con_id from cdb_users where username=’LU’;

USERNAME COM CON_ID

——————– — ———-

LU NO 4

SQL> grant create session to lu;

Grant succeeded.

생성한 Local 유저 [LU]의 접속 테스트를 해 봅니다. [PDB2]에서 생성한 Local 유저는 다른 PDB나 Root 컨테이너에서는 접속할 수 없음을 알 수 있습니다.

SQL> connect lu/x@pdb2

Connected.

SQL> connect lu/x@pdb3

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> connect lu/x@cdb1

ERROR:

ORA-01017: invalid username/password; logon denied

다시 [PDB2]에 접속한 상태에서 모든 유저 현황를 확인해 봅니다. 이 경우 cdb_user와 dba_users에서 보여 주는 유저 목록은 동일한 것을 알 수 있습니다.

SQL> connect sys/oracle_4U@pdb2 as sysdba

Connected.

SQL> show con_name

CON_NAME

——————————

PDB2

SQL> select username,common,con_id from cdb_users order by 1;

USERNAME COM CON_ID

——————– — ———-

ANONYMOUS YES 4

APEX_040200 YES 4

APEX_LISTENER YES 4

SYSKM YES 4

SYSTEM YES 4

WMSYS YES 4

XDB YES 4

XS$NULL YES 4

43 rows selected.

SQL> select username,common from cdb_users minus select username,common from dba_users;

no rows selected

SQL> select username,common from dba_users minus select username,common from cdb_users;

no rows selected

SQL> select count(*) from cdb_users;

COUNT(*)

———-

SQL> select count(*) from dba_users;

COUNT(*)

———-

Common/Local 권한 관리

Privilege 자체는 Common/Local 구분이 없습니다. 다만 Common 또는 Local로 부여될 뿐입니다. 관련 딕셔너리 뷰를 조회해 보면 COMMON 칼럼이 없는 것을 확인 할 수 있습니다.

SQL> connect / as sysdba

Connected.

SQL> show con_name

CON_NAME

——————————

CDB$ROOT

SQL> desc sys.system_privilege_map

Name Null? Type

—————————————————– ——– —————————-

PRIVILEGE NOT NULL NUMBER

NAME NOT NULL VARCHAR2(40)

PROPERTY NOT NULL NUMBER

SQL> desc sys.table_privilege_map

Name Null? Type

—————————————————– ——– —————————-

PRIVILEGE NOT NULL NUMBER

NAME NOT NULL VARCHAR2(40)

앞선 실습에서 생성한 유저 [C##U]와 [LU]에 CREATE SESSION 권한이 어떻게 부여되어 있는 지 확인해 봅니다.

SQL> col grantee for a20

SQL> col privilege for a20

SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee in (‘C##U’,’LU’) order by 4;

GRANTEE PRIVILEGE COM CON_ID

——————– ——————– — ———-

C##U CREATE SESSION YES 1

C##U CREATE SESSION YES 3

C##U CREATE SESSION YES 4

LU CREATE SESSION NO 4

C##U CREATE SESSION YES 5

C##U CREATE SESSION YES 6

C##U CREATE SESSION YES 7

7 rows selected.

[PDB2]에 접속했을 때, PDB 레벨에서는 부여된 권한 정보가 어떻게 보이는 지 살펴보겠습니다.

SQL> connect sys/oracle_4U@pdb2 as sysdba

Connected.

SQL> select grantee,privilege,common from dba_sys_privs where grantee in (‘C##U’,’LU’);

GRANTEE PRIVILEGE COM

——————– ——————– —

LU CREATE SESSION NO

C##U CREATE SESSION YES

Root 컨테이너에서 Common 유저 [C##U] 에 CREATE TABLE, UNLIMITED TABLESPACE 권한을 부여하고, 모든 PDB에 반영되는 지 확인합니다.

SQL> connect sys/oracle_4U@cdb1 as sysdba

Connected.

SQL> grant create table, unlimited tablespace to c##u container=all;

Grant succeeded.

SQL> set pages 100

SQL> col grantee for a15

SQL> col privilege for a30

SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee=’C##U’ order by 2,4;

GRANTEE PRIVILEGE COM CON_ID

————— —————————— — ———-

C##U CREATE SESSION YES 1

C##U CREATE SESSION YES 3

C##U CREATE SESSION YES 4

C##U CREATE SESSION YES 5

C##U CREATE SESSION YES 6

C##U CREATE SESSION YES 7

C##U CREATE TABLE YES 1

C##U CREATE TABLE YES 3

C##U CREATE TABLE YES 4

C##U CREATE TABLE YES 5

C##U CREATE TABLE YES 6

C##U CREATE TABLE YES 7

C##U UNLIMITED TABLESPACE YES 1

C##U UNLIMITED TABLESPACE YES 3

C##U UNLIMITED TABLESPACE YES 4

C##U UNLIMITED TABLESPACE YES 5

C##U UNLIMITED TABLESPACE YES 6

C##U UNLIMITED TABLESPACE YES 7

18 rows selected.

CREATE SEQUENCE 권한을 Common 유저 [C##U]에 부여하되 Root 컨테이너에만 적용되도록 부여합니다. 이는 Local 권한이 됩니다.

SQL> show con_name

CON_NAME

——————————

CDB$ROOT

SQL> grant create sequence to c##u container=current;

Grant succeeded.

SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee=’C##U’ order by 2,4;

GRANTEE PRIVILEGE COM CON_ID

————— —————————— — ———-

C##U CREATE SEQUENCE NO 1

C##U CREATE SESSION YES 1

C##U CREATE SESSION YES 3

C##U CREATE SESSION YES 4

C##U CREATE SESSION YES 5

C##U CREATE SESSION YES 6

C##U CREATE SESSION YES 7

C##U CREATE TABLE YES 1

C##U CREATE TABLE YES 3

C##U CREATE TABLE YES 4

C##U CREATE TABLE YES 5

C##U CREATE TABLE YES 6

C##U CREATE TABLE YES 7

C##U UNLIMITED TABLESPACE YES 1

C##U UNLIMITED TABLESPACE YES 3

C##U UNLIMITED TABLESPACE YES 4

C##U UNLIMITED TABLESPACE YES 5

C##U UNLIMITED TABLESPACE YES 6

C##U UNLIMITED TABLESPACE YES 7

19 rows selected.

이번에는 CREATE SYNONYM 권한을 Common 유저 [C##U]에 부여하되 [PDB2]에서만 적용되도록 부여합니다. (Local 권한)

SQL> connect sys/oracle_4U@pdb2 as sysdba

Connected.

SQL> grant create synonym to c##u container=current;

Grant succeeded.

SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee=’C##U’ order by 2,4;

GRAN PRIVILEGE COM CON_ID

—- —————————————- — ———-

C##U CREATE SESSION YES 4

C##U CREATE SYNONYM NO 4

C##U CREATE TABLE YES 4

C##U UNLIMITED TABLESPACE YES 4

CREAE VIEW 권한을 [PDB2]에 접속한 채 Common 유저 [C##U]에 부여하되 이번에는 Root 컨테이너를 포함한 모든 컨테이너에 적용되도록 해 보겠습니다. 즉 [PDB2]에서 Common 권한을 부여합니다. 이 경우, ORA-65050 에러를 리턴하게 되는데 PDB레벨에서는 Common 궈한을 부여할 수 없음을 알 수 있습니다.

SQL> show con_name

CON_NAME

——————————

PDB2

SQL> grant create view to c##u container=all;

grant create view to c##u container=all

ERROR at line 1:

ORA-65050: Common DDLs only allowed in CDB$ROOT

특정 PDB의 Local 유저에게 부여하는 권한이 모든 컨테이너에 적용되도록 하는 것 역시 불가합니다.

SQL> connect sys/oracle_4U as sysdba

Connected.

SQL> show con_name

CON_NAME

——————————

CDB$ROOT

SQL> grant create any table to lu container=all;

grant create any table to lu container=all

ERROR at line 1:

ORA-01917: user or role ‘LU’ does not exist

특정 PDB의 Local 유저에게 부여하는 권한이 Root 컨테이너에서 적용되도록 하는 것도 불가합니다. 해당 유저가 Root 컨테이너에는 없기 때문입니다.

SQL> grant create any sequence to lu container=current;

grant create any sequence to lu container=current

ERROR at line 1:

ORA-01917: user or role ‘LU’ does not exist

특정 PDB의 Local 유저에게 부여하는 권한이 해당 PDB에서만 적용되도록 하는 것은 가능합니다.

SQL> connect sys/oracle_4U@pdb2 as sysdba

Connected.

SQL> grant unlimited tablespace to lu container=current;

Grant succeeded.

SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee=’LU’;

GRANTEE PRIVILEGE COM CON_ID

———- —————————————- — ———-

LU UNLIMITED TABLESPACE NO 4

LU CREATE SESSION NO 4

하지만 Local 유저에게 부여하는 Local 권한이 다른 컨테이너에도 적용되도록 하는 것은 불가합니다.

SQL> grant drop any view to lu container=all;

grant drop any view to lu container=all

ERROR at line 1:

ORA-65030: one may not grant a Common Privilege to a Local User or Role

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options