本帖最后由 zcs0237 于 2013-5-19 19:23 编辑
a.欢迎对本帖补充、建议、更正
b.测试环境rhel5.4+Ora10.2.0.1.0
c.为节省篇幅，部分输出结果做了精简
************************************************
第01部分 角色基础知识角色是非模式对象,它不由任可用户拥有，也不属于任何方案。01.1-角色是一个集合一、证明ROLE是一个Privilege Set
SQL> select grantee,privilege from dba_sys_privs where grantee='RESOURCE';GRANTEE
PRIVILEGE//查看resource角色拥有的权限-------------------- ------------------------------RESOURCE
CREATE TRIGGERRESOURCE
CREATE SEQUENCERESOURCE
CREATE TYPERESOURCE
CREATE PROCEDURERESOURCE
CREATE CLUSTERRESOURCE
CREATE OPERATORRESOURCE
CREATE INDEXTYPERESOURCE
CREATE TABLE8 rows selected.二、证明ROLE是一个Role Set(角色可以嵌套)
SQL> create role role5;Role created.
//创建新的角色role5SQL> select * from dba_roles where role='CONNECT';ROLE PASSWORD//证明resource是一个role--------------- --------RESOURCE NOSQL> grant connect to role5;Grant succeeded.
//将connect角色关联到role501.2-角色用来高效管理权限有一组人，他们的所需的权限是一样的，则可以：一、首先将角色赋给组内的各个用户
SQL>grant role1 to zcs1;SQL>grant role1 to zcs1 ;……二、其次针对角色进行管理(将权限、角色授给某个角色)
SQL>grant privilege1 to role1;SQL>grant privilege1 to role1;SQL>revoke privilege1from role1;……************************************************
第02部分 预定义角色一、预定义角色是系统自动创建的一些常用的角色
SQL> select count(*) from dba_roles; COUNT(*)----------33
//预定义角色的总数二、角色所包含的权限可以用以下语句查询：
sql>select * from role_sys_privs where role='角色名'; 三、兼容角色：CONNECT, RESOURCE,DBA
oracle建议用户自己设计数据库管理和安全的权限规划，而不要简单的使用这些预定角色。1、主要是简化数据库管理而提供的
对于DBA用户：直接授予dba角色。对于普通用户：一般授予connect, resource角色。 2、这些预定义角色主要是为了向后兼容，将来的版本中这些角色可能不会作为预定义角色
SQL> select * from dba_roles where role='DBA';ROLEPASSWORD------- --------DBA NO//通过此查询证明DBA是角色名02.1-Connect Role 临时用户，通常只赋予他们CONNECT role。一、查看CONNECT角色的权限
SQL> select grantee,privilege from dba_sys_privs where grantee='CONNECT';CONNECT
CREATE SESSION二、新建用户zcs3
SQL> create user zcs3 identified by zcs3;User created.SQL> conn zcs3/zcs3ERROR:ORA-01045: user ZCS3 lacks CREATE SESSION privilege; logon denied三、关联CONNECT角色到zcs3
角色就像权限：可将其授给用户或其它角色。SQL> conn / as sysdbaConnected.SQL> grant connect to zcs3; Grant succeeded. //关联connect角色到zcs3SQL> conn zcs3/zcs3Connected.SQL> select * from session_privs;PRIVILEGE---------------CREATE SESSIONSELECT ANY TABLE //查询当前会话的最终权限02.2-Resource Role 正式、可靠的用户，如开发人员正式的数据库用户可以授予RESOURCE role。一般创建用户时，如果没有特殊需求只要将resource和connect角色赋予用户即可。一、提供创建对象的权限
SQL> select grantee,privilege from dba_sys_privs where grantee='RESOURCE';GRANTEEPRIVILEGE------------ --------------------RESOURCE CREATE TRIGGER 触发器RESOURCE CREATE SEQUENCE序列RESOURCE CREATE TYPE类型RESOURCE CREATE PROCEDURE过程RESOURCE CREATE CLUSTER 簇RESOURCE CREATE OPERATORRESOURCE CREATE INDEXTYPE索引RESOURCE CREATE TABLE 表8 rows selected.二、也会授予UNLIMITED TABLESPACE系统权限
1、创建用户，指定密码
SQL> create user zcs identified by zcs;User created.SQL> select username,default_tablespace from dba_users where username='ZCS';USERNAME DEFAULT_TABLESPACE-------- --------------------ZCSUSERS2、关联connect、resource角色到zcs
SQL> grant connect,resource to zcs;Grant succeeded.SQL> select GRANTEE, GRANTED_ROLE from dba_role_privs where GRANTEE='ZCS';GRANTEEGRANTED_ROLE//查看用户所具有的角色------------ -------------ZCS
RESOURCEZCS
CONNECT 3、自动授予UNLIMITED TABLESPACE系统权限
此系统权限会覆盖所有的单个表空间限额，并向用户提供所有表空间（包括SYSTEM和SYSAUX）的无限制限额，这对数据库系统管理是一大隐患。SQL> select GRANTEE, PRIVILEGE from dba_sys_privs where GRANTEE='ZCS';GRANTEEPRIVILEGE//查询用户所具有的系统权限-------- ------------------ZCSUNLIMITED TABLESPACESQL> select TABLESPACE_NAME,USERNAME,BYTES,MAX_BYTES from dba_ts_quotas; TABLESPACE_NAME USERNAMEBYTESMAX_BYTES//查询表空间限制--------------- ---------- ---------- ----------SYSAUX
OLAPSYS16318464 -1SYSAUX
SYSMAN 50790400 -1SYSAUX
DMSYS
262144209715200SQL> conn zcs/zcsConnected.SQL> create table zcs(id int);Table created.02.3-DBA Role 除非真正需要，dba role(数据库管理员角色)权限不应随便授予那些不是很重要的用户。SQL> SELECT GRANTEE, GRANTED_ROLE FROM dba_role_privs WHERE granted_role = 'DBA'; GRANTEEGRANTED_ROLE//查看角色授予了哪些用户
------------ ------------SYS
DBASYSMAN DBAZCS1 DBASYSTEM DBASQL> select * from v$pwfile_users;USERNAME SYSDBA SYSOPER//查SYSDBA/SYSOPER权限的用户------------ ----------SYSTRUE TRUETEST TRUE FALSE一、DBA role拥有所有的系统权限，可以操作全体用户的任意基表(包括删除)SQL> select count(*) from dba_sys_privs where grantee='DBA';COUNT(*)---------- 160SQL> create user zcs1 identified by zcs1;User created.SQL> grant dba to zcs1;Grant succeeded.SQL> CONN ZCS1/ZCS1Connected.SQL> select count(*) from session_privs;COUNT(*)---------- 161SQL> SET PAGESIZE 999SQL> select * from SESSION_ROLES order by ROLE;ROLE------------------------------DBADELETE_CATALOG_ROLEEXECUTE_CATALOG_ROLEEXP_FULL_DATABASEGATHER_SYSTEM_STATISTICSHS_ADMIN_ROLEIMP_FULL_DATABASEJAVA_ADMINJAVA_DEPLOYOLAP_DBASCHEDULER_ADMINSELECT_CATALOG_ROLEWM_ADMIN_ROLEXDBADMINXDBWEBSERVICES15 rows selected.二、DBA role包括无限制的空间限额
SQL> conn zcs1/zcs1Connected.SQL> select GRANTEE, PRIVILEGE from dba_sys_privs where GRANTEE='ZCS1';GRANTEEPRIVILEGE------------ ----------------------------------------ZCS1
UNLIMITED TABLESPACESQL> select TABLESPACE_NAME,USERNAME,BYTES,MAX_BYTES from dba_ts_quotas where USERNAME='ZCS1';no rows selected
//zcs1无限额三、给其他用户授予各种权限的能力
一个具有DBA角色的用户可以撤消任何别的用户甚至别的DBA的CONNECT、RESOURCE 和DBA的其他权限。
************************************************
第03部分 自定义角色
修改（grant）角色关联的权限，则授予该角色所有的用户都会立即自动获得修过的权限。自定义角色一般是有 dba 来完成的，如果一般的用户想建立，则需要有 create role的系统权限。
03.1-建用户ZCS1(zcs1无角色,无权)SQL> create user zcs1 identified by zcs1;User created.//创建用户SQL> select USERNAME from dba_users where USERNAME='ZCS1';ZCS1
//说明zcs1是否存在SQL> select * from dba_sys_privs where GRANTEE='ZCS1';no rows selected //新建用户默认不拥有任何权限SQL> select * from dba_role_privs where GRANTEE='ZCS1';no rows selected//新建用户默认不拥有任何角色SQL> conn zcs1/zcs1ERROR:
//新建用户默认不能连接到数据ORA-01045: user ZCS1 lacks CREATE SESSION privilege; logon denied03.2-建角色ROLE1(ROLE1无权)SQL> create role role1;Role created.//创建无任何权限的角色SQL> SELECT role,password_required FROM dba_roles where ROLE='ROLE1';//验证新建角色是否已存在ROLE
PASSWORD-------------------- --------ROLE1
NOSQL> select grantee,privilege from dba_sys_privs where grantee='ROLE1'; //ROLE1角色不包含任何权限no rows selected03.3-ROLE1联ZCS1(zcs1角色=role1,无权)SQL> grant role1 to zcs1;Grant succeeded. //将角色授矛给用户SQL> select * from dba_role_privs where GRANTEE='ZCS1';GRANTEEGRANTED_ROLE ADM DEF------------ ------------ --- ---ZCS1 ROLE1NOYES //zcs1用户有角色SQL> select * from dba_sys_privs where GRANTEE='ZCS1';no rows selected //zcs1用户无权限SQL> conn zcs1/zcs1ERROR:
//此时zcs1还是不能登陆ORA-01045: user ZCS1 lacks CREATE SESSION privilege; logon denied03.4-Role1联create session(zcs1=create session)SQL> conn/ as sysdbaConnected. SQL> grant create session to role1;Grant succeeded.SQL> select grantee,privilege from dba_sys_privs where grantee='ROLE1';GRANTEEPRIVILEGE------------ ---------------ROLE1CREATE SESSIONSQL> conn zcs1/zcs1Connected.SQL> select * from SESSION_ROLES order by ROLE;ROLE1
//查看当前会话拥有的角色SQL> select * from USER_ROLE_PRIVS;USERNAME GRANTED_ROLE ADM DEF OS_-------- ------------ --- --- ---ZCS1 ROLE1NOYES NO//查看当前用户的角色SQL> conn / as sysdba; Connected. SQL> drop role role1; Role dropped. //删除角色SQL> select * from dba_sys_privs where GRANTEE='ZCS1';no rows selected//角色删除后，原来拥用该角色的用户不再拥有该角色及相应的权限。 ************************************************第04部分 开关自定义角色
SQL> show parameter MAX_ENABLED_ROLESmax_enabled_roles integer 150 //最大可生效角色数04.1-SYS永久减小ZCS的权限(默认角色)
1、核心命令
alter user zcs3 DEFAULT ROLE ALL EXCEPT RESOURCE;
alter user zcs3 DEFAULT ROLE ALL;
2、经测试有以下两点规律
Shutdown/startup以上结果不会自动恢复
必须用DBA用户alter user才能恢复正常
一、创建用户zcs3并授予resource,connect角色
SQL> grant resource,connect to zcs3;Grant succeeded. //只影响使用GRANT直接授予用户的角色SQL> conn zcs3/zcs3;Connected.SQL> conn / as sysdbaConnected.SQL> select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='ZCS3';GRANTEE GRANTED_ROLEDEF---------------------------------ZCS3RESOURCE YESZCS3CONNECTYES二、设置缺省的角色为NONE后，无法登陆
SQL> alter userzcs3 DEFAULT ROLE NONE;User altered.SQL> select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='ZCS3';GRANTEEGRANTED_ROLEDEFAULT_ROLE
------------------------ ------------
ZCS3RESOURCE NO //resource已禁用
ZCS3CONNECTNO //connect已禁用
SQL> conn zcs3/zcs3;
ERROR:
ORA-01045: user ZCS3 lacks CREATE SESSION privilege; logon denied
SQL> conn /as sysdba;
Connected.
三、重置角色后可以正常登陆
1、重启数据不会自动恢复正常
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
SQL>select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='ZCS3';
GRANTEEGRANTED_ROLE DEFAULT_ROLE
------------ ------------ ------------
ZCS3 RESOURCE NO
ZCS3 CONNECTNO
2、alter user手动重置正常
SQL> conn / as sysdba
Connected.
SQL> alter user zcs3 DEFAULT ROLE ALL;
User altered.
';SQL> select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='ZCS3';
GRANTEEGRANTED_ROLE DEFAULT_ROLE
------------ ------------ ------------
ZCS3 RESOURCE YES
ZCS3 CONNECTYES
SQL> CONN zcs3/zcs3;
Connected.
四、默认角色为除resource之外的所有角色
SQL> alter user zcs3 DEFAULT ROLE ALL EXCEPT RESOURCE;
User altered.
SQL> select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='ZCS3';
GRANTEEGRANTED_ROLE DEFAULT_ROLE
------------ ------------ ------------
ZCS3 RESOURCE NO //resource已禁用
ZCS3 CONNECTYES;
04.2-ZCS给自已临时减小权限
1、核心命令
set role none;set role all;set role role1,role2;2、经测试有以下两点规律
自已禁用自已的ROLE临时:set role all 或重新conn即恢复一、用SYS操作：关联权限到角色role1
1、创建role1,并关联create session系统权限
SQL> create role role1;Role created.SQL> grant create session to role1;Grant succeeded.SQL> select grantee,privilege from dba_sys_privs where grantee='ROLE1';GRANTEEPRIVILEGE------------ ----------------------------------------ROLE1CREATE SESSION2、创建role2,并关联select any table系统权限
SQL> create role role2;Role created.SQL> grant select any table to role2;Grant succeeded.SQL> select grantee,privilege from dba_sys_privs where grantee='ROLE2';
GRANTEEPRIVILEGE
------------ -----------------
ROLE2SELECT ANY TABLE
二、用SYS操作：关联角色role1到用户zcs
SQL> create user ZCS identified by ZCS;User created.//创建用户ZCSSQL> grant role1,role2 to ZCS;Grant succeeded. //关联role1,role2到ZCSSQL> select * from dba_role_privs where GRANTEE='ZCS';GRANTEEGRANTED_ROLE ADM DEF//查看ZCS拥有的角色------------ ------------ --- ---ZCS ROLE1NOYESZCS ROLE2NOYES三、用ZCS操作：二.结果的验证
SQL> conn ZCS/ZCSConnected.SQL> conn ZCS/ZCSConnected.SQL> select empno,ename,deptno,mgr from scott.emp where empno in(7499,7788); EMPNO ENAME
DEPTNOMGR---------- ---------- ---------- ----------7499 ALLEN
30 76987788 SCOTT
20 7566SQL> select * from SESSION_ROLES order by ROLE;ROLE
//查看当前会话拥有的角色--------------ROLE1ROLE2SQL> select USERNAME, GRANTED_ROLE from USER_ROLE_PRIVS;
USERNAME GRANTED_ROLE//查看当前会话拥有的角色
------------ ------------
ZCS ROLE1
ZCS ROLE2
SQL> select * from user_sys_privs;no rows selected //直接授权为空SQL> select * from session_privs;PRIVILEGE---------------CREATE SESSIONSELECT ANY TABLE
//查询当前会话的最终有效权限四、用ZCS操作：开关角色可暂时开关权限
1、set role禁用本用户拥有的角色
SQL> conn ZCS/ZCSSQL> set role none;Role set. //none=所有失效, all=所有生效SQL> select empno,ename,deptno,mgr from scott.emp where empno in(7499,7788);ERROR at line 1:ORA-00942: table or view does not existSQL> select * from session_privs;no rows selected //查询当前会话的最终权限2、恢复方法1：重新conn即恢复
SQL> conn zcs/zcsConnected.SQL> select empno,ename,deptno,mgr from scott.emp where empno in(7499,7788); EMPNO ENAME
DEPTNOMGR---------- ---------- ---------- ----------7499 ALLEN
30 76987788 SCOTT
20 75662、恢复方法2：重新set role 即恢复
SQL> set role role1,role2;Role set.
//使role1,role2生效SQL> select * from session_privs;PRIVILEGE
//查询当前会话的最终权限-------------------CREATE SESSIONSELECT ANY TABLE04.3-带密码的角色 SQL> create role role5 identified by roles;Role created.SQL> alter role role5 not identified;Role altered.SQL> alter role role5 identified by role5;Role altered.SQL> set role role5;ERROR at line 1:ORA-01979: missing or invalid password for role 'ROLE5'SQL> set role role5 identified by role5;Role set.//使用带有口令的role1生效
********************END***************************
oracle知识点精简总结系列持续增加中：
Oracle用户及用户配置文件精简总结
http://www.itpub.net/thread-1775065-1-1.html
Oracle的登陆认证方式精简总结
http://www.itpub.net/thread-1774785-1-1.html
实例恢复相关原理精简总结
http://www.itpub.net/thread-1761630-1-1.html
Oracle权限精简总结
http://www.itpub.net/thread-1775562-1-1.html
Oracle角色精简总结
http://www.itpub.net/thread-1775924-1-1.html
OLTP与OLAP的区别精简总结
http://www.itpub.net/thread-1777924-1-1.html无GUI智能部署oracle脚本及视频
http://www.itpub.net/thread-1778735-1-1.html
有哪些Oracle进程？消耗了多少存？
http://www.itpub.net/thread-1780654-1-1.html
|
