有关Oracle role的总结

oracle的role算是对sys privilege 和object privilege的打包。

今天深入的测试了下,还算有不少的东西。

role不是schema对象

像table等在一个schema里面不能有同名的schema object,但是可以有同名的table和role,如下。

SQL> conn test1/test1
Connected.
SQL> create role testrole;
Role created.
SQL> create table testrole as select *from all_objects;
Table created.
SQL> grant select on testrole to testrole;
Grant succeeded.

role和schema不同,有全局的意思。

切换一个用户,把权限放进testrole里面。

SQL> conn test2/test2
Connected.
SQL> create table a as select *from obj;
Table created.
SQL> grant select on a to testrole;
Grant succeeded.

但是话说回来,role testrole是test1用户创建的,用test2赋给其他用户的话是不允许。

SQL> grant testrole to test3;
grant testrole to test3
*
ERROR at line 1:
ORA-01919: role 'TESTROLE' does not exist

用owner用户来赋予role testrole就没问题。

SQL> conn test1/test1
Connected.
SQL> grant testrole to test3;
Grant succeeded.

role是基于session级的

可以打开两个窗口,使用用户test1,test3来进行测试。

session1:
SQL> conn test1/test1
Connected.
SQL> create role testrole;
Role created.
session2:
SQL> conn test3/test3
Connected.
session1:
SQL> grant select on t1 to testrole;
Grant succeeded.
SQL> grant select on testrole to test3;
Grant succeeded.
session2:
SQL> desc test1.t1
ERROR:
ORA-04043: object test1.t1 does not exist
session2 再次登录:
SQL> conn test3/test3
Connected.
SQL> desc test1.t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

还有一点需要说明一下,如果直接赋予object privilege,在当前session就会生效,这也是和role的一个不同之处。

当前session激活role

可以使用set role或者dbms_session.set_role来激活。

role的限制

--> 在pl/sql中的限制

在动态sql中,如果调用某些表的时候,通过role,会有table or view not found这类的exception, 这时候需要通过直接赋予object privilege来修正。

-->创建视图时的问题

创建视图时,很可能会有insuffisicant privilege的错误。可以参见http://space.itpub.net/23718752/viewspace-762805/

role的加密

如果10个人用同一个db 账号,但是每个人又需要有不同的权限,可以对role加密。

这样每个人在使用的时候都能够相应的通过密码来激活属于自己的role。

SQL> create role user1_role identified by test1;
Role created.
SQL> create role user2_role identified by test2;
Role created.
SQL> create role user3_role identified by test3;
Role created.
SQL> create table t1 as select *from obj;
Table created.
SQL> create table t2 as select *from tab;
Table created.
SQL> create table t3 as select *from syn;
Table created.
SQL> grant select on t1 to user1_role;
Grant succeeded.
SQL> grant select on t2 to user2_role;
Grant succeeded.
SQL> grant select on t3 to user3_role;
Grant succeeded.
SQL> grant user1_role,user2_role,user3_role to test3;
Grant succeeded.
---初始化test3
SQL> conn / as sysdba
Connected.
--一下这句很关键,只指定用户test3有connect的角色
SQL> alter user test3 default role connect;
User altered.
--使用test3来连入,
SQL> conn test3/test3
Connected.
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
--先查看是否可以访问test1.t1
SQL> desc test1.t1
ERROR:
ORA-04043: object test1.t1 does not exist
--尝试无密码直接激活role user1_role
SQL> set role user1_role ;
set role user1_role
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'USER1_ROLE'
--使用密码激活role user1_role
SQL> set role user1_role identified by test1;
Role set.
SQL> select *from session_roles;
ROLE
------------------------------
USER1_ROLE
SQL> desc test1.t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

使用另外一个session,使用同样的账号test3

SQL> conn test3/test3
Connected.
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
SQL> set role user2_role identified by test2;
Role set.
SQL> desc test1.t2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TNAME                                     NOT NULL VARCHAR2(30)
 TABTYPE                                            VARCHAR2(7)
 CLUSTERID                                          NUMBER
secure application role

关于secure applicaton role可以是对role的高级使用,比如你可以限制某些Ip的机器才能激活某些权限。某些特定条件的用户才能激活某些权限,甚至指定在每天的每个时间段才能激活某些权限。这些复杂的需求直接通过external role,dbms_session.set_role, set role等操作就不能实现,需要更细粒度的控制。

下面就举一个例子,来让指定的用户才能激活某些权限。

SQL> select *from all_users;
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
DIP                                    20 06-NOV-13
TSMSYS                                 22 06-NOV-13
TEST1                                  26 07-NOV-13
TEST2                                  30 10-NOV-13
DBSNMP                                 25 07-NOV-13
SYS                                     0 06-NOV-13
SYSTEM                                  5 06-NOV-13
OUTLN                                  11 06-NOV-13
8 rows selected.

