Skip to content

OP-03: Get Oracle connection string

This operation composes an Oracle connection string for the requested instance (a TNS alias) and user, proves that it connects by running a constant probe query, caches it for the lifetime of the process, and returns it to the caller. It follows the same flow as the SQL Server operation, with an Oracle key format, template and probe statement.

Field Value
Operation ID OP-03
Trigger HTTP request from an authorized caller
Entry endpoint GET /v1/Connections/ORA/{instance}/{username}
Owning component ConnectionsController.GetOraConnectionString, delegating to ConnectionManager.GetOracleConnectionString
Inputs Route values instance and username (inferred route binding); Environment API template oraconnectiontemplate and a secure credential for the composed key
Outputs ActionResult<string> holding the credential-bearing connection string
Failure response 400 for an unresolved TNS alias, 404 for a missing secure setting, 500 for other probe or credential failures, produced from ConnectionManagerException by package middleware†

Steps

  1. Authorize the caller against resource DBConnAPIRetrieveResource (BR-DBC-025)
  2. On failure: a denial produced by the package authorization handler†; the exact status is not visible in source**
  3. Write the audit log entry with the key parts, UserId, AuthenticationScheme and the time (BR-DBC-022)
  4. Build the cache key ORA.<instance>.<user>, which is case-sensitive (BR-DBC-002)
  5. Build the lower-cased secure-setting key ora_<instance>_<user> (BR-DBC-005)
  6. Read the template oraconnectiontemplate on every request, before the cache check, and replace {INSTANCE} only (BR-DBC-007)
  7. Look the key up in the in-memory cache (BR-DBC-001)
  8. On a hit: return the cached string with status 200 and stop. Only the secure-credential retrieval, validation, the probe and cache insertion are skipped; the template lookup in step 5 already happened
  9. On a miss, check that the secure setting exists (BR-DBC-009)
  10. On failure: ConnectionManagerException with status 404
  11. Verify the backoff configuration (BR-DBC-011)
  12. Decrypt the secure setting and split it at the first pipe (BR-DBC-010)
  13. A missing pipe raises ArgumentOutOfRangeException outside the retry loop; the status is not visible in source**
  14. Substitute {USER} and {PW} (BR-DBC-008)
  15. Probe the composed string, up to four attempts with 2 s, 3 s and 5 s waits (BR-DBC-012)
    • Each attempt decodes the legacy token for the string under test only (BR-DBC-019)
    • The probe opens a unit of work through IUnitOfWorkFactory and runs SELECT 1 FROM DUAL; a non-null result means valid (BR-DBC-018)
    • A false result counts as a failure with status 500 (BR-DBC-014)
    • Permanent errors (ORA-12154, ORA-12504, TNS-12154, ORA-01017, ORA-01000) stop the loop at once (BR-DBC-013)
  16. After the final failure, map the error to a status
    • TNS alias errors map to 400 (BR-DBC-015)
    • ORA-01034, ORA-01000, ORA-01017, ORA-12570, ORA-12571, ORA-03135 and the generic fallback map to 500 (BR-DBC-016)
    • An existing ConnectionManagerException is rethrown with its status (BR-DBC-017)
  17. On success, log the validation and add the string to the cache under the lock (BR-DBC-003, BR-DBC-004)
  18. Return the connection string with status 200

Origin: Steps 1 and 12 depend on SAIF.NetCore.ServiceExtensions† (authorization handler and DynamicErrorHandler), and step 11 uses IUnitOfWorkFactory from SAIF.NetCore.NHibernate†, both retrieved via the Legends CLI RAG service and executed within this operation.

Process flow

The following diagram visualizes this operation's control flow.

flowchart TD
    A["Authorize DBConnAPIRetrieveResource"] -->|"Denied"| Z["Authorization denial, status not visible in source"]
    A --> B["Audit log, build cache key and secure-setting key"]
    B --> T["Read Environment API template (every request)"]
    T -->|"Template missing"| X["Unhandled/package error, status not visible in source"]
    T --> C{"Key in cache?"}
    C -->|"Yes"| R["Return cached string, 200"]
    C -->|"No"| D{"Secure setting exists?"}
    D -->|"No"| E["404 ConnectionManagerException"]
    D -->|"Yes"| F["Decrypt credential, split at first pipe"]
    F -->|"No pipe"| X2["Unhandled/package error, status not visible in source"]
    F --> G["Compose string"]
    G --> H["Probe SELECT 1 FROM DUAL, up to 4 attempts"]
    H -->|"Valid"| I["Add to cache and return 200"]
    H -->|"TNS alias error"| J["400"]
    H -->|"Other failure"| K["500"]

Transaction and side effects

Aspect Finding
Unit of work IUnitOfWorkFactory (SAIF.NetCore.NHibernate), a throw-away unit of work per probe attempt; no explicit transaction
Commit point None
Records written None; the probe selects a constant from the Oracle DUAL pseudo-table (data model)
Downstream calls SAIF Environment API through IEnvironmentClient; the caller-named Oracle alias through the probe
Repeat-call behavior A repeat call for an identical key reads the template again and is then served from the cache, skipping the secure read and probe; the cache is never invalidated (BR-DBC-004)

Logging

Surface Written content
ISaifLogService (Info) Audit entry per request (BR-DBC-022); a validation success entry with the attempt count
ISaifLogService (Error) Each failed probe attempt with the exception and message

Error handling

  • TNS alias not found: 400 from ConnectionManagerException through package middleware (DynamicErrorHandler†)
  • Secure setting missing: 404 from ConnectionManagerException through the same middleware
  • Database unavailable, cursor exhaustion, invalid credentials, network interruption, probe returned false or any other failure: 500 through the same middleware; messages can include the key name, the track and raw provider text
  • Missing pipe or missing template: unhandled exceptions; the status is not visible in source**
  • Authorization failure: produced by the package authorization handler†; the status is not visible in source**

Note: Statements marked with † are derived from dependency library documentation via the Legends CLI. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

  • DBConnectionAPI/DBConnectionAPI/Controllers/ConnectionsController.cs
  • DBConnectionAPI/DBConnectionAPI/Services/ConnectionManager.cs
  • DBConnectionAPI/DBConnectionAPI/Services/IConnectionManager.cs
  • DBConnectionAPI/DBConnectionAPI/Exceptions/ConnectionManagerException.cs
  • DBConnectionAPI/DBConnectionAPI/EnvironmentKeys.cs