Auditing Systems - Oracle db Admins

An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. In general, a database server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance preventing unauthorized access and providing efficient solutions for failure recovery. Companies run mission critical applications such as ERPs, Financial, Point-of-Sale, Property Management and other. Some popular ones we generally encounter are, Oracle Financials, PeopleSoft, Opera PMS, Micros POS just to name a few.

Oracle is the most popular according to DB-Engines Ranking, which ranks database management systems according to their popularity.

In this article, I will cover some considerations for auditing logical access, keeping in mind that databases contain highly sensitive information such as for example tables with password hashes, privileged access takes a new meaning in this realm.

Multitenant Container Databases (CDB) and Pluggable Databases (PDB)

Oracle 12c Release 1 (12.1) introduced the Multitenant option which represents one of the biggest architectural changes in the history of the Oracle database. The option introduced the concepts of the Container Database (CDB) and Pluggable Database (PDB). In a CDB, every user, role, or object is either common or local. Similarly, a privilege is granted either commonly or locally. A CDB common user can connect to any container in the CDB to which it has sufficient privileges. In contrast, an application common user can only connect to the application root in which it was created, or a PDB that is plugged in to this application root, depending on its privileges. The following graphic shows the possible user account types in a CDB:

Notice that SYS and SYSTEM are Oracle-supplied common users in the CDB. Please reference Oracle docs below for detailed guides on this topic.

Data Classification Policy

The first artifact I request when performing a database audit/assessment is the organizations Data Classification Policy. A data classification policy is primarily concerned with the management of information to ensure that sensitive information is handled well with respect to the threat it poses to an organization. It also factors in how this gathered data is being used and structured within an organization to allow authorized personnel to get the right pieces of information at the right time, while aiding in ensuring that only those who are authorized are able to view or access information. The database of any organization contains data which differs in its level of sensitivity (i.e. some data are more sensitive than others). A variety of components in the organization is mapped out which then considers every type of data belonging to the organization classifying it according to storage and permission rights. 

The Data Classification Policy is at the heart of the organization's risk appetite. It is a key part of the security policy and validating it's effectiveness should be a core component of the audit objectives. In the SOX 404 world, it should be considered when testing ELCs. This is a requirement for the PCI DSS 3.2.1 that we test when performing a PCI assessment.

Audit/Assessment Considerations

These are some audit considerations when I perform an audit of Oracle Databases:

1. Review the dba_roles_privs and dba_sys_privs tables.
2. Review all accounts in the v$pwfile_users file. This file lists all users in the password file, and indicates whether the user has been granted the SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM privileges. This means they can login from the OS as SYS without having to provide a password.
3. Review access to critical views and tables such as sys.dba_users & sys.user$. These are critical views where only a reduced number of accounts should be allowed to SELECT. The sys.user$ table stores the password hashes. NOTE TO MANAGEMENT: Consider monitoring any 'select' statements made against these tables.
4. Review the dba_users_with_defpwd. Note, if in a multi tenant container database (CBD), make sure you run it for the CBD root. You will also need to run it on each PBD to retrieve the local accounts with default password.
5. Review the applications that access the database
6. Review public privileges.
7. Identify objects with potential credentials stored.
8. Review privileges that can be passed on (i.e."WITH ADMIN").
9. Review application objects owned by privileged user.
10. Review all shared accounts.
11. Review the use of ANY privileges - specifically: ALTER ANY PROCEDURE, SELECT ANY TABLE, ALTER ANY LIBRARY and SELECT ANY DICTIONARY
12. Validate that the SQL92_SECURITY parameter must be set to TRUE.

You can use the canned reports for some of these tables/views. I have created custom scripts with sql queries I generally use when conducting assessments. There are also great industry tools you can use (i.e @PeteFinnigan). SANs also has a great course on auditing Oracle databases. Here are some known examples of sql queries you can use:

How to obtain the applications that access the database:

set lines 132
col username for a8
col status for a8
col program for a22
col module for a8
col osuser for a14
col terminal for a10


select 	sess.username,
		sess.osuser,
        	sess.status,
        	proc.program,
	  	sess.module,
        	proc.terminal
from    	v$process proc,
        	v$session sess
where   	proc.addr=sess.paddr

/

Generate a report of privileges granted to the PUBLIC role. There should be no column, role or system privileges assigned:

select count(*),'SYSTEM'
from dba_sys_privs
where grantee='PUBLIC'
union
select count(*),'ROLE'
from dba_role_privs
where grantee='PUBLIC'
union
select count(*),'COLUMN'
from dba_col_privs
where grantee='PUBLIC'
union
select count(*),'TABLE'
from dba_tab_privs
                   
where grantee='PUBLIC';

Identify objects with potential credentials stored:

col object for a30
col item for a24
col owner for a10
select 'TABLE' OBJ, object_name object,
   object_type item,
   owner owner
from dba_objects, dual OBJ
where owner<>'SYS'
and ((object_name like '%USER%'
and object_name not like 'USER_%')
or object_name like '%USR%'
or object_name like '%PASSWD%'
or object_name like '%PWD%'
or object_name like '%PASS%')
and object_type in('VIEW','TABLE')
union
select 'COLUMN' OBJ, table_name object,
  column_name type,
   owner owner
from dba_tab_columns, dual OBJ
where owner<>'SYS'
and (column_name like '%USER%'
or column_name like '%USR%'
or column_name like '%PASSWD%'
or column_name like '%PWD%'
                           
or column_name like '%PASS%');

Review privileges that can be passed on. Privileges that are granted "WITH ADMIN" option allows the grantee to pass the privilege on to other users or roles in the database. These should be reviewed in your audit.

select 'ROLE',
 grantee,
 granted_role
from dba_role_privs
where admin_option='YES'
and grantee not in ('SYS','SYSTEM','DBA')
union
select 'SYSTEM',
 grantee,
 privilege
from dba_sys_privs
where admin_option='YES'
                         
and grantee not in ('SYS','SYSTEM','DBA');

List application objects referenced by an application that are not owned by the application:

col owner for a8
col type for a8
col referenced_owner for a16
col owner for a16
select owner, name, type, referenced_owner
 from dba_dependencies
 where owner <> 'APPOWNER' and 
                               
 referenced_owner = 'APPOWNER';

Generate list of shared accounts:

select username,terminal,action_name,returncode,
     to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),
     to_char(logoff_time,'DD-MON-YYYY HH24:MI:SS')
    from dba_audit_session s
    where username in (select username
     from dba_audit_session
     having count(distinct(terminal))>1
                                       
     group by username);

I still see a lot of 10g and 11g in the wild (automatic audit finding) where these commands work. You may need to modify them to your environments and database versions.

Conclusion

These are just some considerations when auditing logical access on Oracle Databases. You should also review hardening standards applied as well as other considerations such as encryption management, data retention/deletion and capacity management. As mentioned before, proper entity level controls should be in place that include a formal Data Classification Policy. Feel free to add any other controls or procedures you perform in the comments section below.

Links to sites mentioned in the article:

DB Engines Ranking

Oracle-Docs - Overview of the Multitenant Architecture

What is a Data Classification Policy?

Pete Finnigan Tools