ITPub博客

首页 > 数据库 > Oracle > 12c multitenant database 的用户管理

12c multitenant database 的用户管理

原创 Oracle 作者:lfree 时间:2014-02-18 08:19:49 0 删除 编辑

[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.
通过例子来讲解这些内容:

参考连接:http://www.oracle-base.com/articles/12c/multitenant-manage-users-and-privileges-for-cdb-and-pdb-12cr1.php

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1082876/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2622
  • 访问量
    6388072