–查询所有的角色
select * from dba_roles where role in(‘CLERK’,‘SALES’,‘MANAGER’,‘MANAGER2’);
–创建一个不需要口令标识的角色clerk
create role clerk;
–创建一个需要口令标识的角色sales,口令为money
create role sales identified by “money”;
–创建一个需要使用外部标识(如操作系统)的角色manager
create role manager identified externally;##### --自动授予with admin option
create role manager2 not identified;##### --与clerk一样,无密码,自动授予with admin option
–角色修改密码
alter role clerk identified externally;
alter role sales not identified;
alter role manager identified by “vampire”;
–将部分系统权限赋予clerk角色
grant create session,create table,create view to clerk;
–查看clerk角色的所有权限
select * from role_sys_privs where role=‘CLERK’;
–将select any table系统权限、clerk角色赋予用户U2;
grant select any table,clerk to U2;
–查看U2用户所有的权限和角色
select * from user_role_privs where username=‘U2’; ##### --u2用户下查询
select * from dba_role_privs where grantee=‘U2’; ##### --sys用户查询
–查看当前角色有哪些权限
select * from role_sys_privs where role=‘RESOURCE’;
–查看有关角色clerk被授予了哪些用户:
select * from dba_role_privs rp where rp.granted_role =‘CLERK’;
–用with admin option的授权语句将manager角色赋予U1用户;
grant manager to U1 with admin option;
–查看U1的角色(ADMIN_OPTION为Yes的用户,可以将这一角色再授予其他用户或角色)
select * from dba_role_privs where grantee=‘U1’;
–查看U1用户的全部系统角色(在U1用户下查看)
select * from session_privs;
–=======建立默认角色
–将U3用户的所有角色都设置成非默认角色,即当用户U3登录系统时,通过角色赋予的任何系统权限都不能使用
alter user U3 default role none;
–将U3用户的所有角色全部设置成默认:
alter user u3 default role all;
–将U3用户的除了clerk角色的所有角色都设置成默认角色
alter user U3 default role all except clerk;
–=======激活和禁止角色
–禁止所有由角色赋予的系统权限;
set role none;##### --normal用户执行
–激活manager角色
set role manager identified by “vampire”;##### --normal用户执行
–激活除了manager角色以外的所有角色;
set role all except manager;##### --normal用户执行
–角色的回收和删除
–
select * from dba_roles where role in(‘CLERK’,‘SALES’,‘MANAGER’,‘MANAGER2’);
grant CLERK,SALES,MANAGER to u1,u2,u3;
select * from dba_role_privs where grantee in(‘U1’,‘U2’,‘U3’) order by grantee;
–回收角色CLERK、SALES从U1、U2
revoke clerk,sales from U1,U2;
select * from dba_role_privs where grantee in(‘U1’,‘U2’) order by grantee;
–将角色CLERK、SALES授予所有用户
grant CLERK,SALES to public;
select * from dba_role_privs where grantee in(‘U1’,‘U2’,‘U3’,‘PUBLIC’) order by grantee;
–收回角色CLERK、SALES从所有用户
revoke CLERK,SALES from public;
select * from dba_role_privs where grantee in(‘U1’,‘U2’,‘U3’,‘PUBLIC’) order by grantee;
–删除角色sales;
drop role sales;
select * from dba_roles where role in (‘CLERK’,‘SALES’,‘MANAGER’,‘MANAGER2’);
–=======例子
create role manager;
select * from dba_roles where role in(‘CLERK’,‘MANAGER’);
–将Scott用户表emp的select 权限赋予clerk角色:
GRANT select on scott.emp to clerk;
–将Scott用户表emp的增删改权限赋予角色manager:
grant update,delete,insert on scott.emp to manager;
–将角色clerk和manager赋予用户U1
grant clerk,manager to U3;
–登录SCOTT,查看角色clerk和manager所拥有的对象权限
select * from user_tab_privs_made;
–将manager角色改为使用口令标识:
alter role manager identified by “123”;
–将manager角色改为非默认角色
alter user U3 default role all except manager;
–用U3登录,查看是否有对Scott表的查询权限
select * from scott.emp where sal>=2000;
–用U3登录,试着修改Scott表中的数据
update scott.emp set sal=9999;
–用U3登录,激活角色manager
set role manager identified by “123”;