Oracle数据库之用户角色管理（一）

关系型数据库通常指的是数据之间的关系，例如主键约束，外键约束等等这些，这些关系是数据层面的，通常，在关系型数据库系统，还有多用户，多角色的用户系统，不同的用户有不同的权限，将某些用户权限封装之后形成角色，进而通过赋予用户不同的角色使得用户继承角色从而拥有不同的权限，我想，这些应该就是用户，权限，角色之间的紧密关系。

那么，在Oracle数据库中，通常在安装的时候会给你提供一些默认的用户，比如sys，system这样的具有高权限的用户，它们的权限到底是哪些呢？权限，角色，用户三者之间的关系到底是什么？如何做好用户系统管理？

本文基于Oracle11g版本实验并介绍，请注意版本是11g~~~~~~~~

Oracle 权限：

权限允许用户访问属于其它用户的对象或执行程序，ORACLE系统提供三种权限：Object 对象级、System 系统级、Role 角色级。这些权限可以授予给用户、特殊用户public或角色，如果授予一个权限给特殊用户"Public"（用户public是oracle预定义的，每个用户享有这个用户享有的权限）,那么就意味作将该权限授予了该数据库的所有用户。
对管理权限而言，角色是一个工具，权限能够被授予给一个角色，角色也能被授予给另一个角色或用户。用户可以通过角色继承权限，除了管理权限外角色服务没有其它目的。权限可以被授予，也可以用同样的方式撤销。
权限分类
1、系统权限：系统规定用户使用数据库的权限。（系统权限是对用户而言)。
2、实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。

既然是学习并记录用户系统管理，那么，将使用sys，system，和任意一个普通用户展开来讲。（本文内使用一个新建普通用户，用户名为test）。使用工具为SQLdeveloper。

一，到底有哪些权限？？

使用sqldevelope建立三个连接，依次使用sys用户，system用户，test用户连接同一个数据库实例。注意，新建用户test是没有权限连接数据库的，尽管已经赋予密码了，需要后续赋予连接权限，sys用户连接时需要指定角色为sysdba或者sysoper，test用户后续处理连接问题。

Oracle数据库之用户角色管理（一）_python

Oracle数据库之用户角色管理（一）_oracle_02

Oracle数据库之用户角色管理（一）_java_03

==================================================================================================================================

在system用户连接下执行命令select PRIVILEGE from session_privs; （这个命令一般简写为 select * from session_prives;）输出结果为：

Oracle数据库之用户角色管理（一）_python_04

在sys用户连接下执行命令select PRIVILEGE from session_privs; （这个命令一般简写为 select * from session_prives;）输出结果为：

Oracle数据库之用户角色管理（一）_mysql_05

可以看到sys用户权限更多，为208个权限，拥有角色53个，system用户权限为202个，拥有角色为2个。将两个查询结果放入Linux使用diff命令对比后可以发现，system比sys用户少六个权限，这六个权限为：

（sys拥有的角色有哪些？select * from dba_role_privs where grantee='SYS';system用户拥有的角色：select * from dba_role_privs where grantee='SYSTEM';）

40a41,42
> Insert into EXPORT_TABLE (PRIVILEGE) values ('SYSDBA');
> Insert into EXPORT_TABLE (PRIVILEGE) values ('SYSOPER');
123a126
> Insert into EXPORT_TABLE (PRIVILEGE) values ('EXEMPT ACCESS POLICY');
159a163
> Insert into EXPORT_TABLE (PRIVILEGE) values ('EXEMPT IDENTITY POLICY');
203a208,209
> Insert into EXPORT_TABLE (PRIVILEGE) values ('ALTER PUBLIC DATABASE LINK');
> Insert into EXPORT_TABLE (PRIVILEGE) values ('ALTER DATABASE LINK');
===============================================================================
#取values值，整理后如下：
SYSDBA'
'SYSOPER'
'EXEMPT ACCESS POLICY'
'EXEMPT IDENTITY POLICY'
'ALTER PUBLIC DATABASE LINK'
'ALTER DATABASE LINK'

由此得出结论：在Oracle中，所有的权限是208个（sys和system是权限最大的两个用户）。

查询到底有哪些权限也可以使用如下SQL语句：select * from SYSTEM_PRIVILEGE_MAP;（任意用户都有权查询，即使是新用户，Oracle版本11g）

========================================================================================================================================================

Oracle用户：

查看整个Oracle系统内有哪些用户，以及用户所处的状态：

select * from dba_users;--这条命令查询系统内的所有用户，包括状态，所使用的表空间等等详细内容。需要使用高权限用户才可以查询、sys用户执行该命令输出如下：

Oracle数据库之用户角色管理（一）_数据库_06

