这是不是一个隐患啊？（在别的表空间操作）应该如何避免？

不再画地为牢

原帖由 Yong Huang 于 2010-6-30 05:35 发表


I find something new today. In 11gR2 (specifically mine is 11.2.0.1), but not in any other version, not even 11gR1, if you previously gave unlimited quota on a tablespace to a user, granted resource role, then revoking unlimited tablespace privilege will remove his unlimited quota on that tablespace.


SQL> create user u identified by u123456 quota unlimited on users;

User created.

SQL> select tablespace_name, bytes, max_bytes from dba_ts_quotas where username = 'U';

TABLESPACE_NAME        BYTES    MAX_BYTES
--------------- ------------ ------------
USERS                      0           -1

SQL> grant resource to u;

Grant succeeded.

SQL> select privilege from dba_sys_privs where grantee = 'U';

PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE

SQL> select tablespace_name, bytes, max_bytes from dba_ts_quotas where username = 'U';

TABLESPACE_NAME        BYTES    MAX_BYTES
--------------- ------------ ------------
USERS                      0           -1

SQL> revoke unlimited tablespace from u;

Revoke succeeded.

SQL> select tablespace_name, bytes, max_bytes from dba_ts_quotas where username = 'U';

no rows selected

SQL> alter user u quota unlimited on users;

User altered.

SQL> select tablespace_name, bytes, max_bytes from dba_ts_quotas where username = 'U';

TABLESPACE_NAME        BYTES    MAX_BYTES
--------------- ------------ ------------
USERS                      0           -1

SQL> select privilege from dba_sys_privs where grantee = 'U';

no rows selected

SQL> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


This does not happen in my 11.1.0.6 database, and does not happen if I give quota of some specific number (instead of "unlimited"). There may be a note on MOS but I haven't checked.

Yong Huang

这个应该逻辑上是正确的吧，回收所有ST的权限(不包括默认表空间)，应该会收回其他表空间的quota吧

江湖末路

家用电器的使用寿命随着时间的流失而减小，虽然电器本身没有发生故障，但电器本身也有寿终的时候，这就是家用电器的使用寿命。

mylajitong

原帖由 Yong Huang 于 2010-6-30 05:35 发表


I find something new today. In 11gR2 (specifically mine is 11.2.0.1), but not in any other version, not even 11gR1, if you previously gave unlimited quota on a tablespace to a user, granted resource role, then revoking unlimited tablespace privilege will remove his unlimited quota on that tablespace.


SQL> create user u identified by u123456 quota unlimited on users;

User created.

SQL> select tablespace_name, bytes, max_bytes from dba_ts_quotas where username = 'U';

TABLESPACE_NAME        BYTES    MAX_BYTES
--------------- ------------ ------------
USERS                      0           -1

SQL> grant resource to u;

Grant succeeded.

SQL> select privilege from dba_sys_privs where grantee = 'U';

PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE

SQL> select tablespace_name, bytes, max_bytes from dba_ts_quotas where username = 'U';

TABLESPACE_NAME        BYTES    MAX_BYTES
--------------- ------------ ------------
USERS                      0           -1

SQL> revoke unlimited tablespace from u;

Revoke succeeded.

SQL> select tablespace_name, bytes, max_bytes from dba_ts_quotas where username = 'U';

no rows selected

SQL> alter user u quota unlimited on users;

User altered.

SQL> select tablespace_name, bytes, max_bytes from dba_ts_quotas where username = 'U';

TABLESPACE_NAME        BYTES    MAX_BYTES
--------------- ------------ ------------
USERS                      0           -1

SQL> select privilege from dba_sys_privs where grantee = 'U';

no rows selected

SQL> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


This does not happen in my 11.1.0.6 database, and does not happen if I give quota of some specific number (instead of "unlimited"). There may be a note on MOS but I haven't checked.

Yong Huang

感谢大牛继续关注这个问题。

mylajitong

原帖由 不再画地为牢 于 2010-7-1 16:25 发表


这个应该逻辑上是正确的吧，回收所有ST的权限(不包括默认表空间)，应该会收回其他表空间的quota吧

我觉得逻辑上应该也说得通， 但这个问题要不是 Yong Huang 提出来，大家估计都还没注意到这个细节呢。