Api Key Exposure in Phoenix with Oracle Db

When API keys are handled within a Phoenix application that connects to an Oracle database, several architectural and operational patterns can unintentionally expose those keys. This typically occurs when configuration or secrets are stored in code, logs, or database objects, and when runtime queries expose sensitive data through insufficient authorization checks.

Phoenix applications often rely on environment variables or runtime configuration to supply database credentials and external API keys. If these values are written to the database—such as in audit tables, debugging entries, or improperly secured PL/SQL packages—they can be retrieved by an attacker who compromises the Oracle instance or exploits an insecure endpoint. For example, a developer might create a logging function that inserts full request headers, including authorization tokens, into an api_audit table for troubleshooting. Without proper data governance, this table becomes a long-term store for sensitive material.

Additionally, Oracle database links and external procedures can inadvertently expose key material if they are misconfigured. A Phoenix app using a database link to another schema or instance might pass credentials or tokens in clear text across the link, especially if the link definition does not enforce secure credential stores. Similarly, if the application uses Oracle’s UTL_HTTP or UTL_TCP packages to call external services and embeds API keys in the request headers without masking, those keys can be exposed in trace files or network captures.

Another vector involves the interplay between Phoenix Ecto queries and Oracle permissions. An endpoint that constructs dynamic queries using string interpolation might include API keys as bind variables that are logged by the database’s auditing features. If the Oracle audit trail is enabled without appropriate filtering, keys can appear in logs that are readable by broader administrative roles. This expands the attack surface beyond the application layer into the database layer, where traditional monitoring may not inspect sensitive data flows.

The OWASP API Top 10 category ‘2023-A1: Broken Object Level Authorization’ is relevant here because insufficient checks on who can request logs or diagnostic data can lead to unauthorized extraction of API keys stored or referenced in Oracle tables. For instance, an attacker exploiting a BOLA flaw could enumerate audit records and download entries containing key material. This is especially problematic when combined with weak access controls on the database itself, where roles and privileges are not narrowly scoped.

middleBrick’s 12 security checks, including Data Exposure and Authorization, are designed to detect these cross-layer risks by correlating runtime behavior with OpenAPI specifications. When scanning a Phoenix-connected Oracle service, the scanner can identify endpoints that return sensitive fields, inspect database integration patterns via spec analysis, and highlight missing authorization on administrative endpoints. This helps teams understand how configuration and database design choices contribute to key exposure.

Remediation focuses on ensuring API keys never persist in the database in clear text and that database-side operations follow the principle of least privilege. The following examples assume a Phoenix application using Ecto with an Oracle connection via the arc_ecto and myxql drivers, adapted for Oracle syntax where applicable.

1. Avoid storing keys in audit tables

Instead of inserting full headers, store only metadata necessary for debugging, and mask sensitive values. In PL/SQL, create a procedure that redacts keys before insertion:

CREATE OR REPLACE PROCEDURE log_api_call (
    p_endpoint  IN VARCHAR2,
    p_method    IN VARCHAR2,
    p_headers   IN VARCHAR2
) AS
    v_redacted_headers VARCHAR2(4000);
BEGIN
    -- Simple redaction: replace key values with '****'
    v_redacted_headers := REGEXP_REPLACE(p_headers, '(Authorization:\s*Bearer\s*)\S+', '\1****');
    INSERT INTO api_audit_log (endpoint, method, headers_snippet, logged_at)
    VALUES (p_endpoint, p_method, SUBSTR(v_redacted_headers, 1, 200), SYSTIMESTAMP);
END;
/

2. Use secure credential stores for external calls

When calling external services from PL/SQL, reference credentials stored in Oracle Wallet rather than embedding keys in code. Configure the wallet and reference it in your HTTP request:

DECLARE
    l_http_req  UTL_HTTP.req;
    l_response  UTL_HTTP.resp;
BEGIN
    UTL_HTTP.set_wallet('file:/opt/oracle/wallet', 'wallet_password');
    l_http_req := UTL_HTTP.begin_request('https://api.example.com/data', 'GET', 'HTTP/1.1');
    UTL_HTTP.set_header(l_http_req, 'Authorization', 'Bearer ' || SYS.VARCHAR2_VARIABLE('API_TOKEN_REF'));
    l_response := UTL_HTTP.get_response(l_http_req);
    -- process response
    UTL_HTTP.end_response(l_response);
END;

3. Enforce column-level security on sensitive tables

Create a Virtual Private Database policy to restrict who can view columns that might contain references to keys. For an api_audit_log table:

BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema   => 'APP_SCHEMA',
        object_name     => 'api_audit_log',
        policy_name     => 'restrict_key_columns',
        function_schema => 'SECURITY_SCHEMA',
        policy_function => 'hide_sensitive_columns',
        statement_types => 'SELECT',
        update_check    => TRUE
    );
END;

4. Validate and bind variables in dynamic queries

Ensure Ecto queries do not interpolate values into SQL strings. Use parameterized queries even when working with Oracle through NIFs or ports:

# In Phoenix/Ecto context (using MySQL adapter syntax as illustration; Oracle follows similar patterns)
Ecto.Adapters.SQL.query!(Repo,
  "SELECT * FROM api_requests WHERE user_id = :user_id AND auth_token IS NULL",
  [user_id: user_id]
)
# Ensure auth_token is never part of the query string

5. Limit database roles and enable fine-grained auditing

Create a dedicated role for the Phoenix application that has no direct access to audit tables, and enable fine-grained auditing to capture access attempts:

CREATE ROLE app_api_role;
GRANT CONNECT, SELECT ON api_requests TO app_api_role;
REVOKE SELECT ON api_audit_log FROM app_api_role;

BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema   => 'APP_SCHEMA',
        object_name     => 'api_audit_log',
        policy_name     => 'audit_log_access',
        audit_condition => '1=1',
        audit_column    => 'headers',
        handler_schema  => 'SECURITY_SCHEMA',
        handler_module  => 'log_fga_violation'
    );
END;

These steps reduce the likelihood that API keys persist in Oracle logs or are exposed through database interactions, aligning database security with application-level controls.