Oracle权限／角色赋予的差异理解

环境：Oracle 11.2.0.4
目的：验证业务用户的权限／角色赋予的差异

现在创建两个用户jingyu2和jingyu3；

SYS@jyzhao1> create user jingyu2 identified by jingyu2 DEFAULT tablespace tbs_jingyu; SYS@jyzhao1> create user jingyu3 identified by jingyu3 DEFAULT tablespace tbs_jingyu; SYS@jyzhao1> grant connect, resource to jingyu2, jingyu3;

模拟jingyu2用户下有一张表T_jingyu2；jingyu3用户下有一张表T_jingyu3；

JINGYU2@jyzhao1> create table t_jingyu2 as select * from user_objects; Table created. JINGYU3@jyzhao1> create table t_jingyu3 as select * from user_objects; Table created.

分别在两个用户的session下查询被赋予的角色／权限：

JINGYU2@jyzhao1>select * from session_privs; PRIVILEGE ---------------------------------------- CREATE SESSION UNLIMITED TABLESPACE CREATE TABLE CREATE CLUSTER CREATE SEQUENCE CREATE PROCEDURE CREATE TRIGGER CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE 10 rows selected. JINGYU2@jyzhao1>select * from session_roles; ROLE ------------------------------ CONNECT RESOURCE

JINGYU3用户的会话权限和会话角色查询结果一致，输出略。
可以看到赋予connect，resource这两个最常被用于应用开发的角色之后，该用户具有上述10个权限，一般基础开发就够用了。

需求: jingyu2用户访问jingyu3的表jingyu3,并创建同义词jingyu3;

显然当前的这个需求，对于目前的角色／权限是不能满足需求的：

JINGYU2@jyzhao1>select count(1) from jingyu3.t_jingyu3; select count(1) from jingyu3.t_jingyu3 * ERROR at line 1: ORA-00942: table or view does not exist JINGYU2@jyzhao1>create synonym t_jingyu3 for jingyu3.t_jingyu3; create synonym t_jingyu3 for jingyu3.t_jingyu3 * ERROR at line 1: ORA-01031: insufficient privileges

一是jingyu2用户不能访问其他用户jingyu3的表，二是jingyu2用户没有创建同义词的权限。

那么为了满足需求，考虑如何解决。

解决方案一：赋予缺少的权限（推荐使用）。
方案宗旨：根据业务需求，缺什么权限赋予什么权限，精确控制。

赋予缺少的权限：

JINGYU3@jyzhao1> grant select on t_jingyu3 to jingyu2; Grant succeeded. SYS@jyzhao1>grant create synonym to jingyu2; Grant succeeded.

此时赋予完权限后再次尝试,发现已经可以正常满足需求。

JINGYU2@jyzhao1>select count(1) from jingyu3.t_jingyu3; COUNT(1) ---------- 1 JINGYU2@jyzhao1>create synonym t_jingyu3 for jingyu3.t_jingyu3; Synonym created. JINGYU2@jyzhao1>select count(1) from t_jingyu3; COUNT(1) ---------- 1

查看此时的session权限／角色：

JINGYU2@jyzhao1>select * from session_privs; PRIVILEGE ---------------------------------------- CREATE SESSION UNLIMITED TABLESPACE CREATE TABLE CREATE CLUSTER CREATE SYNONYM CREATE SEQUENCE CREATE PROCEDURE CREATE TRIGGER CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE 11 rows selected. JINGYU2@jyzhao1>select * from session_roles; ROLE ------------------------------ CONNECT RESOURCE

发现用户会话权限只多了一个CREATE SYNONYM权限，没有增加额外的风险。

解决方案二：赋予dba角色（不推荐）。
方案宗旨：为了操作简便，直接统一赋予DBA角色，满足一切应用潜在权限要求。

实际在很多应用场景中，尤其是开发测试环境，DBA或是开发人员往往会为了方便直接赋予高权限的dba角色，避免麻烦。当然这是不推荐的方法。
但也是一种解决方案，下面是演示实验，且在过程中还发现了一些有意思的细节。

首先回收方案一的权限赋予：

SYS@jyzhao1>revoke create synonym from jingyu2; Revoke succeeded. JINGYU3@jyzhao1>revoke select on t_jingyu3 from jingyu2; Revoke succeeded.

这样又恢复了初始环境.