Skip to content

CID Software Solutions LTD

Menu
Phone-alt Whatsapp Envelope Linkedin
Phone-alt Whatsapp Envelope Linkedin
Menu
Home » Fusion OTBI: Securely obtain a session ID from OIC by using OCI Vault and an Oracle Function

Fusion OTBI: Securely obtain a session ID from OIC by using OCI Vault and an Oracle Function

Difficulty: Advanced

TL;DR — The Fusion OTBI/BIP SOAP stack (SAWSessionService.logon) requires a plaintext username and password in the request body. The moment activity tracing in an OIC integration is bumped to Debug, those Fusion OTBI credentials in OIC payloads land in OIC’s monitoring database in clear. We rebuilt the OTBI-session step as an Oracle Function backed by OCI Vault: OIC sends only a Fusion URL, the function reads credentials from Vault over OCI’s internal network via the Service Gateway — credentials never traverse the public internet — calls Fusion, and returns a sessionId. Stack: OIC Gen 3 (native OCI Functions action) + OCI Vault + OCI Functions + Fusion OTBI SOAP. Read this if you have any OIC integration that talks to OTBI, BIP CatalogService, or any other Fusion SOAP endpoint whose payload contains credentials.

Tested on: Fusion 26A · OIC Gen 3 · OCI region eu-frankfurt-1 · Fn CLI 0.6.48 · Python runtime · function on a private subnet with Service Gateway + NAT Gateway.

Why Fusion OTBI credentials leak through OIC

Our customer runs several Fusion instances — dev, test, prod — and has a portfolio of OIC integrations that utilise the OBIEE catalog services for downstream analytics. Every one of those integrations follows the same well-known pattern: invoke /analytics-ws/saw.dll?SoapImpl=nQSessionService with a SOAP envelope containing the <v6:logon> element, capture the returned sessionID, then reuse it across subsequent CatalogService calls. This is the same building block we’ve used elsewhere — for example, in our earlier work on submitting OIC integrations through ESS Jobs, the BIP HTTP outbound depends on a usable OTBI/BIP session.

Where the credential exposure happens

The structural problem with the OTBI logon pattern is that the contract has no header-based authentication option. The username and password must travel in the SOAP body. As long as that call lives inside an OIC integration, those credentials sit one toggle away from being written to OIC monitoring storage — any operator who enables activity tracing at Debug level for diagnosis exports the full payload to the activity stream. The Fusion accounts in question typically have BI Consumer + BI Author + report execution privileges across multiple pillars; a compromised one is not a small event.

Where the credential should actually live

The credential has to exist somewhere OIC can reach without ever appearing in an OIC artefact. The architectural question is where, and how the code that needs it actually retrieves it. Our answer: store credentials in OCI Vault, retrieve them at runtime from an Oracle Function authenticated via Resource Principal — and run the function on a private subnet so the entire vault-read happens over OCI’s internal network via the Service Gateway. No API keys, no shared secrets, no credential material in any OIC artefact, and no credential traffic on the public internet.

The same anti-pattern (URLs and credentials embedded in OIC artefacts) is what we eliminated for the Fusion → OIC direction in our Custom Object Integration Events pattern. This post handles the inverse — OIC → Fusion — and uses a different mechanism because OIC, not Fusion, is the caller.

Our Solution

The flow has six steps:

  1. OIC obtains the Fusion base URL from an existing ERP REST response (the BU LOV is a convenient anchor — its links/href always carries the full server URL).
  2. OIC invokes the Oracle Function via the OIC Gen 3 native OCI Functions action — no REST adapter, no connection to maintain, no API keys. OIC authenticates to OCI Functions automatically using its own IDCS Application identity.
  3. The function parses the Fusion URL and derives an instance key (dev1, test, prod, etc.) from the hostname pattern fa-{code}-{instance}-saasfaprod1.fa.ocs.oraclecloud.com. Production hostnames omit the instance segment.
  4. The function reads two secrets from OCI Vault by deterministic name: fusion-{instance}-otbi-username and fusion-{instance}-otbi-password. The function lives on a private subnet; the vault read is routed through the Service Gateway, so the request never leaves OCI’s internal network. Vault access uses Resource Principal authentication — the function inherits an identity from its membership in a Dynamic Group, no static credentials anywhere.
  5. The function calls Fusion OTBI at /analytics-ws/saw.dll?SoapImpl=nQSessionService with the SOAP <v6:logon> envelope and parses the sessionID out of the response. This is the one outbound-internet hop in the whole flow, routed through the NAT Gateway.
  6. OIC receives {sessionId, instanceKey, customerCode} and uses sessionId in the SOAP header of every downstream OTBI / BIP CatalogService call.

