ORA-01950报错解决

162次阅读

共计 6472 个字符，预计需要花费 17 分钟才能阅读完成。

从 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 LINUXIDC

DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP
QUOTA 3M ON users;

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

CREATE USER LINUXIDC IDENTIFIED BY LINUXIDC

DEFAULT 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 TABLESPACE

LINUXIDC1@LINUXIDC> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;

USERNAME GRANTED_ROLE ADM
—————————— —————————— —
LINUXIDC1 CONNECT NO
LINUXIDC1 RESOURCE NO

LINUXIDC1@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’; 2

PRIVILEGE
—————————————-
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE

8 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>
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 SEQUENCE

8 rows selected.

LINUXIDC2@LINUXIDC> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;

USERNAME GRANTED_ROLE ADM
—————————— —————————— —
LINUXIDC2 CONNECT NO

LINUXIDC2@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 selected

LINUXIDC3@LINUXIDC>
LINUXIDC3@LINUXIDC> select username,GRANTED_ROLE,ADMIN_OPTION from user_role_privs;

USERNAME GRANTED_ROLE ADM
—————————— —————————— —
LINUXIDC3 LINUXIDC NO

LINUXIDC3@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);