12c multitenant database 的使用者管理

[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