Oracle Database Security

volleyballbeginnerNetworking and Communications

Oct 27, 2013 (3 years and 11 months ago)

102 views

Oracle Database
Security

…from the application perspective


Martin Nystrom

September 2003

Purpose


In scope: application security of Oracle
databases



Out of scope: system security of Oracle
databases

Agenda


Oracle architecture


System architecture


Network architecture


Common Oracle objects


Schema/object security


Java security


Application integration techniques


Current challenges at Cisco

Database server

Grossly oversimplified Oracle

network architecture

Database

Client host

1521

TNS

Listener

SQL*Net (Net8)

Oracle

client

software

Oracle architecture

ONS server

Oracle

Names

service

ONS query

1526

(ADDRESS=(PROTOCOL=TCP)

(HOST=db.company.com)

(PORT=1521))


(ADDRESS=(PROTOCOL=TCP)

(HOST=cmrsdb.cisco.com)

(PORT=1521))


cmrsdb

Oracle networking example

cmrs

fields
-
sj
-
1

1521

TNS

Listener

Oracle

client

software

Oracle architecture

ons
-
sj

Oracle

Names

service

ONS query

1526

Database server

Simplified Oracle

Network Architecture w/OCM

Database

1521

TNS

Listener

Host #1

Oracle

client

software

OCM Server

Host #2

Oracle

client

software

Host #3

Oracle

client

software

1521

TNS

Listener

rejected

Allowed

Host #1

Host #2

rejected

Oracle architecture

SQL*Net


Introduced in Oracle V5


Renamed “Net8” in Oracle8


Supports multiple protocols (TCP/IP,
DECnet, SPX/IPX, etc.)

Oracle architecture

Authentication & credentials


Can be…


OS authentication


Userid/password


X.509 certificates


Smart card


Etc.


Stored in Oracle


As MD5 hash


…not so for dblinks or FND_USERS


Oracle architecture

Authentication & credentials (cont.)


Transport encryption


DES encryption of db
-
selected random number
w/user’s password hash


OS
-
integrated authentication available too


Password changes travel unencrypted


Password management features available


Aging & expiration


History (e.g., can prohibit reuse of last 3 passwords)


Composition & complexity (e.g., require letters +
numbers)


Account lockout

Common Oracle objects

Database instance

schema

Public area

schema

table

view

trigger

index

stored

procedure

function

table

synonym

Oracle object security

grant select on
EMPLOYEES to ASOK;

alice’s schema

employees

candidates

asok’s schema

orders

customers

Public objects

all_users

Oracle role
-
based security

hrdata schema

employees

candidates

hr_steward

grant all privileges
on EMPLOYEES to role
HR_STEWARD;


grant HR_STEWARD to
CATBERT;


DBA

Database links

dogbert’s schema

orders

EMPLINK

dogbert’s schema

employees

HR_DB

ECOMMERCE_DB

Create database link EMPLINK
connect to DOGBERT identified
by CISCO123 using HR_DB;

Java security in Oracle

dilbert session

wally session

Java server classes (common, read
-
only)

java.*

oracle.aurora.*

oracle.jdbc.*

com.cisco.ipc.*

com.cisco.myapp.calc



System classes loaded by default, accessible & shared by all sessions

Java security in Oracle


System classes loaded in shared area


Users can load classes


Into their own schema/session


Can grant execution rights to other users


Permissions


Stored in Oracle objects, not files


Stored in PolicyTable table


Granted by DBA or JAVA_ADMIN roles


“call dbms_java.grant_permission(

“mnystrom”,

“java.util.SocketPermission”,

“localhost:1024
-
”,

“connect”)


2 privilege models


Invoker’s rights


Definer’s rights (setuid)

Invoker’s rights

alice’s schema

dogbert’s schema

com.cisco.ipc.*

com.cisco.myapp.calc

salary

salary

Definer’s rights

alice’s schema

dogbert’s schema

com.cisco.ipc.*

com.cisco.myapp.calc

salary

salary

Access beyond the database

Database server

Database

/oracle/apps/


Languages: PL/SQL or Java


Techniques: Stored procs or functions


Examples


Execute, read, write local files


Make and receive network calls (HTTP, MMX, etc.)


Access data in remote databases


Send mail

Auditing


Obviously impacts database performance


Writes high
-
level info to a common table


Database user


Object (table, role, etc.)


Action (select, insert, etc.)


Date/time


Currently enabled on
-
request to DBA team


Difficult to trace actions to a live human


Can correlate with IP address

Common integration techniques


Shared database schemas


Separate schemas/dbs


Grant direct access to each other’s schemas


Grant only stored proc access


Typical modern application

application schema

orders

customers

application

Shared schemas

application #2’s schema

orders

customers

Application

#1

Application

#2

select

insert

update

insert

update

delete

select

grant

select

Shared objects

Application #1’s schema

orders

Application

#1

Application

#2

select

insert

update

Application #2’s schema

customers

insert

update

delete

select

grant

select

Shared, protected objects

Application #1’s schema

orders

Application

#1

Application

#2

select

insert

update

Application #2’s schema

customers

insert

update

delete

select

grant

execute

stored

procedure

Application
-
level integration

Application #1’s schema

orders

Application

#1

Application

#2

select

insert

update

Application #2’s schema

customers

insert

update

delete

select

grant


Shared libraries


MMX


Web services


IIOP

Current problems in industry


Account management


Passwords never changed


Accounts/passwords widely known


All developers


cgi
-
bin trees


CVS source repositories


Privileges too broad


No data stewardship


No segregation/special protection for sensitive
data