test1下有个表mv_test, 只能通过用户test2才能访问。

(注意:要实现这个需求,如果对于dba账户来说没有任何限制,因为dba账户本来就可以访问test1.mv_test)

步骤如下:

创建role,指定通过内嵌dbms_session.set_role的包来激活

SQL> conn test1/test1
Connected.
SQL> create role r1 identified using test1.setroles;
Role created.
SQL> grant select on test1.mv_test to r1;
Grant succeeded
SQL> grant r1 to test2;
Grant succeeded.
--注意一定要制定authid current_user
SQL> create or replace package setroles authid current_user as 
  2  procedure setrole;
  3  end;
  4  /
Package created.
--创建包体
SQL> create or replace package body setroles as 
  2  procedure setrole as
  3  begin
  4  if(sys_context('USERENV','current_user')='TEST2') then
  5  dbms_session.set_role('R1');
  6  end if;
  7  end;
  8  end;
  9  /
--如果可以,可以把运行这个包的权限开放,但是只有合适的用户才能激活。
SQL> grant execute on test1.setroles to public;
--用测试用户连入
SQL> conn test2/test2
Connected.
--查看当前持有的role
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SQL> desc test1.mv_test
ERROR:
ORA-04043: object test1.mv_test does not exist
--运行指定的包以后,来验证
SQL> exec test1.setroles.setrole;
PL/SQL procedure successfully completed.
SQL> select *from session_roles;
ROLE
------------------------------
R1
SQL> desc test1.mv_test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                 NOT NULL NUMBER
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
--选择另外一个用户来比较
--新创建一个用户。
SQL> conn / as sysdba
Connected.
SQL> create user test3 identified by test3;
User created.
SQL> grant connect,resource to test3;
Grant succeeded.
SQL> conn test3/test3
Connected.
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SQL> desc test1.mv_test
ERROR:
ORA-04043: object test1.mv_test does not exist
--运行指定的包,来验证。
SQL> exec test1.setroles.setrole;
PL/SQL procedure successfully completed.
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SQL> desc test1.mv_test
ERROR:
ORA-04043: object test1.mv_test does not exist

还有像external role,global之类的role可能使用不是很广泛,先说到这。后面后加以补充。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-03-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏网站那些事

利用 Zipkin 追踪 Mysql 数据库调用链

在前面:微服务调用链追踪中心搭建 一文中我们利用 Zipkin 搭建了一个微服务调用链的追踪中心,并且模拟了微服务调用的实验场景。利用 Zipkin 的库 Br...

48913
来自专栏技术博文

MySQL SHOW PROFILE(剖析报告)的查看

前言:SHOW PROFIL命令是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。 一、参数的开启和关闭设置 1.1 参...

3033
来自专栏跟着阿笨一起玩NET

SQL将本地图片文件插入到数据库

662
来自专栏性能与架构

Phoenix - Hbase与SQL

Phoenix是什么 简单来说,Phoenix 是一个可以让我们通过SQL的方式操作HBase数据库的框架。 HBase是一个NoSQL数据库,shell客户端...

3216
来自专栏伦少的博客

Spark Sql 连接mysql

转载请务必注明原创地址为:http://dongkelun.com/2018/03/21/sparkMysql/

4168
来自专栏Netkiller

数据库安全·开发加密插件

以下节选择《Netkiller Architect 手札》 作者:netkiller 地址 http://www.netkiller.cn/archit...

2967
来自专栏PHP技术

MySQL计划任务(事件调度器)

MySQL5.1.x版本中引入了一项新特性EVENT,顾名思义就是事件、定时任务机制,在指定的时间单元内执行特定的任务,因此今后一些对数据定时性操作不再依赖外部...

32712
来自专栏乐沙弥的世界

一次mysql 5.5升级到5.6导致的ERROR 1805

前阵子将mysql数据库由5.5.14升级到5.6.36,升级后所有的业务数据都正常。运行了几天后,发现在主库上添加用户失败,错误提示为:ERROR 1805 ...

702
来自专栏技术小黑屋

十分钟掌握SQLite操作

最近用Ruby写了一个七牛的demo参赛作品,使用了sqlite3,用到很多操作,利用假期的时间,简单做一个快速掌握SQLite命令的小入门。

723
来自专栏高性能服务器开发

(二) 服务器端的程序的编译与部署

这篇我们来介绍下TeamTalk服务器端的编译与部署,部署文档在auto_setup下,这里我们只介绍下服务器程序的编译与部署,不包括管理后台的部署,其部署方法...

2717

扫描关注云+社区