ORA-01536: space quota exceeded for tablespace案例

 

最近在做数据治理的过程中，回收了部分账号的权限，因为角色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

	symptoms
	cause
	solution
	references

 

applies to:

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.

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.

references

bug:6494010 - ora-01536 after granting,revoking role dba

 

 

 

 参考资料：

 

ora-01536 after revoking dba role (doc id 465737.1)