BI Publisher Security: Row-Level Access and User Permissions

Security layers in BI Publisher

BIP has multiple independent security layers:

1. Catalog permissions: who can open, edit, or delete reports
2. Data security: what data a user sees when they run a report
3. Delivery security: what delivery channels and addresses a user can specify
4. Oracle Fusion data roles: Fusion restricts which subject areas and data an OTBI user can access

Catalog permissions

BIP catalog objects have individual permission settings:

• Open: can run the report
• Read: can view but not run
• Write: can edit the report
• Delete: can delete

Assign to users, roles, or groups. Best practice: create a BIP report viewer role, grant Open permission to all production reports, and add users to the role rather than managing individual permissions.

Row-level security via SQL

The most important security mechanism: filter data in the data model SQL based on the logged-in user:

SELECT po.*, s.supplier_name
FROM po_headers po
JOIN suppliers s ON po.supplier_id = s.supplier_id
WHERE po.requester_id = :xdo_user_id  -- BIP's current user parameter
  AND po.org_id IN (
    SELECT org_id FROM user_org_access WHERE username = :xdo_user_name
  )

:xdo_user_id and :xdo_user_name are BIP built-in parameters containing the authenticated user’s details.

Business unit and ledger security

For Finance reports, use Fusion’s data role hierarchy to determine which ledgers/BUs a user can see:

WHERE gl.ledger_id IN (
  SELECT ledger_id FROM fusion_gl_access_sets
  WHERE user_id = :xdo_user_id
)

Never rely solely on UI-level access control — always enforce at the SQL level.

Sensitive data masking

For reports distributed outside the organisation or to non-privileged roles, mask sensitive fields in the data model:

SELECT
  employee_id,
  last_name,
  REGEXP_REPLACE(national_id, '.{4}$', 'XXXX') AS masked_national_id,
  REGEXP_REPLACE(bank_account, '[0-9]', 'X', 1, 0) AS masked_account
FROM employees

Audit trail

Log all report executions for compliance:

INSERT INTO report_access_log (user_id, report_name, parameters, run_dt)
VALUES (:xdo_user_id, 'PO_Detail_Report', :P_PARAMS, SYSDATE);

Use autonomous transactions so the log persists even if the report fails.