风哥教程

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

【oracle权限管理】ORA-01536 After Revoking DBA Role

[复制链接]
内容发布:风哥| 发布时间:2013-12-24 14:59:28
Oracle回收DBA权限相关问题:
ORA-01536 After Revoking DBA Role [ID 465737.1]
Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.3
This problem can occur on any platform.
Symptoms


ORA-01536: space quota exceeded for tablespace '<Tablespace_Name>'
After revoking DBA or Resource Role from a user

Example:
SQL> conn /as sysdba
Connected.
SQL> create user testrights identified by testos;
User created.
SQL> grant connect, resource to testrights;
Grant succeeded.
SQL> connect testrights/testos;
Connected.

SQL> CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL
, CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS" STORAGE ( INITIAL 64M) ;
Table created.

SQL> conn /as sysdba
Connected.
SQL> grant dba to testrights;
Grant succeeded.
SQL> revoke dba from testrights;
Revoke succeeded.
SQL> show user
USER is "SYS"
SQL> drop table testrights.testtab;
Table dropped.
SQL> conn testrights/testos;
Connected.
SQL> CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL
, CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS"  STORAGE ( INITIAL 64M) ;

CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL ,
CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS"
STORAGE ( INITIAL 64M)
*
ERROR at line 1:
ORA-1536: space quota exceeded for tablespace 'USERS'

SQL> conn /as sysdba
Connected.
SQL> grant connect, resource to testrights;
Grant succeeded.

SQL> conn testrights/testos;
Connected.
SQL>
SQL> CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL , CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS"
STORAGE ( INITIAL 64M) ;

Table created.
Cause

This issue has been discussed in Bug 6494010.
The behavior. seen in the above example is expected and not a bug

When roles were first introduced into Oracle in 7.0, the old Oracle V6 privileges of  RESOURCE and DBA were migrated to use the new role functionality. But because  the RESOURCE and DBA roles are not allowed to be granted UNLIMITED  TABLESPACE, in order to preserve the backwards compatibility with V6, the  parser automatically transforms statements such that "grant resource to abc" automatically becomes "grant resource, unlimited tablespace to abc" and  "revoke resource from abc" automatically becomes "revoke resource, unlimited  tablespace from abc". The same is true when granting and revoking the DBA  role. This behaviour used to be well documented in the SQL reference guide which read:

Note: If you grant or revoke the RESOURCE or DBA role to or from a  user, Oracle implicitly grants or revokes the UNLIMITED TABLESPACE system privilege to or from the user.
Solution

To Resolve this issue you need to :

1] Grant DBA or Resource Role back to the user from whom it was revoked.


上一篇:oracle权限管理Revoking DBA Roles Revokes UNLIMITED TABLESPACE from the user
下一篇:Oracle imp恢复数据库时遭遇ORA-00439: feature not enabled: Partitioning
专业提供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网页
快速回复 返回顶部 返回列表