一個使用者建立引發的許可權控制問題

一個使用者建立引發的許可權控制問題

開發同學提了一個需求，比較有意思。

需求描述：要求開發庫建立一個新使用者A(預設資料表空間TBS_1)，由於這庫是共用庫，還有其他schema(樣本：資料表空間TBS_2)被其他組的開發人員使用，需要避免使用A使用者的開發人員，利用create table t(col name) tablespace tbs_2通過指定資料表空間的方式在tbs_2上建立表，即禁止使用者A可以在tbs_2資料表空間上進行操作。

操作過程：
1.建立使用者A：

create user a identified by a default tablespace tbs_1;

grant resouce,connect to a;

指定預設資料表空間是tbs_1。
授予resource和connect角色。

2.測試建表：

SQL> create table t1(id number);

SQL> insert into t1 values(1);

SQL> commit;

未報錯，t1表會建立在使用者A的預設資料表空間tbs_1上。
接下來，看看他能不能在tbs_2上建立表。

SQL> create table t2(id number) tablespace dep_tbs;

SQL> insert into t2 values(1);

SQL> commit;

也可以。原因是使用者A有如下系統許可權：

SQL> select privilege from user_sys_privs;

PRIVILEGE

---------------------

UNLIMITED TABLESPACE

UNLIMITED TABLESPACE表示對錶空間的使用無限制，因此可以在任意資料表空間中建立表，之所以使用者A有這個系統許可權，是因為授予了resource角色的操作。具體可以參見之前的文章：  

3.收回UNLIMITED TABLESPACE許可權再測試：

revoke UNLIMITED TABLESPACE from a;

create table t1(id number);

insert into t1 values(1)

 * ERROR at line 1: ORA-01950: no privileges on tablespace 'TBS_1'

 

create table t2(id number) tablespace tbs_2;

insert into t2 values(1)

 * ERROR at line 1: ORA-01950: no privileges on tablespace 'TBS_2'

發現仍可以在tbs_1和tbs_2上建表，但均不能插入資料。原因就是由於剛才回收了tablespace的許可權，導致使用者A沒有任何錶空間上的使用許可權。

4.授予使用者A在tbs_1資料表空間使用許可權再測試：

alter user a quota unlimited on gbc_tbs;

create table t1(id number);

SQL> insert into t1 values(1);

SQL> commit;

create table t2(id number) tablespace tbs_2;

insert into t2 values(1)

 * ERROR at line 1: ORA-01950: no privileges on tablespace 'TBS_2'

發現此時使用者A可以在tbs_1上建表，插入資料。但仍可以在tbs_2上建表，以及插入資料。

可能細心的朋友從(3)就能看出一些問題來了，在步驟(3)中，使用者A沒有任何tablespace的使用許可權，但仍可以create table建表，只是不能插入資料。經過查驗，這個問題和11g的一個新特性有關，即“延遲段”(可參見http://blog.itpub.net/7192724/viewspace-1247807/)，此庫的版本是：

SQL> select * from v$version;

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

準確的說，應該是11gR2的一個新特性，叫延遲段，即延遲分配段空間。簡單講，預設將表(以及索引、LOB)的物理空間分配延遲到第一條記錄插入到表中時。即有實際的資料插入表中時，再為每個對象初始化空間分配。其中11.2.0.1不支援分區表 、bitmap join indexes和domain indexes。11.2.0.2版本開始支援分區表。

換句話說，在create table的時候，並不像以前的版本，此時就已經為其分配了空間，而是等表中插入第一條記錄的時候，按照定義的空間大小，開始為其分配空間，此時才能在相關視圖中看見該表的儲存資訊，好處就是空間分配只有當真正使用的時候才會進行，顯得要會精確，但缺點(或者不能叫缺點，只能叫假象)就是看著好像是使用者可以在一個沒有使用許可權的資料表空間中建立表，儘管不能向其插入資料。

為了避免這種“假象”，Oracle提供了一個參數開關：

可以在system或session層級設定該參數，當為false，則會關閉延遲段的功能，此時就不可以在未有許可權的資料表空間中建立表了。

5.針對上述問題的解決方案(資料庫角度)：
方案1：全域設定
直接設定alter session set deferred_segment_creation=false，系統級禁用延遲段特性，即此庫所有使用者都不會使用延遲段功能了。
方案2：使用者級設定
如果覺得方案1粒度太粗，可以做細粒度控制，要求只有使用者A禁止使用延遲段，可以利用觸發器來控制(以前沒用過，第一次寫，要是有疏漏，還請大師們補充指正)：

create or replace trigger log_deferred

after logon on database

declare logon_user VARCHAR2(10);

begin select user into logon_user from dual;

  if logon_user = 'A'

  then

  execute immediate 'alter session set deferred_segment_creation=false';

  end if;

end;

/

即登入時判斷使用者名稱是否是A，如果是，則session級設定此參數為false，可以達到此目的。
無論方案1還是方案2，使用者A再在tbs_2建立表，會有報錯：

create table t2(id number) tablespace tbs_2

 * ERROR at line 1: ORA-01950: no privileges on tablespace 'TBS_2'

總結：
1.UNLIMITED TABLESPACE許可權是隨著resource角色授予。
2.段延遲這個新特性，會造成未有許可權的資料表空間中可以建表的“假象”，可以使用deferred_segment_creation參數關閉之。
3.Oracle實在是博大精深，任何一小細節可能都蘊含著很多知識和原理，同時他有提供了啟用和關閉的方法，軟體設計造詣，只能說是歎為觀止了。

感謝曉飛、maclean還有牛大師幾位的討論和建議。