Oracle 12c用户和安全管理

前言：

---

oracle 12c的多租户（multitenant）环境与sql server的架构非常相似，cdb$root类似于master、pdb$seed类似于model、各个pluggable database就相当于普通的业务库。

官方宣传了12c的很多好处但其实没卵用，12c设想通过合并多个库来避免dblink的性能问题，但其实还是通过压榨单一服务器来实现的，其他好处诸如pdb迁移方便、json支持、资源组、索引压缩、in-memory支持等等要么是炒冷饭，要么实际适用场景很少，不过这些新特性都还有待继续研究，一步一步来。

首先必须要解决的是登录问题，12c的多租户环境下用户与以前不同了，以前的用户性质都是一样的，而在12c中一个实例下可以有多个可插拔的数据库，因此用户管理与之前就有很大区别了，本文介绍12c用户及其管理，以12.2.0.1版本为例，所有观点来源于如下官网链接：

managing security for oracle database users

本文主要包含以下六个部分：

• 关于用户安全的简易说明
  
• 创建用户
• 更改用户
• 配置用户资源限额
• 删除用户
• 用户和profile的的数据字典

一、关于用户安全的简易说明：

---

每个数据库都有自己的用户列表，在创建用户时你可以为这个用户设置一系列的登录limits或resource limits，或者使用profile实现此功能，profile就是一个用户所拥有的属性的集合，你可以查看dba_profiles来详细了解。

此外你还可以为用户赋予一系列的权限和角色，参考：configuring privilege and role authorization

 

二、创建用户

---

这部分涉及的内容很多，保险起见还是全部列出来的比较好。

• 关于common user和local user

在多租户的环境中，common user可以访问整个cdb而local user只能访问特定的pdb，关于cdb与pdb的关系这里用官网的一幅图来表示：

此图来自于：overview of managing a multitenant environment

可以看到cdb其实就是指实例下的所有数据库，包含root、seed和其他所有pdbs，整个instance可以看做一个容器，即container db(cdb).

你可以使用sysdba用户来启动pdb，有以下两种方式：

1.alter session set container=orclpdb1;
  alter database open;
  --对应关闭方式为shutdown immediate;
2.alter pluggable database orclpdb1 open;
  --对应关闭方式为alter pluggable database orclpdb1 close;
ps:pdb的关闭是指将pdb由open变为mount状态，彻底shutdown需要到root中关闭整个实例。

关于common user：

common user必须以c##或者c##开头，你可以通过修改common_user_prefix参数来更改这个prefix，而local user则必须避免以这个prefix开头。

common user的身份和密码对于每个pdb来说都是可见的，只要有合适的权限common user也可以操作pdb。

common user通常用于在root中操作pdbs，例如插拔pdb数据库、更改pdb状态或者为cdb指定临时表空间等。

common user只能在cdb$root中创建，cdb$root中也只能创建common user。

举例来讲，common user可做的操作有：创建或修改common/local user、向其他common/local user赋权限或者赋角色、对整个cdb执行alter database开头的恢复语句、通过alter pluggable database语句来来更改pdb的状态（必须是连接到cdb$root库时）。当然一个local user如果有权限也可以更改其自身pdb的状态。

oracle的系统用户如sys、system等，就是common user，即可操作root数据库也可以操作任意pdb，除非某个pdb开启vault-enabled来限制系统用户的权限。

当你将一个non-cdb的数据库作为pdb插入到cdb中时：

1. non-cdb的系统用户将会与cdb common user合并，即non-cdb中的系统用户将会新增到cdb中，如果username冲突则不新增，这些冲突系统用户的密码全部按照cdb的来，原密码失效。
2. 如果你曾经更改过原non-cdb系统用户的权限，那么合并之后这些差异权限只对这个pdb生效，对其他pdb无效。这里还不知道如果少了权限合并之后是不是依然少，有需要的时候再测试下好了。

当你将一个pdb插入cdb时：

1. 原pdb的common user会失去它所有的公共权限，包括set container的权限。
2. 如果目标cdb有与pdb同名的common user，那么这两个common user合并，cdb的common user密码优先生效。而pdb中其他不同名的common user将会被锁定。你可以对这些锁定的common user做如下任意一种处理：
   • 不处理这些locked用户，在权限适当的情况下你依然可以使用这些schema下的对象。
   • 使用expdp导出这些用户下的数据并导入到另一个用户下，然后删掉此locked user。
   • 关掉pdb，连接到cdb$root,创建同名的common user，然后重开pdb，oracle会自动处理权限差异，此时你可以unlock这些用户，他们的本地权限保持不变。

关于local user：

local user就是只存在于pdb中的用户，local user可以拥有管理类权限，但是这类权限只对local user所在的pdb有效。

local user与common user的区别可以概括为以下几点：

