有关role的一点总结！

最新推荐文章于 2024-09-30 09:09:52 发布

clt3617

最新推荐文章于 2024-09-30 09:09:52 发布

阅读量190

点赞数

文章标签：数据库

没有role完全可以控制权限的使用，只是不够灵活和方便而已，那么角色的作用到底是什么呢？简单的归结为2条：
1）实现批量授权
2）动态控制权限

[@more@]

1、角色的作用
a）实现批量授权
b）动态控制权限

2、使role生效或者失效
SQL> create role rl1;

角色已创建。
--可以为role设置password，防止他人随意设置role
SQL> create role rl2 identified by rl2 ;

角色已创建。

SQL> select * from dba_role_privs where grantee='XYS';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
XYS DBA NO YES
XYS RL1 YES YES
XYS RL2 YES YES
--可以动态的设置role使其失效,不过当把带口令的role设置为none时
不要求输入password
SQL> set role none;

角色集

SQL> select * from dba_role_privs where grantee='XYS';
select * from dba_role_privs where grantee='XYS'
*
第 1 行出现错误:
ORA-00942: 表或视图不存在

SQL> create table t(id int);
create table t(id int)
*
第 1 行出现错误:
ORA-01031: 权限不足
--===================================

SQL> show user
USER 为 "XYS"
--当把带口令的role启用时需要输入口令
SQL> set role all;
set role all
*
第 1 行出现错误:
ORA-01979: 角色 'RL2' 的口令缺失或无效

--all可以和except结合使用，none不能和except结合使用
SQL> set role all except rl2;

角色集

SQL> select * from dba_role_privs where grantee='XYS';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
XYS DBA NO YES
XYS RL1 YES YES
XYS RL2 YES YES
--但是all不能和identified by结合使用
SQL> set role all identified by rl2;
set role all identified by rl2
*
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束

--不过列举出来所有role来代替all则可以和identified by结合使用，
需要注意的时尽管没有为dba，rl1设置password
SQL> set role dba,rl1,rl2 identified by rl2;

角色集
--none不能和except结合使用
SQL> set role none except rl2;
set role none except rl2
*
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束

SQL> set role all except rl2;

角色集

SQL> select * from session_roles;

ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
RL1

ROLE
------------------------------
CONNECT
RESOURCE

已选择13行。

SQL> set role none;

角色集
--role失效之后，该session就没有了role所具有的所有权限，
但是用户xys通过其他窗口登录oracle时依然具有role的权限
SQL> select * from session_roles;

未选定行

SQL> show user
USER 为 "XYS"
SQL>
再开一个sqlplus窗口，建立其他session：
因为具有dba role，所以列出来的权限比较多
C:>sqlplus xys/manager

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 6月 27 14:11:01 2008

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE USER

PRIVILEGE
----------------------------------------
BECOME USER
ALTER USER
DROP USER
CREATE ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
BACKUP ANY TABLE
DROP ANY TABLE

PRIVILEGE
----------------------------------------
LOCK ANY TABLE
COMMENT ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
CREATE CLUSTER
CREATE ANY CLUSTER
ALTER ANY CLUSTER
DROP ANY CLUSTER
CREATE ANY INDEX

PRIVILEGE
----------------------------------------
ALTER ANY INDEX
DROP ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE SEQUENCE

PRIVILEGE
----------------------------------------
CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE

PRIVILEGE
----------------------------------------
AUDIT ANY
ALTER DATABASE
FORCE TRANSACTION
FORCE ANY TRANSACTION
CREATE PROCEDURE
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER

PRIVILEGE
----------------------------------------
ALTER ANY TRIGGER
DROP ANY TRIGGER
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
ALTER RESOURCE COST
ANALYZE ANY
GRANT ANY PRIVILEGE
CREATE MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
ALTER ANY MATERIALIZED VIEW

PRIVILEGE
----------------------------------------
DROP ANY MATERIALIZED VIEW
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE TYPE
CREATE ANY TYPE
ALTER ANY TYPE
DROP ANY TYPE
EXECUTE ANY TYPE
UNDER ANY TYPE
CREATE LIBRARY
CREATE ANY LIBRARY

PRIVILEGE
----------------------------------------
ALTER ANY LIBRARY
DROP ANY LIBRARY
EXECUTE ANY LIBRARY
CREATE OPERATOR
CREATE ANY OPERATOR
DROP ANY OPERATOR
EXECUTE ANY OPERATOR
CREATE INDEXTYPE
CREATE ANY INDEXTYPE
ALTER ANY INDEXTYPE
DROP ANY INDEXTYPE

PRIVILEGE
----------------------------------------
UNDER ANY VIEW
QUERY REWRITE
GLOBAL QUERY REWRITE
EXECUTE ANY INDEXTYPE
UNDER ANY TABLE
CREATE DIMENSION
CREATE ANY DIMENSION
ALTER ANY DIMENSION
DROP ANY DIMENSION
MANAGE ANY QUEUE
ENQUEUE ANY QUEUE

