Be careful !! ORA-01536 after revoking DBA role from user

Published on

September 2, 2014

1,239 hits

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

13 responses to “Be careful !! ORA-01536 after revoking DBA role from user”

Prashant Mishra

January 29, 2015

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.

Reply
1. Adityanath Dewoolkar
  
  January 30, 2015
  
  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
  
  Reply
Prashant Mishra

January 29, 2015

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.

Reply
1. Adityanath Dewoolkar
  
  January 30, 2015
  
  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
  
  Reply
Carlie Eagleson

November 9, 2020

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!

Reply
Sharilyn Dirker

August 7, 2021

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!

Reply
Lewis

August 24, 2021

This post is invaluable. When can I find out
more?

Reply
Mildred Lewis

September 13, 2023

I have read so many articles or reviews on the topic of the blogger lovers but this article is in fact a pleasant post, keep it up.

Reply
Anonymous

September 16, 2023

Very neat blog article.Really thank you! Will read on…

Reply
足球即時比分7m

September 18, 2023

I think this is a real great article.Thanks Again. Fantastic.

Reply
Anonymous

September 20, 2023

Very good post.Thanks Again. Fantastic.

Reply
Hudson Foster

September 21, 2023

Wow, great article post.Really looking forward to read more. Will read on…

Reply
Bret Rhodes

September 22, 2023

Hello mates, its great post on the topic of tutoringand fully explained, keep it up all the time.

Reply