Oracle Label Security | Case Study

Row-Level Security, Done Right: My Oracle Label Security Rollout Across Nepal's 7 Provinces

How I used Oracle Label Security to enforce province- and department-level data access across Nepal's 7 provinces

Munish Kumar Karna - DBA

The Business Problem

A national organization operating across all 7 provinces of Nepal needed a single, shared database — but with very different visibility rules depending on who was looking at it. Executive leadership needed a complete, nationwide view. Province-level managers needed full visibility into their own province, but nothing outside it. And functional staff (HR, IT, Marketing, Finance) needed to see only their own department's records within their own province.

Rather than maintaining separate schemas, views, or application-level filters for every combination of role and province, I implemented Oracle Label Security (OLS) — Oracle's native, row-level access control engine — directly at the database layer. One shared table, one policy, and the database itself decides what each session is allowed to see.

Why Oracle Label Security

• Access control lives in the database, not scattered across application code — every client (reports, BI tools, ad-hoc SQL) inherits the same restriction automatically.
• Row visibility is driven by a data label stored on each row, compared against a user's security clearance — no per-query WHERE clauses to maintain.
• It scales cleanly: adding an 8th province or a 5th department later means adding a component, not rewriting application logic.

Designing the Label Structure

OLS labels are built from three dimensions. I mapped each one directly onto the organization's real-world structure:

