Re: Privileges granted by roles

From: MARK BRINSMEAD <mark.brinsmead_at_gmail.com>
Date: Tue, 21 Apr 2015 23:31:20 -0400
Message-ID: <CAAaXtLA-rBNaX0A-Hn-qFbwYZok6DpS-NBbcR9aBCb9PpaMj=g_at_mail.gmail.com>

Interesting. Of course, I knew that UNLIMITED TABLESPACE was included with resource, but I had never noticed that it was given as a DIRECT grant rather than inherited through the role. That's worth knowing about.

I totally agree about taking care in granting either RESOURCE or DBA roles. And, of course, the DBA role should only ever be granted to actual DBAs -- never to "application schemas" (nor to developers, nor to ...).

On Tue, Apr 21, 2015 at 7:00 PM, Adric Norris <landstander668_at_gmail.com> wrote:

>
> One oddity to keep in mind is that granting the RESOURCE role (and also
> DBA, for that matter), will silently confer a *direct* grant of the
> UNLIMITED TABLESPACE system privilege. Here's a quick example from 11.2.0.4
> on Linux.
>
> [SYSTEM_at_mydb] SQL> create user test identified by ThunderCougarFalconBird;
>
> User created.
>
> [SYSTEM_at_mydb] SQL> grant resource to test;
>
> Grant succeeded.
>
> [SYSTEM_at_mydb] SQL> select privilege from dba_sys_privs where grantee =
> 'TEST';
>
> PRIVILEGE
> ----------------------------------------
> UNLIMITED TABLESPACE
>
>
> I dislike side-effects, so my recommendation would be to create
> app-specific roles holding the necessary privileges, and avoid granting
> either RESOURCE or DBA.
>
> I haven't tested this under 12c, but wouldn't be at all surprised if the
> behaviour is still present. It's been like that in every release I've
> worked with to date.
>

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

Received on Wed Apr 22 2015 - 05:31:20 CEST