Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id F22551960716 for ; Thu, 7 Jul 2016 22:18:01 +0200 (CEST) Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTPS for ; Thu, 7 Jul 2016 22:18:01 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 494DE251AA; Thu, 7 Jul 2016 16:17:58 -0400 (EDT) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id osVF9SJ7NK3k; Thu, 7 Jul 2016 16:17:58 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6C7F124F5F; Thu, 7 Jul 2016 16:17:45 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 07 Jul 2016 16:16:23 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5878C24F81 for ; Thu, 7 Jul 2016 16:16:23 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id rtVIZBlJ6_3N for ; Thu, 7 Jul 2016 16:16:23 -0400 (EDT) Received: from smtp-sp-200-205.kinghost.net (smtp-sp-200-205.kinghost.net [177.185.200.205]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4195224F5F for ; Thu, 7 Jul 2016 16:16:21 -0400 (EDT) Received: from smtp200-farm64.kinghost.net (smtp200-farm64 [127.0.0.1]) by smtp-sp-200-205.kinghost.net (Postfix) with ESMTP id B49B0EF61E58 for ; Thu, 7 Jul 2016 18:14:16 -0300 (BRT) Received: from smtp-sp-201-174.kinghost.net (smtp-sp-201-174.kinghost.net [177.185.201.174]) (using TLSv1 with cipher ADH-AES256-SHA (256/256 bits)) (No client certificate requested) by smtp200-farm64.kinghost.net (Postfix) with ESMTPS id AAAB191726808 for ; Thu, 7 Jul 2016 18:14:16 -0300 (BRT) Received: from smtp-node-13-farm64.kinghost.net (smtp-node-13-farm64.kinghost.net [127.0.0.1]) (using TLSv1.2 with cipher AECDH-AES256-SHA (256/256 bits)) (No client certificate requested) by smtp-sp-201-174.kinghost.net (Postfix) with ESMTPS id 0B6F914BC8F for ; Thu, 7 Jul 2016 17:16:18 -0300 (BRT) Received: from [192.168.1.113] (unknown [187.111.15.86]) (using TLSv1 with cipher ECDHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: rodrigo@mufalani.com.br) by smtp-node-13-farm64.kinghost.net (Postfix) with ESMTPSA id ED074D172C58 for ; Thu, 7 Jul 2016 17:16:17 -0300 (BRT) From: Rodrigo Mufalani Content-Type: multipart/alternative; boundary="Apple-Mail=_7A69D475-91BB-4554-974A-3EDC65537D88" Subject: Behavior on 11gr2 - revoke dba grant remove quotas too Message-Id: Date: Thu, 7 Jul 2016 17:16:17 -0300 To: oracle-l Mime-Version: 1.0 (Mac OS X Mail 9.2 \(3112\)) X-archive-position: 65487 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: rodrigo@mufalani.com.br Precedence: normal Reply-To: rodrigo@mufalani.com.br List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --Apple-Mail=_7A69D475-91BB-4554-974A-3EDC65537D88 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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). =20 The issue only happened just on 11gR2 (I tested on 11.2.0.3, 11.2.0.4 = and 11.2.0.1) versions. =20 Not happened on 10g=20 SQL> select version from v$instance; VERSION =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=93 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=3D=E2=80=99TESTE_MUFALANI=E2= =80=99; TABLESPACE_NAME USERNAME BYTES = MAX_BYTES BLOCKS MAX_BLOCKS DRO =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94 =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94=E2=80=94 =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80= =94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- = =E2=80=94 USERS TESTE_MUFALANI 0 = -1 0 -1 NO SQL> revoke dba from teste_mufalani; SQL> select * from dba_ts_quotas where username=3D=E2=80=99TESTE_MUFALANI=E2= =80=99; TABLESPACE_NAME USERNAME BYTES = MAX_BYTES BLOCKS MAX_BLOCKS DRO =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94 =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94=E2=80=94 =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80= =94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- = =E2=80=94 USERS TESTE_MUFALANI 0 = -1 0 -1 NO On 11g=20 SQL> select version from v$instance; VERSION =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=93 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=3D=E2=80=99TESTE_MUFALANI=E2= =80=99; TABLESPACE_NAME USERNAME BYTES = MAX_BYTES BLOCKS MAX_BLOCKS DRO =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94 =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94=E2=80=94 =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80= =94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- = =E2=80=94 USERS TESTE_MUFALANI 0 = -1 0 -1 NO SQL> revoke dba from teste_mufalani; SQL> select * from dba_ts_quotas where username=3D=E2=80=99TESTE_MUFALANI=E2= =80=99; The tbs quotas were disappeared. Not happened on 12c=20 SQL> select version from v$instance; VERSION =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=93 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=3D=E2=80=99TESTE_MUFALANI=E2= =80=99; TABLESPACE_NAME USERNAME = BYTES MAX_BYTES = BLOCKS MAX_BLOCKS DRO =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94 = =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94= =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94 = =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94= =E2=80=94- =E2=80=94=E2=80=94=E2=80=94- USERS TESTE_MUFALANI = 0 -1 = 0 -1 NO SQL> revoke dba from teste_mufalani; SQL> select * from dba_ts_quotas where username=3D=E2=80=99TESTE_MUFALANI=E2= =80=99; TABLESPACE_NAME USERNAME = BYTES MAX_BYTES = BLOCKS MAX_BLOCKS DRO =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94 = =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94= =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94 = =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94= =E2=80=94- =E2=80=94=E2=80=94=E2=80=94- USERS TESTE_MUFALANI = 0 -1 = 0 -1 NO=20 All the best, =C2=A0=C2=A0 Rodrigo Mufalani - Diretor T=C3=A9cnico | = rodrigo@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 =C2=A0=C2=A0 --Apple-Mail=_7A69D475-91BB-4554-974A-3EDC65537D88 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
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
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=93
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=3D=E2=80=99TESTE_MUFALANI=E2=80= =99;

