Call: +44 (0)1904 557620 Call
Forum

Welcome, Guest. Please Login.
Mar 28th, 2024, 9:56pm
News: If you would like to register contact the forum admin
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 21380 times)
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
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

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
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 #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.
 
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
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 #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

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
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 #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

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
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 #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

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
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 #5 on: Jul 2nd, 2010, 11:05am »
Quote | Modify

thank you very much! down to some further investigating now!
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
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
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 #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

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
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
  • PFCLScan PFCLScan

    Simply connect PFCLScan to your Oracle database and it will automatically discover the security issues that could make your Oracle database vulnerable to attack and to the potential loss of your data.

  • PFCL Obfuscate PFCLObfuscate

    PFCLObfuscate is the only tool available that can automatically add license controls to your PL/SQL code. PFCLObfuscate protects your Intellectual Property invested in your PL/SQL database code.

  • PFCLCode PFCLCode

    PFCLCode is a tool to allow you to analyse your PL/SQL code for many different types of security issues. PFCLCode gives you a detailed review and reports and includes a powerful colour syntax highlighting code editor

  • PFCLForensics PFCLForensics

    PFCLForensics is the only tool available to allow you to do a detailed live response of a breached Oracle database and to then go on and do a detailed forensic analysis of the data gathered.

  • Products We resell PFCLReselling

    PeteFinnigan.com Limited has partnered with a small number of relevant companies to resell their products where they enhance or compliment what we do

  • PFCLATK PFCLATK

    PFCLATK is a toolkit that allows detailed pre-defined policy driven audit trails for your Oracle database. The toolkit also provides for a centralised audit trail and centralised activity reporting

  • PFCLCookie PFCLCookie

    PFCLCookie is a useful tool to use to audit your websites for tracking cookies. Scan websites in a natural way using powerful browser driven scanner

  • PFCL Training PFCLTraining

    PFCLTraining is a set of expert training classes for you, aimed at teaching how to audit your own Oracle database, design audit trails, secure code in PL/SQL and secure and lock down your Oracle database.

  • PFCL Services PFCLServices

    Choose PFCLServices to add PeteFinnigan.com Ltd to your team for your Oracle Security needs. We are experts in performing detailed security audits, data security design work and policy creation

  • PFCLConsulting PFCLConsulting

    Choose PFCLConsulting to ask PeteFinnigan.com Limited to set up and use our products on your behalf

  • PFCLCustom PFCLCustom

    All of our software products can be customised at a number of levels. Choose this to see how our products can be part of your products and services

  • PFCLCloud PFCLCloud

    Private cloud, public cloud, hybrid cloud or no cloud. Learn how all of our services, trainings and products will work in the cloud

  • PFCLUserRights PFCLUserRights

    PFCLUserRights allows you to create a very detailed view of database users rights. The focus of the reports is to allow you to decide what privileges and accounts to keep and which to remove.

  • PFCLSTK PFCLSTK

    PFCLSTK is a toolkit application that allows you to provide database security easily to an existing database. PFCLSTK is a policy driven toolkit of PL/SQL that creates your security

  • PFCLSFTK PFCLSFTK

    PFCLSFTK is a toolkit that solves the problem of securing third party applications written in PL/SQL. It does this by creating a thin layer between the application and database and this traps SQL Injection attempts. This is a static firewall.

  • PFCLSEO PFCLSEO

    PFCLSEO is a web scanner based on the PFCLScan technology so that a user can easily scan a website for technical SEO issues