oracle12g建用户,Oracle 12c中创建用户

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