What Happens When You Revoke the DBA Role in Oracle? Key Impacts and Workarounds

Grant/Revoke Role Effect Timing

The experiment, valid for Oracle 10g through 11.2.0.4, shows that system privileges like UNLIMITED TABLESPACE granted via a role become effective immediately, while the role itself does not take effect until the session uses SET ROLE or the user logs out and back in. The test kept Session 2 open, granted the resource role in Session 1, and observed that the privilege was available instantly, but the role activation required a new login.

Conclusion:

System privileges granted through a role (e.g., UNLIMITED TABLESPACE) are applied immediately.

Granting or revoking a role itself does not take effect until the user issues SET ROLE or reconnects.

Impact of Revoking the DBA Role

Continuing the same sessions, the DBA role was granted and then revoked in Session 1 while Session 2 re‑logged. After revocation, a table was created and data inserted in Session 2 to verify behavior.

Conclusion:

When the DBA role is revoked, the UNLIMITED TABLESPACE privilege is also removed.

The removal of UNLIMITED TABLESPACE instantly exhausts the user’s tablespace quota, preventing any further storage use.

Handling After DBA Role Revocation

Because the user loses UNLIMITED TABLESPACE, administrators must grant alternative tablespace privileges to keep applications functional. Two common approaches are:

Assign a specific tablespace with a defined quota using ALTER USER … QUOTA … ON tablespace_name.

Grant the necessary system and object privileges individually to meet the application’s special requirements.

These steps ensure that after removing the DBA role, the user can still create tables and store data without violating the principle of least privilege.