auditing oracle lisa outlaw cisa, cissp, itil foundation is audit supervisor, north carolina state...

Auditing OracleAuditing Oracle

Lisa Outlaw CISA, CISSP, ITIL Foundation IS Audit Supervisor, North Carolina State Auditor

ObjectiveObjective

To explain the following:1. Key Oracle Audit Areas

2. Risks in each Audit Area

3. Controls that should be implemented

4. Audit procedures to evaluate controls

Audit AreasAudit Areas1. Data Dictionary

2. Privileges

3. Parameters

4.4. Default Users AccountsDefault Users Accounts

5. Profiles

6. Roles

7. Links

8. Audit Trail

Data DictionaryData Dictionary

Contains all the information about the structures and objects of the database– Tables, columns, users, data files, etc.

Owned by the Oracle account “SYS”– This is the most powerful account in Oracle

Contains all the security data– Tables, users, tab privs., etc

Data Dictionary RisksData Dictionary Risks

Attackers can gain valuable information about the database– including accounts and encrypted passwords.

Attackers could destroy the database by deleting key tables.

Data Dictionary ControlsData Dictionary Controls

Ensure Access to the data dictionary is limited to only those accounts that need access to fulfill their job duties.

Normally, only the database administrators should have access to the data dictionary.

Audit Procedure to evaluate Audit Procedure to evaluate controls over Data Dictionary controls over Data Dictionary

O7_DICTIONARY_ACCESSIBILITY=FALSE – this is a database initialization parameter that controls access to

objects in the SYS schema. – Set this to FALSE to prevent users with EXECUTE ANY

PROCEDURE and SELECT ANY DICTIONARY from accessing objects in the SYS schema.

– If set to TRUE, accounts with "ANY" privileges could get access to objects in the SYS schema, hence the data dictionary.

SQL Command to Evaluate Control– SELECT * FROM V$PARAMETER WHERE

UPPER(NAME)=‘O7_DICTIONARY_ACCESSIBILITY’;

PrivilegesPrivileges

Privileges are– the tasks that accounts or roles are allowed to perform.– used by the database to determine if access to resources will

be allowed or denied.

Types of privileges– System Privileges - Apply to the entire database

– Schema Object Privileges -Apply only to a portion of the database

Risks of Powerful PrivilegesRisks of Powerful Privileges

Excessive access to system tables and critical application tables may lead to:– Unauthorized changes to data– Denial of database services

Controls over Powerful PrivilegesControls over Powerful Privileges

Privileges should be granted based on the need to know, need to use basis.

Polices, procedures, and standards should support privilege granting decisions.

Controls over Powerful PrivilegesControls over Powerful Privileges

Privileges on system and database objects should be carefully assessed, documented, and granted to the proper accounts and roles– Typically end-user accounts should not be

granted system privileges except create session– Insert, Update, Delete on critical tables should

be limited to required users

System PrivilegesSystem Privileges System Privileges allow an account to perform a

particular action on any schema– Ex. Read (Select) any table in the database

SELECT ANY TABLE privilege

Over 150 different system privileges in Oracle

Audit Procedures to evaluate controls Audit Procedures to evaluate controls over System Privilegesover System Privileges

Audit Procedures to evaluate Audit Procedures to evaluate controls over System Privilegescontrols over System Privileges

Ensure that only authorized personnel has system privileges in critical databases. – Therefore, all system privileges except for CREATE

SESSION should be restricted to the Database Administrators (DBA)

SQL Command to Evaluate Control– SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE <>

'CREATE SESSION'; – Ensure REVOKE ALL <PRIVS> FROM <USER>;

Example of Script Output


Prevent granting privileges for the entire catalog of objects – prevent granting of privileges that contain the keyword ANY. – The ANY keyword grants the ability for the user to set privileges for the

entire catalogue of objects in the database. – Check for any user or role that has the ANY keyword and revoke this

privilege where possible.

SQL Command to Evaluate Control

– REVOKE ALL <PRIVS> FROM <USER>;– SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE like

