Re: Behavior on 11gr2 - revoke dba grant remove quotas too

Hello Rodrigo,

Per metalink doc#465737.1, this was by design. The user would stop enjoying TS quota, once the DBA role is revoked. I guess, the philosophy would have been an extra caution while revoking DBA (as this would possibly a case of exiting DBA).

However, this is applied from version 8.1.7.4 to 11.2.0.4. Hence, your outcome in 12c is well expected, but 10g should be same as 11g.

Regards,
Abhinav Gupta

On Fri, Jul 8, 2016 at 1:46 AM, Rodrigo Mufalani <rodrigo_at_mufalani.com.br> wrote:

> Hi all,
>
> I ran into a non-expected behavior today when I removed the DBA role
> from a specific user and on 11g there were a lot of ORA-01536 on
> application. So, I've granted the quotas on tablespaces and realized that
> the problem occurred with the revoke command (the user had quotas on those
> tablespaces before the grant dba).
>
> The issue only happened just on 11gR2 (I tested on 11.2.0.3, 11.2.0.4
> and 11.2.0.1) versions.
>
> Not happened on 10g
>
> SQL> select version from v$instance;
>
> VERSION
> —————–
> 10.2.0.5.0
>
> SQL> create user teste_mufalani identified by teste_mufalani quota
> unlimited on users;
>
> SQL> grant dba to teste_mufalani;
>
> SQL> select * from dba_ts_quotas where username=’TESTE_MUFALANI’;
>
> TABLESPACE_NAME USERNAME BYTES
> MAX_BYTES BLOCKS MAX_BLOCKS DRO
> —————————— —————————— ———- ———- ———- ———- —
> USERS TESTE_MUFALANI
> 0 -1 0 -1 NO
>
> SQL> revoke dba from teste_mufalani;
>
> SQL> select * from dba_ts_quotas where username=’TESTE_MUFALANI’;
>
> TABLESPACE_NAME USERNAME BYTES
> MAX_BYTES BLOCKS MAX_BLOCKS DRO
> —————————— —————————— ———- ———- ———- ———- —
> USERS TESTE_MUFALANI
> 0 -1 0 -1 NO
>
>
> On 11g
>
> SQL> select version from v$instance;
>
> VERSION
> —————–
> 11.2.0.4.0
>
> SQL> create user teste_mufalani identified by teste_mufalani quota
> unlimited on users;
>
> SQL> grant dba to teste_mufalani;
>
> SQL> select * from dba_ts_quotas where username=’TESTE_MUFALANI’;
>
> TABLESPACE_NAME USERNAME BYTES
> MAX_BYTES BLOCKS MAX_BLOCKS DRO
> —————————— —————————— ———- ———- ———- ———- —
> USERS TESTE_MUFALANI
> 0 -1 0 -1 NO
>
> SQL> revoke dba from teste_mufalani;
>
> SQL> select * from dba_ts_quotas where username=’TESTE_MUFALANI’;
>
> The tbs quotas were disappeared.
>
> Not happened on 12c
>
> SQL> select version from v$instance;
>
> VERSION
> —————–
> 12.1.0.2.0
>
> SQL> create user teste_mufalani identified by teste_mufalani quota
> unlimited on users;
>
> SQL> grant dba to teste_mufalani;
>
> SQL> select * from dba_ts_quotas where username=’TESTE_MUFALANI’;
>
> TABLESPACE_NAME
> USERNAME
> BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
> —————————— ————————————————————————————————— ———- ———- ———- ———-
> USERS
> TESTE_MUFALANI
> 0 -1 0 -1 NO
>
> SQL> revoke dba from teste_mufalani;
>
> SQL> select * from dba_ts_quotas where username=’TESTE_MUFALANI’;
>
> TABLESPACE_NAME
> USERNAME
> BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
> —————————— ————————————————————————————————— ———- ———- ———- ———-
> USERS
> TESTE_MUFALANI
> 0 -1 0 -1 NO
>
> All the best,
>
> <http://www.mufalani.com.br>Rodrigo Mufalani - Diretor Técnico |
> rodrigo_at_mufalani.com.br | +55 21 988 994 817
> Mufalani - +55 21 3193 0326 | Rua Alm Grenfall, 405, Bl 3, Sl 310, Centro
> Empresarial
> Washington Luiz, Duque de Caxias, RJ | CEP 25085-009 | www.mufalani.com.br
> <rodrigo_at_mufalani.com.br>
> <http://www.mufalani.com.br>[image: Mufalani] <http://www.mufalani.com.br>[image:
> acelogo]
>
> <http://www.mufalani.com.br>
>
>

--
http://www.freelists.org/webpage/oracle-l