相信大家一定對Resource 角色不會陌生，Resource 角色是授予開發人員的，能在自己的方案中建立表、序列、檢視等。很多DBA習慣在建立新使用者後直接賦予Connect和Resource 角色，這樣就可以在資料庫裡執行建立表等操作了。

最近在測試過程中發現一些奇怪的現象，有時候擁有Connect和Resource 角色的使用者會提示“ORA-01950: no privileges on tablespace 'USERS'”錯誤，也就是說沒有操作表空間的許可權，這是怎麼回事呢？

透過一系列的測試發現，unlimited tablespace是隱含在resource角色中的一個系統許可權，當使用者得到resource的角色時，unlimited tablespace系統許可權也隱式授權給使用者。但是需要注意的是，unlimited tablespace系統許可權只能授予使用者，不能被授予角色；也不會隨著resource角色被授予role而級聯授予給使用者。

首先，我們瞭解一下和unlimited tablespace系統許可權的一個概念QUOTA，然後透過若干測試來驗證以上結論。

關於QUOTA

對於一個新建的使用者，如果沒有分配給unlimited tablespace系統許可權的使用者，必須先給他們指定限額，之後他們才能在表空間中建立物件。

限額是指定標空間中允許的空間容量，預設的情況下，使用者在任何表空間中都是沒有限額的，可以使用以下三個選項來為使用者提供表空間限額：

A、無限制的：允許使用者最大限度的使用表空間中的可用空間

B、值：使用者可以使用的表空間，以千位元組或者兆位元組為單位。但是這並不能保證會為使用者保留該空間。

C、UNLIMITED TABLESPACE系統許可權：此係統許可權會覆蓋所有的單個表空間限額，並向使用者提供所有表空間（包括SYSTEM和SYSAUX）的無限制限額（注：授予resource角色的時候也會授予此許可權）

如果需要為一個使用者指定一個限額，可以有兩種方法：

1、在建立使用者的時候指定限額：

2、在建立使用者完成之後，對使用者限額進行指定：

測試1 授予connect和resource角色

建立新使用者hoegh1，授予connect和resource角色，嘗試建表和插入操作，透過查詢user_sys_privs資料字典來驗證使用者的系統許可權。
我們看到hoegh1使用者擁有了unlimited tablespace系統許可權，插入記錄成功。也就是說，當使用者hoegh1得到resource的角色時，unlimited tablespace系統許可權也隱式授權給使用者。

測試2 逐條授予resource角色包含的系統許可權

建立新使用者hoegh2，授予connect並逐條授予resource角色包含的系統許可權，嘗試建表和插入操作，透過查詢user_sys_privs資料字典來驗證使用者的系統許可權。

點選(此處)摺疊或開啟

1. SYS@HOEGH> create user hoegh2 identified by hoegh2;
   
2. 
   
3. User created.
   
4. 
   
5. SYS@HOEGH> grant connect to hoegh2;
   
6. 
   
7. Grant succeeded.
   
8. 
   
9. SYS@HOEGH>
   
10. SYS@HOEGH> select privilege from role_sys_privs
    
11. where role='RESOURCE'; 2
    
12. 
    
13. PRIVILEGE
    
14. ----------------------------------------
    
15. CREATE SEQUENCE
    
16. CREATE TRIGGER
    
17. CREATE CLUSTER
    
18. CREATE PROCEDURE
    
19. CREATE TYPE
    
20. CREATE OPERATOR
    
21. CREATE TABLE
    
22. CREATE INDEXTYPE
    
23. 
    
24. 8 rows selected.
    
25. 
    
26. SYS@HOEGH>
    
27. SYS@HOEGH> select 'grant '||PRIVILEGE||' to hoegh2;' from role_sys_privs
    
28. where role='RESOURCE'; 2
    
29. 
    
30. 'GRANT'||PRIVILEGE||'TOhoegh2;'
    
31. -----------------------------------------------------
    
32. grant CREATE SEQUENCE to hoegh2;
    
33. grant CREATE TRIGGER to hoegh2;
    
34. grant CREATE CLUSTER to hoegh2;
    
35. grant CREATE PROCEDURE to hoegh2;
    
36. grant CREATE TYPE to hoegh2;
    
37. grant CREATE OPERATOR to hoegh2;
    
38. grant CREATE TABLE to hoegh2;
    
39. grant CREATE INDEXTYPE to hoegh2;
    
40. 
    
41. 8 rows selected.
    
42. 
    
43. SYS@HOEGH> grant CREATE SEQUENCE to hoegh2;
    
44. grant CREATE TRIGGER to hoegh2;
    
45. grant CREATE CLUSTER to hoegh2;
    
46. grant CREATE PROCEDURE to hoegh2;
    
47. grant CREATE TYPE to hoegh2;
    
48. grant CREATE OPERATOR to hoegh2;
    
49. grant CREATE TABLE to hoegh2;
    
50. grant CREATE INDEXTYPE to hoegh2;
    
51. 
    
52. Grant succeeded.
    
53. 
    
54. SYS@HOEGH>
    
55. Grant succeeded.
    
56. 
    
57. SYS@HOEGH>
    
58. Grant succeeded.
    
59. 
    
60. SYS@HOEGH>
    
61. Grant succeeded.
    
62. 
    
63. SYS@HOEGH>
    
64. Grant succeeded.
    
65. 
    
66. SYS@HOEGH>
    
67. Grant succeeded.
    
68. 
    
69. SYS@HOEGH>
    
70. Grant succeeded.
    
71. 
    
72. SYS@HOEGH>
    
73. Grant succeeded.
    
74. 
    
75. SYS@HOEGH>
    
76. SYS@HOEGH>
    
77. SYS@HOEGH> conn hoegh2/hoegh2
    
