Explain the unlimited tablespace system permissions of Oracle

1.
The system permission unlimited tablespace is an implicit system permission in the DBA and resource roles. When a user obtains the role of DBA or resource,
The unlimited tablespace system permission is also implicitly authorized to the user.

2.
The system permission unlimited tablespace cannot be granted to role,
Can be granted to users.

3.
The system permission unlimited tablespace will not be granted to the user with the resource, and the DBA is granted the role.

1
Lab 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 Yes

U1
Connect No Yes

SQL>
Select * From dba_sys_privs A where a. grantee = 'u1 ';

Grantee
Privilege Adm

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

U1
Unlimited tablespace No

SQL>
Revoke unlimited tablespace from U1;

Revoke succeeded.

SQL>
Select * From dba_sys_privs A where a. grantee = 'u1 ';

No rows selected

2
Lab 2

SQL>
Create role R1;

Role created.

SQL>
Grant unlimited tablespace to R1;

ORA-01931: cannot grant unlimited tablespace to a role

Role R1.

 

SQL>
Grant unlimited tablespace to U1;

Grant succeeded.

Can be authorized to user u1.

3
Lab 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
Yes

U1
Connect No Yes

SQL> select * From dba_sys_privs A where a. grantee = 'u1 ';

No rows selected

The system permission does not have the unlimit tablespace system permission.