最近在做数据治理的过程中，回收了部分账号的权限，因为角色resource里拥有create table的权限，所以我想回收resource角色。例如，对于test账号，收回其创建表的权限，就收回了授予其的resource的角色，结果不到几小时，support人员就反馈这个账号遇到了ora-01536错误。开始还有点懵，后面梳理清楚后，才感慨自己踩了一个大坑。下面简单的重新构造、模拟这样的一个案例。
sql> select * from v$version;
banner
----------------------------------------------------------------
oracle database 10g release 10.2.0.5.0 - 64bit production
pl/sql release 10.2.0.5.0 - production
core 10.2.0.5.0 production
tns for linux: version 10.2.0.5.0 - production
nlsrtl version 10.2.0.5.0 - production
sql>create tablespace tbs_test_data
datafile '/u03/oradata/gps/tbs_test_data.dbf'
size 200m
extent management local
segment space management auto online;
sql> create user test identified by "test#1232134$#3" default tablespace tbs_test_data temporary tablespace temp;
user created.
sql> grant connect, resource to test;
grant succeeded.
sql> select * from dba_sys_privs where grantee='test';
grantee privilege adm
------------------------------ ---------------------------------------- ---
test unlimited tablespace no
sql> select * from dba_role_privs where grantee='test';
grantee granted_role adm def
------------------------------ ------------------------------ --- ---
test resource no yes
test connect no yes
sql> select * from dba_sys_privs where grantee='resource';
grantee privilege adm
------------------------------ ---------------------------------------- ---
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表
sql> show user;
user is "test"
sql> create table test
2 as
3 select * from all_objects;
table created.
sql> select count(*) from test;
count(*)
----------
34859
sql>
然后收回账号test的resource角色，如下所示：
sql> show user;
user is "sys"
sql> revoke resource from test;
revoke succeeded.
sql> select * from dba_sys_privs where grantee='test';
no rows selected
然后此时test做dml操作就会报ora-01536错误，如下
sql> show user;
user is "test"
sql> insert into test
2 select * from test;
insert into test
*
error at line 1:
ora-01536: space quota exceeded for tablespace 'tbs_test_data'
那么为什么出现这种情况呢？ 其实刚开始我也有点懵，检查表空间发现表空间正常，检查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被收回，就出现问题了。
解决这个问题也比较简单，设置账号使用表空间的配额限制或不限制用户使用表空间，如下所示
grant unlimited tablespace to test;
或
alter user test quota unlimited on tbs_test_data;
|
ora-01536 after revoking dba role (doc id 465737.1) |
to bottom |
|
|
in this document
applies to: oracle database – enterprise edition – version 8.1.7.4 to 11.2.0.4 [release 8.1.7 to 11.2] symptoms
example: sql> conn /as sysdba sql> create table “testrights”.”testtab” ( “testfield” varchar2(200) not null sql> conn /as sysdba create table “testrights”.”testtab” ( “testfield” varchar2(200) not null , sql> conn /as sysdba sql> conn testrights/testos; table created. cause this issue has been discussed in bug 6494010. 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:
solution to resolve this issue you need to : 1] grant dba or resource role back to the user from whom it was revoked. references bug:6494010 – ora-01536 after granting,revoking role dba |
参考资料：
ora-01536 after revoking dba role (doc id 465737.1)