PRIVILEGE
----------------------------------------
DEQUEUE ANY QUEUE
CREATE ANY CONTEXT
DROP ANY CONTEXT
CREATE ANY OUTLINE
ALTER ANY OUTLINE
DROP ANY OUTLINE
ADMINISTER RESOURCE MANAGER
ADMINISTER DATABASE TRIGGER
MERGE ANY VIEW
ON COMMIT REFRESH
RESUMABLE

PRIVILEGE
----------------------------------------
SELECT ANY DICTIONARY
DEBUG CONNECT SESSION
DEBUG ANY PROCEDURE
FLASHBACK ANY TABLE
GRANT ANY OBJECT PRIVILEGE
CREATE EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT
ALTER ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
EXECUTE ANY EVALUATION CONTEXT
CREATE RULE SET

PRIVILEGE
----------------------------------------
CREATE ANY RULE SET
ALTER ANY RULE SET
DROP ANY RULE SET
EXECUTE ANY RULE SET
EXPORT FULL DATABASE
IMPORT FULL DATABASE
CREATE RULE
CREATE ANY RULE
ALTER ANY RULE
DROP ANY RULE
EXECUTE ANY RULE

PRIVILEGE
----------------------------------------
ANALYZE ANY DICTIONARY
ADVISOR
CREATE JOB
CREATE ANY JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER
SELECT ANY TRANSACTION
DROP ANY SQL PROFILE
ALTER ANY SQL PROFILE
ADMINISTER SQL TUNING SET

PRIVILEGE
----------------------------------------
ADMINISTER ANY SQL TUNING SET
CREATE ANY SQL PROFILE
MANAGE FILE GROUP
MANAGE ANY FILE GROUP
READ ANY FILE GROUP
CHANGE NOTIFICATION
CREATE EXTERNAL JOB

已选择161行。

SQL>
3、使user的role失效或者生效
SQL> show user
USER 为 "XYS"
SQL> create user test identified by test;

用户已创建。

SQL> grant rl1,rl2 to test;

授权成功。

SQL> select * from dba_role_privs where grantee in ('RL1','RL2');

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
RL1 CONNECT NO YES
RL1 RESOURCE NO YES

SQL> connect test/test
已连接。
--不能创建表，没有使用表空间users的权限，说明unlimited tablespace
没有随着resource被授予role RL1而被授予RL1（因为unlimited tablespace不能被授予role）
,为什么？暂时不知道。当然TEST也没有unlimited tablespace
权限
SQL> create table t(id int);
create table t(id int)
*
第 1 行出现错误:
ORA-01950: 对表空间 'USERS' 无权限

SQL> connect xys/manager
已连接。
--不能把系统权限unlimited tablespace授予role
SQL> grant unlimited tablespace to rl2;
grant unlimited tablespace to rl2
*
第 1 行出现错误:
ORA-01931: 无法将 UNLIMITED TABLESPACE 授予角色

SQL> grant unlimited tablespace to test;

授权成功。

SQL> select * from dba_role_privs where grantee='TEST';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST RL1 NO YES
TEST RL2 NO YES

SQL> select * from dba_sys_privs where grantee='TEST';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TEST UNLIMITED TABLESPACE NO

SQL> show user
USER 为 "XYS"
SQL> connect test/test
已连接。
SQL> create table t(id int);

表已创建。

SQL> connect xys/manager
已连接。
SQL> alter user test default role none;

用户已更改。

SQL> connect test/test
ERROR:
ORA-01045: 用户 TEST 没有 CREATE SESSION 权限; 登录被拒绝

警告: 您不再连接到 ORACLE。
SQL> connect xys/manager
已连接。
SQL> select * from dba_role_privs where grantee='TEST';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST RL1 NO NO
TEST RL2 NO NO
--此时为什么不需要输入rl2的口令？莫非是因为rl2是xys用户创建的？
其实不是，再来看看alter user xys的default role的效果，发现
通过alter user ...default role...也不需要输入口令，这里不知道
是否是orale的漏洞还是这样设计另有其他目的，不得而知，这样看来
通过这种方法可以跨过role的password！
SQL> alter user test default role all;

用户已更改。
SQL> alter user xys default role none;

用户已更改。

SQL> alter user xys default role all;

用户已更改。

SQL> set role none;

角色集

SQL> set role all;
set role all
*
第 1 行出现错误:
ORA-01979: 角色 'RL2' 的口令缺失或无效

--通过set role all就需要输入rl2的口令，而通过alter user ...
default role all就不需要输入口令！
SQL>

SQL> select * from dba_role_privs where grantee='TEST';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST RL1 NO YES
TEST RL2
NO YES
--同样none不能和except结合使用
SQL> alter user test default role none except rl2;
alter user test default role none except rl2
*
第 1 行出现错误:
ORA-00922: 选项缺失或无效

SQL> alter user test default role none;

用户已更改。
--all可以和except结合使用
SQL> alter user test default role all except rl2;

用户已更改。

SQL> select * from dba_role_privs where grantee='TEST';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST RL1 NO YES
TEST RL2 NO NO

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1006184/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1006184/