SQL> select * from v$version;
BANNER                                                                                                                 CON_ID
--------------------------------------------------------------------------------                       ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production      0
PL/SQL Release 12.1.0.2.0 - Production                                                                 0
CORE    12.1.0.2.0      Production                                                                           0
TNS for Solaris: Version 12.1.0.2.0 - Production                                                    0
NLSRTL Version 12.1.0.2.0 - Production                                                                0

SQL> show pdbs;
    CON_ID CON_NAME             OPEN MODE RESTRICTED
---------- ------------------------------ ----------            ----------
         2 PDB$SEED                       READ ONLY   NO
         3 PDB1                                 READ WRITE NO
         4 PDB2                                 READ WRITE NO

檢視listener的狀態，可以看到pdb1和pdb2都分別各有一個服務。
$ lsnrctl status

LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 30-MAR-2015 06:48:50
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sca-t4-1-14-ld1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 12.1.0.2.0 - Production
Start Date                30-MAR-2015 06:40:39
Uptime                    0 days 0 hr. 8 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /export/home/dbusr/app/dbusr/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /export/home/dbusr/app/dbusr/diag/tnslsnr/sca-t4-1-14-ld1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sca-t4-1-14-ld1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=sca-t4-1-14-ld1)(PORT=5500))(Security=(my_wallet_directory=/export/home/dbusr/app/dbusr/admin/oste/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "oste" has 2 instance(s).
Instance "oste", status UNKNOWN, has 1 handler(s) for this service...
Instance "oste", status READY, has 1 handler(s) for this service...
Service "osteXDB" has 1 instance(s).
Instance "oste", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "oste", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
Instance "oste", status READY, has 1 handler(s) for this service...
The command completed successfully

在cdb中只能建立全域性使用者(c##開頭), 預設container=all,會在cdb和所有的pdb中建立該使用者, 在pdb中只能建立本地使用者。
使用者授權預設情況下是隻會給當前container, 在cdb中也可以指定container=all, 對所有open的pdb中的該使用者進行授權。但是pdb中的全域性使用者需要另外授權才能夠在pdb中訪問。
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> create user george identified by george;
create user george identified by george
            *
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> create user c##george identified by george;
User created.
SQL> grant connect,resource to c##george;
Grant succeeded.

SQL> SELECT USERNAME,CON_ID,USER_ID FROM CDB_USERS where username='C##GEORGE';
USERNAME                 CON_ID    USER_ID
-------------------- ---------- ----------
C##GEORGE                     4        104
C##GEORGE                     3        105
C##GEORGE                     1        103

以下是在pdb中建立使用者。
SQL> alter session set container=pdb1;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
PDB1

SQL> create user soe identified by soe;
User created.
SQL> grant connect, resource to soe;
Grant succeeded.

SQL> SELECT USERNAME,CON_ID,USER_ID FROM CDB_USERS where username='SOE';
USERNAME      CON_ID    USER_ID
-------------------- ----------      ----------
SOE                    3                 106

Oracle 12c中建立使用者

相關文章