如何在oracle 12c中创建普通用户
-------如何在oracle 12c中创建普通用户-------
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 29 21:43:50 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
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
--通过con_name能看到当前登录的环境是CDB根库,CDB包含一个名为PDBORCL的可插拔库。
SQL> show con_name pdbs;
CON_NAME
------------------------------
CDB$ROOT
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
--创建公用用户admin，需要使用C##或者c##作为该用户名的开头。
SQL> create user C##admin identified by Lxf$txx282534;
User created.
--如果使用普通方法创建会报错
SQL> create user admin identified by Lxf$txx282534;
create user admin identified by Lxf$txx282534
*
ERROR at line 1:
ORA-65096: invalid common user or role name
--创建公用角色， 同公用用户一样也需要使用C##或者c##作为角色名的开头。
SQL> create role C##admin01 container=all;
Role created.
--将dba角色授予公用角色, 适用范围为所有PDB
SQL> grant dba to c##admin01 container=all;
Grant succeeded.
--将公用角色授予公用用户， 使用范围为所有PDB
SQL> grant C##admin01 to C##admin container=all;
Grant succeeded.
--下面使用公用用户分别登录CDB、PDB看看是否通用。
[oracle@oracle admin]$ sqlplus C##admin/'Lxf$txx282534'@pdborcl
SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 29 22:33:29 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sun Nov 29 2015 22:31:12 +08: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
------------------------------
PDBORCL
[oracle@oracle admin]$ sqlplus C##admin/'Lxf$txx282534'
SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 29 22:33:53 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sun Nov 29 2015 22:33:29 +08: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中创建用户和创建普通用户就没有什么区别了
SQL> create user test identified by test;
User created.
SQL> grant create session to test;
Grant succeeded.
[oracle@oracle admin]$ sqlplus test/test@pdborcl
SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 29 22:17:16 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
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 user
USER is "TEST"
SQL> show con_name
CON_NAME
------------------------------
PDBORCL
SQL> exit
--将PDB打开
SQL> alter pluggable database pdborcl open;
Pluggable database altered.
--切换到PDB容器
SQL> alter session set container=pdborcl;
Session altered.
--2015/11/30更新
今天发现重新开机后想直接登陆PDB容器的用户是做不到的，会出现以下报错
[oracle@oracle ~]$ sqlplus C##admin/'Lxf$txx282534'@pbdorcl
SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 30 20:59:02 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
但是能正常登陆CDB容器的数据库
[oracle@oracle ~]$ sqlplus C##admin/'Lxf$txx282534'
SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 30 21:39:15 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Nov 30 2015 21:31:02 +08: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>
查看发现PDBORCL容器没有打开
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 30 21:13:03 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
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 pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
打开PDB容器
SQL> alter pluggable database pdborcl open;
Pluggable database altered.
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@oracle ~]$ sqlplus C##admin/'Lxf$txx282534'@pdborcl
SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 30 21:15:11 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sun Nov 29 2015 22:47:17 +08: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
------------------------------
PDBORCL
