Pete Finnigan's Oracle Security Forum - How to find DBA's on Oracle

Forum

Home/Forum

Welcome, Guest. Please Login. Dec 3^rd, 2024, 5:57pm 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 21608 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 30^th, 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 30^th, 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
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 30^th, 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
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 1^st, 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
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 1^st, 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
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 2^nd, 2010, 11:05am »

Quote | Modify

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

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 2^nd, 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
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 8^th, 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 »

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.

Learn more
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.

Learn more
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

Learn more
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.

Learn more
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

Learn more
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

Learn more
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

Learn more
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.

Learn more
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

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

Learn more
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

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

Learn more
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.

Learn more
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

Learn more
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.

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

Learn more