Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Welcome, Guest. Please Login.
Nov 17th, 2017, 9:19pm
News: Welcome to Pete Finnigan's Oracle security forum
Home | Help | Search | Members | Login
   Pete Finnigan's Oracle Security Forum
   Database Security
   Database Security
(Moderator: Pete Finnigan)
   How to find DBA's on Oracle
« Previous topic | Next topic »
Pages: 1  Reply | Notify of replies | Send Topic | Print
   Author  Topic: How to find DBA's on Oracle  (Read 16176 times)
sonia
PeteFinnigan.com Newbie
*





   
View Profile |

Gender: female
Posts: 5
How to find DBA's on Oracle
« on: Jun 30th, 2010, 3:41pm »
Quote | Modify

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.  Wink
 
Thanks
 
Sonia
IP Logged
Marcel-Jan
PeteFinnigan.com Junior Member
**






   
View Profile | WWW |

Gender: male
Posts: 83
Re: How to find DBA's on Oracle
« Reply #1 on: Jun 30th, 2010, 4:32pm »
Quote | Modify

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.
 
« Last Edit: Jun 30th, 2010, 4:34pm by Marcel-Jan » IP Logged
sonia
PeteFinnigan.com Newbie
*





   
View Profile |

Gender: female
Posts: 5
Re: How to find DBA's on Oracle
« Reply #2 on: Jun 30th, 2010, 6:02pm »
Quote | Modify

This is great! just what I needed. I have sent this to the DBA's so see what happens next...
 
Thanks once again!
 
Sonia
IP Logged
sonia
PeteFinnigan.com Newbie
*





   
View Profile |

Gender: female
Posts: 5
Re: How to find DBA's on Oracle
« Reply #3 on: Jul 1st, 2010, 3:10pm »
Quote | Modify

Hi  
 
After running the stated SQL queries, the output is not what I expected   Sad
 
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
IP Logged
Marcel-Jan
PeteFinnigan.com Junior Member
**






   
View Profile | WWW |

Gender: male
Posts: 83
Re: How to find DBA's on Oracle
« Reply #4 on: Jul 1st, 2010, 11:33pm »
Quote | Modify

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).
IP Logged
sonia
PeteFinnigan.com Newbie
*





   
View Profile |

Gender: female
Posts: 5
Re: How to find DBA's on Oracle
« Reply #5 on: Jul 2nd, 2010, 11:05am »
Quote | Modify

thank you very much! down to some further investigating now!
« Last Edit: Jul 2nd, 2010, 11:06am by sonia » IP Logged
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
Re: How to find DBA's on Oracle
« Reply #6 on: Jul 2nd, 2010, 3:57pm »
Quote | Modify

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
IP Logged

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
sonia
PeteFinnigan.com Newbie
*





   
View Profile |

Gender: female
Posts: 5
Re: How to find DBA's on Oracle
« Reply #7 on: Jul 8th, 2010, 11:02am »
Quote | Modify

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
IP Logged
Pages: 1  Reply | Notify of replies | Send Topic | Print

« Previous topic | Next topic »

Powered by YaBB 1 Gold - SP 1.4!
Forum software copyright 2000-2004 Yet another Bulletin Board