你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle权限管理:After Revoking DBA Role

[日期:2012-07-28] 来源:Linux社区  作者:mfkqwyc86 [字体: ]

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 ''
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相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

linux
相关资讯       Oracle权限管理 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款