当前位置: 首页 > Oracle, Oracle 12c > 正文

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。

00003

 

 

 

 

 

 

 

 

 

 

这个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——————————————–

 

本文固定链接: http://www.dbdream.com.cn/2013/08/oracle-12c-common-user%e4%b8%8elocal-user/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2013年08月28日发表在 Oracle, Oracle 12c 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: oracle 12c common user与local user | 信春哥,系统稳,闭眼上线不回滚!
关键字: , , , , ,

oracle 12c common user与local user:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter