Who Goes There?

1. Who Goes There? End-to-End User Identification bill coulam

2. Problems • Most modern security features are rooted in access to the user’s identity. • But modern connection pools, using a shared database account, render users anonymous. • This cripples AUDIT, RLS, VPD and FGA. • Difficult to comply with recent regulations regarding auditing and accountability.

3. Alternatives • Banish pools and shared accounts. Go two-tier! • Global Application Context –(NA for RAC) • Pass the user’s ID with every call to the database (assumes a PL/SQL API for DB access) • OCI method OCIAttrSet (limited to 3g apps) • Place the user’s ID in the OracleConnection properties • setClientIdentifier (deprecated in 9iR2) • setEndToEndMetrics (can work, but issues in OAS/OC4J/APEX environments) • Place the user’s ID in the USERENV namespace

4. Goals • Provide a unique identifier for each user, available to Oracle through the USERENV context, client_identifier attribute. • Must Be: • Useable by basic AUDIT features. (AUD$.CLIENTID) • Useable by FGA for auditing access (9i) & DML (10g) • Useable by DBMS_MONITOR to trace a given user’s activity across sessions and RAC nodes. • Useable by RLS and VPD • Consider a separate application context for security, other identifying attributes and frequently used reference values.

5. Solution #1 (IMOS) • app_ctx_mgr PL/SQL package • Init (userID): • dbms_session.set_identifier(userID) • Reset: • dbms_session.clear_identifier • dbms_session.modify_package_state(dbms_session.reinitialize) • Get: • SYS_CONTEXT(‘USERENV', 'client_identifier') • To enable old val/new val historic audit: • Generic audit tables • Triggers on financial-related DML

6. Solution #1 (IMOS) • Modify connection classes package org.lds.imos.hibernate;… import java.sql.Connection;… public class AuditingConnection implements Connection {… private void resetUserContext() throws SQLException {… stmt = delegate.prepareCall("{CALL app_ctx_mgr.reset_user_ctx}"); import javax.sql.DataSource;… public class AuditingDataSource implements DataSource {… public Connection getConnection() throws SQLException {… if (user != null) {initUserContext(user, con);}… protected void initUserContext(User user, Connection con) throws SQLException {… stmt = con.prepareCall("{CALL app_ctx_mgr.init_user_ctx(?)}"); stmt.setString(1, user.getRecordNumber()); stmt.execute();

7. Solution #2 (Records) • PL/SQL application framework implemented • app_ctx_mgr became “env” • New application context (secure, fast, flexible) • CREATE OR REPLACE CONTEXT app_core_ctx USING app_core.env • Audit and Security tables moved to APP_CORE • Client ID used in application logging & debugging • LDAP package added • Synchronization with LDS Account added • “Dynamic users” • Application authorization supplemented by roles/positions known to CDOL • Access to immutable user IDs like ldsAccountID and ldsMRN, as well as name components.

8. Generic Approach • Lowest common-denominator approach is much like the IMOS solution: • Proc to set the client_identifier • Proc to clear it • Modify connection classes to set/unset • Should add ability to set/unset module, action, client_info and service (10g) as well for tracing, tuning and monitoring. • Call dbms_application_info routines, dbms_service.create_service for new 10g column. • Provide modify feature for business processes with multiple trips to the DB. Enable reset as well.

9. 10g Approach • Slightly less overhead is to use JDBC End-to-End Metrics approach • Built-in getters and setters. • Built-in spots for module, action and client (none for service) • No need for stored procs • Covered up in 11g docs. “Customer use of end-to-end metrics in JDBC is generally discouraged.”

10. 11g Approach • In 11gR2 docs the blurb from the 11.1 docs about customers being discouraged from end-to-end metrics is gone. • New verbiage clarifies that DMS is mutually exclusive with traditional end-to-end metrics. • So if not using DMS: • Use OracleConnection.setEndToEndMetrics • END_TO_END_CLIENTID_INDEX (max 64 chars) • END_TO_END_MODULE_INDEX (max 48 chars) • END_TO_END_ACTION_INDEX (max 32 chars)

11. 10g + Hibernate + Spring + AOP • JDBC End-to-End Metrics and AOP • See SlavikMarkovich’s Blog (CTO of Sentrigo): • http://www.slaviks-blog.com/2007/05/22/propagating-middle-tier-and-application-users-to-the-dbms-part-1-of-3/ • http://www.slaviks-blog.com/2007/06/10/propagating-middle-tier-and-application-users-to-the-dbms-part-2-of-3/ • http://www.slaviks-blog.com/2007/11/17/propagating-middle-tier-and-application-users-to-the-dbms-part-3-of-3/

12. Security Concerns • For logging, monitoring, tracing, and tuning, any of the above approaches suffice. • For security (VPD, FGA, RLS, AUDIT), anyone that can get into the database can call dbms_session.set_identifier. • Can’t revoke EXECUTE on dbms_session from PUBLIC • Possible loss of Oracle support • Must use application context in conjunction with client_identifier.

13. Notes/Remaining Questions • Client Identifier limited to 64 bytes • Do the OCI methods have a place here? • What about DMS? • WebSphere is an absolute pain in the shorts. OAS is quite stable and probably included in our EA with Oracle. $ saved by conversion, or is WS sunk costs? • Implications of EUS and global users • What about a better ORM while we’re at it? (EclipseLink) or homegrown (POJOs + PL/SQL API) • Simplify. Remove RAC wherever it doesn’t fit • Change the culture to value data as much as scripture and revelation. Front end development becomes a side dish or garnish, instead of the main course. • …a guy can dream

14. References • J2EE CMP/Transaction Trick (and great discussion on this subject in general): • http://www.oracle.com/technology/tech/java/oc4j/pdf/j2ee-cmp-with-vpd.pdf • 10g End-to-End Metrics Support • http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/endtoend.htm • Client ID Spoofing • http://www.integrigy.com/security-resources/analysis/Integrigy_Spoofing_Oracle_Session_Information.pdf • 11g DMS superceding End-to-End Metrics • http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/dmsmtrc.htm#CHDIJFHA