Re: database privileges

From: <kenneth.koenraadt_at_no-spam.hotmail.com>
Date: Tue, 22 Oct 2002 16:19:55 GMT
Message-ID: <3db575a6.391172@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 - 11:19:55 CDT