多少条记录就是多少个用户，其中第四列是用户状态列，open表示该用户可正常使用，expired &locked 表示用户账号被锁定并且密码是过期的。 第六列表示账号失效时间，比如，sys用户密码失效时间为21年7月2号。第七列表示用户所对应的默认表空间，比如sys用户的表空间为system。

select username,account_status from dba_users; --这条命令是上面命令的扩展，仅查询用户的状态。需要使用高权限用户才可以查询。

用户的状态共有11种，分别为：

前五种是基本状态：0 OPEN、1 EXPIRED、2 EXPIRED(GRACE)、3 LOCKED(TIMED)、4 LOCKED（启用，过期，已过期（但可登录），账号被锁定）

后四种是组合状态5 EXPIRED & LOCKED(TIMED)、6 EXPIRED(GRACE) & LOCKED(TIMED)、9 EXPIRED & LOCKED、10 EXPIRED(GRACE) & LOCKED（账号过期并被锁定等等状态），具体的各种状态和组合可通过视图查询：

select * from user_astatus_map;--输出如下：

Oracle数据库之用户角色管理（一）_oracle_07

新建用户默认是open状态，open状态是用户的正常状态。

在前面我们通过SQL语句select * from dba_users;可以查询到所有用户都有密码期限，换句话说也就是所有用户都使用的一个密码策略，180天后过期，执行SQL语句：

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME' ; --输出如下：

Oracle数据库之用户角色管理（一）_java_08

表示默认策略为180天后密码过期，那么现在我们有两条路可选择，要么把default默认策略改为永不过期，要么自定义一个策略，指定某个用户使用自定义的策略，其余的用户仍然使用默认的策略。

（1），使用自定义策略，指定test用户使用自定义的策略，并且sys用户也更改为使用新的策略。假设自定义策略名称为test。总共分三步，一步一个SQL语句。

执行如下SQL语句：

CREATE PROFILE "TEST" LIMIT
 SESSIONS_PER_USER UNLIMITED
 CPU_PER_SESSION UNLIMITED
 CPU_PER_CALL UNLIMITED
 CONNECT_TIME UNLIMITED
 IDLE_TIME UNLIMITED
 LOGICAL_READS_PER_SESSION UNLIMITED
 LOGICAL_READS_PER_CALL UNLIMITED
 COMPOSITE_LIMIT UNLIMITED
 PRIVATE_SGA UNLIMITED
 FAILED_LOGIN_ATTEMPTS 10
 PASSWORD_LIFE_TIME 180
 PASSWORD_REUSE_TIME UNLIMITED
 PASSWORD_REUSE_MAX UNLIMITED
 PASSWORD_LOCK_TIME 1
 PASSWORD_GRACE_TIME 7
 PASSWORD_VERIFY_FUNCTION NULL;
 
 ALTER profile TEST limit PASSWORD_LIFE_TIME UNLIMITED;
 alter user test profile TEST;
alter user sys profile TEST;

执行SQL语句查询所有的用户策略 SELECT * FROM dba_profiles;--可以看到如下输出：

Oracle数据库之用户角色管理（一）_java_09

再次执行SQL语句 select * from dba_users;查询，可以看到如下输出：

Oracle数据库之用户角色管理（一）_数据库_10

经过以上的处理，sys用户和test用户都使用了自定义的密码策略，并且密码永不过期了，需要注意的是，以上操作均需高权限用户sys或者system执行。

（二），更改默认策略，使得所有用户都密码永不过期。

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;--如果有已经过期的账号，重新修改一次密码即可，可使用原密码。

用户赋权和去权操作：

前面示例所示范的test用户现在还是一个空账号，其账号没有任何权限，现在开始给该账号赋权，顺便解决无法连接的问题。

使用sys用户执行如下SQL语句：

grant CONNECT to test;--切换到test用户，test用户已经可以连接了。

查询所有的用户有哪些？select * from dba_users;

查询所有的角色有哪些？select * from dba_roles;--输入如下：

Oracle数据库之用户角色管理（一）_java_11

以dba这个角色为例，查询该角色所具有的权限是哪些？

select * from role_sys_privs where role='DBA';--dba角色共有202个权限，输出如下：

Oracle数据库之用户角色管理（一）_oracle_12

查询某个用户所具有的所有角色，比如查询system用户所具有的角色：

select * from dba_role_privs where grantee='SYSTEM';

Oracle数据库之用户角色管理（一）_python_13

说了这么多，其实要说的是，角色也可以自定义，那么角色如何使用的？先建立角色，然后给角色赋予权限，最后赋予用户这个自定义角色，那么，这个自定义的角色有什么权限，被添加角色的用户就可以继承拥有这个自定义角色的所有权限了。

