revoking DBA privs and ORA-01536

[randy]

I have a user that has no dba privilege. I granted the the user the DBA privilge.
Minutes later I revoked the dba privilege from the user. As soon as I revoked the privilege,
I started getting:

ORA-01536: space quota exceeded for tablespace

Any explanations?

[arjun]

Can you tell the exact REVOKE statement you used to revoke "DBA privilge"?

[slimdave]

i believe that the role granted unlimited tablespace to the user -- revoking it removed that privilege, but the user probably left tables in a TS they then had no quota on

[arjun]

Originally posted by slimdave
i believe that the role granted unlimited tablespace to the user -- revoking it removed that privilege, but the user probably left tables in a TS they then had no quota on

OK.. but does revoke change the quota of existing users on TS?

If so what may be the correct approach to revoke the DBA role under such circumstances?

[slimdave]

idon't think that granting the dba role establishes quotas in the same way as granting them through "alter user .. quota ... on ...".

MAybe the sol'n would be not to grant the dba role - pick out the exact privs you need.

[randy]

This is the syntax that I used to revoke the dba:

revoke dba from username;

[patel_dil]

hmm, Try granting him DBA role again and see if he gets this error. If he still gets Tablespace Quota Error, then it has got nothing to do with revoke. Check if he is accessing the right Tablespace, default tablespace, Quota, etc. In short, Assign proper Tablespace and required Quota to the User.