为什么用户在任何表空间下都有建立表的权限？

wonder

oracle 11.2.0.1.0

创建用户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.

现在的问题是：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.

表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'

请问，为什么用户aaa可以在system，zaodian表空间上有创建表的权限？ 谢谢！

xifenfei

11g 你创建表，默认不创建segment的，所以你在插入数据的时候，创建segment的失败，导致插入失败
deferred_segment_creation 这个设置为false再试试看

[ 本帖最后由 xifenfei 于 2012-6-6 18:48 编辑 ]

wonder

原帖由 xifenfei 于 2012-6-6 18:47 发表
11g 你创建表，默认不创建segment的，所以你在插入数据的时候，创建segment的失败，导致插入失败

现在不管插入报错的问题，现在是想解决让aaa用户在system和zaodian表空间下没有创建表的权限

[ 本帖最后由 wonder 于 2012-6-6 18:50 编辑 ]

xifenfei

deferred_segment_creation =false

然后看看能否创建

wonder

原帖由 xifenfei 于 2012-6-6 18:50 发表
deferred_segment_creation =false

然后看看能否创建

确实可以，问题解决了，谢谢！

Maclean Liu(刘相兵

请参考这个帖子 http://t.askmaclean.com/viewthre ... intable&tid=682  一样由11g deferred_segment_creation引起的问题

wonder

原帖由 maclean 于 2012-6-6 19:41 发表
请参考这个帖子 http://t.askmaclean.com/viewthread.php?action=printable&tid=682  一样由11g deferred_segment_creation引起的问题

谢谢！看了这个帖子确实解开了不少的疑惑，但我对UNLIMITED TABLESPACE这个权限还不是很了解，看一个正在使用的生产库上的一个例子：


[oracle@mlydserver22 /]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 6 21:16:11 2012

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


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

SQL> @/tmp/ckprivs.sql    --这里用的是maclean那个链接的检查权限的脚本

USERNAME                       TABLESPACE_NAME
------------------------------ ------------------------------
PRIVILEGE
----------------------------------------
ZAODIAN                        Any Tablespace
DEVELOPER


SQL> show parameter deferred

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation             boolean         TRUE     --注意这里为true
SQL> conn zaodian
Enter password:
Connected.
SQL> create table testa(id int) tablespace users;    --这里创建马上报错了
create table testa(id int) tablespace users
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'


SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR

PRIVILEGE
----------------------------------------
CREATE INDEXTYPE

12 rows selected.

请问：
1：那么为什么在deferred_segment_creation=true 的时候，在表空间users上创建表马上报错？
2：带有UNLIMITED TABLESPACE权限的时候，应该是可以在任何表空间创建表啊，导致报错的原因是不是对应的users表空间没对zaodian用户设置quota？ 谢谢！

wkc168

DEFERRED_SEGMENT_CREATION
Property         Description
Parameter type         Boolean
Default value         true
Modifiable         ALTER SESSION, ALTER SYSTEM
Range of values         true | false
Basic         No

DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, then segments for non-partitioned tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.

Before creating a set of tables, if it is known that a significant number of them will not be populated, then consider setting this parameter to true. This saves disk space and minimizes install time.


确实  必须等到有首条数据插入的时候才会创建segment

wkc168

而且对于没有创建SEGMENT的表EXP 不能导出

只能通过EXPDP导出表

wonder

原帖由 wkc168 于 2012-6-6 21:33 发表
DEFERRED_SEGMENT_CREATION
Property         Description
Parameter type         Boolean
Default value         true
Modifiable         ALTER SESSION, ALTER SYSTEM
Range of values         true | false
Basic         No

DEFERRED_SEGMENT_CREATION s ...

回8楼的，请看7楼的问题，现在DEFERRED_SEGMENT_CREATION=true，但创建表，马上就报错了，如果照你这样说的，应该不报错才对啊？谢谢！

Maclean Liu(刘相兵

在我的环境没有出现这样的问题

做一个errorstack ，运行以下命令 设置event 并触发错误

alter session set events '1950 trace name errorstack level 4';

create table testa(id int) tablespace users;


上传生成的TRACE

wonder

上传tarce文件，谢谢！

Maclean Liu(刘相兵

尝试 重新赋予 unlimited tablespace权限

grant unlimited tablespace to XXX

若仍发生该问题  做一个level 12的10046 trace 之前的errorstack中 未捕捉到有用的递归SQL



action plan 2：

重新创建一个用户 并实施之前的步骤  重现该问题， 并贴出 实施的步骤





ODM FINDING:

11.2.0.1 存在unlimited tablespace的bug

Bug 12873187  grant unlimited tablespace to role works incorrectly
This note gives a brief overview of bug 12873187.
The content was last updated on: 12-SEP-2011
Click here for details of each of the sections below.
Affects:

    Product (Component)        Oracle Server (Rdbms)
    Range of versions believed to be affected         Versions >= 11.2.0.1 but BELOW 12.1
    Versions confirmed as being affected        

        11.2.0.2

    Platforms affected        Generic (all / most platforms affected)

Fixed:

    This issue is fixed in       

        (None Specified)

Symptoms:
       
Related To:

    Error May Occur
    ORA-1950 / ORA-1931

       

    Security ( Authentication / Privileges / Auditing )
    GRANT

Description

    Rediscovery Notes:
    Until 11.1.0.7, granting UNLIMITED TABLESPACE privilege to a role
    raised ORA-1931 error. On 11.2, granting UNLIMITED TABLESPACE privilege
    to a role succeeds, but CREATE TABLE statement still raises ORA-1950.
     
    After this bug fix, ORA-1931 is raised when UNLIMITED TABLESPACE
    privildge is being granted to a role, as it was in 11.1.
     
    .
    Workaround: