Unlimited tablespace Permissions

When a user is added to Oracle, a connect or resource role is usually assigned, as shown in the following figure:

　　Grant connect, resource to chenwm;

However, the connect role has an unlimited tablespace permission, that is, the chenwm user can

To create tables.

Connected
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

Connected as sys

SQL>Select * From dba_sys_privs where grantee = 'chenwm ';

Grantee privilege admin_option

----------------------------------------------------------------------------------

Chenwm unlimited tablespace No

SQL>

Generally, DBA should disable the unlimited tablespace permission.

SQL>Revoke unlimited tablespace from chenwm;# Revoke the unlimited tablespace permission

Revoke succeeded

SQL>Alter user chenwm quota unlimited on chenwm;# The following chenwm is the tablespace name.

User altered

 

View online information

Unlimted tablespace SYSTEM privileges cannot be granted to roles (as described in the Oracle document)

If the DBA or resource role is granted to the user, the user is automatically granted the system permission of the unlimited tablespace. If you want to revoke the role, the unlimited tablespace system permission will also be revoked. Of course, the unlimited tablespace permission can also be revoked separately.