The OIC integration never sees the password. The function never holds the password longer than a single invocation. The credential retrieval traffic stays inside OCI. The vault audit trail records every GetSecretBundle event. Adding a new Fusion instance becomes a vault-only operation: create two new secrets named to the convention; no function redeploy.

Why the Service Gateway matters here

This is the security property most worth understanding before reading the code. The function sits on a private subnet with no public IP and no Internet Gateway. Its outbound routing table contains two rules:

  • All OCI service traffic (anything destined for *.oraclecloud.com services such as Vault and OCIR) is routed to the Service Gateway, which keeps the packets inside OCI’s internal backbone. The vault GetSecretBundle call — the one that returns the OTBI credentials in plaintext — uses this path. The credentials never traverse the public internet on the way to the function.
  • All other outbound traffic (everything else, in our case just the Fusion SOAP call) is routed to the NAT Gateway, which gives the function outbound-only access to the public internet. No inbound path exists; the function is not reachable from the internet.

The practical implication: a packet capture anywhere on the public internet would never see the OTBI credentials in transit, because they travel from Vault to the function over OCI’s internal network. They appear on the wire only once — in the SOAP body of the call to Fusion OTBI — and that call uses HTTPS to a Fusion endpoint that requires those credentials anyway, so no new exposure is introduced.

Implementation Details

1. Vault layout — single vault, naming-convention isolation

We standardised on one vault per tenancy and isolated environments by secret name. Tag namespaces are not required.

Vault layout
OCI Compartment: funcs (or Security)
└── Vault: fusion-security-vault
├── Key: fusion-security-key   (shared, AES-256)
├── Secret: fusion-dev1-otbi-username
├── Secret: fusion-dev1-otbi-password
├── Secret: fusion-test-otbi-username
├── Secret: fusion-test-otbi-password
├── Secret: fusion-prod-otbi-username
└── Secret: fusion-prod-otbi-password

The hostname-to-secret mapping is the entire reason this works:

Fusion hostname patternInstance keySecret names
fa-{code}-saasfaprod1.fa.ocs.oraclecloud.comprodfusion-prod-otbi-{username,password}
fa-{code}-dev1-saasfaprod1.fa.ocs.oraclecloud.comdev1fusion-dev1-otbi-{username,password}
fa-{code}-test-saasfaprod1.fa.ocs.oraclecloud.comtestfusion-test-otbi-{username,password}
fa-{code}-uat-saasfaprod1.fa.ocs.oraclecloud.comuatfusion-uat-otbi-{username,password}

2. IAM — three policies, zero shared secrets

Three IAM objects do the heavy lifting:

  • A Dynamic Group that contains the function (resource.type = 'fnfunc' in the function compartment) — this is the function’s Resource Principal identity.
  • A policy that grants the Dynamic Group read secret-bundles on the specific vault OCID (least privilege — scoped to the vault, not the compartment).
  • A tenancy-level policy that allows the OIC IDCS Application principal to use functions-family and inspect compartments in the tenancy.
IAM — three policies, zero shared secrets
# Function reads vault — scoped to the vault OCID, not the whole compartment

oci iam policy create 
--compartment-id $FUNCTION_COMPARTMENT_OCID 
--name "fusion-security-function-policy" 
--statements '[
"Allow dynamic-group fusion-security-functions-dg to inspect secrets in compartment funcs",
"Allow dynamic-group fusion-security-functions-dg to read secret-bundles in compartment funcs where target.vault.id = '$VAULT_OCID'"
]'

# OIC invokes function — scoped to the actual OIC IDCS principal ID

oci iam policy create 
--compartment-id $TENANCY_OCID 
--name "fusion-oic-tenancy-policy" 
--statements '[
"Allow any-user to use functions-family in tenancy where request.principal.id = 'OIC-IDCS-APP-APPID'",
"Allow any-user to inspect compartments in tenancy where request.principal.id = 'OIC-IDCS-APP-APPID'"
]'

The OIC IDCS principal ID is not visible in the OIC console. Discover it from OCI Audit after the first (deliberately failing) connection attempt — see Gotchas below.

3. The function — Python, Resource Principal, two secrets, one SOAP call

import io
import json
import logging
import os
import re
import base64
import requests
from urllib.parse import urlparse
from xml.etree import ElementTree as ET

import oci
from fdk import response

