对于12c之前的数据库创建用户方式,大家都会写创建语句。但是12c开始因为有了CDB和PDB的说法,实现扩展数据库,则创建用户方式则有所不同。
当12c数据库创建完成后,使用sqlplus / as sysdba 方式登录数据库连接的是CDB,如果要创建用户则需要使用固定模式:
示例如下:
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string ora12c
db_unique_name string ora12c
global_names boolean FALSE
instance_name string ora12c
lock_name_space string
log_file_name_convert string
pdb_file_name_convert string
processor_group_name string
service_names string ora12c
SQL> CREATE USER c##comm_ora12c IDENTIFIED BY comm_oracle DEFAULT TABLESPACE users;
User created.
SQL> grant connect,resource to c##comm_ora12c; 用户成功创建并授权成功!
SQL>
Grant succeeded.
SQL>
上述命令是创建了通用的CDB用户,注意必须使用c##开头。
查询授权:
SQL> col GRANTED_ROLE for a30
SQL> run
1* select * from cdb_ROLE_PRIVS where GRANTED_ROLE='RESOURCE' AND GRANTEE='C##COMM_ORA12C'
GRANTEE GRANTED_ROLE ADM DEF COM CON_ID
-------------------- ------------------------------ --- --- --- ----------
C##COMM_ORA12C RESOURCE NO YES NO 1
SQL> select * from cdb_ROLE_PRIVS where GRANTED_ROLE='CONNECT' AND GRANTEE='C##COMM_ORA12C';
GRANTEE GRANTED_ROLE ADM DEF COM CON_ID
-------------------- ------------------------------ --- --- --- ----------
C##COMM_ORA12C CONNECT NO YES NO 1
SQL>
SQL> conn c##comm_ora12c
Enter password:
Connected.
SQL> show user
USER is "C##COMM_ORA12C"
SQL> create table test12c (id number(10,0) primary key,name varchar2(30));
Table created.
SQL>表成功创建
SQL> insert into TEST12C values(1,'测试12c');
insert into TEST12C values(1,'测试12c')
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
SQL> 此时无法插入数据!
SQL> alter user C##COMM_ORA12C QUOTA unlimited ON users TEMPORARY TABLESPACE temp;
User altered.
SQL> insert into TEST12C values(1,'测试12c');
1 row created.
SQL> commit; 数据插入成功!
通过上述实验可以看出12c 版本与之前数据库版本的不同之处。注意CDB与PDB之间关系!
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- -------------------- ----------
2 4062021294 PDB$SEED READ ONLY
3 2255993317 PDB12C1 MOUNTED
4 1951738610 PDB12C2 MOUNTED
SQL>此时PDB还处于mount状态。
如果要启动PDB;
SQL> alter PLUGGABLE database PDB12C1 open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4062021294 PDB$SEED READ ONLY
3 2255993317 PDB12C1 READ WRITE
4 1951738610 PDB12C2 MOUNTED
SQL> PDB12C1 成功启动,PDB12C2同理。
上述问题可以参考最新官方文档,找到解决方案!
官方文档叙述:
All of the following examples use the example
tablespace, which exists in the seed database and is accessible to the sample schemas.
Creating a Database User: Example If you create a new user with PASSWORD
EXPIRE
, then the user's password must be changed before the user attempts to log in to the database. You can create the user sidney
by issuing the following statement:
CREATE USER sidney IDENTIFIED BY out_standing1 DEFAULT TABLESPACE example QUOTA 10M ON example TEMPORARY TABLESPACE temp QUOTA 5M ON system PROFILE app_user PASSWORD EXPIRE;
The user sidney
has the following characteristics:
-
The password
out_standing1
-
Default tablespace
example
, with a quota of 10 megabytes -
Temporary tablespace
temp
-
Access to the tablespace
SYSTEM
, with a quota of 5 megabytes -
Limits on database resources defined by the profile
app_user
(which was created in "Creating a Profile: Example") -
An expired password, which must be changed before
sidney
can log in to the database
Creating External Database Users: Examples The following example creates an external user, who must be identified by an external source before accessing the database:
CREATE USER app_user1 IDENTIFIED EXTERNALLY DEFAULT TABLESPACE example QUOTA 5M ON example PROFILE app_user;
The user app_user1
has the following additional characteristics:
-
Default tablespace
example
-
Default temporary tablespace
example
-
5M of space on the tablespace
example
and unlimited quota on the temporary tablespace of the database -
Limits on database resources defined by the
app_user
profile
To create another user accessible only by an operating system account, prefix the user name with the value of the initialization parameter OS_AUTHENT_PREFIX
. For example, if this value is "ops$
", then you can create the externally identified user external_user
with the following statement:
CREATE USER ops$external_user IDENTIFIED EXTERNALLY DEFAULT TABLESPACE example QUOTA 5M ON example PROFILE app_user;
Creating a Global Database User: Example The following example creates a global user. When you create a global user, you can specify the X.509 name that identifies this user at the enterprise directory server:
CREATE USER global_user IDENTIFIED GLOBALLY AS 'CN=analyst, OU=division1, O=oracle, C=US' DEFAULT TABLESPACE example QUOTA 5M ON example;
Creating a Common User in a CDB The following example creates a common user called c##comm_user
in a CDB. Before you run this CREATE USER
statement, ensure that the tablespaces example
and temp_tbs
exist in all of the containers in the CDB.
CREATE USER c##comm_user IDENTIFIED BY comm_pwd DEFAULT TABLESPACE example QUOTA 20M ON example TEMPORARY TABLESPACE temp_tbs;
The user comm_user
has the following additional characteristics:
-
The password
comm_pwd
-
Default tablespace
example
, with a quota of 20 megabytes -
Temporary tablespace
temp_tbs