Transcript Oracle Database Security (from the Application Perspective

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
Grossly oversimplified Oracle
network architecture
Client host
Oracle
client
software
ONS server
ONS query
Oracle
1526 Names
service
SQL*Net (Net8)
1521
(ADDRESS=(PROTOCOL=TCP)
(HOST=db.company.com)
(PORT=1521))
Database server
TNS
Listener
Database
Oracle architecture
Oracle networking example
fields-sj-1
ons-sj
Oracle
client
software
ONS query
Oracle
1526 Names
service
1521
(ADDRESS=(PROTOCOL=TCP)
(HOST=cmrsdb.cisco.com)
(PORT=1521))
cmrsdb
TNS
Listener
cmrs
Oracle architecture
Simplified Oracle
Network Architecture w/OCM
Host #1
Oracle
client
software
Host #2
Oracle
client
software
OCM Server
1521
TNS
Listener
Allowed
Host #1
Host #2
rejected
rejected 1521
Database server
TNS
Listener
Host #3
Oracle
client
software
Oracle architecture
Database
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
stored
procedure
schema
view
function
table
table
trigger
index
Public area
synonym
Oracle object security
grant select on
EMPLOYEES to ASOK;
orders
candidates
customers
employees
asok’s schema
alice’s schema
all_users
Public objects
Oracle role-based security
grant all privileges
on EMPLOYEES to role
HR_STEWARD;
hr_steward
grant HR_STEWARD to
CATBERT;
DBA
candidates
employees
hrdata schema
Database links
Create database link EMPLINK
connect to DOGBERT identified
by CISCO123 using HR_DB;
ECOMMERCE_DB
orders
HR_DB
employees
EMPLINK
dogbert’s schema
dogbert’s schema
Java security in Oracle
• System classes loaded by default, accessible & shared by all sessions
com.cisco.ipc.*
wally session
java.*
com.cisco.myapp.calc
dilbert session
oracle.aurora.*
Java server classes (common, read-only)
oracle.jdbc.*
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
com.cisco.myapp.calc
com.cisco.ipc.*
salary
dogbert’s schema
salary
alice’s schema
Definer’s rights
com.cisco.myapp.calc
com.cisco.ipc.*
salary
dogbert’s schema
salary
alice’s schema
Access beyond the database



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
Database server
Database
/oracle/apps/
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
orders
application schema
customers
Shared schemas
Application
#1
select
insert
update
Application
#2
select
orders
customers
application #2’s schema
insert
update
delete
select
grant
Shared objects
Application
#1
select
insert
update
orders
Application #1’s schema
Application
#2
select
insert
update
delete
select
grant
customers
Application #2’s schema
Shared, protected objects
Application
#1
select
insert
update
orders
Application #1’s schema
Application
#2
insert
update
delete
select
grant
execute
stored
procedure
customers
Application #2’s schema
Application-level integration
Application
#1
select
insert
update
•Shared libraries
•MMX
•Web services
•IIOP
Application
#2
insert
update
delete
select
grant
orders
customers
Application #1’s schema
Application #2’s schema
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