logger = logging.getLogger(__name__)

_FUSION_HOST_RE = re.compile(
    r'^fa-(?P<full_middle>.+?)-saasfaprod1\.fa\.ocs\.oraclecloud\.com$',
    re.IGNORECASE
)

_KNOWN_INSTANCES = {
    "dev",  "dev1",  "dev2",  "dev3",  "dev4",
    "test", "test1", "test2",
    "uat",  "uat1",  "uat2",
    "stage", "staging", "sit",
    "preprod", "sandbox",
    "train", "training", "demo",
    "perf", "perftest", "dr"
}

SOAP_TEMPLATE = """<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:v6="urn://oracle.bi.webservices/v6">
  <soapenv:Header/>
  <soapenv:Body>
    <v6:logon>
      <v6:name>{username}</v6:name>
      <v6:password>{password}</v6:password>
    </v6:logon>
  </soapenv:Body>
</soapenv:Envelope>"""


NS = {
    "soapenv": "http://schemas.xmlsoap.org/soap/envelope/",
    "v6":      "urn://oracle.bi.webservices/v6"
}

def get_signer():
    return oci.auth.signers.get_resource_principals_signer()

def extract_instance_key(fusion_url):
    hostname = (urlparse(fusion_url).hostname or fusion_url).lower().strip()
    match = _FUSION_HOST_RE.match(hostname)
    if not match:
        raise ValueError(
            f"URL does not match Oracle Fusion SaaS pattern: {hostname}"
        )
    segments = match.group("full_middle").split("-")
    last = segments[-1]
    if last in _KNOWN_INSTANCES:
        return "-".join(segments[:-1]), last
    return match.group("full_middle"), "prod"

def find_secret_ocid(secret_name, signer):
    vault_ocid     = os.environ.get("VAULT_OCID", "").strip()
    compartment_id = os.environ.get("SECURITY_COMPARTMENT_OCID", "").strip()
    if not vault_ocid:
        raise ValueError("Function config 'VAULT_OCID' is not set.")
    if not compartment_id:
        raise ValueError("Function config 'SECURITY_COMPARTMENT_OCID' is not set.")
    client = oci.vault.VaultsClient({}, signer=signer)
    secrets = oci.pagination.list_call_get_all_results(
        client.list_secrets,
        compartment_id=compartment_id,
        vault_id=vault_ocid,
        name=secret_name
    ).data
    for secret in secrets:
        if secret.secret_name == secret_name and secret.lifecycle_state == "ACTIVE":
            logger.info(f"Resolved: '{secret_name}' -> '{secret.id}'")
            return secret.id
    raise RuntimeError(f"No ACTIVE secret named '{secret_name}' in vault.")

def get_secret_value(secret_ocid, signer):
    bundle = oci.secrets.SecretsClient({}, signer=signer) \
                         .get_secret_bundle(secret_id=secret_ocid).data
    return base64.b64decode(bundle.secret_bundle_content.content).decode("utf-8")

def build_soap_url(fusion_url):
    base = fusion_url.rstrip("/")
    if "saw.dll" in base:
        return base
    return f"{base}/analytics-ws/saw.dll?SoapImpl=nQSessionService"

def xml_escape(v):
    return (v.replace("&", "&amp;").replace("<", "&lt;")
             .replace(">", "&gt;").replace('"', "&quot;")
             .replace("'", "&apos;"))

def call_logon(soap_url, username, password):
    body = SOAP_TEMPLATE.format(
        username=xml_escape(username),
        password=xml_escape(password)
    )
    try:
        resp = requests.post(
            soap_url,
            data=body,
            headers={
                "Content-Type": "text/xml; charset=UTF-8",
                "SOAPAction":   "urn:logon"
            },
            timeout=30
        )
        resp.raise_for_status()
    except requests.Timeout:
        raise RuntimeError(f"Timeout connecting to '{soap_url}'")
    except requests.HTTPError as e:
        raise RuntimeError(f"HTTP {e.response.status_code} from Fusion SOAP")

    root  = ET.fromstring(resp.text)
    fault = root.find(".//soapenv:Fault", NS)
    if fault is not None:
        raise RuntimeError(
            f"SOAP Fault [{fault.findtext('faultcode')}]: "
            f"{fault.findtext('faultstring')}"
        )
    for tag in ("sessionID", "sessionId", "return"):
        node = root.find(f".//{{{NS['v6']}}}{tag}")
        if node is not None and node.text:
            return node.text.strip()
    raise RuntimeError("sessionId not found in response")

