用户表空间权限问题

liuxbgiant

新建立用户
create user  test identified by test default tablespace users;
grant connect,resource to test;
alter user test quota 100M on users;

但是该用户还能在其它表空间建立表
如：create table test (id number) tablespace examples;

如何限制此问题？

action825

resource 默认有unlimited tablespace 权限，所以执行下
revoke   unlimited   tablespace   from   test;
就可以了

liuxbgiant

这个还用你说，谁都知道，不行，不能解决这个问题，你试下就知道了。

Maclean Liu(刘相兵

ODM TEST:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production



select * from v$version;





SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production



SQL> drop user test;

User dropped.

SQL>
SQL>
SQL> create user  test identified by test default tablespace users;
grant connect,resource to test;

User created.

SQL> alter user test quota 100M on users;
Grant succeeded.

SQL>

User altered.




SQL> SELECT
  2    username,
  3    tablespace_name,
  4    privilege
  5  FROM (
  6    SELECT
  7      grantee username, 'Any Tablespace' tablespace_name, privilege
  8    FROM (
  9      -- first get the users with direct grants
10      SELECT
11        p1.grantee grantee, privilege
12      FROM
13        dba_sys_privs p1
14      WHERE
15        p1.privilege='UNLIMITED TABLESPACE'
16      UNION ALL
17      -- and then the ones with UNLIMITED TABLESPACE through a role...
18      SELECT
19        r3.grantee, granted_role privilege
20      FROM
21        dba_role_privs r3
22        START WITH r3.granted_role IN (
23            SELECT
24              DISTINCT p4.grantee
25            FROM
26              dba_role_privs r4, dba_sys_privs p4
27            WHERE
28              r4.granted_role=p4.grantee
29              AND p4.privilege = 'UNLIMITED TABLESPACE')
30      CONNECT BY PRIOR grantee = granted_role)
31      -- we just whant to see the users not the roles
32    WHERE grantee IN (SELECT username FROM dba_users) OR grantee = 'PUBLIC'
33    UNION ALL
34    -- list the user with unimited quota on a dedicated tablespace
35    SELECT
36      username,tablespace_name,'DBA_TS_QUOTA' privilege
37    FROM
38      dba_ts_quotas
39    WHERE
40      max_bytes = -1 )
41  WHERE tablespace_name LIKE UPPER('SYSTEM')
42      OR tablespace_name = 'Any Tablespace'
43     and username='TEST';

USERNAME                       TABLESPACE_NAME                PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
TEST                           Any Tablespace                 UNLIMITED TABLESPACE





conn test/test

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.




conn / as sysdba

SQL> revoke  UNLIMITED TABLESPACE from test;

Revoke succeeded.



conn test/test



SQL> select * from session_privs;   

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

9 rows selected


SQL> create table tv(t1 int) tablespace sysaux;

Table created.



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


conn / as sysdba


SQL> alter system set deferred_segment_creation=FALSE;

System altered.



conn test/test


SQL>  create table tvs(t1 int) tablespace sysaux;
create table tvs(t1 int) tablespace sysaux
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSAUX'

Maclean Liu(刘相兵

这是因为 11g中的 deferred_segment_creation 延迟段创建特性，在CREATE TABLE DDL执行时实际不会在指定的表空间上生成segment ，而会延迟到实际有INSERT数据后才会产生segment，因为没有实际的segment所以也就不会使用到tablespace quota 表空间配额， 但是在INSERT数据时 如果没有对应表空间的quota的话 仍会报 ORA-01950错误。

如以上演示， "alter system set deferred_segment_creation=FALSE;"后 禁用该11g新特性，回复到10g的状态。


在论坛提问，大家给出答案是出于善意， 请你在会贴的时候 遵守基本的论坛礼仪 、保持基本的礼貌， 这样别人才会有兴趣回答你的问题。

liuxbgiant

感谢maclean的指导和建议，本人佩服你的见解!