1. local user不能创建common user，也不能在cdb$root中被授予权限。而common user只要有合适的权限，就能创建、修改common/local user，就能公共的或本地的grant/revoke权限。local user如果权限充足，可以创建、修改local user，也可以在pdb中向common/local user赋权限。
2. 你可以向local user赋予公共角色或权限（类似于select any table这种），但是这些公共角色或权限包含的权限只对其所属pdb有效。
3. local user在一个pdb中应当是唯一的。
4. 如果local user有合适的权限，那么它也可以访问common user里的对象。
5. local user只能在pdb中被创建，pdb中也只能创建local user而不能创建common user。
6. 你可以根据版本来选择是否禁用local user，但是却不能这么操作common user。

• 创建用户示例

创建common user：

$ sqlplus / as sysdba
sql> show pdbs;
    con_id con_name			  open mode  restricted
---------- ------------------------------ ---------- ----------
	 2 pdb$seed			  read only  no
	 3 orclpdb1			  read write no
	 4 orclpdb2			  mounted
sql> show con_name
con_name
------------------------------
cdb$root
sql> alter pluggable database orclpdb1 open;
pluggable database altered.
sql> create user c##test identified by test container=all;  
--common user只能以c##开头，且强制container=all，创建时可以不用写container=all。
sql> grant dba to c##test;
--至此common user c##test创建完毕，但是目前为止这个common user只能操作cdb$root schema对象下的objects和创建pdb的local user，并没有操作pdb下objects的权限，一般来说common user不必有此权限，但是如果你想拥有，那么：
sql> alter session set container=orclpdb1;  --切换至orclpdb1的pdb后再次grant权限
sql> grant dba to c##test;
--至此common user c##test就拥有了操作pdb orclpdb1下objects的权限了。但是这种common user删除时必须添加cascade：
sql> drop user c##test;
drop user c##test
*
error at line 1:
ora-65048: error encountered when processing the current ddl statement in pluggable database orclpdb1
ora-01922: cascade must be specified to drop 'c##test'
sql> drop user c##test cascade;
user dropped.

创建local user：

$ sqlplus / as sysdba
sql> alter session set container=orclpdb1;
sql> create user test identified by test default tablespace users quota 100m on example quota 200m on test temporary tablespace temp profile default container=current;
--local user只能以非c##开头，且强制container=current，创建时可以不用写container=current。
sql> grant connect,resource,unlimited tablespace to test;
$ sqlplus test/test@localhost/orclpdb1
--至此local user创建完毕。
--需要特别注意的一点是：12c的resource角色中已经不包含unlimited tablespace权限因此需要单独赋予，或者通过quota项来分配一定的表空间限额。

• 创建用户总结：

一般来说没有必要创建除系统用以外的common user，或者只需要创建一个不含操作具体pdb权限的common user，common user不应当用于操作具体业务数据，正如官方说明的那样，common user通常的操作应当是：

　　创建或修改common/local user、向其他common/local user赋权限或者赋角色、对整个cdb执行alter database开头的恢复语句、通过alter pluggable database语句来来更改pdb的状态（必须是连接到cdb$root库时）。

我们可以使用如下sql来查看pdb或cdb$root库内的用户：

$ sqlplus / as sysdba
sql> alter session set container=orclpdb1;
sql> set lines 200
sql> set pages 100
sql> col username for a40
sql> select username,account_status,common from dba_users;
--结果可以看到common user是在所有pdb中可见的，在单个pdb中查询dba_users你能看到所有common user和属于本pdb的local user。

再来考虑一种特殊情况，假如我们需要在一个pdb中访问另一个pdb中的数据呢？
使用local user是不可能的，因为local user完全pdb隔离，就算local user被赋予了类似dba、select any table这些公共权限，也只是在pdb内有效，无法跨库访问，那么只能使用common user。

接下来举例common user跨pdbs访问：

sql> show pdbs;
    con_id con_name			  open mode  restricted
---------- ------------------------------ ---------- ----------
	 2 pdb$seed			  read only  no
	 3 orclpdb1			  read write no
	 4 orclpdb2			  read write no
--我们有两个pdb：orclpdb1和orclpdb2都是open状态，各自有一个scott用户，用户下分别有一个test1、test2表。我们先创建一个common user：
$ sqlplus / as sysdba
sql> create user c##scott identified by tiger;
sql> grant connect,resource,unlimited tablespace,select any table,create view to c##scott;
--然后在orclpdb1中给c##scott赋权限
sql> alter session set container=orclpdb1;
sql> grant connect,resource,unlimited tablespace,select any table,create view to c##scott;
--接下来在orclpdb2中给c##scott赋权限
sql> alter session set container=orclpdb2;
sql> grant connect,resource,unlimited tablespace,select any table,create view to c##scott;