def handler(ctx, data: io.BytesIO = None):
    fusion_url   = ""
    instance_key = ""
    try:
        payload    = json.loads(data.getvalue()) if data else {}
        fusion_url = (payload.get("fusionUrl") or "").strip()
        if not fusion_url:
            return _resp(ctx, 400, {"error": "Missing required field: fusionUrl"})
        signer             = get_signer()
        customer, instance = extract_instance_key(fusion_url)
        soap_url           = build_soap_url(fusion_url)
        instance_key       = instance
        username = get_secret_value(
            find_secret_ocid(f"fusion-{instance}-otbi-username", signer), signer)
        password = get_secret_value(
            find_secret_ocid(f"fusion-{instance}-otbi-password", signer), signer)
        session_id = call_logon(soap_url, username, password)
        return _resp(ctx, 200, {
            "sessionId":    session_id,
            "instanceKey":  instance,
            "customerCode": customer,
            "vaultOcid":    os.environ.get("VAULT_OCID", "")
        })
    except ValueError as e:
        return _resp(ctx, 400, {"error": str(e), "instanceKey": instance_key})
    except RuntimeError as e:
        return _resp(ctx, 502, {"error": str(e), "instanceKey": instance_key})
    except Exception as e:
        logger.exception("Unhandled exception")
        return _resp(ctx, 500, {"error": f"Internal error: {e}"})

def _resp(ctx, code, body):
    return response.Response(
        ctx,
        response_data=json.dumps(body),
        headers={"Content-Type": "application/json"},
        status_code=code
    )

Three things to note in this code:

  • oci.auth.signers.get_resource_principals_signer() is the entire authentication story. There are no API keys, no PEMs, no config files. The signer works only inside an OCI Function whose enclosing compartment matches a Dynamic Group rule — try to run this from your laptop and it will fail.
  • The instance-key parser is deliberately conservative. If the trailing segment is not in _KNOWN_INSTANCES, the hostname is assumed to be production (prod hostnames have no instance segment at all). This rule is the one Oracle SaaS hostnames have followed in our experience — confirm yours matches before going live.
  • The OTBI response tag name has varied. We’ve seen sessionID, sessionId, and bare return across releases — the loop covers all three.

4. OIC — native Functions action, no REST adapter

In the OIC Gen 3 designer, add the OCI Functions action and point it at the compartment / application / function. There is no connection to create. OIC’s IDCS identity is the one that the tenancy policy authorised.

The integration flow is straightforward:

Trigger → Map → Invoke ERP REST (BU LOV, 1 row) → Assign fusionUrl → Map → OCI Function → use sessionId

The trick we use to derive the base URL without hardcoding it is an XPath against the BU LOV response:

substring-before(
  $GetBU/ns13:getAllResponse/ns13:links/ns12:href,
  '/fscmRestApi'
)

That always returns https://fa-{code}-{instance}-saasfaprod1.fa.ocs.oraclecloud.com regardless of pod or environment — so the same integration runs in dev, test and prod without environment-specific configuration.

In subsequent OTBI / BIP CatalogService calls, use the sessionId in the SOAP header:

<soapenv:Envelope
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:v6="urn://oracle.bi.webservices/v6">
  <soapenv:Header>
    <v6:SAWSessionParameters>
      <v6:sessionID>{$OTBISessionId}</v6:sessionID>
    </v6:SAWSessionParameters>
  </soapenv:Header>
  <soapenv:Body>
    <!-- CatalogService.readObjects, BIP runReport, etc. -->
  </soapenv:Body>
</soapenv:Envelope>

Gotchas / Production notes

Networking gotchas

  • The Function App subnet cannot be changed after creation. If you provisioned it on the wrong subnet (or, classically, on a public subnet without a NAT Gateway), the only fix is delete-and-recreate the app, then redeploy the function. Decide on private subnet + Service Gateway + NAT Gateway before you create the app.
  • The function needs both gateways, and they do different jobs. Service Gateway carries OCI service traffic (OCIR image pull at startup, Vault GetSecretBundle at runtime) over OCI’s internal network — this is the path that keeps credentials off the public internet. NAT Gateway gives the function outbound-only access to the public internet so it can call Fusion OTBI. Miss the NAT and you get a 504 timeout on /analytics-ws; miss the Service Gateway and you get a 502 “failed to pull function image” at first invocation.
  • The Service Gateway route must use a SERVICE_CIDR_BLOCK destination, not a regular CIDR. Use the regional aggregate (e.g. all-fra-services-in-oracle-services-network for Frankfurt) so the route covers both OCIR and Vault. A 0.0.0.0/0 route to Service Gateway will not work.

