Many of the times we DBA get requests from AUDITORS to apply security policies or DB hardening checklists on databases so that all schemas/users will be with least required privileges/roles.
Now here we will discuss about one of the most critical – UNILIMITED TABLESPACE privilege – is the one which allows – grantee to have unlimited quota on ALL tablespaces in the database.
Yesterday I faced very weird issue on one of the production databaase. Application owner raised a severity call with us that his application is throwing following error:
ORA-1536: space quota exceeded for tablespace ‘JVX_DATA’.
I quickly checked all privileges/roles that were assigned to application schema & granted UNILIMITED TABLESPACE system privilege to resolve the issue.
Issue resolved!! but why this happened now 😦 What was the reason behind this error. Definitely something must have gone wrong.
Luckily we were having audit_sys_operations parameter set to TRUE, which helped us for thorough investigation of the issue.
after going through in audit trace files, we found following audit records:
Mon Sep 1 12:19:08 2014 +02:00 LENGTH : '179' ACTION :[24] 'revoke dba from jvx' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[7] 'JVXDB' CLIENT TERMINAL:[5] 'pts/3' STATUS:[3] '604' DBID:[9] '494762650'
So in morning someone had revoked DBA role from application schema which was assigned to it earlier (This was done by one of our team member while applying security policies from auditors). IS THIS THE REASON BEHIND ORA-01536??
After searching through metalink we found note ORA-01536 After Revoking DBA Role (Doc ID 465737.1)
“We need to be very careful while revoking any grants/roles especially roles like DBA as revoking such roles will subsequently revoke all privileges which were explicitly assigned to schema along with role itself.”
“In our case UNLIMITED TABLESPACE privilege – which was granted to JVX schema explicitly – was revoked along with revoking DBA role.”
“Also we need to take an extra care while revoking UNILIMITED TABLESPACE, as this will revoke all granted quotas on any individual tablespace from the user.”
Hope so u will find this post very useful 🙂
Cheers
Regards,
Adityanath
Categories: Administration, ORA errors
Adityanath's Oracle Blog 
Hello all,
Need suggestions.It’s very urgent.
I am a bit new to Oracle PL/SQL.
The Oracle Developer team ,I am working with was having DBA privileges till now but as per the DBA’s need ,I have to revoke DBA priv’s from developers’ schema.this is my part of work to do the analysis and then revoke the DBA priv.But I am being very much confused.
Please tell which all privileges are needed by developer’s in environments like DEV /QA/UAT to continue the work as before.
sad.gif sad.gif sad.gif sad.gif sad.gif sad.gif sad.gif sad.gif
I dont want to mess up the functionality .please guide.
Hi Prashant,
Basically for doing any development work, u don’t need to have DBA role assigned to your schema.
Ask ur DBA’s to provide u list of all privileges under role DBA, (u can google it too) & just decide the one which are necessary & ask DBA’s to grant it. As developer u can only decide which privileges u need for ur development work.
It is not a good practice to grant DBA role to any application schema.
Regards,
Adi
Hello all,
Need suggestions.It’s very urgent.
I am a bit new to Oracle PL/SQL.
The Oracle Developer team ,I am working with was having DBA privileges till now but as per the DBA’s need ,I have to revoke DBA priv’s from developers’ schema.this is my part of work to do the analysis and then revoke the DBA priv.But I am being very much confused.
Please tell which all privileges are needed by developer’s in environments like DEV /QA/UAT to continue the work as before.
I dont want to mess up the functionality .please guide.
Hi Prashant,
Basically for doing any development work, u don’t need to have DBA role assigned to your schema.
Ask ur DBA’s to provide u list of all privileges under role DBA, (u can google it too) & just decide the one which are necessary & ask DBA’s to grant it. As developer u can only decide which privileges u need for ur development work.
It is not a good practice to grant DBA role to any application schema.
Regards,
Adi
Howdy! I’m at work surfing around your blog from my new apple iphone! Just wanted to say I love reading through your blog and look forward to all your posts! Carry on the outstanding work!
Having read this I believed it was rather informative. I appreciate you taking the time and energy to put this content together. I once again find myself spending way too much time both reading and leaving comments. But so what, it was still worthwhile!
This post is invaluable. When can I find out
more?