风哥教程

培训 . 交流 . 分享
Make progress together!

oracle权限管理Revoking DBA Roles Revokes UNLIMITED TABLESPACE from the user

[复制链接]
内容发布:风哥| 发布时间:2013-12-24 14:57:11
Oracle回收DBA权限相关问题:
Revoking DBA or RESOURCE Roles Revokes UNLIMITED TABLESPACE from the User [ID 1084014.6]
Applies to:

Oracle Server - Enterprise Edition
Information in this document applies to any platform.
Checked for relevance on 30-Dec-2011
Symptoms

Revoking either RESOURCE or DBA roles revokes the UNLIMITED TABLESPACE privilege.

SQL> connect system/manager ---> this will be referred to as Session 1
Connected.

SQL> create user abc identified by abc;
Statement processed.

SQL> grant connect, resource to abc;
Statement processed.

SQL> connect abc/abc ---> this will be referred to as Session 2

SQL> create table test1 (c1 number);
Table created.



From another session connected as SYSTEM:

Session 1(SYSTEM):


SQL> grant dba to abc;
Statement processed.

SQL> revoke dba from abc;
Statement processed.

Session 2(ABC):


SQL> create table test2(c1 number);
create table test2(c1 number)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'SYSTEM'




When the dba privilege is revoked, the quota resources from any tablespace are lost although they were acquired earlier via another role(RESOURCE).

Re granting the RESOURCE role to the user, will allow the user to allocate space in the tablespaces.

Session 1(SYSTEM):

SQL> grant resource to abc;
Statement processed.

Session 2(ABC):

SQL> create table test2(c1 number);
Table created.
Cause

Whenever an user is granted the RESOURCE and DBA roles these will be reflected in the  DBA_ROLE_PRIVS view. Additionally a new privilege will be added to DBA_SYS_PRIVS. This additional privilege is  UNLIMITED TABLESPACE .
Whenever one of the above two roles is revoked the UNLIMITED TABLESPACE privilege is automatically revoked as well. This is the expected behaviour.
Solution

The problem can be solved by either granting the RESOURCE role once again, granting quota on specific tablespaces this user has to work with or by granting directly the UNLIMITED TABLESPACE privilege.
SQL> grant RESOURCE to ABC;
SQL> alter user ABC quota unlimited on TOOLS;
SQL> grant UNLIMITED TABLESPACE to ABC;


上一篇:oracle dataguard主备库切换后出现FAL[server]: Fail to queue the whole FAL ...
下一篇:【oracle权限管理】ORA-01536 After Revoking DBA Role
专业提供Oracle/MySQL/NoSQL/Linux数据库培训与技术支持服务,QQ号:113257174
关注风哥教程微信公众号itpux_com  ,了解本站最新技术资料的分享.

欢迎加QQ群,提供超多高质量Oracle/Unix/Linux技术文档与视频教程的下载。

Oracle/MySQL/Linux群4-5:189070296  150201289  
Oracle/MySQL/Linux群6-8:244609803   522261684   522651731
备注:请勿重复加群,另请注明 from itpux
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

热门文章教程

  • 实战PHP与MySQL权威指南PDF电子书下载
  • 大数据技术与应用入门培训教程(电子版下载
  • Oracle 12cR2 九大新功能全面曝光_详解云数
  • Oracle OCP认证考试IZ0-053题库共712题数据
  • MySQL5权威指南(第3版)PDF电子版下载
  • 风哥Oracle数据库巡检工具V1.0(附2.6网页
快速回复 返回顶部 返回列表