오라클 12c CDB common 유저 생성

오라클 12c 부터 CDB, PDB 라는 새로운 개념이 도입되었다. 

CDB 를 사용하는 DB 에서는 기존처럼 유저를 생성하면 에러가 난다. 

DB에 접속하는 것도 기존과 조금 다르다. 

CDB 에 접속하는 유저들을 common user 라고 부르는데, 

CDB 에 접속하려면 c## 이라는 접두어를 붙여야 한다.

(오라클이 제공하는 common user 는 예외. ex: sys,system)

● Common user 생성

 [oracle@oracdb response]$ sqlplus system/oracle

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 13 15:15:43 2017

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

Last Successful login time: Wed Dec 13 2017 15:12:59 +09:00

Connected to:

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

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

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

User created.

SQL> grant create session to c##test;

Grant succeeded.

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

[oracle@oracdb response]$ sqlplus test/test            -- 이전 버전방식의 로긴 시도

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 13 15:16:35 2017

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

ERROR:

ORA-01017: invalid username/password; logon denied

 

[oracle@oracdb response]$ 

[oracle@oracdb response]$ sqlplus c##test/test        -- c## 접두어 붙여서 접속  

 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 13 15:17:02 2017

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

Last Successful login time: Wed Dec 13 2017 15:16:47 +09:00

Connected to:

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

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

SQL> show con_name;

CON_NAME

------------------------------

CDB$ROOT

● PDB 에 접속권한 주기

[oracle@oracdb ~]$ sqlplus system/oracle

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 14 13:51:46 2017

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

Last Successful login time: Wed Dec 13 2017 17:20:59 +09:00

Connected to:

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

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

SQL> 

SQL> alter session set container=pdb01;

Session altered.

SQL> grant connect to c##test container=current;

Grant succeeded.

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

[oracle@oracdb ~]$ sqlplus c##test/test

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 14 13:59:31 2017

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

Last Successful login time: Thu Dec 14 2017 13:49:30 +09:00

Connected to:

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

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

SQL> alter session set container=pdb01;      

Session altered.

SQL> show con_name 

CON_NAME

------------------------------

PDB01

SQL> 

끗. 공감 구걸