"%ANY%";– SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE like

'%SELECT_ANY_%'


Prevent granting of ALL privileges. – The GRANT ALL PRIVILEGES must not be used; it gives full

access to all tables, views and objects to the user or role it is granted to.

SQL Command to Evaluate Control– REVOKE ALL PRIVILEGES FROM <USER/ROLE> – GRANT <SPECIFIC PRIVILEGES> TO <USER/ROLE>; – SELECT * FROM DBA_SYS_PRIVS WHERE

UPPER(PRIVILEGE) like 'GRANT ANY %' OR PRIVILEGE like 'GRANT ALL %';


Prevent granting of SELECT ANY TABLE. – If application data is sensitive, and it is possible, revoke

this privilege from the DBA accounts as well.

SQL Command to Evaluate Control– REVOKE SELECT ANY <OBJECT> FROM <USER>; – SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE like

'%SELECT ANY %'


Prevent granting of privileges that have CREATE. – Check for any user that has object creation privileges and revoke

where possible. – Excessive create privileges can allow an attack to create arbitrary

objects, tables, and views.

SQL Command to Evaluate Control

– REVOKE CREATE <PRIV> FROM <USER/ROLE> – SELECT * FROM DBA_SYS_PRIV FROM PRIVILEGE LIKE

'CREATE %';

Schema Object PrivilegesSchema Object Privileges

Privileges on tables, views, and stored procedures within a particular schema

Typically used to control access to critical application data

SchemaSchema A schema is

– a collection of database objects.

A schema is – owned by a database user and has

the same name as that user.

Schema database objects – are logical structures created by

users to contain, or reference, their data.

– include structures like tables, views, and indexes.

Examples of Schema database objectsExamples of Schema database objects

Tables

Indexes

Views

Synonyms

Sequences

Database links

Packages

Procedures

Functions

Etc.

Audit Procedures to evaluate controls Audit Procedures to evaluate controls over Schema Object Privilegesover Schema Object Privileges

Audit Procedures to evaluate controls Audit Procedures to evaluate controls over Schema Object Privileges over Schema Object Privileges

Lock account access for application schema owners– Lock the account for the application schema owner. Users must not

connect to the database as the application owner.

SQL Command to Evaluate Control– ALTER USER <USERNAME> ACCOUNT LOCK PASSWORD

EXPIRE – SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS; – SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS;– SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS

WHERE USERNAME in (list of application schema owner accounts);

Audit Procedures to evaluate controls Audit Procedures to evaluate controls over Schema Object Privilegesover Schema Object Privileges

Review users access to various application data and determine if access is granted based on a need to know, need to use basis.

ParametersParameters

Updatable configuration settings for the database Default settings populated during installation Typically stored in files on the host server, such as

Unix

Parameter RisksParameter Risks Improperly configured parameters could allow

– unauthorized access to sensitive data – unauthorized control of the database

Unauthorized access to configuration parameters could allow unauthorized modification of the database.

Parameter ControlsParameter Controls

Gain an Understanding of all configuration parameters for the database.– The organization should develop a baseline defining secure

parameter configurations.

Default configuration parameters should be modified, – Defaults Settings could lessen database security.

Authorized Access to files or tables with configuration parameters – The organizations should define those people who need access to

these files or tables to perform their job duties.

Audit Procedures to evaluate controls Audit Procedures to evaluate controls over Parametersover Parameters

Evaluate Controls over ParametersEvaluate Controls over Parameters

SQL Command to Evaluate Control– SELECT * FROM V$PARAMETER; – Provides the runtime values of all the parameters

Key Parameters to AuditKey Parameters to Audit

Set REMOTE_OS_AUTHENT= FALSE– to disallow a user that is authenticated to the network

domain to access the database without DB credentials.


UPPER(NAME) LIKE ‘%REMOTE_OS_AUTHENT %’;


Set _trace_files_public= FALSE– prevents users from having the ability to read trace files

which may contain sensitive information about the running Oracle instance. This is an internal Oracle parameter. (default: FALSE)


