Different handling of UNLIMITED QUOTA when you revoke the DBA role between 11.2.0.4 and future releases

“Oh heck” you may think. “Why is he blogging about 11.2.0.4?”. But as a matter of fact, I work right now with a customer in Germany where we try to lift a fairly large database into Autonomous Database Serverless (ADB-S). And as I love to learn new things every day but (as most of us) have just limited brain capacity, I prefer to put things on the blog. Only challenge as Klaus from our team demonstrated to me yesterday: I need to remember that I wrote something about it. Anyhow, since we still have a number of customers on Oracle Database 11.2.0.4 out there, this may be useful for one or another project. Today, I learned that there is a different behavior when you revoke the DBA role between 11.2.0.4 and future releases.

Photo by Will Echols on Unsplash

How did we come across this?

In the following weeks, as soon as time allows, I will write a bit more about my learnings with ADB-S migrations. Moving to ADB-S is not as straight forward as a normal migration. It is a logical migration where you may need to change some things since ADB-S is not only locked-down in some way, but for instance some default roles have different names.

Now, in order to proceed with our migration, we do some massaging to users before we export on the source database. Reason for the massaging is that we want to achieve a smooth import into ADB-S. In ADB-S, there is no DBA role. It has an equivalent role with a different name and scope. Hence, the users get the DBA role revoked before the export to prevent and import error. The customer then granted UNLIMITED QUOTA on the DATA tablespace in source to a user. Well, the DATA tablespace does not exist on source but we created it since ADB-S has only this tablespace. Idea simply is that the user will be created with exactly this quota on DATA, and everything can be created then during import.

In theory, this works magically smooth – but we didn’t take into account that 11.2 and earlier releases behave differently than 12.1 and newer releases.

What happened?

This is the simple test case.

create tablespace data datafile '/u02/oradata/V121/data01.dbf' size 5M online;

create user REMOTE identified by remote;
alter user REMOTE quota unlimited on DATA;
grant RESTRICTED SESSION, DBA to REMOTE;

select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username = 'REMOTE';

revoke dba from remote;

The expectation was simply that user REMOTE still has UNLIMITED QUOTA on tablespace DATA.

And this is the case at least in Oracle Database 12.1.0.2 and any newer release including 19c and 23ai. But it is different in Oracle 11.2.0.4 and previous releases.

Let’s query the quota

When you execute the above actions in Oracle 19c, this is the result:

select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username = 'REMOTE';

TABLESPACE_NAME      USERNAME		BYTES  MAX_BYTES
-------------------- ------------- ---------- ----------
DATA		     REMOTE		    0	      -1

Expected. Why should revoking the DBA role revoke the quota on tablespace DATA?

Well, but in Oracle 11.2.0.4 (and supposingly older releases, too) the result is different:

select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username = 'REMOTE';

no rows selected

I didn’t really drill into the root cause for this change but I found some bugs in 11.2 being closed as “not a bug” with an explanation why revoking system privileges should revoke also quota. But obviously, this had been either changed or something has been altered with the role’s contents since Oracle 12.1. In the Links section I list some of my findings.

This has an interesting side effect in 11.2.0.4 which I cannot reproduce in newer release from 12.1.0.2 on:

alter user remote quota 199M on users;

select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username = 'REMOTE';

TABLESPACE_NAME     USERNAME              BYTES      MAX_BYTES
------------------- --------------------- ---------- ----------
USERS               REMOTE                0          208666624


grant dba to remote;
revoke dba from remote;

select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username = 'REMOTE';

no rows selected

So, in any case I revoke the DBA role from a user in 11.2.0.4 all tablespace quota will be wiped out.

Well, but things aren’t that obvious as they seem

Now, I ran the previous test in my 12.1.0.2 database (and in my 19.23.0 one as well). But the result was quite surprising to me.

alter user remote quota 199M on users;

select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username = 'REMOTE';

TABLESPACE_NAME      USERNAME		BYTES  MAX_BYTES
-------------------- ------------- ---------- ----------
USERS		     REMOTE		    0  208666624
DATA		     REMOTE		    0	      -1

grant dba to remote;
revoke dba from remote;


select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username = 'REMOTE';

TABLESPACE_NAME      USERNAME		BYTES  MAX_BYTES
-------------------- ------------- ---------- ----------
DATA		     REMOTE		    0	      -1

Interesting, isn’t it? The UNLIMITED QUOTA on tablespace DATA stays, but the 199MB quota on tablespace USERS has been removed.

Now, I wanted to see whether the individual quota disappears as well when I grant it unlimited.

alter user remote quota unlimited on users;

grant dba to remote;
revoke dba from remote;

select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username = 'REMOTE';

TABLESPACE_NA USERNAME		 BYTES	MAX_BYTES
------------- ------------- ---------- ----------
USERS	      REMOTE		     0	       -1
DATA	      REMOTE		     0	       -1

Nope. Hence, I conclude that UNLIMITED QUOTA is handled differently.

Summary

When you move from 11.2.0.4 to 19c, be aware that this is a tiny behavior change. You may not encounter this at all. But we’ve been hit by it since we were building some workarounds to smoothen the migration into ADB-S. For some or many of you, this may be well known knowledge, but for me it was totally new.

I tried really hard to find doc or a MOS note explaining the change but I failed. In case you know a MOS note explaining it, let me know and I happily add it to my blog post.

Further Links and Information

• MOS Note: 1084014.6 – Revoking DBA or RESOURCE Roles Revokes UNLIMITED TABLESPACE from the User
• MOS Note: 1169515.1 – When Revoking Unlimited Tablespace System Privilege Tablespace Quota Also Removed (until 12.1.0.1)

–Mike