回收resource角色unlimited tablespace权限

回收resource角色unlimited tablespace权限

𝓓𝓸𝓷 Lv6
  2026-01-28 18:39:21 2026-01-28 18:39:21 Created   2026-01-28 18:39:53 2026-01-28 18:39:53 Updated    

UNLIMITED TABLESPACE存在一定的安全隐患，需谨慎使用，这种权限允许用户在所有表空间中自由使用，不受空间限制

一、创建表空间及用户

1
2
create tablespace logincenter_yc datafile '+DATA' size 500M autoextend on next 100M maxsize 20g;
create user logincenter_yc identified by "uT_9hK$11jva" default tablespace logincenter_yc ;

二、设置默认表空间quota

1
2
3
4
alter user logincenter_yc quota unlimited on logincenter_yc;

查看quota限额:
select * from dba_ts_quotas;

三、授权resource权限

1
grant connect,resource to logincenter_yc;

四、查看用户权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
SQL> select * from dba_sys_privs where grantee='CONNECT';

GRANTEE 		       PRIVILEGE		      ADM
------------------------------ ------------------------------ ---
CONNECT 		       CREATE SESSION		      NO


SQL> 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

8 rows selected.

SQL> select * from dba_role_privs a where a.grantee='LOGINCENTER_YC';

GRANTEE 		       GRANTED_ROLE		      ADM DEF
------------------------------ ------------------------------ --- ---
LOGINCENTER_YC		       CONNECT			      NO  YES
LOGINCENTER_YC		       RESOURCE 		      NO  YES


set linesize 500
set pagesize 500
col role for a20
col username for a23
col privilege for a30
col default_tablespace for a23
col temporary_tablespace for a23
col profile for a14
select a.grantee username, a.granted_role role,b.privilege,c.default_tablespace,c.temporary_tablespace,c.profile from dba_role_privs a,role_sys_privs b,dba_users c where a.granted_role=b.role and
 a.grantee='LOGINCENTER_YC' and a.grantee=c.username union select grantee username,(select 'N/A' from dual) role,privilege,d.default_tablespace,d.temporary_tablespace,profile from
 dba_sys_privs,dba_users d where grantee='LOGINCENTER_YC' and grantee=d.username  order by username;
 
 USERNAME		ROLE		     PRIVILEGE			    DEFAULT_TABLESPACE	    TEMPORARY_TABLESPACE    PROFILE
----------------------- -------------------- ------------------------------ ----------------------- ----------------------- --------------
LOGINCENTER_YC		CONNECT 	     CREATE SESSION		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		N/A		     UNLIMITED TABLESPACE	    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE CLUSTER		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE INDEXTYPE		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE OPERATOR		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE PROCEDURE		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE SEQUENCE		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE TABLE		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE TRIGGER		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE TYPE		    LOGINCENTER_YC	    TEMP		    DEFAULT

10 rows selected.

五、回收resource角色unlimited tablespace权限

1
revoke unlimited tablespace from logincenter_yc;

六、再次查看权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
set linesize 500
set pagesize 500
col role for a20
col username for a23
col privilege for a30
col default_tablespace for a23
col temporary_tablespace for a23
col profile for a14
select a.grantee username, a.granted_role role,b.privilege,c.default_tablespace,c.temporary_tablespace,c.profile from dba_role_privs a,role_sys_privs b,dba_users c where a.granted_role=b.role and
 a.grantee='LOGINCENTER_YC' and a.grantee=c.username union select grantee username,(select 'N/A' from dual) role,privilege,d.default_tablespace,d.temporary_tablespace,profile from
 dba_sys_privs,dba_users d where grantee='LOGINCENTER_YC' and grantee=d.username  order by username;


USERNAME		ROLE		     PRIVILEGE			    DEFAULT_TABLESPACE	    TEMPORARY_TABLESPACE    PROFILE
----------------------- -------------------- ------------------------------ ----------------------- ----------------------- --------------
LOGINCENTER_YC		CONNECT 	     CREATE SESSION		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE CLUSTER		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE INDEXTYPE		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE OPERATOR		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE PROCEDURE		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE SEQUENCE		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE TABLE		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE TRIGGER		    LOGINCENTER_YC	    TEMP		    DEFAULT
LOGINCENTER_YC		RESOURCE	     CREATE TYPE		    LOGINCENTER_YC	    TEMP		    DEFAULT

9 rows selected.

  • Title: 回收resource角色unlimited tablespace权限
  • Author: 𝓓𝓸𝓷
  • Created at : 2026-01-28 18:39:21
  • Updated at : 2026-01-28 18:39:53
  • Link: https://www.zhangdong.me/revoke-unlimited-tablespace-privs.html
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐文章
查看Oracle用户权限及角色权限 查看Oracle用户权限及角色权限 查看Oracle表空间使用情况 查看Oracle表空间使用情况 查看Oracle表空间增长过快原因 查看Oracle表空间增长过快原因
推荐文章
查看Oracle用户权限及角色权限 查看Oracle用户权限及角色权限 查看Oracle表空间使用情况 查看Oracle表空间使用情况
评论
  1. 一、创建表空间及用户
  2. 二、设置默认表空间quota
  3. 三、授权resource权限
  4. 四、查看用户权限
  5. 五、回收resource角色unlimited tablespace权限
  6. 六、再次查看权限