异常ORA-01950: 对表空间 ‘XXXXXX‘ 无权限

本文主要是介绍异常ORA-01950: 对表空间 ‘XXXXXX‘ 无权限，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

解决方法：grant unlimited tablespace to 用户；

1. 系统权限unlimited tablespace是隐含在dba, resource角色中的一个系统权限. 当用户得到dba或resource的角色时, unlimited tablespace系统权限也隐式受权给用户.
2. 系统权限unlimited tablespace不能被授予role, 可以被授予用户.
3. 系统权限unlimited tablespace不会随着resource, dba被授予role而授予给用户.

1 实验1

SQL> create user u1 identified by u1;User created.SQL> grant connect, resource to u1;Grant succeeded.SQL> select * from dba_role_privs a where a.grantee='U1';GRANTEE              GRANTED_ROLE         ADM DEF-------------------- -------------------- --- ---U1                   RESOURCE             NO  YESU1                   CONNECT              NO  YESSQL> select * from dba_sys_privs a where a.grantee='U1';GRANTEE              PRIVILEGE            ADM-------------------- -------------------- ---U1                   UNLIMITED TABLESPACE NOSQL> revoke unlimited tablespace from u1;Revoke succeeded.SQL> select * from dba_sys_privs a where a.grantee='U1';no rows selected

2 实验2

SQL> create role r1;Role created.SQL> grant unlimited tablespace to r1;ORA-01931: cannot grant UNLIMITED TABLESPACE to a role不能受权给角色r1.SQL> grant unlimited tablespace to u1;Grant succeeded.可以受权给用户u1.

3 实验3

SQL> revoke resource from u1;Revoke succeeded.SQL> grant resource to r1;Grant succeeded.SQL> grant r1 to u1;Grant succeeded.SQL> select * from dba_role_privs a where a.grantee='U1';GRANTEE              GRANTED_ROLE         ADM DEF-------------------- -------------------- --- ---U1                   R1                   NO  YESU1                   CONNECT              NO  YESSQL> select * from dba_sys_privs a where a.grantee='U1';no rows selected