ora-01950 "no privileges on tablespace '%s'"一例

现象：插入数据少量数据时可以正常插入，当插入大量数据则报错，报错信息为对表空间无权限
背景：原本这个库上的普通用户授予的是dba权限，存在安全隐患，就回收了dba权限。在排查过程中已授予了owner用户权限，操作使用的也是这个用户，仍然报错。最后在查看索引信息时，发现存在其他用户创建的索引。
分析：先查看下官方的报错信息：

oracle@oel:/home/oracle>oerr ora 01950
01950, 00000, "no privileges on tablespace '%s'"
// *Cause:  User does not have privileges to allocate an extent in the
//          specified tablespace.
// *Action: Grant the user the appropriate system privileges or grant the user
//          space resource on the tablespace.

可以从上面看出，这里的权限实际是缺少分配extent的权限，少量的数据的插入修改的是已分配的extent，当插入的数据量较大，需要分配新的extent就报错了。
经过进一步查看，发现这个表上，另外一个用户建了一个索引，实际的真实原因是这个索引缺少权限，但是在报错的时候并不能正确的提示出来。另外resource权限并不包含unlimited tablespace权限。

以下进行测试验证：
用户test创建表作为表的owner，并进行修改操作。用户test02在测试表上创建索引。

1，创建测试表
TEST@regan1> create table test tablespace test as select * from sys.dba_objects;

Table created.

TEST@regan1> create index idx_test_01 on test(OBJECT_NAME) tablespace test;

Index created.

TABLESPACE_NAME      TABLESPACE_T SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
-------------------- ------------ ------------ ---------- ------------- ------------ -------------
USERS                PERMANENT            1399     179040          1330        95.05            69
TEST                 PERMANENT             200      25600             4         1.94           196

2，创建测试用户
SYS@regan1> create user test02 identified by test;

User created.

SYS@regan1> select username,default_tablespace from dba_users where username like 'TEST%';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST                           TEST
TEST02                         USERS

SYS@regan1> grant connect to test02;

Grant succeeded.

SYS@regan1> grant resource to test02;

Grant succeeded.



3，创建测试用户下索引
TEST@regan1> grant index on test to test02;

Grant succeeded.

SYS@regan1> grant unlimited tablespace to test02;

Grant succeeded.

TEST02@regan1>  create index idx_test_02 on test.test(OBJECT_ID) tablespace test;

Index created.--这里test02用户在test用户的表上创建了一个索引

TEST@regan1> select index_name,tablespace_name,status from dba_indexes where table_name='TEST';

INDEX_NAME                     TABLESPACE_NAME      STATUS
------------------------------ -------------------- --------
IDX_TEST_01                    TEST                 VALID
IDX_TEST_02                    TEST                 VALID

4，查看索引extent
TEST@regan1> select OWNER,EXTENT_ID,BYTES/1024 from dba_extents where SEGMENT_NAME='IDX_TEST_02';


OWNER                           EXTENT_ID BYTES/1024
------------------------------ ---------- ----------
TEST02                                  0         64
TEST02                                  1         64
TEST02                                  2         64
TEST02                                  3         64
TEST02                                  4         64


5，插入测试
TEST@regan1> insert into test select * from test;

14082 rows created.

TEST@regan1> COMMIT;

Commit complete.

TEST@regan1> select OWNER,EXTENT_ID,BYTES/1024 from dba_extents where SEGMENT_NAME='IDX_TEST_02';

OWNER                           EXTENT_ID BYTES/1024
------------------------------ ---------- ----------
TEST02                                  0         64
TEST02                                  1         64
TEST02                                  2         64
TEST02                                  3         64
TEST02                                  4         64
TEST02                                  5         64
TEST02                                  6         64
TEST02                                  7         64
TEST02                                  8         64

9 rows selected.


6，授予dba权限并回收
SYS@regan1> select * from dba_sys_privs where grantee='TEST02';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
TEST02                         UNLIMITED TABLESPACE                     NO

SYS@regan1> grant dba to test02;

Grant succeeded.

