Nationwide
Implementing Oracle
Enterprise User Security
Bill Parsley
Database Administration
2 Nationwide ©©Nationwide Nationwide --20042004
2
Nationwide Environment
Very Heterogeneous Server/OS Environment
– Mainframes, CICS, VSAM, etc... – 4,600+ Windows/Intel servers
– 1,100+ UNIX servers (Sun, HP, IBM)
Nationwide Oracle Environment
– 630+ Oracle database instances (development & production). – 160+ database servers, almost all UNIX.
– 100’s(?) Oracle database applications, many “client/server”. (Oracle Forms, Reports, VisualBasic, Pro*C/Cobol, etc…)
Database Support
– 7 on-call production support DBAs. – 3 plan/build DBAs
Business Drivers for Enterprise User Security (EUS)
Weak Password Security
– Users managed many passwords (Windows account, UNIX account, firewall account, email account, etc…)
– Difficult to implement security features such as password aging, password complexity rules, and failed attempt lockouts.
Managing Oracle passwords
– User passwords stored in 100’s of database instances.
– Many Oracle applications require passwords to be coordinated between multiple database instances.
Overall Nationwide Security Goal: reduce the number of places where passwords are stored and managed.
– Password coordination, not single sign-on.
Novell
eDirectory
Active
Directory
Oracle
Internet
Directory
•User accounts provisioned and deprovisioned through
eDirectory (directly from Human Resources system)
•User passwords set from web page (enforces complexity)
•userpassword vs. orclpassword attributes in OID entry.
user
web page
ldap
Novell DirXML
Enterprise User Security in the Database
Database USER function does
not return correct user name, user
is not shown in v$session.
sys_context(‘USERENV’,’EXTERNAL_NAME’)
Must implement OID Enterprise Roles.
Simple user provisioning and
deprovisioning, all done in central OID server.
Unresolved issues: How to
manage temp tables, pl/sql direct grants, user_role_privs, etc...
Database USER function returns correct user name, v$ tables & audit triggers function normally.
Existing database application roles and non-default roles work without change.
User provisioning requires database schema creation,
deprovisioning requires cleanup.
6 Nationwide ©©Nationwide Nationwide --20042004
6
Enterprise User Security in the Database
Nationwide Goal: Improve password security with
minimal impact on existing applications
==> Global Private Schema.
alter user bill identified globally as
‘cn=bill,cn=staff,dc=nationwide,dc=com’ ;
Required 9.2 application database.
Supports 7.3, 8i, 9i clients. No ldap or digital certificate
on clients.
OID Processes on UNIX
9.2 RDBMS Repository Server-1 Net listener oidmon LDAP process LDAP process LDAP process LDAP process OID listener port 636 SSL LDAP configset=1 OS Replication Server OS 9.2 Databases oidctl tool Names Proxy (9i) LD AP Oracle Net Oracle Net LDAP on SSL Server-28 Nationwide ©©Nationwide Nationwide --20042004
8
Nationwide OID Architecture - Stage 1
SunFireV4 8 0 Sun SunFireV4 8 0 Sun SunFireV4 8 0 Sun EMC EMC
Veritas Cluster Disk Hot Copy Server-1 V480 active Server-2 V480 passive Server-3 V480 disaster recovery Production Data Center Disaster Recovery Data Center
Nationwide Architecture Stage 2 - OID Replication
Content Switch eDirectory dirXML SunFireV480 Sun SunFireV480 Sun SunFireV480 Sun Slave-1 Read Only OID Server Slave-2 Read Only OID Server Master OID server Content Switch Future Server Multi-Master ?? External Storage Array External Storage Array External Storage Array External Storage Array North Datacenter South Datacenter10 Nationwide ©©Nationwide Nationwide --20042004
10
Oracle GUI Tools for OID
. oidadmin - Oracle Directory Manager
Create/delete ldap DIT, manage OID server configurations and ACLs.
. netca - Network Configuration Assistant
Create/upgrade an Oracle context, or the Oracle OID schema.
. netmgr - Network Manager
Create/delete database service name entries.
. esm - Enterprise Security Manager
Register databases (9i only), create/delete enterprise users and roles.
. owm - Oracle Wallet Manager
OID Server Installation Steps
Installed OID from 9.2 RDBMS media, patch to 9.2.0.4
Use oidca tool to create database schema for OID.
Use owm (oracle Wallet Manager) to create certificate wallet, load Certificate Authority and user certificate.
Use oidadmin tool to create new configset for SSL listener.
User oidadmin tool to create subscriber subtree (dc=nationwide,dc=com).
12 Nationwide ©©Nationwide Nationwide --20042004
12
Database Server Setup for OID
Use owm to create certificate wallet, load Certificate authority, one user certificate per database instance.
Put WALLET_LOCATION in sqlnet.ora
Create ldap.ora file in TNS_ADMIN directory.
Use esm (Enterprise Security Manager) tool to register database instance in OID.
Set RDBMS_SERVER_DN =
‘cn=mysid,cn=OracleContext,dc=nationwide,dc=com’) in init.ora
Useful Metalink Reference Papers
178714.1 Config & Test OID with SSL
191137.1 Troubleshooting Enterprise User Security
185275.1 Example: Setting up EUS with Password Authentication
189260.1 How to Configure Database SSL using DN Certificate
158905.1 Quickstart Guide: OID Replication Setup
208694.1 ldaprepl.sh steps for OID 9.2 Replication Setup
185480.1 Misc. Solutions for OID Replication Setup & Config.
14 Nationwide ©©Nationwide Nationwide --20042004
14
Results
Large reduction in help desk calls for ID resets.
More consistent implementation of password complexity rules (different vendor rules require least common denominator)
Centralized management of password aging, lockout.
Oracle 8i & 9i Clients v7, v8, v9 Oracle Clients (ldap.ora ) (sqlnet.ora) Oracle Database Oracle Internet Directory Server Oracle 9i Names Server Proxy LDAP LDAP SQL NET SQL NET
Net Service Names
Resolution
16 Nationwide ©©Nationwide Nationwide --20042004
16
