Securing Data in Google BigQuery: Implementing ORG_ID-Based Security for Oracle EBS Data

[amit_kothari]

When working with enterprise systems like Oracle E-Business Suite (EBS), ensuring that users only see the data relevant to their operating unit (ORG_ID) is a critical security and compliance requirement.

In this post, we’ll walk through how you can implement Operating Unit–based security for EBS data in Google BigQuery — using a simple, scalable approach that leverages Incorta and BigQuery Row-Level Security (RLS). The same approach can be used to secure by Legal Entity or Inventory Org or any such dimensional values.

---

🧩 Step 1: Create a User-Access Mapping in Incorta

Start by creating a mapping table in Incorta that links each user to the operating units they are authorized to access.

Create a new Incorta table named user_org_access using the EBS data source with the following SQL:

SELECT DISTINCT 
    fpov.profile_option_value AS org_id,
    fu.user_name,
    fu.email_address
FROM apps.hr_organization_units hou,
     apps.fnd_profile_options_vl fpo,
     apps.fnd_profile_option_values fpov,
     apps.fnd_responsibility_vl frv,
     apps.fnd_user_resp_groups furg,
     apps.fnd_user fu
WHERE fpov.level_value = frv.responsibility_id
  AND fpo.profile_option_id = fpov.profile_option_id
  AND fpo.user_profile_option_name = 'MO: Operating Unit'
  AND hou.organization_id = TO_NUMBER(fpov.profile_option_value)
  AND frv.responsibility_id = furg.responsibility_id
  AND furg.user_id = fu.user_id;

This query retrieves the Operating Unit (ORG_ID) assigned to each EBS user, along with their email address.

Once the table is created, load it into Incorta and then push it to BigQuery — where it will serve as the reference for user-to-ORG_ID security mapping.

---

🧱 Step 2: Define a Row-Level Security Policy in BigQuery

Now that your user_org_access table is available in BigQuery, you can create a Row-Level Security (RLS) policy to enforce access control on your fact tables.

For example, let’s secure the AP Invoice Distributions fact table so that each user can only see data for the operating units they have access to. Please refer to this document for more info on how to load these facts and dimensions to BigQuery.

Use the following SQL to create the RLS policy:

CREATE ROW ACCESS POLICY ap_invoice_org_filter
ON `project.dataset.FactAPInvoiceDistributions`
GRANT TO ('domain:example.com')
FILTER USING (
  org_id IN (
    SELECT org_id 
    FROM `project.dataset.user_org_access`
    WHERE email_address = SESSION_USER()
  )
);

⚙️ How It Works

• The user_org_access table acts as a bridge between users and their permitted operating units.

• The RLS policy dynamically filters rows based on the currently logged-in user’s email address, obtained through the SESSION_USER() function.

• As a result, any query run in BigQuery against the FactAPInvoiceDistributions table automatically applies this security filter — ensuring users only see data relevant to their organization.

---

🔗 Learn More

To dive deeper into Incorta's data delivery solution for Google BigQuery refer to this article.

---

🚀 Summary

With just a few simple steps, you’ve implemented Operating Unit–based data security for EBS data in BigQuery:

1. Extract user-to-ORG_ID mapping in Incorta.

2. Push the mapping table to BigQuery.

3. Create and apply a Row-Level Security policy on your fact tables.

This approach keeps your data secure, auditable, and compliant — without complicating your data pipelines or analytics workflows.