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

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 S1 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.

Luckly 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

Advertisements

4 thoughts on “Be careful !! ORA-01536 after revoking DBA role from user

  1. 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

  2. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s