UPPER(NAME) LIKE ‘%TRACE_FILES_PUBLIC%’;


Set REMOTE_OS_ROLES=FALSE – to prevent Connection spoofing. Default is FALSE.


UPPER(NAME) LIKE ‘%REMOTE_OS_ROLES %’;


Set REMOTE_LISTENER=’ ’ – to prevent the use of a listener on a remote machine

separate from the database instance.


UPPER(NAME) LIKE ‘%REMOTE_LISTENER%’;


AUDIT_TRAIL = OS (recommended setting)– AUDIT_TRAIL parameter ensures basic auditing is turn on.– AUDIT_TRAIL to OS reduces the likelihood of a Denial of

Service attack and it is easier to secure the audit trail. – Any auditing information stored in the database is viewable and

modifiable by the DBA if set to DB or DB_EXTENDED.


UPPER(NAME) LIKE ‘%AUDIT_TRAIL%’;


Set OS_ROLES=FALSE – OS_ROLES allows externally created groups to be

used to manage database roles. This can lead to misaligned or inherited permissions.


UPPER(NAME) LIKE ‘%OS_ROLES %’;


REMOTE_LOGIN_PASSWORDFILE=NONE– Prevent remote privileged connections to the database.


UPPER(NAME)=‘REMOTE_LOGIN_PASSWORDFILE’;

Default User AccountsDefault User Accounts

Default accounts are created during installation These accounts own different features of the

database

Default User RisksDefault User Risks

Initial passwords should be changed after database installation

Default user ids and passwords are well known and could be used to allow unauthorized access to the database.

Default User ControlsDefault User Controls

All initial passwords should be changedSome default user accounts should be

removed or locked as appropriate.

Audit Procedures to evaluate controls Audit Procedures to evaluate controls over Default Accountsover Default Accounts

Default User Account Audit Default User Account Audit ProceduresProcedures

SQL Command to Evaluate ControlSQL Command to Evaluate Control– SELECT * FROM DBA_USERS_WITH_DEFPWD;

(DBA_USERS_WITH_DEFPWD not in versions prior to Oracle 11g)

Note: the script was design to run on database versions 11g and prior. The error message above is what you should see if you are runningthis command on an Oracle database that is not 11g.

Sample Output From ScriptSample Output From Script

Note:Note: the script looks for known default accounts, to determinethe script looks for known default accounts, to determine if the accounts are lockedif the accounts are locked.

SQL Command to Evaluate Control SQL Command to Evaluate Control – SELECT USERNAME, ACCOUNT_STATUS FROM

DBA_USERS WHERE USERNAME in (list of default accounts);

Default User Account Audit Default User Account Audit ProceduresProcedures

Check if Default Passwords are Changed– Compare default password per hash values in Oracle white papers to password hash

from database under audit to determine if default passwords were changed.

USERNAME USERNAME

PASSWORD-HASHFrom Oracle

Whitepapers

PASSWORD-HASH From database under audit ACCOUNT_STATUS

WKSYS WKSYS 69ED49EE1851900D 69ED49EE1851900D OPEN

CTXSYS CTXSYS 24ABAB8B06281B4C 24ABAB8B06281B4C LOCKED

MDSYS MDSYS 72979A94BAD2AF80 72979A94BAD2AF80 EXPIRED & LOCKED

WMSYS WMSYS 7C9BA362F8314299 7C9BA362F8314299 EXPIRED & LOCKED

ORDSYS ORDSYS 7EFA02EC7EA6B86F 7EFA02EC7EA6B86F EXPIRED & LOCKED

ProfilesProfilesOracle Profiles are used for resource and

password management.– Typically, a profile is created, then assigned to a user

account. – The profile contains the password settings, failed login

attempt setting, password complexity etc.– All user accounts are automatically assigned a

DEFAULT profile. – Specific users profiles are not explicitly assigned a

user profile.

Profile RiskProfile Risk

