第十五章 管理角色
<p>[TOC]</p>
<h2>引入角色的原因</h2>
<ul>
<li>批量处理</li>
</ul>
<h2>角色创建</h2>
<pre><code>connect sys/oracle as sysdba
create role clerk;
create role sales identified by money;
create role manager identified externally;
select * from dba_roles where role in ('CLERK','SALES','MANAGER');</code></pre>
<h2>角色修改</h2>
<ul>
<li>只能修改角色的验证方法
<pre><code>select * from session_privs;
alter role clerk identified externally;
alter role sales not identified ;
alter role manager identified by vampire ;
select * from dba_roles where role in ('CLERK','SALES','MANAGER');</code></pre></li>
</ul>
<h2>角色授权</h2>
<ul>
<li>
<p>先回收用户的所有权限</p>
<pre><code>select * from dba_sys_privs where grantee in ('CAT','DOG','PIG','FOX');
revoke CREATE TABLE,CREATE SESSION from cat;
revoke CREATE TABLE,CREATE SESSION,CREATE VIEW from dog,pig,fox;</code></pre>
</li>
<li>
<p>授予角色权限</p>
<pre><code>grant create session,create table,create view to clerk;
grant select any table,clerk to manager;</code></pre>
</li>
<li>查看角色的权限(直接授予的权限，不包括角色授予角色的权限)
<code>select * from role_sys_privs where role in ('CLERK','MANAGER','SALES');</code></li>
<li>
<p>查看角色间接授予的权限
<code>select * from dba_role_privs where granted_role like'CL%';</code></p>
</li>
<li>授予角色权限给用户
<pre><code>grant manager to cat with admin option;
connect cat/miaomiao
select * from dba_role_privs where grantee='CAT';
select * from session_privs;</code></pre></li>
</ul>
<h2>建立默认角色</h2>
<ul>
<li>
<p>三种情况不能用，<code>alter user defualt role</code></p>
</li>
<li>
<p>cat用户所有权限是角色授予的，非默认，登录前无权限</p>
<pre><code>connect system/oracle
grant sales to cat;
alter user cat default role none;
connect cat/miaomiao</code></pre>
</li>
<li>
<p>授予cat用户create session 权限</p>
<pre><code>connect system/oracle
grant create session to cat;
connect cat/miaomiao
select * from session_privs;
col username for a15
col granted_role for a15
select * from user_role_privs;</code></pre>
</li>
<li>
<p>设置manager,clerk为默认角色</p>
<pre><code>connect system/oracle
alter user cat default role clerk,manager;--报错,clerk不是直接授予cat的
alter user cat default role all;
connect cat/miaomiao
col username for a15
col granted_role for a15
select * from user_role_privs;--全部角色为默认角色</code></pre>
</li>
<li>设置除sales角色外全部角色为默认角色
<pre><code>connect system/oracle
alter user cat default role all except sales;
--检验设置结果
connect cat/miaomiao
col username for a15
col granted_role for a15
select * from user_role_privs;--除sales外其他角色为默认角色</code></pre></li>
</ul>
<h2>激活和禁止角色<code>set role</code>、<code>set role none</code></h2>
<ul>
<li>
<p>禁止cat的角色</p>
<pre><code>connect cat/miaomiao
select * from session_privs;
set role none;
select * from session_privs;--只剩create session权限直接grant的</code></pre>
</li>
<li>
<p>恢复回manager的口令
<code>alter role manager identified by vampire ;</code></p>
</li>
<li>
<p>激活有口令的role
<code>set role manager identified by vampire;</code></p>
</li>
<li>激活除manager外的权限
<pre><code>set role all except manager;
select * from session_privs;</code></pre></li>
</ul>
<h2>角色的回收与删除</h2>
<ul>
<li>
<p>删除取消角色的校验口令</p>
<pre><code>alter role manager not identified;
select * from dba_roles where role in ('CLERK','MANAGER','SALES');
select * from dba_role_privs where grantee in ('CLERK','MANAGER','SALES');
select * from dba_sys_privs where grantee in ('CLERK','MANAGER','SALES');</code></pre>
</li>
<li>
<p>授予所有角色给四个用户</p>
<pre><code>grant clerk,sales,manager to cat,dog,pig,fox;
select * from dba_role_privs where grantee in ('CAT','PIG','DOG','FOX');</code></pre>
</li>
<li>
<p>回收fox,pig的sales,manager权限
<code>revoke sales,manager from fox,pig;</code></p>
</li>
<li>
<p>回收所有用户的权限（先授予权限给所有用户public）</p>
<pre><code>grant manager,sales to public;
select * from dba_role_privs where grantee in ('CAT','PIG','DOG','FOX','PUBLIC');
revoke manager,sales from public;
--回收所有用户的权限，不影响单独授予的用户
select * from dba_role_privs where grantee in ('CAT','PIG','DOG','FOX','PUBLIC');</code></pre>
</li>
<li>删除角色，权限要求：<code>drop any role</code>`admin option`
<pre><code>select * from dba_roles where role in ('CLERK','MANAGER','SALES');
drop role sales;
connect dog/wangwang
drop role manager;
--ORA-01031: insufficient privileges
connect cat/miaomiao
drop role manager;
select * from dba_roles where role in ('CLERK','MANAGER','SALES');</code></pre></li>
</ul>
<h2>创建和使用角色指南</h2>
<ul>
<li>
<p>不重要权限的角色设置为默认，重要的权限角色添加口令设置为非默认</p>
</li>
<li>
<p>重新创建manager角色，为clerk,manager授予试验检查</p>
<pre><code>create role manager;
select * from dba_roles where role in ('CLERK','MANAGER');
grant select on scott.emp to clerk;
grant update,delete,insert on scott.emp to manager;
grant clerk,manager to cat,dog;</code></pre>
</li>
<li>
<p>查看scott用户表的权限分配</p>
<pre><code>connect scott/tiger
select * from user_tab_privs_made;</code></pre>
</li>
<li>
<p>为manager添加口令及设置为非默认</p>
<pre><code>connect system/oracle
alter role manager identified by vampire;
alter user dog default role all except manager;</code></pre>
</li>
<li>用dog用户使用有口令，非默认的角色manager的权限
<pre><code>connect dog/wangwang
select * from scott.emp where sal>=2000;
update scott.emp set sal = 9999 ;--权限不足
--激活manager权限
set role manager ;--要求口令才能激活
set role manager identified by vampire;
update scott.emp set sal = 9999 ;--有权限了
rollback;</code></pre></li>
</ul>
<h2>oracle 预定义角色</h2>
<ul>
<li>
<p>预定义角色
1.EXP_FULL_DATABASE:导出数据库的权限
2.IMP_FULL_DATABASE:导入数据库的权限
3.SELECT_CATALOG_ROLE:查询数据字典权限
4.EXECUTE_CATALOG_ROLE:数据字典的执行权限
5.DELETE_CATALOG_ROLE:数据字典上删除权限(已经没有)
6.DBA、CONNECT、RESOURCE:为兼容以前的版本而设置
<code>select distinct role from dba_roles;</code></p>
</li>
<li>查看connect,resource的权限
<code>select * from role_sys_privs where role in ('CONNECT','RESOURCE') order by role;</code></li>
</ul>
<h2>用户、概要文件、权限和角色的实用实例</h2>
<ul>
<li>
<p>创建有口令的角色</p>
<pre><code>create role change identified by zhubajie;
select * from dba_roles where role ='CHANGE';
grant select any table to change;
select * from role_sys_privs where role='CHANGE';</code></pre>
</li>
<li>创建用户</li>
</ul>
<pre><code>create user sudaji identified by szhouwang
default tablespace pioneer_data
temporary tablespace pioneer_temp
quota 38M on pioneer_data
quota 28M on pioneer_indx
profile pioneer_prof;
--授予角色给sudaji
grant connect,resource,change to sudaji;
alter user sudaji default role all except change;
--查看用户信息
select username,default_tablespace,temporary_tablespace,created,profile from dba_users where username='SUDAJI';
select * from dba_ts_quotas where username='SUDAJI';
--创建更多用户
create user wuzetian identified by dazhou
default tablespace pioneer_data
temporary tablespace pioneer_temp
quota 38M on pioneer_data
quota 28M on pioneer_indx
profile pioneer_prof;
create user yguifei identified by lilongji
default tablespace pioneer_data
temporary tablespace pioneer_temp
quota 38M on pioneer_data
quota 28M on pioneer_indx
profile pioneer_prof;
create user pjinlian identified by wuda
default tablespace pioneer_data
temporary tablespace pioneer_temp
quota 38M on pioneer_data
quota 28M on pioneer_indx
profile pioneer_prof;
create user muguiyin identified by yzongbao
default tablespace pioneer_data
temporary tablespace pioneer_temp
quota 38M on pioneer_data
quota 28M on pioneer_indx
profile pioneer_prof;
grant connect,resource,change to wuzetian,yguifei,pjinlian,muguiyin ;
alter user wuzetian default role all except change;
alter user yguifei default role all except change;
alter user pjinlian default role all except change;
alter user muguiyin default role all except change;
--查询创建的用户信息
select username,default_tablespace,temporary_tablespace,created,profile from dba_users where default_tablespace like 'PIO%';
select username,tablespace_name,bytes/1024/1024 MB,max_bytes/1024/1024 "MAX MB" from dba_ts_quotas where tablespace_name like 'PIO%';
select * from dba_role_privs where grantee in ('SUDAJI','WUZETIAN','YGUIFEI','PJINLIAN','MUGUIYIN')</code></pre>