Dimension	Values in this build	What it controls
LEVEL (hierarchy)	MGR (Manager) > ASST (Assistant / Province Manager)	Rank — who can see whom
COMPARTMENT (dept.)	HR · IT · MAKT (Marketing) · FIN (Finance)	Which functional data a user may touch
GROUP (org. unit)	PROV1 – PROV7 (Nepal's 7 provinces)	Which province's data a user may touch

A label such as MGR:HR:PROV5 therefore reads as “Manager-level clearance, HR compartment, Province 5” — and a row tagged with that label is only visible to a session whose own clearance covers all three parts.

Role	Province scope	Department scope
CEO / National Manager (MGR)	All 7	All (HR, IT, MAKT, FIN)
Province Assistant Manager (ASST_PROVINCE)	Own province only	All (HR, IT, MAKT, FIN)
HR User	Own province only	HR only
IT User	Own province only	IT only
Marketing User	Own province only	MAKT only
Finance User	Own province only	FIN only

Step 1 — Registering and Enabling OLS

Oracle Label Security ships with the Oracle Home but isn't active by default. The first step is confirming it's registered, then enabling it per container database using Oracle's concurrent script runner (catcon.pl).

Confirming OLS is registered via V$OPTION before enabling it.

Running catols.sql through catcon.pl to install the OLS data dictionary, types, and packages.

Install log confirming the catcon.pl run completed.

Installing the component doesn't automatically switch on enforcement — that has to be enabled explicitly for the CDB/PDBs, then verified.

Enabling Label Security enforcement and verifying it's active.

Final OLS status check before policy creation.

With OLS active, LBACSYS needs inherited privileges on SYS to manage policies cleanly:

SQL> GRANT INHERIT PRIVILEGES ON USER SYS TO lbacsys;
 
Grant succeeded.

Step 2 — Creating the Policy, Levels, Compartments and Groups

With OLS enabled, I created a single policy — demo_policy — bound to a demo_label column, then defined the three dimensions covered above: two hierarchy levels, four department compartments, and (further down) seven province groups.

BEGIN
  SA_SYSDBA.CREATE_POLICY(
    policy_name => 'demo_policy',
    column_name => 'demo_label'
  );
END;
/
 
-- Lower number = lower rank, Higher number = higher rank
EXEC SA_COMPONENTS.CREATE_LEVEL('demo_policy', 20, 'MGR', 'Manager');
EXEC SA_COMPONENTS.CREATE_LEVEL('demo_policy', 10, 'ASST', 'Assistant Manager PROVINCE');
 
-- Define Compartments (non-hierarchical departments)
EXEC SA_COMPONENTS.CREATE_COMPARTMENT('demo_policy', 100, 'HR',   'Human Resources');
EXEC SA_COMPONENTS.CREATE_COMPARTMENT('demo_policy', 200, 'IT',   'Information Technology');
EXEC SA_COMPONENTS.CREATE_COMPARTMENT('demo_policy', 300, 'MAKT', 'Marketing');
EXEC SA_COMPONENTS.CREATE_COMPARTMENT('demo_policy', 400, 'FIN',  'Finance');

Policy creation and level/compartment registration confirmed in SQL*Plus.

Verifying the created levels and compartments against the OLS data dictionary.

Creating the Data Labels

Each label combines Level : Compartment : Group — one label per department, per province. With 2 levels × 4 departments × 7 provinces, this produced the full label set used to tag every row in the demo dataset.

Generating the province-level data labels (Level:Compartment:Province) via SA_LABEL_ADMIN.CREATE_LABEL.

Full label set confirmed after creation — every province/department/level combination is represented.

The single top-level label reserved for the national / headquarters manager, who sees all provinces, all departments, and both levels.

Step 3 — Creating the Protected Table

A dedicated application user, demo_user, owns the table that OLS will protect. The demo_data table holds a realistic mix of employee records tagged by province and department.

CREATE USER demo_user IDENTIFIED BY password123;
GRANT CONNECT, RESOURCE TO demo_user;
ALTER USER demo_user QUOTA UNLIMITED ON USERS;
 
CREATE TABLE demo_data (
  record_id    NUMBER,
  province     VARCHAR2(30),
  department   VARCHAR2(20),
  name         VARCHAR2(20),
  Salary       NUMBER,
  data_type    VARCHAR2(30),
  description  VARCHAR2(100)
);

demo_user created and the demo_data table built to hold province/department-tagged records.

Applying the Policy and Granting Access

The policy is then applied to the table (as SYS, using the LBACSYS / LBAC_DBA privilege), and demo_user is granted write access so the application can populate it.

Applying demo_policy to the demo_data table's demo_label column.

Granting demo_user privileges on the newly protected table.

Step 4 — Provisioning Users by Role, Province and Department

This was the largest provisioning step. For every one of the 7 provinces I created five database users — one Assistant Manager (full province visibility) and one each for HR, IT, Marketing and Finance (single-department visibility) — giving 35 province-level accounts, plus one national manager account (national_ceo) with unrestricted, organization-wide access. In total, 36 accounts, each mapped to exactly one row in the access matrix shown earlier.

The four functional accounts for each province were created together, following the same repeatable pattern:

-- Province 1
CREATE USER prov1_hr   IDENTIFIED BY password123;
GRANT CONNECT, RESOURCE TO prov1_hr;
ALTER USER prov1_hr QUOTA UNLIMITED ON USERS;
 
CREATE USER prov1_makt IDENTIFIED BY password123;
GRANT CONNECT, RESOURCE TO prov1_makt;
ALTER USER prov1_makt QUOTA UNLIMITED ON USERS;
 
CREATE USER prov1_it   IDENTIFIED BY password123;
GRANT CONNECT, RESOURCE TO prov1_it;
ALTER USER prov1_it QUOTA UNLIMITED ON USERS;
 
CREATE USER prov1_fin  IDENTIFIED BY password123;
GRANT CONNECT, RESOURCE TO prov1_fin;
ALTER USER prov1_fin QUOTA UNLIMITED ON USERS;
 
-- Identical block repeated for prov2_* through prov7_*

The national manager and the seven province Assistant Manager accounts were created separately, since they carry a broader clearance than a single-department user:

CREATE USER national_ceo IDENTIFIED BY password123;
GRANT CONNECT, RESOURCE TO national_ceo;
ALTER USER national_ceo QUOTA UNLIMITED ON USERS;
 
CREATE USER prov1_asst IDENTIFIED BY password123;
GRANT CONNECT, RESOURCE TO prov1_asst;
ALTER USER prov1_asst QUOTA UNLIMITED ON USERS;
 
CREATE USER prov2_asst IDENTIFIED BY password123;
-- ... and so on through prov7_asst

Table access was then granted in bulk with a single PL/SQL loop over every PROV%-prefixed account, rather than 35 individual GRANT statements:

BEGIN
  FOR r IN (SELECT username FROM all_users WHERE username LIKE 'PROV%') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON demo_data TO ' || r.username;
  END LOOP;
END;
/

Granting the national manager account the top-level clearance — full read/write across every province and department.

Assigning OLS Clearances

Creating a user and granting table privileges only makes the row visible to the database engine — it says nothing about the OLS label check. The clearance itself is set separately with SA_USER_ADMIN.SET_USER_LABELS(), and this is where the three-way access model actually gets enforced. Two distinct patterns were used:

• Assistant Managers get a comma-separated max_read_label spanning all four compartments for their own province — full department breadth, single-province depth.
• Functional users (HR / IT / Marketing / Finance) get a single compartment for their own province only — single-department breadth, single-province depth.

Province Assistant Managers, authorized across all four departments within their own province:

BEGIN
  -- Province 1
  SA_USER_ADMIN.SET_USER_LABELS(
    policy_name    => 'demo_policy',
    user_name      => 'prov1_asst',
    max_read_label => 'ASST:HR,MAKT,IT,FIN:PROV1'
  );
 
  SA_USER_ADMIN.SET_USER_LABELS(
    policy_name    => 'demo_policy',
    user_name      => 'prov2_asst',
    max_read_label => 'ASST:HR,MAKT,IT,FIN:PROV2'
  );
 
  -- ... repeated for prov3_asst through prov7_asst
END;
/

Functional (single-department) users, authorized to exactly one compartment within their own province:

BEGIN
  -- Province 1
  SA_USER_ADMIN.SET_USER_LABELS('demo_policy', 'prov1_hr',   'MGR:HR:PROV1');
  SA_USER_ADMIN.SET_USER_LABELS('demo_policy', 'prov1_makt', 'MGR:MAKT:PROV1');
  SA_USER_ADMIN.SET_USER_LABELS('demo_policy', 'prov1_it',   'MGR:IT:PROV1');
  SA_USER_ADMIN.SET_USER_LABELS('demo_policy', 'prov1_fin',  'MGR:FIN:PROV1');
 
  -- Province 2
  SA_USER_ADMIN.SET_USER_LABELS('demo_policy', 'prov2_hr',   'MGR:HR:PROV2');
  SA_USER_ADMIN.SET_USER_LABELS('demo_policy', 'prov2_makt', 'MGR:MAKT:PROV2');
  -- ... and so on through prov7_fin
END;
/

Note the deliberate label choice: functional accounts are cleared at MGR level (so their single-department view isn't further blocked by the level hierarchy), while province-wide accounts are cleared at ASST level across every compartment. The level dimension isn't used to rank humans here so much as to separate “single-department” clearance from “all-department, single-province” clearance — both patterns still resolve to the same province + department scoping shown in the verification section below.

Step 5 — Loading Sample Data

With every user's clearance in place, 50 sample employee records were inserted into demo_data — spread evenly across all 7 provinces and 4 departments — to give the verification step something realistic to query against. Each row is tagged at insert time with CHAR_TO_LABEL(), which converts a plain-text label string ('ASST:IT:PROV2') into the internal RAW label value OLS actually stores and compares.

INSERT INTO demo_data (record_id, province, department, name, Salary, data_type, description, demo_label)
VALUES (37, 'PROV2', 'IT', 'Umesh Parajuli', 87000, 'Technical', 'DevOps pipeline deployment',
        CHAR_TO_LABEL('demo_policy', 'ASST:IT:PROV2'));
 
INSERT INTO demo_data (record_id, province, department, name, Salary, data_type, description, demo_label)
VALUES (38, 'PROV3', 'FIN', 'Laxmi Sapkota', 72000, 'Financial', 'Cost-benefit study',
        CHAR_TO_LABEL('demo_policy', 'ASST:FIN:PROV3'));
 
INSERT INTO demo_data (record_id, province, department, name, Salary, data_type, description, demo_label)
VALUES (39, 'PROV4', 'HR', 'Subash Kharel', 59000, 'Administrative', 'Grievance resolution support',
        CHAR_TO_LABEL('demo_policy', 'ASST:HR:PROV4'));
 
INSERT INTO demo_data (record_id, province, department, name, Salary, data_type, description, demo_label)
VALUES (40, 'PROV5', 'MAKT', 'Ranjana Rana', 60000, 'Marketing', 'SEO optimization pass',
        CHAR_TO_LABEL('demo_policy', 'ASST:MAKT:PROV5'));
 
INSERT INTO demo_data (record_id, province, department, name, Salary, data_type, description, demo_label)
VALUES (41, 'PROV6', 'IT', 'Mahesh Yadav', 94000, 'Technical', 'Microservices migration',
        CHAR_TO_LABEL('demo_policy', 'ASST:IT:PROV6'));
 
INSERT INTO demo_data (record_id, province, department, name, Salary, data_type, description, demo_label)
VALUES (42, 'PROV7', 'FIN', 'Bhawana Chettri', 80000, 'Financial', 'Capital allocation review',
        CHAR_TO_LABEL('demo_policy', 'ASST:FIN:PROV7'));
 
-- ... continuing in the same pattern through record_id 50

• Every row's demo_label was set explicitly at insert time — there's no default label or trigger involved, so it's obvious in the script exactly which clearance each row requires.
• The record set was built to touch every province and every department at least once, so the verification step (next) has real data to prove — not just a happy-path single row.
• Because demo_user (the table owner) and the OLS administrator both hold FULL privileges under the policy, inserting rows with arbitrary labels during setup was unaffected by the very access controls being configured — a useful distinction between the privileged "setup" identity and the restricted "end user" identities created in Step 4.

Verification — Does It Actually Work?

This is the part that matters: the exact same SELECT statement, run by different users against the same shared table, returns a different result set for each — enforced entirely by the database, with zero WHERE clauses in the query.

col province for a15
col department for a5
col name for a40
select province, department, name from demo_user.demo_data;

National Manager — sees everything

Logged in with top-level clearance, the manager account returns all 50 rows across all 7 provinces and all 4 departments — an unrestricted view.

National Manager session: full result set across every province and department.

Province Assistant Manager — full province, all departments

The PROV5_ASST account sees every department within Province 5 only — HR, IT, Marketing and Finance — and nothing from any other province.

Assistant Manager, Province 5: all departments, Province 5 only (7 rows).

Province 5 — single-department users

Each functional account for Province 5 is further restricted to its own department. The HR account below sees only HR records for Province 5 — nothing from IT, Marketing or Finance, and nothing from any other province.

PROV5_HR: HR records for Province 5 only.

Province 5 — Marketing, IT, and Finance sessions, each scoped to their own department only.

Province 2 — the pattern holds across provinces

The same role structure was verified for a second province to confirm the pattern generalizes rather than being a one-off configuration.

Assistant Manager, Province 2: all departments, Province 2 only.

Province 2 — IT, Finance, Marketing and HR sessions, each correctly scoped.

Troubleshooting Notes

A few checks were essential while building this out — confirming every user's assigned clearance against the OLS data dictionary, and catching a couple of label-assignment and privilege issues before they reached the demo.

Auditing every user's MAX_READ_LABEL / MAX_WRITE_LABEL via DBA_SA_USERS to confirm clearances were assigned correctly.

Diagnosing and correcting a label-assignment mismatch.

Final checks confirming policy enforcement and privilege grants before sign-off.

Key Takeaways

• One shared table, one OLS policy, zero application-side filtering — the database enforces province + department visibility on every query, from every client.
• The Level / Compartment / Group model maps cleanly onto real organizational structure: hierarchy, function, and geography.
• The design is extensible — a new province or department is a new component, not a rewrite.
• Verification matters as much as configuration: proving the same query returns different, correctly-scoped results per role is what makes this production-credible.

Munish Kumar Karna - DBA. Senior Database Solution Architect / Oracle DBA (16+ years, Oracle Certified Professional) based in Abu Dhabi, UAE, specializing in Oracle RAC/ASM, Data Guard, GoldenGate and OCI.