ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 从ORA-01950报错聊起——令人困惑的Resource角色和隐含unlimited tablespace系统权限

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

原创 Oracle 作者：Hoegh 时间：2016-10-26 14:40:39 1 删除 编辑

相信大家一定对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;

  3. User created.

  5. SYS@HOEGH>
  6. SYS@HOEGH> grant connect,resource to hoegh1;

  8. Grant succeeded.

  10. SYS@HOEGH> conn hoegh1/hoegh1
  11. Connected.
  12. hoegh1@HOEGH>
  13. hoegh1@HOEGH> create table test(id number);

  15. Table created.

  17. hoegh1@HOEGH> insert into test values(1);

  19. 1 row created.

  21. hoegh1@HOEGH>
  22. hoegh1@HOEGH> select privilege from user_sys_privs;

  24. PRIVILEGE
  25. ----------------------------------------
  26. UNLIMITED TABLESPACE

  28. hoegh1@HOEGH> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;

  30. USERNAME GRANTED_ROLE ADM
  31. ------------------------------ ------------------------------ ---
  32. HOEGH1 CONNECT NO
  33. HOEGH1 RESOURCE NO

  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;

  3. User created.

  5. SYS@HOEGH> grant connect to hoegh2;

  7. Grant succeeded.

  9. SYS@HOEGH>
  10. SYS@HOEGH> select privilege from role_sys_privs
  11. where role='RESOURCE'; 2

  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

  24. 8 rows selected.

  26. SYS@HOEGH>
  27. SYS@HOEGH> select 'grant '||PRIVILEGE||' to hoegh2;' from role_sys_privs
  28. where role='RESOURCE'; 2

  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;

  41. 8 rows selected.

  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;

  52. Grant succeeded.

  54. SYS@HOEGH>
  55. Grant succeeded.

  57. SYS@HOEGH>
  58. Grant succeeded.

  60. SYS@HOEGH>
  61. Grant succeeded.

  63. SYS@HOEGH>
  64. Grant succeeded.

  66. SYS@HOEGH>
  67. Grant succeeded.

  69. SYS@HOEGH>
  70. Grant succeeded.

  72. SYS@HOEGH>
  73. Grant succeeded.

  75. SYS@HOEGH>
  76. SYS@HOEGH>
  77. SYS@HOEGH> conn hoegh2/hoegh2
  78. Connected.
  79. hoegh2@HOEGH> create table test(id number);

  81. Table created.

  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'


  90. hoegh2@HOEGH>
  91. hoegh2@HOEGH>
  92. hoegh2@HOEGH> select privilege from user_sys_privs;

  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

  105. 8 rows selected.

  107. hoegh2@HOEGH> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;

  109. USERNAME GRANTED_ROLE ADM
  110. ------------------------------ ------------------------------ ---
  111. HOEGH2 CONNECT NO

  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;

  4. User created.

  6. SYS@HOEGH>
  7. SYS@HOEGH> create role hoegh;

  9. Role created.

  11. SYS@HOEGH> grant connect,resource to hoegh;

  13. Grant succeeded.

  15. SYS@HOEGH> grant hoegh to hoegh3;

  17. Grant succeeded.

  19. SYS@HOEGH>
  20. SYS@HOEGH> conn hoegh3/hoegh3
  21. Connected.
  22. hoegh3@HOEGH>
  23. hoegh3@HOEGH> create table test(id number);

  25. Table created.

  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'


  34. hoegh3@HOEGH>
  35. hoegh3@HOEGH> select privilege from user_sys_privs;

  37. no rows selected

  39. hoegh3@HOEGH>
  40. hoegh3@HOEGH> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;

  42. USERNAME GRANTED_ROLE ADM
  43. ------------------------------ ------------------------------ ---
  44. HOEGH3 HOEGH NO

  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;

  3. User created.

  5. SYS@HOEGH> grant connect to hoegh4;

  7. Grant succeeded.

  9. SYS@HOEGH> grant create table to hoegh4;

  11. Grant succeeded.

  13. SYS@HOEGH> grant unlimited tablespace to hoegh4;

  15. Grant succeeded.

  17. SYS@HOEGH>
  18. SYS@HOEGH> conn hoegh4/hoegh4
  19. Connected.
  20. hoegh4@HOEGH>
  21. hoegh4@HOEGH> create table test(id number);

  23. Table created.

  25. hoegh4@HOEGH> insert into test values(1);

  27. 1 row created.

  29. hoegh4@HOEGH>
  30. hoegh4@HOEGH> select privilege from user_sys_privs;

  32. PRIVILEGE
  33. ----------------------------------------
  34. CREATE TABLE
  35. UNLIMITED TABLESPACE

  37. hoegh4@HOEGH> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;

  39. USERNAME GRANTED_ROLE ADM
  40. ------------------------------ ------------------------------ ---
  41. HOEGH4 CONNECT NO

  43. hoegh4@HOEGH>
  44. hoegh4@HOEGH>
我们看到hoegh4用户拥有了unlimited tablespace系统权限，插入记录成功。




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

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

0
0
上一篇： Ask Hoegh(9)——SET UNUSED列可以恢复吗？
下一篇： Windows下关闭MySQL的自动提交(autocommit)功能
请登录后发表评论 登录
全部评论
Hoegh

注册时间：2015-03-13

  • 博文量
    132
  • 访问量
    2219915

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593