Re: Privileges granted by roles

From: MARK BRINSMEAD <mark.brinsmead_at_gmail.com>
Date: Tue, 21 Apr 2015 15:32:05 -0400
Message-ID: <CAAaXtLAxDGdtk2-s7JQv082tAXhNStgbV=Nh9XJO4Bio9jhdQQ_at_mail.gmail.com>



Of course, I'm sure Mladen meant to prefix that with "By default ..."

The roles RESOURCE and CONNECT are standard roles provided in every Oracle database, and have a standard (and quite limited) set of default privileges.

But nothing stops somebody from granting *more* privileges to these roles. Well, nothing aside from "good practice", anyway. :-) In a well managed database, no additional privileges will *ever* be granted to roles like CONNECT and RESOURCE. But not all databases are (or were throughout their lifetime) "well managed".

Usually, there is little to fear from these roles, but when you are assessing security it is a BAD IDEA to make assumptions. This is why Tim and Sayan correctly recommend that you actually *verify* the privileges that have been granted to these roles, rather than simply assuming the default behaviour.

Note, by the way, that these roles can also be granted OTHER roles, so you need to unwind the grants recursively (that is, with a CONNECT BY query) to guarantee a full list of all privileges gained through these roles.

On Tue, Apr 21, 2015 at 1:17 PM, Mladen Gogala <dmarc-noreply_at_freelists.org> wrote:

> On 04/21/2015 05:51 AM, John Dunn wrote:
>
> Am I correct in saying that granting the following to a user will NOT
> grant them CREATE ANY TABLE or SELECT ANY TABLE privileges?
>
>
>
> GRANT RESOURCE TO JOHN;
> GRANT CREATE SESSION TO JOHN;
> GRANT CONNECT TO JOHN;
>
>
>
> …i.e. the user JOHN will NOT be able to perform select or create table on
> another schema?
>
>
>
> Am I correct?
>
>
>
> *John *
>
>
> In version 11G, the RESOURCE role is essentially meaningless. Grant
> connect is the same as "CREATE SESSION". John will have a privilege to
> connect to database and see everything that's granted to him or to public.
>
> --
> Mladen Gogala
> Oracle DBAhttp://mgogala.freehostia.com
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 21 2015 - 21:32:05 CEST

Original text of this message