ITPub博客

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

转载 作者:lsy721 时间:2018-05-23 21:14:04 0 删除 编辑

相信大家一定对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. User created.

  3. SYS@HOEGH>
  4. SYS@HOEGH> grant connect,resource to hoegh1;

  5. Grant succeeded.

  6. SYS@HOEGH> conn hoegh1/hoegh1
  7. Connected.
  8. hoegh1@HOEGH>
  9. hoegh1@HOEGH> create table test(id number);

  10. Table created.

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

  12. 1 row created.

  13. hoegh1@HOEGH>
  14. hoegh1@HOEGH> select privilege from user_sys_privs;

  15. PRIVILEGE
  16. ----------------------------------------
  17. UNLIMITED TABLESPACE

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

  19. USERNAME GRANTED_ROLE ADM
  20. ------------------------------ ------------------------------ ---
  21. HOEGH1 CONNECT NO
  22. HOEGH1 RESOURCE NO

  23. 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. User created.

  3. SYS@HOEGH> grant connect to hoegh2;

  4. Grant succeeded.

  5. SYS@HOEGH>
  6. SYS@HOEGH> select privilege from role_sys_privs
  7. where role='RESOURCE'; 2

  8. PRIVILEGE
  9. ----------------------------------------
  10. CREATE SEQUENCE
  11. CREATE TRIGGER
  12. CREATE CLUSTER
  13. CREATE PROCEDURE
  14. CREATE TYPE
  15. CREATE OPERATOR
  16. CREATE TABLE
  17. CREATE INDEXTYPE

  18. 8 rows selected.

  19. SYS@HOEGH>
  20. SYS@HOEGH> select 'grant '||PRIVILEGE||' to hoegh2;' from role_sys_privs
  21. where role='RESOURCE'; 2

  22. 'GRANT'||PRIVILEGE||'TOhoegh2;'
  23. -----------------------------------------------------
  24. grant CREATE SEQUENCE to hoegh2;
  25. grant CREATE TRIGGER to hoegh2;
  26. grant CREATE CLUSTER to hoegh2;
  27. grant CREATE PROCEDURE to hoegh2;
  28. grant CREATE TYPE to hoegh2;
  29. grant CREATE OPERATOR to hoegh2;
  30. grant CREATE TABLE to hoegh2;
  31. grant CREATE INDEXTYPE to hoegh2;

  32. 8 rows selected.

  33. SYS@HOEGH> grant CREATE SEQUENCE to hoegh2;
  34. grant CREATE TRIGGER to hoegh2;
  35. grant CREATE CLUSTER to hoegh2;
  36. grant CREATE PROCEDURE to hoegh2;
  37. grant CREATE TYPE to hoegh2;
  38. grant CREATE OPERATOR to hoegh2;
  39. grant CREATE TABLE to hoegh2;
  40. grant CREATE INDEXTYPE to hoegh2;

  41. Grant succeeded.

  42. SYS@HOEGH>
  43. Grant succeeded.

  44. SYS@HOEGH>
  45. Grant succeeded.

  46. SYS@HOEGH>
  47. Grant succeeded.

  48. SYS@HOEGH>
  49. Grant succeeded.

  50. SYS@HOEGH>
  51. Grant succeeded.

  52. SYS@HOEGH>
  53. Grant succeeded.

  54. SYS@HOEGH>
  55. Grant succeeded.

  56. SYS@HOEGH>
  57. SYS@HOEGH>
  58. SYS@HOEGH> conn hoegh2/hoegh2
  59. Connected.
  60. hoegh2@HOEGH> create table test(id number);

  61. Table created.

  62. hoegh2@HOEGH> insert into test values(1);
  63. insert into test values(1)
  64.             *
  65. ERROR at line 1:
  66. ORA-01950: no privileges on tablespace 'USERS'


  67. hoegh2@HOEGH>
  68. hoegh2@HOEGH>
  69. hoegh2@HOEGH> select privilege from user_sys_privs;

  70. PRIVILEGE
  71. ----------------------------------------
  72. CREATE TABLE
  73. CREATE CLUSTER
  74. CREATE TYPE
  75. CREATE TRIGGER
  76. CREATE PROCEDURE
  77. CREATE OPERATOR
  78. CREATE INDEXTYPE
  79. CREATE SEQUENCE

  80. 8 rows selected.

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

  82. USERNAME GRANTED_ROLE ADM
  83. ------------------------------ ------------------------------ ---
  84. HOEGH2 CONNECT NO

  85. hoegh2@HOEGH>
  86. 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. User created.

  4. SYS@HOEGH>
  5. SYS@HOEGH> create role hoegh;

  6. Role created.

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

  8. Grant succeeded.

  9. SYS@HOEGH> grant hoegh to hoegh3;

  10. Grant succeeded.

  11. SYS@HOEGH>
  12. SYS@HOEGH> conn hoegh3/hoegh3
  13. Connected.
  14. hoegh3@HOEGH>
  15. hoegh3@HOEGH> create table test(id number);

  16. Table created.

  17. hoegh3@HOEGH> insert into test values(1);
  18. insert into test values(1)
  19.             *
  20. ERROR at line 1:
  21. ORA-01950: no privileges on tablespace 'USERS'


  22. hoegh3@HOEGH>
  23. hoegh3@HOEGH> select privilege from user_sys_privs;

  24. no rows selected

  25. hoegh3@HOEGH>
  26. hoegh3@HOEGH> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;

  27. USERNAME GRANTED_ROLE ADM
  28. ------------------------------ ------------------------------ ---
  29. HOEGH3 HOEGH NO

  30. hoegh3@HOEGH>
  31. 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. User created.

  3. SYS@HOEGH> grant connect to hoegh4;

  4. Grant succeeded.

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

  6. Grant succeeded.

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

  8. Grant succeeded.

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

  14. Table created.

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

  16. 1 row created.

  17. hoegh4@HOEGH>
  18. hoegh4@HOEGH> select privilege from user_sys_privs;

  19. PRIVILEGE
  20. ----------------------------------------
  21. CREATE TABLE
  22. UNLIMITED TABLESPACE

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

  24. USERNAME GRANTED_ROLE ADM
  25. ------------------------------ ------------------------------ ---
  26. HOEGH4 CONNECT NO

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




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

请登录后发表评论 登录
全部评论

注册时间:2014-03-31

  • 博文量
    26
  • 访问量
    8452