12c oracle 01109,oracle 12c common user与local user | 信春哥，系统稳，闭眼上线不回滚！...

12cR1数据库中创建测试用户是报ORA-65096错误。

SQL> create user dbdream identified by dbdream default tablespace users;

create user dbdream identified by dbdream default tablespace users

*

ERROR at line 1:

ORA-65096: invalid common user or role name

以前没见过这个错误，通用用户(common user)是什么玩意？之前的版本可是没这概念啊，看看官方文档怎么说。

在官方文档中看到了下面的图片，原来和common user对应的还有local user。

这个common user 和local user是和oracle 12c的新特性pluggable database(PDB)有关。在PDB中创建的用户就是local user。从上图可以看出，common user必须以大写或小写的c##开头，尝试建立以c##开头的common user。

SQL> create user c##dbdream identified by dbdream default tablespace users;

User created.

SQL> grant dba to c##dbdream;

Grant succeeded.

再到PDB中创建个local user试试。连接PDB有两种方式。

Database connection using easy connect

Database connection using a net service name

使用easy connect的方式连接PDB。

[oracle@dbdream ~]$ sqlplus sys/oracle@localhost:1521/stream as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 15 01:39:21 2013

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

Connected to:

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

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

已经成功登录了，但是怎样才能确定登录的到底是PDB还是CDB呢？可以用下面的方法。

SQL> show con_name

CON_NAME

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

STREAM

可以看到已经登录到PDB了(本案例PDB的名字是STREAM)，如果登录的是CDB，通常结果是下面这样。

SQL> show con_name

CON_NAME

--------

CDB$ROOT

继续实验，创建local user。

SQL> create user dbdream identified by dbdream default tablespace users;

create user dbdream identified by dbdream default tablespace users

*

ERROR at line 1:

ORA-01109: database not open

PDB也需要启动到OPEN状态，可以用两种方式STARTUP或者ALTER PLUGGABLE DATABASE OPEN;。

SQL> startup

Pluggable Database opened.

或者

SQL> ALTER PLUGGABLE DATABASE OPEN;

Pluggable database altered.

创建local user。

SQL> create user dbdream identified by dbdream default tablespace users;

User created.

SQL> grant dba to dbdream;

Grant succeeded.

从官方文档看，local user只能存在一个PDB中，而common user可以存在每个CDB中。也间接说明common user可以登录任何一个PDB，而local user只能登录自己的PDB，下面测试下。

使用common user(C##DBDREAM)登录PDB(STREAM和WIND)。

先测试使用common user登录wind库。

[oracle@dbdream ~]$ sqlplus c##dbdream/dbdream@localhost/wind

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 15 02:56:45 2013

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

ERROR:

ORA-01045: user C##DBDREAM lacks CREATE SESSION privilege; logon denied

提示common user(C##DBDREAM)在PDB(WIND)中没有create session权限，用sys登录PDB(WIND)，授予C##DBDREAM用户相应权限就可以登录PDB(WIND)了。

[oracle@dbdream ~]$ sqlplus sys/oracle@localhost/wind as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 15 02:58:28 2013

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

Connected to:

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

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

SQL> grant dba to c##dbdream;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

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

[oracle@dbdream ~]$ sqlplus c##dbdream/dbdream@localhost/wind

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 15 02:58:44 2013

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

Connected to:

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

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

SQL> show con_name

CON_NAME

--------

WIND

在测试使用common user(C##DBDREAM)登录PDB(STREAM)。

[oracle@dbdream ~]$ sqlplus c##dbdream/dbdream@localhost/stream

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 15 06:34:15 2013

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

ERROR:

ORA-01045: user C##DBDREAM lacks CREATE SESSION privilege; logon denied

Enter user-name:

同样，PDB(STREAM)中也需要为common user(C##DBDREAM)赋予create session权限。

[oracle@dbdream ~]$ sqlplus sys/oracle@localhost/stream as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 15 06:35:57 2013

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

Connected to:

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

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

SQL> grant dba to c##dbdream;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

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

[oracle@dbdream ~]$ sqlplus c##dbdream/dbdream@localhost/stream

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 15 06:36:15 2013

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

Connected to:

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

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

SQL> show con_name

CON_NAME

--------

STREAM

可见，只要有足够的权限，common user可以穿越于任何PDB，那么PDB(STREAM)中的DBDREAM用户是否可以登录PDB(WIND)呢？

[oracle@dbdream ~]$ sqlplus dbdream/dbdream@localhost/wind

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 15 06:38:35 2013

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

ERROR:

ORA-01017: invalid username/password; logon denied

登录不了，因为PDB(WIND)中并没有dbdream用户，dbdream登录自己的PDB(STREAM)是没问题的。

[oracle@dbdream ~]$ sqlplus dbdream/dbdream@localhost/stream

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 15 06:39:33 2013

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

Connected to:

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

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

SQL>

每个PDB都是独立的单元，有自己的用户(local user)、表空间、数据文件，每个local user只能访问自己的PDB，而common user只要权限足够，可以访问任意PDB。

—————————————-end——————————————–