摘自：http://blog.csdn.net/indexman/article/details/7897658
最近学习oracle角色管理，遂整理一下内容：
一 学习目标
1.创建和修改角色
2.控制角色的可用性
3.移除角色
4.使用预定义角色
5.通过数据字典查询角色信息
二 角色的概念和特性
1.什么是角色？
角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理。
2.角色的特性有哪些？
a.使用grant和revoke赋予和回收系统权限
b.角色可以赋予给任何除自身之外的角色和用户
c.角色可以由系统和对象权限组成
d.可以启用和禁用角色
e.可以指定一个密码
f.角色不被任何用户拥有，不在任何方案内
g.角色在数据字典中有各自的描述
三 创建、修改、分配角色
1.语法如下：
CREATEROLE role [NOTIDENTIFIED |IDENTIFIED
{BY password | EXTERNALLY | GLOBALLY | USING package}]
ALTERROLE role {NOT IDENTIFIED | IDENTIFIED
{BY password |USING package| EXTERNALLY |GLOBALLY }};
GRANT role [, role ]...
TO {user|role|PUBLIC}
[, {user|role|PUBLIC} ]...
[WITH ADMIN OPTION] --被授予者可赋予其他用户权限
2.例如：
a. CREATE ROLE oe_clerk; --普通
b.CREATE ROLE hr_clerk IDENTIFIED BY bonus; --指定密码
c. CREATE ROLE hr_managerIDENTIFIED EXTERNALLY; --外部认证
d. ALTER ROLE oe_clerk IDENTIFIEDBY order;
e. ALTER ROLE hr_clerk IDENTIFIED EXTERNALLY;
f. ALTER ROLE hr_managerNOT IDENTIFIED;
g. GRANT oe_clerk TOscott;
h. GRANT hr_clerk TOhr_manager;
i. GRANT hr_manager TOscott WITH ADMIN OPTION;
四 预定义角色
预定义角色是指Oracle所提供的角色，每种角色都用于执行一些特定的管理任务，下面我们介绍常用的预定义角色connect，resource，dba。
1、connect角色
connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，只要给用户授予connect和resource角色就够了，
那么connect角色具有以下系统权限：
alter session
create cluster
create database link
create session
create view
create sequence
2、resource角色
resource角色具有应用开发人员所需要的其他权限，比如建立存储过程、触发器等。这里需要注意的是resource角色隐含了unlimited tablespace系统权限。
resource角色包含以下系统权限：
create cluster
create indextype
create table
create sequence
create type
create procedure
create trigger
3、dba角色
dba角色具有所有的系统权限，及with admin option选项，默认的dba用户为sys和system他们可以将任何系统权限授予其他用户。
但是要注意的是dba角色不具备sysdba和sysoper的特权（启动和关闭数据库）。
五 默认角色（default role）
我们可以给某个用户分配一些角色，比如role r1,r2,r3 而其中可以将某些角色比如r1设置为default role，其他的不设置成default role，
这样，当该用户登录时，自动具有default role中所包含的权限，其他的角色所具有的权限要通过set role 角色来获得。
语法如下：
ALTERUSER user DEFAULT ROLE {role [,role]... | ALL [EXCEPT role[,role]... ] | NONE}
下面我们举个例子：
1. sys用户作为sysdba登录，创建3个角色：
- SQL> create role r1;
- 角色已创建。
- SQL> create role r2 identified by r2;
- 角色已创建。
- SQL> create role r3 identified by r3;
- 角色已创建。
SQL> create role r1;
角色已创建。
SQL> create role r2 identified by r2;
角色已创建。
SQL> create role r3 identified by r3;
角色已创建。2. 给三个角色赋予权限：
- SQL> grant create session to r1;
- 授权成功。
- SQL> grant select on scott.emp to r2;
- 授权成功。
- SQL> grant insert on scott.dept to r3;
- 授权成功。
SQL> grant create session to r1;
授权成功。
SQL> grant select on scott.emp to r2;
授权成功。
SQL> grant insert on scott.dept to r3;
授权成功。3.创建用户u1并将三个角色赋予：
- SQL> create user u1 identified by u1;
- 用户已创建。
- SQL> grant r1, r2, r3 to u1;
- 授权成功。
SQL> create user u1 identified by u1;
用户已创建。
SQL> grant r1, r2, r3 to u1;
授权成功。
4. 在修改用户u1的默认角色前，r1,r2,r3 角色均为u1的 default role，以u1用户登录，查询、新增，都没有问题。
- C:\Documents and Settings\Administrator>sqlplus u1/u1
- SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 8月 22 23:21:25 2012
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- 连接到:
- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
- With the Partitioning, OLAP and Data Mining options
- SQL> select * from scott.emp where rownum<3;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM
- ---------- ---------- --------- ---------- -------------- ---------- ----------
- DEPTNO
- ----------
- 7369 SMITH CLERK 7902 17-12月-80 800
- 20
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
- 30
- SQL> insert into scott.dept(deptno) values(80);
- 已创建 1 行。
C:\Documents and Settings\Administrator>sqlplus u1/u1
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 8月 22 23:21:25 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from scott.emp where rownum<3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 800
20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
30
SQL> insert into scott.dept(deptno) values(80);
已创建 1 行。5. 现在sys用户修改用户u1的default role，仅将r1作为u1的默认角色：
alter user u3 default role r1;
- C:\Documents and Settings\Administrator>sqlplus u1/u1
- SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 8月 22 23:29:05 2012
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- 连接到:
- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
- With the Partitioning, OLAP and Data Mining options
- SQL> select * from scott.emp where rownum<3;
- select * from scott.emp where rownum<3
- *
- 第 1 行出现错误:
- ORA-00942: 表或视图不存在
- SQL> insert into scott.dept(deptno) values(80);
- insert into scott.dept(deptno) values(80)
- *
- 第 1 行出现错误:
- ORA-00942: 表或视图不存在
C:\Documents and Settings\Administrator>sqlplus u1/u1
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 8月 22 23:29:05 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from scott.emp where rownum<3;
select * from scott.emp where rownum<3
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> insert into scott.dept(deptno) values(80);
insert into scott.dept(deptno) values(80)
*
第 1 行出现错误:
ORA-00942: 表或视图不存在6. 用户自己打开role权限
set role r2 identified by r2;
set role r3 identified by r3;
此时插入记录没有问题，但是select 确发现不行了。
- SQL> set role r2 identified by r2;
- 角色集
- SQL> set role r3 identified by r3;
- 角色集
- SQL> insert into scott.dept(deptno) values(80);
- 已创建 1 行。
- SQL> select * from scott.emp where rownum<3;
- select * from scott.emp where rownum<3
- *
- 第 1 行出现错误:
- ORA-00942: 表或视图不存在
SQL> set role r2 identified by r2;
角色集
SQL> set role r3 identified by r3;
角色集
SQL> insert into scott.dept(deptno) values(80);
已创建 1 行。
SQL> select * from scott.emp where rownum<3;
select * from scott.emp where rownum<3
*
第 1 行出现错误:
ORA-00942: 表或视图不存在证明此时用户所属的角色仅仅是默认角色r1，和刚刚打开的角色r3，而r2被set role r3 identified by r3; 覆盖掉了。
那要同时有r2，r3 的权限怎么办呢？
set role r2 identified by r2,r3 identified by r3; 此时就同时查询新增了。
- SQL> set role r2 identified by r2, r3 identified by r3;
- 角色集
- SQL> select * from scott.emp where rownum<3;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- SQL> insert into scott.dept(deptno) values(80);
- 已创建 1 行。
SQL> set role r2 identified by r2, r3 identified by r3;
角色集
SQL> select * from scott.emp where rownum<3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
SQL> insert into scott.dept(deptno) values(80);
已创建 1 行。注：不过set role 的效果是临时的，只是当前session有效，其他的session无效，当结束当前session后再登录，又只有default role 的权限了。
六 应用角色（application role）
通过上图可以一目了然知道应用角色的位置，oracle提供安全应用角色仅允许被授权的PL/SQL包启用。下面举例说明：
1. 以sys用户登录，创建测试用户user1，测试角色role1，验证角色的存储过程p_enable_role;
将role1授予user1，将执行存储过程p_enable_role权限赋予user1, 将select在hr.countries上的权限赋予role;
- SQL> create user user1 identified by user1;
- 用户已创建。
- SQL> create role role1 identified using sys.p_enable_role1;
- 角色已创建。
- SQL> create or replace procedure p_enable_role1 authid current_user is
- 2 begin
- 3 if sys_context('userenv','ip_address') ='192.168.0.166' then
- 4 dbms_session.set_role('role1');
- 5 else
- 6 null;
- 7 end if;
- 8 end;
- 9 /
- SQL> grant execute on p_enable_role1 to user1;
- 授权成功。
- SQL> grant select on hr.countries to role1;
- 授权成功。
- SQL> grant role1 to user1;
- 授权成功。
SQL> create user user1 identified by user1;
用户已创建。
SQL> create role role1 identified using sys.p_enable_role1;
角色已创建。
SQL> create or replace procedure p_enable_role1 authid current_user is
2 begin
3 if sys_context('userenv','ip_address') ='192.168.0.166' then
4 dbms_session.set_role('role1');
5 else
6 null;
7 end if;
8 end;
9 /
SQL> grant execute on p_enable_role1 to user1;
授权成功。
SQL> grant select on hr.countries to role1;
授权成功。
SQL> grant role1 to user1;
授权成功。2. 将user1的默认role设置为none ，将createsession赋予user1这个用户
- SQL> alter user user1 default role none;
- 用户已更改。
- SQL> grant create session to user1;
- 授权成功。
SQL> alter user user1 default role none;
用户已更改。
SQL> grant create session to user1;
授权成功。3. 在192.168.0.53这个ip上测试过程
- C:\Users\Administrator>sqlplus user1/user1@xu
- SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 8月 23 12:46:22 2012
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- 连接到:
- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
- With the Partitioning, OLAP and Data Mining options
- SQL> select sys_context('userenv','ip_address') from dual;
- SYS_CONTEXT('USERENV','IP_ADDRESS')
- --------------------------------------------------------------------------------
- 192.168.0.53
- SQL> select sys_context('userenv','host')from dual;
- SYS_CONTEXT('USERENV','HOST')
- --------------------------------------------------------------------------------
- WORKGROUP\HUI
- SQL> select sys_context('userenv','server_host')from dual;
- SYS_CONTEXT('USERENV','SERVER_HOST')
- --------------------------------------------------------------------------------
- pc-20120517eoxo
- SQL> select * from session_roles;
- 未选定行
- SQL> exec sys.p_enable_role1;
- PL/SQL 过程已成功完成。
- SQL> select * from session_roles;
- 未选定行
- SQL> select * from hr.dept;
- select * from hr.dept
- *
- 第 1 行出现错误:
- ORA-00942: 表或视图不存在
C:\Users\Administrator>sqlplus user1/user1@xu
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 8月 23 12:46:22 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select sys_context('userenv','ip_address') from dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
192.168.0.53
SQL> select sys_context('userenv','host')from dual;
SYS_CONTEXT('USERENV','HOST')
--------------------------------------------------------------------------------
WORKGROUP\HUI
SQL> select sys_context('userenv','server_host')from dual;
SYS_CONTEXT('USERENV','SERVER_HOST')
--------------------------------------------------------------------------------
pc-20120517eoxo
SQL> select * from session_roles;
未选定行
SQL> exec sys.p_enable_role1;
PL/SQL 过程已成功完成。
SQL> select * from session_roles;
未选定行
SQL> select * from hr.dept;
select * from hr.dept
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
4. 在192.168.0.166上测试：
- C:\Documents and Settings\Administrator>sqlplus user1/user1@orcl
- SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 8月 23 10:39:29 2012
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- 连接到:
- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
- With the Partitioning, OLAP and Data Mining options
- SQL> show user
- USER 为 "USER1"
- SQL> select sys_context('userenv','ip_address') from dual;
- SYS_CONTEXT('USERENV','IP_ADDRESS')
- --------------------------------------------------------------------------------
- 192.168.0.166
- SQL> select sys_context('userenv','host') from dual;
- SYS_CONTEXT('USERENV','HOST')
- --------------------------------------------------------------------------------
- WORKGROUP\PC-20120517EOXO
- SQL> select sys_context('userenv','server_host') from dual;
- SYS_CONTEXT('USERENV','SERVER_HOST')
- --------------------------------------------------------------------------------
- pc-20120517eoxo
- SQL> select role from session_roles;
- 未选定行
- SQL> exec sys.p_enable_role1;
- PL/SQL 过程已成功完成。
- SQL> select * from session_roles;
- ROLE
- ------------------------------
- ROLE1
- SQL> select * from hr.countries;
- CO COUNTRY_NAME REGION_ID
- -- ---------------------------------------- ----------
- AR Argentina 2
- AU Australia 3
- BE Belgium 1
- BR Brazil 2
- CA Canada 2
- CH Switzerland 1
- CN China 3
- DE Germany 1
- DK Denmark 1
- EG Egypt 4
- FR France 1
- .....
- 已选择25行。
C:\Documents and Settings\Administrator>sqlplus user1/user1@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 8月 23 10:39:29 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show user
USER 为 "USER1"
SQL> select sys_context('userenv','ip_address') from dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
192.168.0.166
SQL> select sys_context('userenv','host') from dual;
SYS_CONTEXT('USERENV','HOST')
--------------------------------------------------------------------------------
WORKGROUP\PC-20120517EOXO
SQL> select sys_context('userenv','server_host') from dual;
SYS_CONTEXT('USERENV','SERVER_HOST')
--------------------------------------------------------------------------------
pc-20120517eoxo
SQL> select role from session_roles;
未选定行
SQL> exec sys.p_enable_role1;
PL/SQL 过程已成功完成。
SQL> select * from session_roles;
ROLE
------------------------------
ROLE1
SQL> select * from hr.countries;
CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ----------
AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
CA Canada 2
CH Switzerland 1
CN China 3
DE Germany 1
DK Denmark 1
EG Egypt 4
FR France 1
.....
已选择25行。七 从数据字典获取角色信息
例子：
- SQL> select role, password_required
- 2 from dba_roles;
- ROLE PASSWORD
- ------------------------------ --------
- R1 NO
- R2 YES
- R3 YES
- ROLE1 YES
- ......
- 已选择37行。
- SQL> select * from dba_role_privs;
- GRANTEE GRANTED_ROLE ADM DEF
- ------------------------------ ------------------------------ --- ---
- SYS XDBADMIN YES YES
- SYS IMP_FULL_DATABASE YES YES
- DBA OLAP_DBA NO YES
- DBA SCHEDULER_ADMIN YES YES
- SYSTEM AQ_ADMINISTRATOR_ROLE YES YES
- TSMSYS RESOURCE NO YES
- WMSYS RESOURCE NO YES
- WMSYS CONNECT NO YES
- SYSMAN MGMT_USER YES YES
- SCOTT RESOURCE NO YES
- U1 R2 NO NO
- SQL> select * from role_sys_privs where role='R1';
- ROLE PRIVILEGE ADM
- ------------------------------ ---------------------------------------- ---
- R1 CREATE SESSION NO
- SQL> set linesize 1000
- SQL> select role, owner, table_name, privilege from role_tab_privs where role='ROLE1';
- ROLE OWNER TABLE_NAME PRIVILEGE
- -------------------- -------------------- ------------------------------ --------------------
- ROLE1 HR COUNTRIES SELECT
SQL> select role, password_required
2 from dba_roles;
ROLE PASSWORD
------------------------------ --------
R1 NO
R2 YES
R3 YES
ROLE1 YES
......
已选择37行。
SQL> select * from dba_role_privs;
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SYS XDBADMIN YES YES
SYS IMP_FULL_DATABASE YES YES
DBA OLAP_DBA NO YES
DBA SCHEDULER_ADMIN YES YES
SYSTEM AQ_ADMINISTRATOR_ROLE YES YES
TSMSYS RESOURCE NO YES
WMSYS RESOURCE NO YES
WMSYS CONNECT NO YES
SYSMAN MGMT_USER YES YES
SCOTT RESOURCE NO YES
U1 R2 NO NO
SQL> select * from role_sys_privs where role='R1';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
R1 CREATE SESSION NO
SQL> set linesize 1000
SQL> select role, owner, table_name, privilege from role_tab_privs where role='ROLE1';
ROLE OWNER TABLE_NAME PRIVILEGE
-------------------- -------------------- ------------------------------ --------------------
ROLE1 HR COUNTRIES SELECT
3108
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
