ORACLE11G用户在任何表空间都有创建表的权限问题

oracle 11.2.0.1.0 32bit

1：创建用户aaa，给其connect和resource角色，但回收unlimited tablespace权限：

SQL> create user aaa identified by aaa default tablespace users;

User created.

SQL> grant connect,resource to aaa;

Grant succeeded.

SQL> revoke unlimited tablespace from aaa;

Revoke succeeded.

SQL> select * from role_sys_privs where role='RESOURCE';

ROLE                               PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE SEQUENCE                                NO
RESOURCE                       CREATE TRIGGER                                NO
RESOURCE                       CREATE CLUSTER                                NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE TYPE                                NO
RESOURCE                       CREATE OPERATOR                                NO
RESOURCE                       CREATE TABLE                                NO
RESOURCE                       CREATE INDEXTYPE                         NO

8 rows selected.

SQL> alter user aaa quota unlimited on users;

User altered.

2：现在的问题是：aaa在任何表空间都有创建表的权限
[oracle@master /]$ sqlplus aaa

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 6 18:38:25 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create table test1(id int) tablespace users;

Table created.

SQL> create table test2 (id int) tablespace system;

Table created.

SQL> create table test3(id int) tablespace zaodian;

Table created.

3：表test1可以正常插入数据，test2和test3都无法插入数据，这是正常的：

SQL> insert into test1 values(1);

1 row created.

SQL> insert into test2 values(1);
insert into test2 values(1)
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'

SQL> insert into test3 values(1);
insert into test3 values(1)
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'ZAODIAN'

 

导致这样的情况是：

ORACLE11G新特性：deferred_segment_creation 默认为true导致的，这个参数使创建表的时候，并不会使用任何的segment，当真正有数据插入的时候才会使用相关segment，所以会导致可以在任何表空间建立表的假象，但插入数据时会报错无权限

 

解决此问题： alter system set deferred_segment_creation=false 即可。