SYS@regan1> revoke dba from test02;

Revoke succeeded.

SYS@regan1> select * from dba_sys_privs where grantee='TEST02';

no rows selected--这里验证了回收dba权限时回收了UNLIMITED TABLESPACE全新

7，插入测试
TEST@regan1> insert into test select * from test;
insert into test select * from test
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TEST'
--这是test用户插入数据时会报错，但是报错信息只提示了对test表空间无权限。test用户对test表空间有权限，实际是test02在test的表上创建了索引，而test02对test表空间无权限。

TEST@regan1> select OWNER,EXTENT_ID,BYTES/1024 from dba_extents where SEGMENT_NAME='IDX_TEST_02';

OWNER                           EXTENT_ID BYTES/1024
------------------------------ ---------- ----------
TEST02                                  0         64
TEST02                                  1         64
TEST02                                  2         64
TEST02                                  3         64
TEST02                                  4         64
TEST02                                  5         64
TEST02                                  6         64
TEST02                                  7         64
TEST02                                  8         64

9 rows selected.

8，授予配额或权限
SYS@regan1> alter user test02 quota  unlimited on test;

User altered.

SYS@regan1> select * from dba_ts_quotas where username='TEST02';

TABLESPACE_NAME      USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
-------------------- ------------------------------ ---------- ---------- ---------- ---------- ---
TEST                 TEST02                             589824         -1         72         -1 NO

9，插入测试
TEST@regan1> insert into test select * from test;

28173 rows created.

TEST@regan1> select OWNER,EXTENT_ID,BYTES/1024 from dba_extents where SEGMENT_NAME='IDX_TEST_02';

OWNER                           EXTENT_ID BYTES/1024
------------------------------ ---------- ----------
TEST02                                  0         64
TEST02                                  1         64
TEST02                                  2         64
TEST02                                  3         64
TEST02                                  4         64
TEST02                                  5         64
TEST02                                  6         64
TEST02                                  7         64
TEST02                                  8         64
TEST02                                  9         64
TEST02                                 10         64
TEST02                                 11         64
TEST02                                 12         64
TEST02                                 13         64
TEST02                                 14         64
TEST02                                 15         64
TEST02                                 16       1024

17 rows selected.

TEST@regan1> rollback;

Rollback complete.

TEST@regan1> select OWNER,EXTENT_ID,BYTES/1024 from dba_extents where SEGMENT_NAME='IDX_TEST_02';


OWNER                           EXTENT_ID BYTES/1024
------------------------------ ---------- ----------
TEST02                                  0         64
TEST02                                  1         64
TEST02                                  2         64
TEST02                                  3         64
TEST02                                  4         64
TEST02                                  5         64
TEST02                                  6         64
TEST02                                  7         64
TEST02                                  8         64
TEST02                                  9         64
TEST02                                 10         64
TEST02                                 11         64
TEST02                                 12         64
TEST02                                 13         64
TEST02                                 14         64
TEST02                                 15         64
TEST02                                 16       1024

17 rows selected.--分配一定的extent

SYS@regan1> alter user test02 quota 0 on test;

User altered.

SYS@regan1> select * from dba_ts_quotas where username='TEST02';

no rows selected

TEST@regan1> insert into test select * from test;

28164 rows created.--第一次插入时由于使用的已分配的extent，所以成功了。

TEST@regan1> insert into test select * from test;
insert into test select * from test
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TEST'--第二次插入由于需要新的extent，但是由于缺少权限，这里插入失败了。

小结：
这个案例发生的主要原因在于权限分离没有做好。
这里test用户作为表的owner，那么表的创建，以及相关的索引对象的创建应该都要由owner用户来执行。
其他用户则应该只能拥有这些表上数据的增删改查权限，而没有对数据库对象的权限。
由于dba权限过大，也不该图省事，直接把dba权限直接授予普通用户。
Oracle的报错信息有些时候也不一定能很完整的展现错误原因，比如这里如果报错是user ‘TEST02’ have no privileges on tablespace ‘TEST’ ，这样的话在排查的时候能更快的定位到原因所在。