最近在做数据治理的过程中，回收了部分账号的权限，因为角色RESOURCE里拥有CREATE TABLE的权限，所以我想回收RESOURCE角色。例如，对于TEST账号，收回其创建表的权限，就收回了授予其的RESOURCE的角色，结果不到几小时，SUPPORT人员就反馈这个账号遇到了ORA-01536错误。开始还有点懵，后面梳理清楚后，才感慨自己踩了一个大坑。下面简单的重新构造、模拟这样的一个案例。
SQL> select * from v$version;
BANNER
TEST UNLIMITED TABLESPACE NO
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='TEST';
GRANTEE GRANTED_ROLE ADM DEF
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
8 rows selected.
用账号TEST登录数据库，创建了一个test表
然后收回账号TEST的RESOURCE角色，如下所示：
然后此时TEST做DML操作就会报ORA-01536错误，如下
那么为什么出现这种情况呢？ 其实刚开始我也有点懵，检查表空间发现表空间正常，检查RESOURCE角色，发现里面没有关于表空间的配额限制。怎么回收RESOURCE角色，就整出这么一档子事呢？那么到底是怎么一回事呢，直到我看到Doc ID 465737.1才豁然开朗。
其实细心的人应该也有所发现（上面截图），如果您授予或撤销用户的 RESOURCE 或 DBA 角色，ORACLE会隐式授予或撤销该用户的 UNLIMITED TABLESPACE 系统权限。Doc ID 465737.1中介绍，其实当角色在Oracle 7.0 中首次引入时，RESOURCE 和 DBA 的权限从旧的Oracle V6中迁移到新的角色中。 但是由于不允许为 RESOURCE 和 DBA 角色授予 UNLIMITED TABLESPACE权限，为了保持与Oracle V6版本的向后兼容性，解析器会自动将语句转换为”grant resource to abc “自动变为”grant resource,unlimited tablespace to abc “并且将”revoke resource from abc “自动变为”revoke resource, unlimited tablespace from abc “。 授予和撤销 DBA 角色时也是如此。 也就是说UNLIMITED TABLESPACE的系统权限已经被硬编码到RESOURCE角色。而我们创建用户时，没有额外授予用户关于表空间使用配额。所以一旦系统权限UNLIMITED TABLESPACE被收回，就出现问题了。
解决这个问题也比较简单，设置账号使用表空间的配额限制或不限制用户使用表空间，如下所示
ORA-01536 After Revoking DBA Role (Doc ID 465737.1)
To Bottom
In this Document
Symptoms
Cause
Solution
References
Oracle Database – Enterprise Edition – Version 8.1.7.4 to 11.2.0.4 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
ORA-01536: space quota exceeded for tablespace ‘
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.
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.
To Resolve this issue you need to :
1] Grant DBA or Resource Role back to the user from whom it was revoked.
BUG:6494010 – ORA-01536 AFTER GRANTING,REVOKING ROLE DBA
参考资料：
ORA-01536 After Revoking DBA Role (Doc ID 465737.1)
Original: https://www.cnblogs.com/kerrycode/p/14861386.html
Author: 潇湘隐者
Title: ORA-01536: space quota exceeded for tablespace案例
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/546406/
转载文章受原作者版权保护。转载请注明原作者出处！