Oracle Database Security and Audit

(1)

Oracle Database

Security and Audit

Beyond Checklists

Database Security and Risk Assessment

Learning objectives

• Understand

• Oracle architecture

• Database

• Listener

• Oracle connection handshake

• Client/server architecture

• Authentication methods

• Password protection

Reidy Database Consulting, LLC Database Security and Risk Assessment

Oracle architecture

• Architecture of an Oracle database instance

• Oracle Real Application Cluster (RAC)

• Oracle Automatic Storage Management (ASM)

• Oracle Enterprise Manager (OEM) 12c Cloud

Control

(2)

• Oracle database is not just a data store

• Complex system - like an operating system

• Networking services (FIFO pipes, etc.) and support for many protocols (TCP, etc.)

• File subsystem (space allocation, deletion, reuse, recovery, corruption detection, etc.)

• Job schedulers

• Kernel interrupts and instrumentation

• XML storage and processing

• Shared memory and memory pools

• Interprocess communication (IPC) and threading

• Large object storage and processing

• Encryption support at the data storage and network layers (strong algorithms, SSL

support)

• Multiple authentication methods (database, Kerberos, LDAP, etc.)

Oracle RDBMS

Architecture

What is an Oracle

instance?

• Composed of many subsystems

• This is same regardless if you are speaking of a

single instance database, RAC database, or ASM

database

• Based on UNIX architecture

• Software (executables, shared libraries, JAR files,

etc)

• Disk files (database files, log files, control files)

• Shared memory

(3)

RAC Architecture

• High availability solution

• Multiple computers open and access

one database instance

• Transparent application failover

(network layer)

ASM Architecture

• Disk volume manager for Oracle database

• Commonly used in Real Application Cluster (RAC)

configurations

• But, it can be used in a stand-alone single instance

system

• Implemented with a small Oracle companion

database

(4)

OEM 12c

• Infrastructure to manage, monitor, provision, and baseline

enterprise computing platforms

• Oracle database repository (OMR)

• Web and application servers (OMS)

• Management agent (OMA)

• Console (OMC)

• Operating system configuration

• Database - single instance, RAC, and ASM services

• non-Oracle databases via plugins

• Sensitive data discovery and mapping (Real Application Testing -

RAT)

• Really, anything you want to monitor and/or manage and/ or test

Reidy Database Consulting, LLC

Deployed

to target

servers

Web servers

(5)

Oracle networking

• Database and database application are

separated into a client/server architecture

• Client runs the database application

• SQL*Plus, Desktop programs, web

applications, etc.

• Server runs the Oracle database software

• Functions for concurrent, shared data

access

http://docs.oracle.com/cd/E11882_01/server.112/e16508/

dist_pro.htm#CNCPT006

Client/server

architecture

Oracle listener

• Server side process

• Traffic manager

• Incoming client connection requests

• Establishes a pathway to the database

instance

(6)

Connection handshake

• 03Logon pre-11g

• 05Logon

!

Essentially the same process regardless of

database version.

03Logon

User application

Logon as SYSTEM

Send username to server = SYSTEM

Database server

Client software such as SQL*Plus starts a connection with SQL>connect system/ manager

03Logon

User application

Logon as SYSTEM

Send username to server = SYSTEM

Database server

The server retrieves the hash from sys.user$, generates a random number, and encrypts the random number with the hash used as a key.

The server receives the

request, gets the hash and

generates the session key

(7)

03Logon

User application

Logon as SYSTEM

Send username to server = SYSTEM

Database server

Send session key

The server send the session

key to the client

03Logon

User application

Logon as SYSTEM

Send username to server = SYSTEM

Database server

Send session key

1) The client evaluates

the Oracle password algorithm and generates the password hash 2) The password hash is

used to decrypt the session key

At this point it is possible to

enumerate users in the database

03Logon

User application

Logon as SYSTEM

Send username to server = SYSTEM

Database server

Send session key

1) The client evaluates

the Oracle password algorithm and generates the password hash 2) The password hash is

used to decrypt the session key

The client evaluates

the hash and decrypts

(8)

03Logon

User application

Logon as SYSTEM

Send username to server = SYSTEM

Database server

Send session key

1) The client evaluates

the Oracle password algorithm and generates the password hash 2) The password hash

is used to decrypt the session key

Send encrypted password = AUTH_PASSWORD

Client encrypts the clear text password using the session key as the new key

At this point, if we know

the hash and have access

to SQL*Net trace files we

can decrypt the password

03Logon

User application

Logon as SYSTEM

Send username to server = SYSTEM

Database server

Send session key

1) The client evaluates the Oracle password algorithm and generates the password hash 2) The password hash is

used to decrypt the session key

Send encrypted password = AUTH_PASSWORD

Client encrypts the clear text password using the session key as the new key

The server decrypts the session key, evaluates the password algorithm, and compares the hash with

sys.user$.password or

sys.user$.spare4

The server gets the

password, creates the hash

and checks if login can

proceed

03Logon

User application

Logon as SYSTEM

Send username to server = SYSTEM

Database server

Send session key

1) The client evaluates

the Oracle password algorithm and generates the password hash 2) The password hash

is used to decrypt the session key

Client encrypts the clear text password using the

session key as the new key _{Send encrypted password = AUTH_PASSWORD}

The server decrypts the session key, evaluates the password algorithm, and compares the hash with

sys.user$.password or

sys.user$.spare4

Begin Session

If the hashes match, session is started

Session can begin to send and receive data from the database

(9)

Client/server architectures

• Dedicated server architecture

• Server process created on behalf of each client

process

• Shared server architecture

• Dispatcher directs incoming requests to pool of

shared server processes

• Database resident connection pooling

• Connection pool of dedicated servers for typical

Web application scenarios

Dedicated server process

(10)

Database resident connection pooling

Oracle networking

• Establish and maintain connection between client and

database

• Based on the OSI architecture

• Stack based architecture

• 3 basic type of connections

• Client/Server Connections

• Java Connections

• Web client connections

http://docs.oracle.com/cd/B28359_01/network.111/b28316/

architecture.htm#i1048731

!

http://en.wikipedia.org/wiki/OSI_model