关于unlimited tablespace系统权限

来源：互联网发布：杨幂养小鬼知乎编辑：程序博客网时间：2024/05/16 08:02

1. 系统权限unlimited tablespace是隐含(没有显式包括)在dba, resource角色中的一个系统权限；当用户得到dba或resource的角色时, unlimited tablespace系统权限也隐式受权给用户。

sys@JIAGULUN> create user t1 identified by t1;

User created.

sys@JIAGULUN> grant create session,create table to t1;

Grant succeeded.

sys@JIAGULUN> conn t1/t1;

Connected.

t1@JIAGULUN> create table t(id int);
create table t(id int)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

t1@JIAGULUN> conn / as sysdba
Connected.

sys@JIAGULUN> grant resource to t1;

Grant succeeded.

sys@JIAGULUN> conn t1/t1
Connected.

t1@JIAGULUN> create table t(id int);

Table created.

t1@JIAGULUN> conn / as sysdba
Connected.

sys@JIAGULUN> select * from dba_sys_privs where grantee='T1';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
T1 CREATE TABLE NO
T1 UNLIMITED TABLESPACE NO
T1 CREATE SESSION NO

sys@JIAGULUN> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO

2. 系统权限unlimited tablespace不能被授予role, 可以被授予用户.

sys@JIAGULUN> revoke unlimited tablespace from t1;

Revoke succeeded.

sys@JIAGULUN> select * from dba_sys_privs where grantee='T1';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
T1 CREATE TABLE NO
T1 CREATE SESSION

sys@JIAGULUN> CREATE ROLE r1;

Role created.

sys@JIAGULUN> grant unlimited tablespace to r1;
grant unlimited tablespace to r1
*
ERROR at line 1:
ORA-01931: cannot grant UNLIMITED TABLESPACE to a role

sys@JIAGULUN> grant unlimited tablespace to t1;

Grant succeeded.

3. 系统权限unlimited tablespace不会随着resource, dba被授予role而授予给用户.

sys@JIAGULUN> select * from dba_role_privs where grantee='T1';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
T1 RESOURCE NO YES

sys@JIAGULUN> revoke resource from t1;

Revoke succeeded.

sys@JIAGULUN> grant resource to r1;

Grant succeeded.

sys@JIAGULUN> select * from dba_role_privs where grantee='T1';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
T1 R1 NO YES

sys@JIAGULUN> select * from dba_sys_privs where grantee='T1';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
T1 CREATE TABLE NO
T1 CREATE SESSION NO

sys@JIAGULUN> conn t1/t1
Connected.

t1@JIAGULUN> drop table t;

Table dropped.

t1@JIAGULUN> create table t(id int);
create table t(id int)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

t1@JIAGULUN> conn / as sysdba
Connected.

sys@JIAGULUN> grant unlimited tablespace to t1;

Grant succeeded.

sys@JIAGULUN> conn t1/t1
Connected.

t1@JIAGULUN> create table t(id int);

Table created.

0 0