至此c##scott用户就有了在两个pdb中查询的权限，但是此权限是严格pdb隔离的，不能跨库查询。虽然不能像sql server那样跨库直接查询，不过oracle却提供了变通的办法--containers子句，参考网址如下（此特性开始于12.1.0.2版本）：

https://docs.oracle.com/database/121/newft/chapter12102.htm#newft507

https://docs.oracle.com/database/121/admin/cdb_mon.htm#admin14319

举例：select ename from containers(scott.emp) where con_id in (45, 49);

想要使用containers子句必须满足以下条件：

1.containers子句中的表、视图或同义词必须在root和所有pdb中都存在。

2.containers子句中涉及的表、视图的属主必须是执行sql语句的common user；如果是同义词，那么这个同义词必须是引用的此common user下的表或视图。

3.包含containers子句的sql必须在cdb$root数据库下使用common user执行。

如果containers子句涉及的表里有以下列类型，那么会被自动过滤掉：

1.用户定义的类型：object types, varrays, refs, and nested tables

2.系统类型：anytype, anydataset, uri types, sdo_topo_geometry, sdo_georaster, and expression

看完以上5条要求后突然发现这些要求很奇葩，如果containers子句中的表、视图或同义词必须在root和所有pdb中都存在，那我还为什么还要跨pdb查询，直接在root中查不就得了？

稍微思考和实践了一下，发现使用containers子句的条件应该是：

• 必须使用common user在cdb$root中查询
• containers()方法的输入是表、视图或同义词，这个对象必须在cdb$root中有个名称+结构完全相同的对象，无论是表还是视图还是同义词，表可以没有数据。
• 必须使用执行此查询的common user到表所在的pdb中创建一个同名view，这需要common user拥有查询此表的权限。

那么接着上边的实例就可以很容易的进行跨pdb查询了：

1.首先我们使用common user c##scott在orclpdb1中创建针对scott.test1的视图：
$ sqlplus c##scott/tiger@orclpdb1
sql> create view test1 as select * from scott.test1;
2.然后使用common user c##scott在cdb$root中创建同名的表或者视图：
$ sqlplus c##scott/tiger
sql> create table test1(name varchar2(20));
3.此时我们就可以使用sql查询了：
sql> select * from containers(test1) where con_id=3;
4.如果还需要联结查询orclpdb2中的test2表，那么：
$ sqlplus c##scott/tiger@orclpdb2
sql> create view test2 as select * from scott.test2;
$ sqlplus c##scott/tiger
sql> create table test2(name varchar2(20));
5.这样就相当于test1和test2在cdb$root中都有了一个接口，我们通过接口访问相应pdb中的common user下的视图，视图又是直接定义在最终表上的。

 

画一幅图来直观的表示：

 

三、修改用户

---

修改用户与创建用户的语法很相似：

--一个官方示例：
alter user c##hr_admin
 default tablespace data_ts
 temporary tablespace temp_ts
 quota 100m on data_ts
 quota 0 on test_ts
 set container_data = (emp_db, hr_db) for v$session 
 container = current;
 --有以下几个地方需要解释：
 1.quota的大小表示在表空间上限额的总大小，而非增/减量。
 2.set container_data表示此common user在root中访问v$session时可以获取到emp_db和hr_db相关的session信息。

此外一般用户的密码可以通过alter user语句修改，而sys密码可以通过orapwd工具修改：

orapwd file='orapworcl' force=y
orapwd input_file='orapworcl' file='orapwd' sys=y force=y

 

四、配置用户资源限额

---

这点可以通过一个视图很容易的看到：

因此修改用户资源限额就很简单了，例如：

alter user scott limit password_life_time unlimited;
--或者新建一个profile，然后将其指定为用户的profile：
create profile profile_scott limit
  failed_login_attempts 6
  password_life_time 60
  password_reuse_time 60
  password_reuse_max 5
  password_lock_time 1/24
  password_grace_time 10
  password_verify_function default;
alter user scott profile profile_scott; 

五、删除用户

---

略

六、用户和profile涉及的数据字典

---

all_objects
describes all objects accessible to the current user
all_users
lists users visible to the current user, but does not describe them
dba_profiles
displays all profiles and their limits
dba_ts_quotas
describes tablespace quotas for users
dba_objects
describes all objects in the database
dba_users
describes all users of the database
dba_users_with_defpwd
lists all user accounts that have default passwords
proxy_users
describes users who can assume the identity of other users
resource_cost
lists the cost for each resource in terms of cpus for each session, reads for each session, connection times, and sga
user_password_limits
describes the password profile parameters that are assigned to the user
user_resource_limits
displays the resource limits for the current user
user_ts_quotas
describes tablespace quotas for users
user_objects
describes all objects owned by the current user
user_users
describes only the current user
v$session
lists session information for the current database session
v$sesstat
displays user session statistics
v$statname
displays decoded statistic names for the statistics shown in the v$sesstat view