[20140218]12c multitenant database 的用户管理.txt
12c 引入了multitenant的概念,允许单个数据库(CDB)下包含多个pluggable databases (PDB).也就引入了新的用户管理模式.
Common Users,Local Users,Common Roles,Local Roles 以及Granting Roles and Privileges to Common and Local Users.
通过例子来讲解这些内容:
1.Common User和Local User :
@ver
在连接 multitenant database管理用户和权限时,与传统的oracle数据库不同,oracle 12c引入了两类用户类型:
Common User : The user is present in all containers (root and all PDBs).
Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are
unrelated.
同样也存在两类角色:
Common Role : The role is present in all containers (root and all PDBs).
Local Role : The role is only present in a specific PDB. The same role name can be used in multiple PDBs, but they are
unrelated.
Create Common Users
When creating a common user the following requirements must all be met.
You must be connected to a common user with the CREATE USER privilege.
The current container must be the root container.
The username for the common user must be prefixed with "C##" or "c##" and contain only ASCII or EBCDIC characters.
The username must be unique across all containers.
The DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA and PROFILE must all reference objects that exist in all containers.
You can either specify the CONTAINER=ALL clause, or omit it, as this is the default setting when the current
container is the root.
例子:
CREATE USER c##test IDENTIFIED BY xxxxxx CONTAINER=ALL;
GRANT CREATE SESSION TO c##test CONTAINER=ALL;
Create Local Users
When creating a local user the following requirements must all be met.
You must be connected to a user with the CREATE USER privilege.
The username for the local user must not be prefixed with "C##" or "c##".
The username must be unique within the PDB.
You can either specify the CONTAINER=CURRENT clause, or omit it, as this is the default setting when the current
container is a PDB.
例子:
ALTER SESSION SET CONTAINER = test01p;
CREATE USER test IDENTIFIED BY xxxx CONTAINER=CURRENT;
GRANT CREATE SESSION TO test CONTAINER=CURRENT;
-- Connect to a privileged user in the PDB.
CONN system/password@test01p
-- Create the local user using the default CONTAINER setting.
CREATE USER test IDENTIFIED BY password1;
GRANT CREATE SESSION TO test;
--实际上如果可以进入pdb数据库按照原来的方式管理用户以及权限.
--至于Common Role,Local Role,非常像Common User,Local User 的建立管理方式.
2.Common Users的前缀是否可以不使用C##:
SYS@test> column DESCRIPTION format a40
SYS@test> set linesize 280
SYS@test> @hide common_user_prefix
old 10: and lower(a.ksppinm) like lower('%&1%')
new 10: and lower(a.ksppinm) like lower('%common_user_prefix%')
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
-------------------- ------------------------------------------------------------------ ---------------------- ---------------------- --------------
_common_user_prefix Enforce restriction on a prefix of a Common User/Role/Profile name TRUE C## C##
--很明显修改这个参数就可以使用替换不使用C##.
测试:
SYS@test> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SYS@test> alter system set "_common_user_prefix"=CU scope=spfile;
System altered.
--重启:
SYS@test> CREATE USER c##test1 IDENTIFIED BY xxxxxx CONTAINER=ALL;
CREATE USER c##test1 IDENTIFIED BY xxxxxx CONTAINER=ALL
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SYS@test> CREATE USER cutest IDENTIFIED BY xxxxxx CONTAINER=ALL;
User created.
SYS@test> select username,password,CREATED from dba_users where username like '%TEST%';
USERNAME PASSWORD CREATED
-------------------- -------------------- -------------------
C##TEST 2014-02-14 22:46:51
CUTEST 2014-02-17 20:49:22
SYS@test> alter session set container=test01p;
Session altered.
SYS@test> select username,password,CREATED from dba_users where username like '%TEST%';
select username,password,CREATED from dba_users where username like '%TEST%'
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
SYS@test> alter session set container=cdb$root ;
Session altered.
SYS@test> alter pluggable database all open;
Pluggable database altered.
SYS@test> select username,password,CREATED from dba_users where username like '%TEST%';
USERNAME PASSWORD CREATED
-------------------- -------------------- -------------------
TEST1 2014-01-03 21:19:48
C##TEST 2014-02-14 22:46:51
TEST 2014-01-03 21:00:55
CUTEST 2014-02-17 20:52:51