TABLESPACE_NAME        =         = USERNAME           =             &n= bsp;    BYTES  MAX_BYTES     = BLOCKS MAX_BLOCKS DRO
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94 =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94=E2=80=94 =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80= =94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- = =E2=80=94
= USERS           &nb= sp;            = ;  = TESTE_MUFALANI          =             &n= bsp;   0         = -1          = 0         -1 NO

SQL> revoke dba from teste_mufalani;

SQL> select * from dba_ts_quotas where = username=3D=E2=80=99TESTE_MUFALANI=E2=80=99;

TABLESPACE_NAME        =         = USERNAME           =             &n= bsp;    BYTES  MAX_BYTES     = BLOCKS MAX_BLOCKS DRO
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94 =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94=E2=80=94 =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80= =94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- = =E2=80=94
= USERS           &nb= sp;            = ;  = TESTE_MUFALANI          =             &n= bsp;   0         = -1          = 0         -1 NO


On 11g 

SQL> select version from v$instance;

VERSION
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=93
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=3D=E2=80=99TESTE_MUFALANI=E2=80= =99;

TABLESPACE_NAME        =         = USERNAME           =             &n= bsp;    BYTES  MAX_BYTES     = BLOCKS MAX_BLOCKS DRO
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94 =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94=E2=80=94 =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80= =94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- = =E2=80=94
= USERS           &nb= sp;            = ;  = TESTE_MUFALANI          =             &n= bsp;   0         = -1          = 0         -1 NO

SQL> revoke dba from teste_mufalani;

SQL> select * from dba_ts_quotas where = username=3D=E2=80=99TESTE_MUFALANI=E2=80=99;

The tbs = quotas were disappeared.

Not happened on = 12c 

SQL> select version from v$instance;

VERSION
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=93
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=3D=E2=80=99TESTE_MUFALANI=E2=80=99;

TABLESPACE_NAME        =        =20 = USERNAME           =             &n= bsp;           &nbs= p;            =             &n= bsp;           &nbs= p;            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94 = =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94= =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94 = =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94= =E2=80=94- =E2=80=94=E2=80=94=E2=80=94-
= USERS           &nb= sp;            = ; =20 = TESTE_MUFALANI          =             &n= bsp;           &nbs= p;            =             &n= bsp;           &nbs= p;            0         = -1          = 0         -1 NO

SQL> revoke dba from teste_mufalani;

SQL> select * from dba_ts_quotas where = username=3D=E2=80=99TESTE_MUFALANI=E2=80=99;

TABLESPACE_NAME        =        =20 = USERNAME           =             &n= bsp;           &nbs= p;            =             &n= bsp;           &nbs= p;            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94 = =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94= =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94 = =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94=E2=80=94- =E2=80=94=E2=80=94= =E2=80=94- =E2=80=94=E2=80=94=E2=80=94-
= USERS           &nb= sp;            = ; =20 = TESTE_MUFALANI          =             &n= bsp;           &nbs= p;            =             &n= bsp;           &nbs= p;           0         = -1          = 0         -1 NO 

All the best,
  
Rodrigo Mufalani - Diretor = T=C3=A9cnico | rodrigo@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
3D"Mufalani"3D"acelogo"

   


= --Apple-Mail=_7A69D475-91BB-4554-974A-3EDC65537D88-- -- http://www.freelists.org/webpage/oracle-l