78. Connected.
    
79. hoegh2@HOEGH> create table test(id number);
    
80. 
    
81. Table created.
    
82. 
    
83. hoegh2@HOEGH> insert into test values(1);
    
84. insert into test values(1)
    
85.             *
    
86. ERROR at line 1:
    
87. ORA-01950: no privileges on tablespace 'USERS'
    
88. 
    
89. 
    
90. hoegh2@HOEGH>
    
91. hoegh2@HOEGH>
    
92. hoegh2@HOEGH> select privilege from user_sys_privs;
    
93. 
    
94. PRIVILEGE
    
95. ----------------------------------------
    
96. CREATE TABLE
    
97. CREATE CLUSTER
    
98. CREATE TYPE
    
99. CREATE TRIGGER
    
100. CREATE PROCEDURE
     
101. CREATE OPERATOR
     
102. CREATE INDEXTYPE
     
103. CREATE SEQUENCE
     
104. 
     
105. 8 rows selected.
     
106. 
     
107. hoegh2@HOEGH> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;
     
108. 
     
109. USERNAME GRANTED_ROLE ADM
     
110. ------------------------------ ------------------------------ ---
     
111. HOEGH2 CONNECT NO
     
112. 
     
113. hoegh2@HOEGH>
     
114. hoegh2@HOEGH>

我們看到hoegh2使用者雖然擁有了resource角色下的所有系統許可權，但是卻沒有unlimited tablespace系統許可權，插入記錄失敗。

測試3 將connect和resource角色授予新的角色

建立角色hoegh，並將connect和resource角色授予這個角色；然後建立新使用者hoegh3，將hoegh角色授予使用者hoegh3，嘗試建表和插入操作。

點選(此處)摺疊或開啟

1. SYS@HOEGH>
   
2. SYS@HOEGH> create user hoegh3 identified by hoegh3;
   
3. 
   
4. User created.
   
5. 
   
6. SYS@HOEGH>
   
7. SYS@HOEGH> create role hoegh;
   
8. 
   
9. Role created.
   
10. 
    
11. SYS@HOEGH> grant connect,resource to hoegh;
    
12. 
    
13. Grant succeeded.
    
14. 
    
15. SYS@HOEGH> grant hoegh to hoegh3;
    
16. 
    
17. Grant succeeded.
    
18. 
    
19. SYS@HOEGH>
    
20. SYS@HOEGH> conn hoegh3/hoegh3
    
21. Connected.
    
22. hoegh3@HOEGH>
    
23. hoegh3@HOEGH> create table test(id number);
    
24. 
    
25. Table created.
    
26. 
    
27. hoegh3@HOEGH> insert into test values(1);
    
28. insert into test values(1)
    
29.             *
    
30. ERROR at line 1:
    
31. ORA-01950: no privileges on tablespace 'USERS'
    
32. 
    
33. 
    
34. hoegh3@HOEGH>
    
35. hoegh3@HOEGH> select privilege from user_sys_privs;
    
36. 
    
37. no rows selected
    
38. 
    
39. hoegh3@HOEGH>
    
40. hoegh3@HOEGH> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;
    
41. 
    
42. USERNAME GRANTED_ROLE ADM
    
43. ------------------------------ ------------------------------ ---
    
44. HOEGH3 HOEGH NO
    
45. 
    
46. hoegh3@HOEGH>
    
47. hoegh3@HOEGH>

我們看到hoegh3使用者雖然擁有了hoegh角色，並且hoegh角色包含了connect角色resource角色，但是hoegh3使用者並沒有unlimited tablespace系統許可權，插入記錄失敗。也就是說，unlimited tablespace系統許可權不會隨著resource角色被授予hoegh角色而級聯授予給使用者hoegh3。

測試4 直接授予使用者unlimited tablespace系統許可權

建立新使用者hoegh4，授予connect角色後，直接授予create table和unlimited tablespace系統許可權，嘗試建表和插入操作。

點選(此處)摺疊或開啟

1. SYS@HOEGH> create user hoegh4 identified by hoegh4;
   
2. 
   
3. User created.
   
4. 
   
5. SYS@HOEGH> grant connect to hoegh4;
   
6. 
   
7. Grant succeeded.
   
8. 
   
9. SYS@HOEGH> grant create table to hoegh4;
   
10. 
    
11. Grant succeeded.
    
12. 
    
13. SYS@HOEGH> grant unlimited tablespace to hoegh4;
    
14. 
    
15. Grant succeeded.
    
16. 
    
17. SYS@HOEGH>
    
18. SYS@HOEGH> conn hoegh4/hoegh4
    
19. Connected.
    
20. hoegh4@HOEGH>
    
21. hoegh4@HOEGH> create table test(id number);
    
22. 
    
23. Table created.
    
24. 
    
25. hoegh4@HOEGH> insert into test values(1);
    
26. 
    
27. 1 row created.
    
28. 
    
29. hoegh4@HOEGH>
    
30. hoegh4@HOEGH> select privilege from user_sys_privs;
    
31. 
    
32. PRIVILEGE
    
33. ----------------------------------------
    
34. CREATE TABLE
    
35. UNLIMITED TABLESPACE
    
36. 
    
37. hoegh4@HOEGH> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;
    
38. 
    
39. USERNAME GRANTED_ROLE ADM
    
40. ------------------------------ ------------------------------ ---
    
41. HOEGH4 CONNECT NO
    
42. 
    
43. hoegh4@HOEGH>
    
44. hoegh4@HOEGH>

我們看到hoegh4使用者擁有了unlimited tablespace系統許可權，插入記錄成功。



                                                                                            ~~~~~~~ the end~~~~~~~~~
                                                                                                                                                                                                               hoegh
                                                                                                                                                                                                           2016.10.26