Improperly configured profiles could allow:– unauthorized access to sensitive data – unauthorized control of the database– passwords that never expire– unlimited failed login attempts to the oracle

database.

Profile ControlsProfile Controls Organizations should define secure profile settings in a

configuration baseline.– Recommendation:

Set three separate profiles instead of using the default profile:

- application profiles,

- system profiles, and

- user profiles– Rationale:

User, System, and Application accounts require different resource and password settings.

Profile Audit ProceduresProfile Audit Procedures SQL Command to Evaluate Control SQL Command to Evaluate Control

– SELECT * FROM DBA_PROFILES;SELECT * FROM DBA_PROFILES;

PROFILE CONFIGURATION SETTING

RESOURCE VALUE

CPSPID_USER FAILED_LOGIN_ATTEMPTS PASSWORD 3

CUSTOMER FAILED_LOGIN_ATTEMPTS PASSWORD 5

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 5

DEVELOPER FAILED_LOGIN_ATTEMPTS PASSWORD 5

CPSPID_USER PASSWORD_LIFE_TIME PASSWORD DEFAULT

CUSTOMER PASSWORD_LIFE_TIME PASSWORD DEFAULT

DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMIT

DEVELOPER PASSWORD_LIFE_TIME PASSWORD DEFAULT

CPSPID_USER PASSWORD_LOCK_TIME PASSWORD DEFAULT

CUSTOMER PASSWORD_LOCK_TIME PASSWORD DEFAULT

DEFAULT PASSWORD_LOCK_TIME PASSWORD .0208

DEVELOPER PASSWORD_LOCK_TIME PASSWORD DEFAULT

Key Profile Settings to Audit Check the values of the following resources listed in the

RESOURCE_NAME column, and compare to organization or statewide security standards:

1.1. failed_login_attempts failed_login_attempts must be set to 3 (default 10 counts)2.2. password_life_time password_life_time must be set to 60 or 90 days (default 180

days)3.3. password_lock_timepassword_lock_time must be set to 1 (default unlimited days)4. password_reuse_max must be set to 20 (default unlimited

counts), limits the number of different passwords that must be rotated

by the user before the current password can be reused5. password_reuse_time must be set to 365 (default unlimited days)

sets the amount of time that must pass before a password can be reused;

Key Profile Settings to Audit password_grace_time password_grace_time must be set to 3 (default unlimited days)

– to specify the number of days that the user is warned to change their password before their password expires

idle_time idle_time must be set to a number (default unlimited minutes); – limits the maximum length of time (in minutes) a session can be idle

connect_timeconnect_time must set to a number to limits the maximum length of time (in minutes) a session can be held open (default unlimited minutes);

password_verify_function – Contains the password complexity– The password verification function can be obtained from DBA_SOURCE table:

SQL Command to Evaluate ControlSQL Command to Evaluate Control– SELECT * FROM DBA_SOURCE WHERE TYPE='FUNCTION';

RolesRoles

A group of privileges that can be assigned to individual users

Oracle Databases have default roles already defined

Roles make management of individual users easier to control

Roles RisksRoles Risks

Default roles can grant excessive privilegesCareless assignment of roles may grant

users excessive elevated privileges Changes in job assignments makes role

maintenance difficult.

Roles ControlsRoles Controls

Passwords should be assigned to roles with important privileges

Written maintenance procedures should exist

Evidence should exist to support that maintenance procedures are followed

Default roles should not be used

Audit Procedures to evaluate controls Audit Procedures to evaluate controls over Rolesover Roles


Review the following powerful role privileges:

– DBA– DELETE_CATALOG_ROLE– EXECUTE_CATALOG_ROLE– EXP_FULL_DATABASE– IMP_FULL_DATABASE– RECOVERY_CATALOG_OWNER– SELECT_CATALOG_ROLE

SQL Command to Evaluate ControlSQL Command to Evaluate Control– SELECT * FROM DBA_ROLES ORDER BY ROLE;SELECT * FROM DBA_ROLES ORDER BY ROLE;


Determines if the persons in roles are appropriate.

