oracle角色管理

来源：互联网发布：编曲用什么软件编辑：程序博客网时间：2024/05/20 20:01

最近学习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;

注：USING package适用于application role[应用角色]，只有通过相关的包模块才能启用。

四预定义角色

预定义角色是指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;角色已创建。

2. 给三个角色赋予权限：

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;授权成功。

4. 在修改用户u1的默认角色前，r1,r2,r3 角色均为u1的 default role，以u1用户登录，查询、新增，都没有问题。

C:\Documents and Settings\Administrator>sqlplus u1/u1SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 8月 22 23:21:25 2012Copyright (c) 1982, 2005, Oracle.  All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> 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        30SQL> insert into scott.dept(deptno) values(80);已创建 1 行。

5. 现在sys用户修改用户u1的default role，仅将r1作为u1的默认角色：

alter user u3 default role r1; --此时将覆盖原来的设置，u1 的default role =r1，仅仅有登录权限。

C:\Documents and Settings\Administrator>sqlplus u1/u1SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 8月 22 23:29:05 2012Copyright (c) 1982, 2005, Oracle.  All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> 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: 表或视图不存在

证明此时用户所属的角色仅仅是默认角色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         30SQL> insert into scott.dept(deptno) values(80);已创建 1 行。

注：不过set role 的效果是临时的，只是当前session有效，其他的session无效，当结束当前session后再登录，又只有default role 的权限了。

六应用角色（application role）

Description of Figure 20-2 follows

通过上图可以一目了然知道应用角色的位置，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;授权成功。

2. 将user1的默认role设置为none ，将createsession赋予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@xuSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 8月 23 12:46:22 2012Copyright (c) 1982, 2005, Oracle.  All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> select sys_context('userenv','ip_address') from dual;SYS_CONTEXT('USERENV','IP_ADDRESS')--------------------------------------------------------------------------------192.168.0.53SQL> select sys_context('userenv','host')from dual;SYS_CONTEXT('USERENV','HOST')--------------------------------------------------------------------------------WORKGROUP\HUISQL> select sys_context('userenv','server_host')from dual;SYS_CONTEXT('USERENV','SERVER_HOST')--------------------------------------------------------------------------------pc-20120517eoxoSQL> 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@orclSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 8月 23 10:39:29 2012Copyright (c) 1982, 2005, Oracle.  All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> show userUSER 为 "USER1"SQL> select sys_context('userenv','ip_address') from dual;SYS_CONTEXT('USERENV','IP_ADDRESS')--------------------------------------------------------------------------------192.168.0.166SQL> select sys_context('userenv','host') from dual;SYS_CONTEXT('USERENV','HOST')--------------------------------------------------------------------------------WORKGROUP\PC-20120517EOXOSQL> select sys_context('userenv','server_host') from dual;SYS_CONTEXT('USERENV','SERVER_HOST')--------------------------------------------------------------------------------pc-20120517eoxoSQL> select role from session_roles;未选定行SQL> exec sys.p_enable_role1;PL/SQL 过程已成功完成。SQL> select * from session_roles;ROLE------------------------------ROLE1SQL> select * from hr.countries;CO COUNTRY_NAME                              REGION_ID-- ---------------------------------------- ----------AR Argentina                                         2AU Australia                                         3BE Belgium                                           1BR Brazil                                            2CA Canada                                            2CH Switzerland                                       1CN China                                             3DE Germany                                           1DK Denmark                                           1EG Egypt                                             4FR France                                            1.....已选择25行。

七从数据字典获取角色信息

例子：

SQL> select role, password_required  2  from dba_roles;ROLE                           PASSWORD------------------------------ --------R1                             NOR2                             YESR3                             YESROLE1                          YES......已选择37行。SQL> select * from  dba_role_privs;GRANTEE                        GRANTED_ROLE                   ADM DEF------------------------------ ------------------------------ --- ---SYS                            XDBADMIN                       YES YESSYS                            IMP_FULL_DATABASE              YES YESDBA                            OLAP_DBA                       NO  YESDBA                            SCHEDULER_ADMIN                YES YESSYSTEM                         AQ_ADMINISTRATOR_ROLE          YES YESTSMSYS                         RESOURCE                       NO  YESWMSYS                          RESOURCE                       NO  YESWMSYS                          CONNECT                        NO  YESSYSMAN                         MGMT_USER                      YES YESSCOTT                          RESOURCE                       NO  YESU1                             R2                             NO  NOSQL> select * from  role_sys_privs where role='R1';ROLE                           PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---R1                             CREATE SESSION                           NOSQL> set linesize 1000SQL> select role, owner, table_name, privilege from role_tab_privs where role='ROLE1';ROLE                 OWNER                TABLE_NAME                     PRIVILEGE-------------------- -------------------- ------------------------------ --------------------ROLE1                HR                   COUNTRIES                      SELECT

------------------------------------

Present by dylan.