ORA-01950报错解决

从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、在创建用户的时候指定限额：点击（此处）折叠或打开CREATE USER LINUXIDC IDENTIFIED BY LINUXIDCDEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP
QUOTA 3M ON users;
2、在创建用户完成之后，对用户限额进行指定：CREATE USER LINUXIDC IDENTIFIED BY LINUXIDCDEFAULT TABLESPACE TEST;
ALTER USER LINUXIDC QUOTA 3M ON users;
测试1 授予connect和resource角色
创建新用户LINUXIDC1，授予connect和resource角色，尝试建表和插入操作，通过查询user_sys_privs数据字典来验证用户的系统权限。SYS@LINUXIDC> create user LINUXIDC1 identified by LINUXIDC1;
User created.SYS@LINUXIDC>
SYS@LINUXIDC> grant connect,resource to LINUXIDC1;Grant succeeded.SYS@LINUXIDC> conn LINUXIDC1/LINUXIDC1
Connected.
LINUXIDC1@LINUXIDC>
LINUXIDC1@LINUXIDC> create table test（id number）;Table created.LINUXIDC1@LINUXIDC> insert into test values（1）;1 row created.LINUXIDC1@LINUXIDC>
LINUXIDC1@LINUXIDC> select privilege from user_sys_privs;PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACELINUXIDC1@LINUXIDC> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;USERNAME GRANTED_ROLE ADM
------------------------------ ------------------------------ ---
LINUXIDC1 CONNECT NO
LINUXIDC1 RESOURCE NOLINUXIDC1@LINUXIDC>
我们看到LINUXIDC1用户拥有了unlimited tablespace系统权限，插入记录成功。也就是说，当用户LINUXIDC1得到resource的角色时，unlimited tablespace系统权限也隐式授权给用户。测试2 逐条授予resource角色包含的系统权限
创建新用户LINUXIDC2，授予connect并逐条授予resource角色包含的系统权限，尝试建表和插入操作，通过查询user_sys_privs数据字典来验证用户的系统权限。
点击（此处）折叠或打开SYS@LINUXIDC> create user LINUXIDC2 identified by LINUXIDC2;
User created.SYS@LINUXIDC> grant connect to LINUXIDC2;Grant succeeded.SYS@LINUXIDC>
SYS@LINUXIDC> select privilege from role_sys_privs
where role="RESOURCE"; 2PRIVILEGE
----------------------------------------
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE8 rows selected.SYS@LINUXIDC>
SYS@LINUXIDC> select "grant "||PRIVILEGE||" to LINUXIDC2;" from role_sys_privs
where role="RESOURCE"; 2"GRANT"||PRIVILEGE||"TOLINUXIDC2;"
-----------------------------------------------------
grant CREATE SEQUENCE to LINUXIDC2;
grant CREATE TRIGGER to LINUXIDC2;
grant CREATE CLUSTER to LINUXIDC2;
grant CREATE PROCEDURE to LINUXIDC2;
grant CREATE TYPE to LINUXIDC2;
grant CREATE OPERATOR to LINUXIDC2;
grant CREATE TABLE to LINUXIDC2;
grant CREATE INDEXTYPE to LINUXIDC2;8 rows selected.SYS@LINUXIDC> grant CREATE SEQUENCE to LINUXIDC2;
grant CREATE TRIGGER to LINUXIDC2;
grant CREATE CLUSTER to LINUXIDC2;
grant CREATE PROCEDURE to LINUXIDC2;
grant CREATE TYPE to LINUXIDC2;
grant CREATE OPERATOR to LINUXIDC2;
grant CREATE TABLE to LINUXIDC2;
grant CREATE INDEXTYPE to LINUXIDC2;Grant succeeded.SYS@LINUXIDC>
Grant succeeded.SYS@LINUXIDC>
Grant succeeded.SYS@LINUXIDC>
Grant succeeded.SYS@LINUXIDC>
Grant succeeded.SYS@LINUXIDC>
Grant succeeded.SYS@LINUXIDC>
Grant succeeded.SYS@LINUXIDC>
Grant succeeded.SYS@LINUXIDC>
SYS@LINUXIDC>
SYS@LINUXIDC> conn LINUXIDC2/LINUXIDC2
Connected.
LINUXIDC2@LINUXIDC> create table test（id number）;Table created.LINUXIDC2@LINUXIDC> insert into test values（1）;
insert into test values（1）
*
ERROR at line 1:
ORA-01950: no privileges on tablespace "USERS"
LINUXIDC2@LINUXIDC>
LINUXIDC2@LINUXIDC>
LINUXIDC2@LINUXIDC> select privilege from user_sys_privs;PRIVILEGE
----------------------------------------
CREATE TABLE
CREATE CLUSTER
CREATE TYPE
CREATE TRIGGER
CREATE PROCEDURE
CREATE OPERATOR
CREATE INDEXTYPE
CREATE SEQUENCE8 rows selected.LINUXIDC2@LINUXIDC> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;USERNAME GRANTED_ROLE ADM
------------------------------ ------------------------------ ---
LINUXIDC2 CONNECT NOLINUXIDC2@LINUXIDC>
LINUXIDC2@LINUXIDC>
我们看到LINUXIDC2用户虽然拥有了resource角色下的所有系统权限，但是却没有unlimited tablespace系统权限，插入记录失败。测试3 将connect和resource角色授予新的角色
创建角色LINUXIDC，并将connect和resource角色授予这个角色；然后创建新用户LINUXIDC3，将LINUXIDC角色授予用户LINUXIDC3，尝试建表和插入操作。
点击（此处）折叠或打开SYS@LINUXIDC>SYS@LINUXIDC> create user LINUXIDC3 identified by LINUXIDC3;User created.SYS@LINUXIDC>
SYS@LINUXIDC> create role LINUXIDC;Role created.SYS@LINUXIDC> grant connect,resource to LINUXIDC;Grant succeeded.SYS@LINUXIDC> grant LINUXIDC to LINUXIDC3;Grant succeeded.SYS@LINUXIDC>
SYS@LINUXIDC> conn LINUXIDC3/LINUXIDC3
Connected.
LINUXIDC3@LINUXIDC>
LINUXIDC3@LINUXIDC> create table test（id number）;Table created.LINUXIDC3@LINUXIDC> insert into test values（1）;
insert into test values（1）
*
ERROR at line 1:
ORA-01950: no privileges on tablespace "USERS"
LINUXIDC3@LINUXIDC>
LINUXIDC3@LINUXIDC> select privilege from user_sys_privs;no rows selectedLINUXIDC3@LINUXIDC>
LINUXIDC3@LINUXIDC> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;USERNAME GRANTED_ROLE ADM
------------------------------ ------------------------------ ---
LINUXIDC3 LINUXIDC NOLINUXIDC3@LINUXIDC>
LINUXIDC3@LINUXIDC>
我们看到LINUXIDC3用户虽然拥有了LINUXIDC角色，并且LINUXIDC角色包含了connect角色resource角色，但是LINUXIDC3用户并没有unlimited tablespace系统权限，插入记录失败。也就是说，unlimited tablespace系统权限不会随着resource角色被授予LINUXIDC角色而级联授予给用户LINUXIDC3。测试4 直接授予用户unlimited tablespace系统权限
创建新用户LINUXIDC4，授予connect角色后，直接授予create table和unlimited tablespace系统权限，尝试建表和插入操作。SYS@LINUXIDC> create user LINUXIDC4 identified by LINUXIDC4;User created.SYS@LINUXIDC> grant connect to LINUXIDC4;Grant succeeded.SYS@LINUXIDC> grant create table to LINUXIDC4;Grant succeeded.SYS@LINUXIDC> grant unlimited tablespace to LINUXIDC4;Grant succeeded.SYS@LINUXIDC>
SYS@LINUXIDC> conn LINUXIDC4/LINUXIDC4
Connected.
LINUXIDC4@LINUXIDC>
LINUXIDC4@LINUXIDC> create table test（id number）;Table created.LINUXIDC4@LINUXIDC> insert into test values（1）;1 row created.LINUXIDC4@LINUXIDC>
LINUXIDC4@LINUXIDC> select privilege from user_sys_privs;PRIVILEGE
----------------------------------------
CREATE TABLE
UNLIMITED TABLESPACELINUXIDC4@LINUXIDC> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;USERNAME GRANTED_ROLE ADM
------------------------------ ------------------------------ ---
LINUXIDC4 CONNECT NOLINUXIDC4@LINUXIDC>
LINUXIDC4@LINUXIDC>
我们看到LINUXIDC4用户拥有了unlimited tablespace系统权限，插入记录成功。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址