从ORA-01950报错聊起——令人困惑的Resource角色和隐含unlimited tablespace系统权限

相信大家一定对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、在创建用户的时候指定限额：

点击(此处)折叠或打开

1. CREATE USER hoegh IDENTIFIED BY hoegh
   
2. DEFAULT TABLESPACE users
   
3. TEMPORARY TABLESPACE TEMP
   
4. QUOTA 3M ON users;

2、在创建用户完成之后，对用户限额进行指定：

点击(此处)折叠或打开

1. CREATE USER hoegh IDENTIFIED BY hoegh
   
2. DEFAULT TABLESPACE TEST;
   
3. ALTER USER hoegh QUOTA 3M ON users;

测试1 授予connect和resource角色

创建新用户hoegh1，授予connect和resource角色，尝试建表和插入操作，通过查询user_sys_privs数据字典来验证用户的系统权限。

点击(此处)折叠或打开

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

我们看到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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30162081/viewspace-2127149/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30162081/viewspace-2127149/