表空間配額和UNLIMITED TABLESPACE許可權

dawn009發表於2014-04-11
MIT
對於一個新建的使用者，如果沒有分配給unlimited tablespace系統許可權的使用者，必須先給他們指定限額，之後他們才能在表空間中建立物件，限額可以是：
A、以兆位元組或者千位元組為單位的特定值
B、無限制的

限額是指定標空間中允許的空間容量，預設的情況下，使用者在任何表空間中都是沒有限額的，可以使用一下三個選項來為使用者提供表空間限額：
A、無限制的：允許使用者最大限度的使用表空間中的可用空間
B、值：使用者可以使用的表空間，以千位元組或者兆位元組為單位。這並不能保證會為使用者保留該空間。因此此值可能大於或小於表看三毛中的當前可用表空間
C、UNLIMITED TABLESPACE系統許可權：此係統許可權會覆蓋所有的單個表空間限額，並向使用者提供所有表空間（包括SYSTEM和SYSAUX）的無限制限額（注：授予resource角色的時候也會授予此許可權）
一定不要為使用者提供system或sysaux表空間的限額。通常，只有sys和system使用者才能在system或sysaux表空間中建立物件。
對於分配的臨時表空間或臨時還原表空間則不需要限額。

如果需要為一個使用者指定一個限額，可以有兩種方法：
1、在建立使用者的時候指定限額：
CREATE USER ABC IDENTIFIED BY ABC
DEFAULT TABLESPACE TEST
TEMPORARY TABLESPACE TEMP
QUOTA 3M ON TEST;
2、在建立使用者完成之後對使用者限額進行指定：
CREATE USER BCD IDENTIFIED BY BCD
DEFAULT TABLESPACE TEST;
ALTER USER BCD QUOTA 3M ON TEST;
3、更改使用者的表空間限額：
全域性：
grant unlimited tablespace to abc;
針對某個表空間：
alter user abc quota unlimited on test;
回收：
revoke unlimited tablespace from abc;
alter user abc quota 0 on test;


在此需要注意兩個概念：表空間不足和使用者配額不足
這兩著不是一個概念。表空間的大小是指實際的使用者表空間的大小；配額大小是使用者指定使用表空間的大小
二者的解決方法也不相同。配額問題的解決：alter user abc auota 2g on tablespace_name;表空間不足的話就是擴充套件表空間或者增加資料檔案了。

與表空間限額先關的資料字典：
dba_ts_quotas：DBA_TS_QUOTAS describes tabelspace quotas for all users
user_ts_quotas：USER_TS_QUOTAS describes tablespace quotas for the current user. This view does not display the USERNAME column;
在兩個資料字典中，max_bytes欄位就是表示表空間限額的值了，單位是B，其中-1代表沒有限制，其他的值多少就是多少的限額了。

-----------------昏割線------------------------
在最後我們需要關於一個比較重要的許可權做一個說明，這個系統許可權就是UNLIMITED TABLESPACE
unlimited tablespace的特點：
1、系統許可權unlimited tablespace不能被授予role，只能被授予使用者。也就是說，unlimited tablespace系統許可權不能包含在一個角色role中
2、unlimited tablespace沒有被包括在resource role和dba role中，但是unlimited tablespace隨著resource或者dba的授予而授予使用者的。也就是說，如果將role角色授予了某個使用者，那麼這個使用者將會擁有unlimited tablespace系統許可權
3、unlimited tablespace不能伴隨這resource被授予role而授予使用者。也就是說加入resource角色被授予了role_test角色，然後把role_test授予了test使用者，但是此時unlimited tablespace沒有被授予test使用者

接下來針對這個問題做一個測試吧
A、首先建立兩個使用者，並賦予相關的許可權：
SYS@test > CREATE USER TEST01 IDENTIFIED BY test01 DEFAULT TABLESPACE TEST;
SYS@test > CREATE USER TEST02 IDENTIFIED BY test02 DEFAULT TABLESPACE TEST;
SYS@test > GRANT CONNECT,CREATE TABLE TO TEST01;
SYS@test > GRANT CONNECT,RESOURCE TO TEST02;
SYS@test > GRANT SELECT ON SCOTT.EMP TO TEST01;
SYS@test > GRANT SELECT ON SCOTT.EMP TO TEST02;
SYS@test > ALTER USER TEST01 QUOTA 1k ON TEST;
SYS@test > ALTER USER TEST02 QUOTA 1k ON TEST;
test01，包含connect角色和create table許可權
test02，包含connect和resource角色

B、連線到兩個使用者檢視相應的許可權：
SYS@test > conn test01/test01
TEST01@test > select * from session_privs;
PRIVILEGE
---------------
CREATE SESSION
CREATE TABLE
TEST01@test > select * from user_ts_quotas;
TABLESPACE_NAME    BYTES  MAX_BYTES  BLOCKS MAX_BLOCKS DRO
---------------- ------- ---------- ------- ---------- ---
TEST                   0       8192       0          1 NO
TEST01@test > conn test02/test02
TEST02@test > select * from session_privs;
PRIVILEGE
--------------------
?????UNLIMITED TABLESPACE???????
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
TEST02@test > select * from user_ts_quotas;
TABLESPACE_NAME    BYTES  MAX_BYTES  BLOCKS MAX_BLOCKS DRO
---------------- ------- ---------- ------- ---------- ---
TEST                   0       8192       0          1 NO
從上面加問號的unlimited tablespace，這個許可權只是在test02使用者中才有的，可見是在resource分配給test02的時候，系統自動劃分過來的物件許可權，因為在resource角色中根本就沒有包含也不會包含unlimited tablespace物件許可權：
MAI@test > select ROLE,PRIVILEGE from role_sys_privs where role='RESOURCE';
ROLE       PRIVILEGE
---------- --------------------
RESOURCE   CREATE SEQUENCE
RESOURCE   CREATE TRIGGER
RESOURCE   CREATE CLUSTER
RESOURCE   CREATE PROCEDURE
RESOURCE   CREATE TYPE
RESOURCE   CREATE OPERATOR
RESOURCE   CREATE TABLE
RESOURCE   CREATE INDEXTYPE

3、建立表進行驗證許可權
TEST01@test > create table test as select * from scott.emp;
TEST01@test > insert into test select * from test;
ORA-01536: space quota exceeded for tablespace 'TEST'
可能是由於1k的劃分實在太小的緣故，在這裡執行的話，會直接報ORA-01536錯誤，不過這個不影響我們的判斷，我們切換到test02使用者執行同樣的操作：
TEST02@test > create table test as select * from scott.emp;
Table created.
建立成功，說明這個使用者的unlimited tablespace的物件許可權發揮了作用。說明了上面提到的unlimited tablespace物件許可權的第一個問題和第二個問題的一半。
至於上面提及的三個說明，童鞋們可以分別做實驗進行測試。
 
 
近期有位博友在評論中提到了，對上文的兩個疑點，我在此做以解釋。
是我的疏忽，沒有在寫日誌的時候習慣性的加上資料庫的版本和平臺，在此向大家道歉了，謝謝博友 就是愛問 的支援！
評論中提到的的兩個疑點都是在11g r2中有的功能（已測試，確實如此），在此對其進行補充：
資料庫版本：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
作業系統版本：Redhat Linux Enterprise 5.4

----------------------&gt>試驗：
第一步:


第二步:


第三步:



第四步:

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29119536/viewspace-1139928/，如需轉載，請註明出處，否則將追究法律責任。

相關文章