SQL Command to Evaluate ControlSQL Command to Evaluate Control– Select * from DBA_USERs;– Select * from DBA_ROLE_PRIVS; – Select * from DBA_ROLES;– Select * from DBA_ROLE_PRIVS WHERE

GRANTED_ROLE='DBA';

Link$Link$

Connections that allow one database to interact with another

Link$ RisksLink$ Risks

Unknown users in the remote databaseCauses Clear text password to be stored in

the data dictionaryMultiple databases are at risk for

unauthorized accessPublic links are not uncommon

Links ControlsLinks Controls

Public links should not be used Access should not be granted to the data

dictionary table– may have passwords stored in clear text

User permissions and/or executable procedures should restrict a users ability to use links

Links should not exist from test or development databases into production databases

Audit Procedures to evaluate controls Audit Procedures to evaluate controls over LINK$over LINK$

Audit Procedures to evaluate Audit Procedures to evaluate controls over controls over LiNK$LiNK$

Restrict Access to LINK$– Revoke access to the LINK$ table from all users and

roles except for SYS and DBA accounts.

SQL Command to Evaluate ControlSQL Command to Evaluate Control– SELECT GRANTEE, PRIVILEGE FROM DBA_TAB_PRIVS WHERE SELECT GRANTEE, PRIVILEGE FROM DBA_TAB_PRIVS WHERE

TABLE_NAME in 'LINK$‘TABLE_NAME in 'LINK$‘

– SELECT * from DBA_TAB_PRIVS WHERE TABLE_NAME in 'LINK$',SELECT * from DBA_TAB_PRIVS WHERE TABLE_NAME in 'LINK$',

Audit TrailAudit Trail

Built-in facility to monitor activity that occurs within the database

Logs are created of the activity that is audited Logs are kept either on the operating system or in

the database. – However, it is preferred to keep logs on the operating

systems to avoid unauthorized modification from database administrators.

Audit Trail RisksAudit Trail Risks

Without an Audit Trail– No accountability is assigned to actions

occurring within or to the database– Data could be stolen without the knowledge of

the database owners– The database can be damaged or misconfigured

without the knowledge of the database owners

Audit Trail ControlsAudit Trail Controls

Policies and procedures should exist defining the auditing that should be performed

Auditing should be enabledAudit logs should be monitoredAudit logs should be secured within the

operating system

Audit Procedures to evaluate controls Audit Procedures to evaluate controls over Audit Trailover Audit Trail

Audit Procedures to evaluate Audit Procedures to evaluate controls over controls over Audit TrailAudit Trail

Restrict Access to AUD$ and USER_HISTORY$', – Revoke access to the 'AUD$' table from all users and roles

except for SYS and DBA accounts.– Allowing users to alter the AUD$ or USER_HISTORY$ table

can compromise the audit trail or integrity of the Oracle database Note: This is only applicable if the audit trail parameter is set to db or db_extended;

SQL Command to Evaluate ControlSQL Command to Evaluate Control– SELECT GRANTEE, PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME in SELECT GRANTEE, PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME in AUD$ – SELECT GRANTEE, PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME in SELECT GRANTEE, PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME in USER_HISTORY$

– SELECT * from DBA_TAB_PRIVS WHERE TABLE_NAME in SELECT * from DBA_TAB_PRIVS WHERE TABLE_NAME in AUD$

– SELECT * from DBA_TAB_PRIVS WHERE TABLE_NAME in SELECT * from DBA_TAB_PRIVS WHERE TABLE_NAME in USER_HISTORY$

Discussion of HandoutsDiscussion of Handouts

Oracle ScriptsSample Work papersQuestions??Questions??

Sources:Sources:– Oracle WhitepapersOracle Whitepapers– Oracle WebsiteOracle Website

auditing oracle lisa outlaw cisa, cissp, itil foundation is audit supervisor, north carolina state...

Documents

powerful privileges

granting privileges

granting of privileges

database slide

different system privileges

object creation privileges

oracle slide

data dictionary controls