Deployment gotchas

  • The OIC IDCS principal ID is not exposed in the OIC console. First attempt to add an OCI Functions action will fail with “Couldn’t load applications list”. That failure is exactly what you want — it generates an audit event in OCI. Open OCI Audit → Search for ListApplications events in the last hour; the principal-id field ending in _APPID is your OIC IDCS principal. Plug it into the where request.principal.id = '..._APPID' clause and retry.
  • Use runtime: python in func.yaml, not python39 or python3.9. Fn CLI 0.6.48 only knows the generic identifier; the version-specific ones produce a confusing “runtime not supported” deploy error.
  • OCIR Docker login username format depends on identity setup. Native OCI users: <namespace>/<username>. Federated IDCS in the default domain: <namespace>/oracleidentitycloudservice/<email>. Federated IDCS in a custom domain: <namespace>/<IdentityDomainName>/<email>. Get this wrong and docker login returns an unhelpful unauthorized.
  • Auth-token rotation is silent. OCIR auth tokens expire (or get revoked) without notifying the function. If a redeploy starts failing with unauthorized after months of working, regenerate the token from OCI Console → Profile → Tokens and keys.

Security and runtime gotchas

  • Resource Principal works only inside the function. Don’t try oci secrets get-secret-bundle from your laptop using the same setup — your CLI uses your user-level config, not the function’s resource principal. Test the function end-to-end via fn invoke, not by simulating the OCI calls locally.
  • Vault secret list is paginated. find_secret_ocid uses oci.pagination.list_call_get_all_results deliberately — a single-page list_secrets call will silently miss your secret once the compartment grows past the default page size.
  • VAULT_OCID and SECURITY_COMPARTMENT_OCID are the only environment-aware values the function needs. Inject them via the Function App config. Do not hard-code secret OCIDs anywhere — secrets get rotated and OCIDs change.

How to verify

Function reachable end-to-end:

echo '{"fusionUrl":"https://fa-{code}-dev1-saasfaprod1.fa.ocs.oraclecloud.com"}' 
| fn invoke fusion-security-app fusion-otbi-session

Expected: a JSON document with sessionId, instanceKey: "dev1", customerCode: "{code}".

Vault traffic stayed on the Service Gateway path. In the VCN flow logs (if enabled) the GetSecretBundle calls do not appear on the NAT Gateway interface — they show up only on the Service Gateway path. This is the operational proof that the credential read never traversed the public internet.

Vault read auditable:

oci audit event list 
--compartment-id $SECURITY_COMPARTMENT_OCID 
--start-time $(date -u -v-1H +%Y-%m-%dT%H:%M:%SZ) 
--end-time   $(date -u      +%Y-%m-%dT%H:%M:%SZ) 
| jq '.data[] | select(."event-name" == "GetSecretBundle")'

Every legitimate sessionId issued maps to two GetSecretBundle events (username + password) signed by the function’s Resource Principal.

OIC payloads carry no credentials. Re-enable Debug-level activity tracing on the integration and re-run. The OIC monitoring console will show the function request body as {"fusionUrl":"https://..."} and the response as {"sessionId":"...","instanceKey":"...",...}. There is no point in the activity stream at which a Fusion username or password appears.

Conclusion

OTBI credentials never leave OCI Vault except over OCI’s internal network, into a function that holds them for the duration of a single invocation. OIC integrations carry no Fusion identity material in any artefact, parameter, connection, or log. Adding a new Fusion instance (UAT, perf, training) is a vault-only operation — two new secrets named to the convention, no function redeploy, no OIC change. P2T from prod to test leaves both ends intact because nothing in OIC is bound to a specific hostname; the function derives the instance key from the URL it is given at runtime. And every secret access is recorded in the OCI Audit log, so a security review has a real trail rather than a screenshot of an OIC connection page.

Want this in your environment?

Facing the same exposure with OTBI, BIP CatalogService, or any other Fusion SOAP endpoint that demands credentials in the payload? We’ve shipped this pattern end-to-end — OCI Vault, IAM with least-privilege scoping, private-subnet networking with the right Service Gateway and NAT Gateway routing, the Python function, the OIC native action wiring, and the cutover from credential-in-OIC to credential-in-Vault — and we can do the same for you. Reach out at info@cidsolutions.co.il or on WhatsApp — let’s get it solved.

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *