Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: database privileges

Re: database privileges

From: Terry Dykstra <dontreply_tddykstra_at_forestoil.ca>
Date: Tue, 22 Oct 2002 18:33:09 GMT
Message-ID: <FRgt9.49189$ER5.3357099@news2.telusplanet.net>


Point taken. However, in our environment we don't grant roles to roles. We use a flat structure of assigning object/system privileges to roles.

--
Terry Dykstra
Canadian Forest Oil Ltd.
<kenneth.koenraadt_at_no-spam.hotmail.com> wrote in message
news:3db575a6.391172_at_news.mobilixnet.dk...

> Because it *is* exactly that difficult. I hope not many DBA's treat
> security as carelessly as you suggest!
>
>
> What about the following :
>
> create role role1;
> create role role2;
> create role role3;
> grant role3 to role2;
> grant role2 to role1;
> grant dba to role3;
> grant role3 to public;
>
> select * from dba_role_privs
> where grantee = 'PUBLIC';
>
>
> GRANTEE GRANTED_ROLE
> ADMIN_OPTION DEFAULT_ROLE
> ------------------------------ ------------------------------
> ------------ ------------
> PUBLIC ROLE3 NO
> YES
> 1 row selected
>
>
> PUBLIC has the DBA role, but your query does not reveal that. Because
> it does not take into consideration roles granted to roles.
>
> That problem is common, not only in Oracle but also in e.g. Novell and
> Win NT useradm. A user in NT can belong to a group, which again
> belongs to a group, belonging to a group with certain privileges.
>
> Of course you should create your user hierarchy without nesting
> privileges like that or at least keep it to a minimum. But when you
> stand with a DB you haven't had full control of for a period of time,
> you cannot rely on other administrators being just as careful. We are
> talking security here.
>
> I spent 1 hour writing a PL/SQL proc. which does the job correctly. It
> is not rocket science, and I'll mail it to you on request.
>
> - Kenneth Koenraadt
>
>
>
>
>
>
>
>
>
> On Tue, 22 Oct 2002 14:32:25 GMT, "Terry Dykstra"
> <dontreply_tddykstra_at_forestoil.ca> wrote:
>
> >Why do so difficult?
> >
> >SQL> grant dba to public;
> >
> >Grant succeeded.
> >
> >SQL> select * from dba_role_privs
> > 2 where grantee = 'PUBLIC';
> >
> >GRANTEE GRANTED_ROLE ADM DEF
> >------------------------------ ------------------------------ --- ---
> >PUBLIC DBA NO YES
> >
> >--
> >Terry Dykstra
> >Canadian Forest Oil Ltd.
> ><kenneth.koenraadt_at_no-spam.hotmail.com> wrote in message
> >news:3db5189a.1690530_at_news.mobilixnet.dk...
> >> On Mon, 21 Oct 2002 19:20:39 +0200, Sybrand Bakker
> >> <gooiditweg_at_sybrandb.demon.nl> wrote:
> >>
> >> >On 21 Oct 2002 09:39:05 -0700, oil1can_at_yahoo.com (mark) wrote:
> >> >
> >> >>What are the default privileges granted to public from users sys and
> >> >>system, and what privileges does public really need ?
> >> >>I am trying to create a user with a minimum of rights and privileges,
> >> >>and want to know what minimum specific grants and privileges from sys
> >> >>and system I can get by with.
> >> >>Thanks,
> >> >>Mark
> >> >
> >> >
> >> >select * from dba_tab_privs where grantee = 'PUBLIC'
> >> >and
> >> >select * from dba_sys_privs where grantee = 'PUBLIC'
> >> >
> >> >should give you the results.
> >>
> >> Nope.
> >>
> >> Not if the privileges granted to PUBLIC were granted through roles.
> >> Something (stupid, yes, but not uncommon...) like
> >>
> >> grant DBA to public;
> >>
> >> would not be discovered by your query.
> >>
> >> There is nothing else to do than to loop recursively through
> >> dba_sys_privs, dba_role_privs and dba_tab_privs. Regret.
> >>
> >>
> >>
> >> - Kenneth Koenraadt
> >>
> >>
> >>
> >>
> >>
> >> >Regards
> >> >
> >> >
> >> >Sybrand Bakker, Senior Oracle DBA
> >> >
> >> >To reply remove -verwijderdit from my e-mail address
> >>
> >
> >
>
Received on Tue Oct 22 2002 - 13:33:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US