- 关于用户安全的简易说明
- 创建用户
- 更改用户
- 配置用户资源限额
- 删除用户
- 用户和profile的的数据字典
一、关于用户安全的简易说明:
每个数据库都有自己的用户列表,在创建用户时你可以为这个用户设置一系列的登录limits或resource limits,或者使用profile实现此功能,profile就是一个用户所拥有的属性的集合,你可以查看dba_profiles来详细了解。
这部分涉及的内容很多,保险起见还是全部列出来的比较好。
- 关于Common User和Local User
可以看到CDB其实就是指实例下的所有数据库,包含ROOT、Seed和其他所有PDBs,整个Instance可以看做一个容器,即Container DB(CDB).
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:
当你将一个non-CDB的数据库作为PDB插入到CDB中时:
- non-CDB的系统用户将会与CDB common user合并,即non-CDB中的系统用户将会新增到CDB中,如果username冲突则不新增,这些冲突系统用户的密码全部按照CDB的来,原密码失效。
- 如果你曾经更改过原non-CDB系统用户的权限,那么合并之后这些差异权限只对这个PDB生效,对其他PDB无效。这里还不知道如果少了权限合并之后是不是依然少,有需要的时候再测试下好了。
- 原PDB的common user会失去它所有的公共权限,包括set container的权限。
- 如果目标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不能创建common user,也不能在cdb$root中被授予权限。而common user只要有合适的权限,就能创建、修改common/local user,就能公共的或本地的grant/revoke权限。Local user如果权限充足,可以创建、修改local user,也可以在PDB中向common/local user赋权限。
- 你可以向local user赋予公共角色或权限(类似于select any table这种),但是这些公共角色或权限包含的权限只对其所属PDB有效。
- Local user在一个PDB中应当是唯一的。
- 如果local user有合适的权限,那么它也可以访问common user里的对象。
- Local user只能在PDB中被创建,PDB中也只能创建local user而不能创建common user。
- 你可以根据版本来选择是否禁用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项来分配一定的表空间限额。
- 创建用户总结:
$ 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版本):
想要使用CONTAINERS子句必须满足以下条件:
如果CONTAINERS子句涉及的表里有以下列类型,那么会被自动过滤掉:
稍微思考和实践了一下,发现使用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;
五、删除用户
略
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