Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Database Security >> Database Security >> How to find DBA's on Oracle
        
(Message started by: Pete Finnigan on Jun 30^th, 2010, 3:41pm)

Title: How to find DBA's on Oracle
Post by Pete Finnigan on Jun 30^th, 2010, 3:41pm

Hi,

I am currently auditing some of the Oracle databases within my organisation and require a system generated list of users that have been assigned the role of DBA. What command/script could be given to the DBA's to run on the database to generate a useful output?

Also, what is the privilege assigned to a user that allows them to add/delete users? Again, what command/script can be given to the DBA's to run on the databases so that it will output a list of users who have this privilege assigned to their role?

Appreciate it if anyone can help asap as not getting far with the DBA's. ;)

Thanks

Sonia

Title: Re: How to find DBA's on Oracle
Post by Pete Finnigan on Jun 30^th, 2010, 4:32pm

The simple answer on your first question is:

select grantee
from dba_role_privs
where granted_role='DBA';

Even junior Oracle DBAs should be able to come up with this query themselves or find this in the Oracle docs/Google. But that might be my years of experience speaking.

As for the second question: the thing you're looking for, are the system privileges CREATE USER, DROP USER and possibly ALTER USER might be useful. These privileges might have been granted directly to a user or via a role.

This query checks and shows both:

select grantee, 'DIRECTLY' type, privilege priv_or_role
from dba_sys_privs
where privilege in ('CREATE USER', 'DROP USER', 'ALTER USER')
and grantee in (select username from dba_users)
UNION
select grantee, 'VIA ROLE' type, granted_role priv_or_role
from dba_role_privs
where granted_role in (
select grantee
from dba_sys_privs
where privilege in ('CREATE USER', 'DROP USER', 'ALTER USER')
and grantee in (select role from dba_roles)
);

Some Oracle accounts might also appear in these lists, like SYS or MDSYS.

But of course, for a complete audit of privileges, you might want to check Pete's scripts in the Scripts menu at this site. The output will probably very large.

Title: Re: How to find DBA's on Oracle
Post by Pete Finnigan on Jun 30^th, 2010, 6:02pm

This is great! just what I needed. I have sent this to the DBA's so see what happens next...

Thanks once again!

Sonia

Title: Re: How to find DBA's on Oracle
Post by Pete Finnigan on Jul 1^st, 2010, 3:10pm

Hi

After running the stated SQL queries, the output is not what I expected :(

The query regarding those users that have been assigend the DBA role gave an output of a few system accounts but we have a handful of DBA's in the org, that I assumed would have appeared on the list. Any ideas why this is the case? can individual's be assigned privileges without having the DBA role assigned to them? seems a bit illogical but what are your thoughts?

Also the 2nd query also resulted in generic systems accounts, where as individual users (DBA's) have the ability to do this??

Unless the DBA's are not informing me of something? like they are using the generic sys account to administer the db...

Let me know your thoughts either way.

Kind regards

Sonia

Title: Re: How to find DBA's on Oracle
Post by Pete Finnigan on Jul 1^st, 2010, 11:33pm

Well, it looks like that they didn't define seperate DBA accounts for themselves and just log in with SYS and SYSTEM. That happens a lot and in that case the output is correct.

Logging in with SYS/SYSTEM goes against the possible wish of being able to identify who did what (traceability and identification).

Title: Re: How to find DBA's on Oracle
Post by Pete Finnigan on Jul 2^nd, 2010, 11:05am

thank you very much! down to some further investigating now!

Title: Re: How to find DBA's on Oracle
Post by Pete Finnigan on Jul 2^nd, 2010, 3:57pm

Hi Sonia and Marcel-Jan,

Sounds like an interesting thread.

It sounds from your comments that your DBA's do have their own accounts as you expected to seer them listed. Is this the case? - do you know thatr they do have their own accounts?

As they were not granted the DBA role this does not mean its a bad thing. If they have their own accounts then you should prove this and also review the privileges granted to those accounts.

A check that simply tests who has DBA is fine as DBA is a risk but you should not assume from the results that its bad. If on the other hand your DBA's do not have their own accounts then Marcel-Jan is right, they are most likely using SYS and SYSTEM.

The check should not stop at DBA though. SYSDBA is just as bad, anyone with ALL PRIVILEGES is just as bad, anyone with IMP_FULL_DATABASE is just as bad, anyone with quite a number of specific system privileges including the ones you asked for and more are also dangerous.

Kind regards

Pete

Title: Re: How to find DBA's on Oracle
Post by Pete Finnigan on Jul 8^th, 2010, 11:02am

Hi Pete and Marcel-Jan,

Thank you both for your suggestions they were very useful. I have got the information that was required for my review but we are going to do a much more detailed review over Oracle Database, later in the year, for which we will be using the resources on your website.

Very useful information and quick responses so I'm impressed! Keep up the good work!

Sonia