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 : 'revoke dba from jvx' DATABASE USER: '/' PRIVILEGE : 'SYSDBA' CLIENT USER: 'JVXDB' CLIENT TERMINAL: 'pts/3' STATUS: '604' DBID: '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 🙂