下面演示新建用户，用户名为Oracle，赋予该用户密码为wodemima，赋予该用户dba权限。新建用户Oracle1，赋予该用户密码为wodemima，自定义一个角色，角色名为oracle1_role，该角色拥有建表，新增用户,登陆系统的权限，其余的权限没有。

值得一提的是，在system用户下，dba这个角色应该就包括了AQ_ADMINISTRATOR_ROLE这个角色，因为select * from role_sys_privs where role='DBA';和select * from SESSION_PRIVS;查询结果是一样的。

1，Oracle用户

在sys账号下操作，执行如下SQL语句：

Oracle数据库之用户角色管理（一）_java_14

Oracle数据库之用户角色管理（一）_数据库_15

此时，Oracle这个新建用户拥有dba角色，它所具有的权限有哪些呢？

切换用户到Oracle，执行SQL语句：select * from session_privs; 这个时候应该是具有202项权限的。

查看Oracle这个用户所拥有的角色：select * from dba_role_privs where grantee='ORACLE';--输出如下：

Oracle数据库之用户角色管理（一）_java_16

当然了，现在这个新建用户Oracle 可以执行dba所能执行的命令了，包括建表，新建用户，删除用户等等操作了。不一一演示了，确定是可以完成的哦。

2，oracle1用户

执行以下SQL语句：

create user oracle1 identified by wodemima;

create role oracle1_role;
        grant create any table,create user,create session to oracle1_role;
        grant oracle1_role to oracle1;

测试角色是否正确应用到了用户oracle1。

select * from session_privs;--当前用户所拥有的权限，可以看到三个权限，但这三个权限没有赋予给用户，而是赋予了角色，现在可以看到，证明权限是继承的。
create table emps(id number(10),name VARCHAR2(10));--可以新建表了
create user mytest identified by wodemima;--可以新建用户了


select * from dba_role_privs where grantee='ORACLE1';--这个语句需要切换到高权限用户执行，可以看到所oracle1所拥有的角色是哪个。

Oracle数据库之用户角色管理（一）_python_17

总结：

本文中涉及到的比较常用并且重要的用户管理SQL语句总结如下：（一般在role=后面的角色名称必须大写，否则什么都查不到）

select * from session_privs;--当前登陆用户的所有权限，等价于这个语句：select PRIVILEGE  from session_privs;
select * from dba_users;--系统内所有的用户
select * from dba_roles;--系统内所有的角色，特殊角色public并不显示，需要注意哦~~~~~！！~~~~~
select * from role_sys_privs where role='ORACLE1_ROLE';--查询角色所拥有的权限，这里查询的是自定义的那个角色，每个角色所具有的权限哦~~~~~~~！~~！~~~~！~，查看一下DBA，你会发现dba角色的权限真的很多哦~~~~~
select * from dba_profiles;--所有的系统变量定义

grantee后面跟的用户名也是必须大写，不管原来是什么样

SELECT GRANTEE,GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE ='ORACLE1';--（dba权限）执行，查询用户所拥有的角色

select * from dba_role_privs where grantee='ORACLE1';--查询用户所拥有的角色，同样需要高权限查询
Select * from USER_ROLE_PRIVS;--查询当前用户所拥有的角色，同样需要高权限查询，
 ###这三个SQL语句是等价的
========================================================================
select * from dba_roles;--查询系统内的所有角色，这个是系统内的所有角色，重要的事情在说一遍！！！！！！！！！！！！
=========================================================================
select * from dba_role_privs;--查询系统内所有用户以及所有用户和所有角色的关系图

以上两个是等价的SQL语句！！！！

oracle这个系统的环境变量更改设定也十分重要哦。

CREATE PROFILE "TEST" LIMIT
 SESSIONS_PER_USER UNLIMITED
 CPU_PER_SESSION UNLIMITED
 CPU_PER_CALL UNLIMITED
 CONNECT_TIME UNLIMITED
 IDLE_TIME UNLIMITED
 LOGICAL_READS_PER_SESSION UNLIMITED
 LOGICAL_READS_PER_CALL UNLIMITED
 COMPOSITE_LIMIT UNLIMITED
 PRIVATE_SGA UNLIMITED
 FAILED_LOGIN_ATTEMPTS 10
 PASSWORD_LIFE_TIME 180
 PASSWORD_REUSE_TIME UNLIMITED
 PASSWORD_REUSE_MAX UNLIMITED
 PASSWORD_LOCK_TIME 1
 PASSWORD_GRACE_TIME 7
 PASSWORD_VERIFY_FUNCTION NULL;
 
 ALTER profile TEST limit PASSWORD_LIFE_TIME UNLIMITED;
 alter user oracle1 profile TEST;--指定oracle1这个用户使用自定义的系统环境变量test

标签：角色,dba,数据库,用户,--,Oracle,权限,select
From： https://blog.51cto.com/u_15966109/6082649

Oracle数据库之用户角色管理（一）