Private AI Search in Oracle APEX: Data In-House, Tenants Apart
AI search inside the Oracle database, with no outside AI service. I check what really leaves the server, show how one forgotten WHERE clause leaks another franchise's data, and close that gap with Virtual Private Database. Plus an honest look at what this kind of AI can not do.
After part 3 I showed the search page to people who run businesses, not databases. The questions were the same every time. If the AI sits inside our database, does that mean our data stays with us and we do not depend on an American AI service? We have franchises. Can the AI show one franchise owner the customers of another? And can I simply ask it who my strongest customer was?
Fair questions. This post answers all three, on the same demo stack as parts 1 to 3, with every script run for real. Short version. Yes, the data stays. Yes, it can leak between tenants, but not because of the AI, and one database feature closes it for good. And no, this kind of AI can not tell you your best customer.
Part 1 installed Oracle AI Database 26ai Free, ORDS and APEX 26.2 in Docker. Part 2 loaded the embedding model into the database. Part 3 built the Smart Search page. Everything here runs on exactly that stack. No new container, no language model.
What the AI in the Database Really Is
"AI in the database" sounds like a chat bot living next to your tables. It is not. What parts 2 and 3 put into Oracle is an embedding model. It does exactly one thing. It turns a piece of text into a list of 384 numbers that describe its meaning. Two texts with a similar meaning get similar numbers, and the database can measure that distance. That is the whole trick behind the search.
Look at the Model
The model is a database object like a table or a package. You can query it, grant it and back it up.
-- What the "AI" in the database really is: one embedding model, stored like any other object. set lines 150 pages 50 feedback off col owner format a13 col model_name format a20 col mining_function format a10 col algorithm format a10 col size_mb format 990.0 select owner, model_name, mining_function, algorithm, round(model_size / 1024 / 1024, 1) as size_mb from all_mining_models; col dims format 9990 col first_values format a60 select vector_dimension_count(v) as dims, substr(from_vector(v returning clob), 1, 52) || '...' as first_values from (select vector_embedding(apexnote_vec.all_minilm_l12_v2 using 'The customer was billed twice this month' as data) as v from dual);
OWNER MODEL_NAME MINING_FUN ALGORITHM SIZE_MB ------------- -------------------- ---------- ---------- ------- APEXNOTE_VEC ALL_MINILM_L12_V2 EMBEDDING ONNX 127.1 DIMS FIRST_VALUES ----- ------------------------------------------------------------ 384 [-4.81662303E-002,4.34131473E-002,-4.3135602E-002,4....
| The embedding model can | The embedding model can not |
|---|---|
| Find records by meaning, not by exact words | Answer a question in sentences |
| Find duplicates and similar records | Calculate sums, totals or rankings |
| Group texts by topic | Remember anything between two queries |
| Give the best context to a language model later (RAG) | Decide who may see which row |
The model was trained once, years ago, on public text. It never learns from your data and it keeps nothing between two calls. Every call is pure math on the text you pass in. That matters for the franchise question later.
Part A. Does Anything Leave the Server?
"The data stays in the database" is easy to write. I wanted to see it. Oracle controls every outgoing network connection from PL/SQL with access control lists. If a schema has no entry, it can not open a connection. Not to OpenAI, not to anybody.
Who May Talk to the Network?
-- Who may open network connections from this database at all? set lines 150 pages 50 feedback off col host format a10 col principal format a20 col privilege format a10 select host, principal, privilege from dba_host_aces order by principal;
HOST PRINCIPAL PRIVILEGE ---------- -------------------- ---------- * APEX_260200 CONNECT * GSMADMIN_INTERNAL RESOLVE
APEXNOTE, the schema behind the app. The APEX_260200 entry came with the APEX install of the ORDS container, I did not add it.APEX_260200 with CONNECT on * means APEX itself may call any host. That is what APEX_WEB_SERVICE and REST Data Sources use. The search in this series does not use them, but if your app calls a web service somewhere, that call can leave the server. Check dba_host_aces on your own system and narrow it to the hosts you really need.
Try to Reach the Internet, Then Search
Now the real test. As the application schema I try to call an AI service directly, and right after that I run the semantic search from part 3 in the same session.
-- The application schema can not reach the internet, and the search still works. set lines 150 pages 50 feedback off serveroutput on begin dbms_output.put_line(substr(utl_http.request('https://api.openai.com/'), 1, 80)); end; / col title format a52 col match_pct format 990 with question as ( select vector_embedding(apexnote_vec.all_minilm_l12_v2 using 'customers get duplicate bills by e-mail' as data) as v from dual ) select t.title, round((1 - vector_distance(t.search_vec, question.v, cosine)) * 100) as match_pct from demo_tasks t, question order by vector_distance(t.search_vec, question.v, cosine) fetch first 3 rows only;
begin * ERROR at line 1: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1594 ORA-24247: network access denied by access control list (ACL) TITLE MATCH_PCT ---------------------------------------------------- --------- Invoices arrive twice in the customer mailbox 52 Duplicate tasks are created twice 33 Fix ORA-01555 in the monthly sales report 20
What this means for data protection
I am not a lawyer, so read this as a technical view. For this search no text and no personal data goes to an outside AI provider. There is no extra processor to sign a data processing agreement with, and no transfer to a third country for this feature. The data is where it was before, in your database on your server.
Two honest limits. Oracle is an American company, so the software itself is from the US, even when it runs on your server in Germany. And the database still needs everything it needed before. Hosting contract, access rights, backups, a deletion concept. The AI part does not make that worse, it just does not add a new place where data goes.
Part B. One Database, Many Franchises
Now the question that worried people most. One app, one database, several franchises. Can the AI show the Frankfurt owner a customer from Hamburg? To test it I built a small franchise in the demo database. A cleaning company with two branches, customer notes and orders.
Franchise Tables and Sample Data
Every row carries a FRANCHISE_ID. The notes get a vector column and a trigger, exactly like the tasks in part 3. A small mapping table says which APEX user belongs to which franchise.
-- A small franchise: one database, one app, two branches. Every row belongs to exactly one franchise. set lines 150 pages 50 feedback on timing off create table franchises ( id number primary key, name varchar2(50) not null ); insert into franchises values (1, 'Frankfurt'); insert into franchises values (2, 'Hamburg'); create table franchise_users ( username varchar2(100) primary key, franchise_id number not null references franchises ); insert into franchise_users values ('FRANKFURT_MANAGER', 1); insert into franchise_users values ('HAMBURG_MANAGER', 2); create table franchise_notes ( id number generated always as identity primary key, franchise_id number not null references franchises, customer varchar2(60) not null, note varchar2(1000) not null, created_on date default on null sysdate not null, note_vec vector(384, float32) ); create or replace trigger franchise_notes_vec_biu before insert or update of customer, note on franchise_notes for each row begin select vector_embedding(apexnote_vec.all_minilm_l12_v2 using :new.customer || '. ' || :new.note as data) into :new.note_vec from dual; end; / create table orders ( id number generated always as identity primary key, franchise_id number not null references franchises, customer varchar2(60) not null, order_date date not null, amount number(10,2) not null );
-- Similar problems in both branches, different customers. Exactly the case where a search can mix them up. set lines 150 pages 50 feedback off timing on insert into franchise_notes (franchise_id, customer, note) values (1, 'Mainblick Dental Practice', 'The customer says the invoice for September arrived twice and was paid twice. Refund the second payment.'); insert into franchise_notes (franchise_id, customer, note) values (1, 'Mainblick Dental Practice', 'Cleaning team arrived 40 minutes late on Monday. Practice manager was unhappy because patients were already waiting.'); insert into franchise_notes (franchise_id, customer, note) values (1, 'Westend Law Office', 'Office key was not returned after the evening shift. Key is now in the branch safe.'); insert into franchise_notes (franchise_id, customer, note) values (1, 'Westend Law Office', 'Customer wants a fixed quote for window cleaning twice a year.'); insert into franchise_notes (franchise_id, customer, note) values (1, 'Sachsenhausen Bakery', 'Floor in the sales room was still wet when the shop opened. A customer almost slipped.'); insert into franchise_notes (franchise_id, customer, note) values (1, 'Sachsenhausen Bakery', 'Contract renewal for next year agreed by phone, waiting for the signed copy.'); insert into franchise_notes (franchise_id, customer, note) values (1, 'Ostend Fitness Studio', 'Showers in the changing rooms smell of mould. Customer asks for a deep clean.'); insert into franchise_notes (franchise_id, customer, note) values (1, 'Ostend Fitness Studio', 'Alarm system went off at 5 am because the cleaner used the wrong code.'); insert into franchise_notes (franchise_id, customer, note) values (1, 'Bockenheim Kita', 'Parents complained about strong cleaning product smell in the group rooms.'); insert into franchise_notes (franchise_id, customer, note) values (1, 'Bockenheim Kita', 'Customer pays always 30 days late. Send a friendly reminder before the next invoice.'); insert into franchise_notes (franchise_id, customer, note) values (2, 'Alster Tax Advisors', 'Customer reports that the October invoice was sent two times by e-mail. Check the billing run.'); insert into franchise_notes (franchise_id, customer, note) values (2, 'Alster Tax Advisors', 'Cleaner came one hour late, the office was already full of clients.'); insert into franchise_notes (franchise_id, customer, note) values (2, 'Harbour Coffee Roasters', 'Grease on the kitchen floor was not removed. Customer sent photos.'); insert into franchise_notes (franchise_id, customer, note) values (2, 'Harbour Coffee Roasters', 'Customer cancels the Saturday cleaning from December on.'); insert into franchise_notes (franchise_id, customer, note) values (2, 'Eppendorf Physio Centre', 'Treatment rooms need disinfection after every shift, new hygiene plan attached.'); insert into franchise_notes (franchise_id, customer, note) values (2, 'Eppendorf Physio Centre', 'Front door was left unlocked after cleaning on Friday night.'); insert into franchise_notes (franchise_id, customer, note) values (2, 'Altona Co-Working', 'Customer wants to double the cleaning hours, ask for a new offer.'); insert into franchise_notes (franchise_id, customer, note) values (2, 'Altona Co-Working', 'Toilet paper and soap were not refilled for a whole week.'); insert into franchise_notes (franchise_id, customer, note) values (2, 'Speicherstadt Gallery', 'Paintings must not be touched, only dry dusting of the frames.'); insert into franchise_notes (franchise_id, customer, note) values (2, 'Speicherstadt Gallery', 'Last two invoices are still open, the customer asked for a payment plan.'); commit; insert into orders (franchise_id, customer, order_date, amount) select f.franchise_id, f.customer, trunc(sysdate, 'MM') - (level - 1) * 30 + mod(level * 7, 20) as order_date, round(f.base * (0.85 + mod(level * 13, 30) / 100), 2) as amount from (select 1 franchise_id, 'Mainblick Dental Practice' customer, 1850 base from dual union all select 1, 'Westend Law Office', 2400 from dual union all select 1, 'Sachsenhausen Bakery', 980 from dual union all select 1, 'Ostend Fitness Studio', 3100 from dual union all select 1, 'Bockenheim Kita', 1450 from dual union all select 2, 'Alster Tax Advisors', 2750 from dual union all select 2, 'Harbour Coffee Roasters', 1200 from dual union all select 2, 'Eppendorf Physio Centre', 2100 from dual union all select 2, 'Altona Co-Working', 4300 from dual union all select 2, 'Speicherstadt Gallery', 890 from dual) f connect by level <= 6 and prior f.customer = f.customer and prior sys_guid() is not null; commit; set timing off col franchise format a10 select f.name as franchise, (select count(*) from franchise_notes n where n.franchise_id = f.id) as notes, (select count(case when n.note_vec is not null then 1 end) from franchise_notes n where n.franchise_id = f.id) as with_vector, (select count(*) from orders o where o.franchise_id = f.id) as orders from franchises f order by f.id;
FRANCHISE NOTES WITH_VECTOR ORDERS ---------- ---------- ----------- ---------- Frankfurt 10 10 30 Hamburg 10 10 30
The Leak
The Frankfurt manager asks about a customer who received the same invoice twice. The query is the search from part 3, pointed at the notes. It has no franchise filter. Not because I am careless, but because this is exactly the line that gets lost when a page is copied, a report is added in a hurry or a developer tests something.
-- The Frankfurt manager asks a question. The query has no franchise filter, because somebody forgot one line. set lines 160 pages 50 feedback off col franchise format a10 col customer format a26 col note format a70 trunc col match_pct format 990 variable q varchar2(200) exec :q := 'customer received the same invoice twice' with question as ( select vector_embedding(apexnote_vec.all_minilm_l12_v2 using :q as data) as v from dual ) select f.name as franchise, n.customer, n.note, round((1 - vector_distance(n.note_vec, question.v, cosine)) * 100) as match_pct from franchise_notes n join franchises f on f.id = n.franchise_id, question order by vector_distance(n.note_vec, question.v, cosine) fetch first 4 rows only;
FRANCHISE CUSTOMER NOTE MATCH_PCT ---------- -------------------------- ---------------------------------------------------------------------- --------- Hamburg Alster Tax Advisors Customer reports that the October invoice was sent two times by e-mail 68 Frankfurt Mainblick Dental Practice The customer says the invoice for September arrived twice and was paid 66 Hamburg Speicherstadt Gallery Last two invoices are still open, the customer asked for a payment pla 42 Hamburg Altona Co-Working Customer wants to double the cleaning hours, ask for a new offer. 38
The model only compared texts. It returned Hamburg because the query allowed Hamburg. A normal LIKE search or a classic report with the same missing WHERE leaks the same rows. So the fix does not belong in the AI. It belongs in the database, where nobody can forget it.
Virtual Private Database
Virtual Private Database, VPD for short, lets Oracle add a WHERE condition to every statement on a table. Select, insert, update, delete. The application does not see it and can not skip it. The condition comes from a policy function. Mine asks APEX who is logged in, through the context APEX$SESSION that APEX fills on every request, and looks up the franchise of that user.
First try, straight as the application schema.
-- First try: add the policy as the application schema. set lines 150 pages 50 feedback on create or replace function franchise_policy ( p_schema in varchar2, p_object in varchar2 ) return varchar2 is begin return 'franchise_id = (select fu.franchise_id from franchise_users fu ' || 'where fu.username = sys_context(''APEX$SESSION'', ''APP_USER''))'; end; / begin dbms_rls.add_policy( object_schema => 'APEXNOTE', object_name => 'FRANCHISE_NOTES', policy_name => 'FRANCHISE_NOTES_SEP', function_schema => 'APEXNOTE', policy_function => 'FRANCHISE_POLICY', statement_types => 'select, insert, update, delete', update_check => true, policy_type => dbms_rls.shared_static); end; /
Function created. policy_type => dbms_rls.shared_static); * ERROR at line 10: ORA-06550: line 10, column 24: PLS-00201: identifier 'DBMS_RLS' must be declared ORA-06550: line 2, column 3: PL/SQL: Statement ignored
-- The application schema needs the right to manage policies. That is a DBA decision, so it is a grant. set lines 150 pages 50 feedback on grant execute on dbms_rls to apexnote;
-- One policy function, used for the notes and the orders. The predicate is added to EVERY statement on these tables. set lines 150 pages 50 feedback on begin dbms_rls.add_policy( object_schema => 'APEXNOTE', object_name => 'FRANCHISE_NOTES', policy_name => 'FRANCHISE_NOTES_SEP', function_schema => 'APEXNOTE', policy_function => 'FRANCHISE_POLICY', statement_types => 'select, insert, update, delete', update_check => true, policy_type => dbms_rls.shared_static); dbms_rls.add_policy( object_schema => 'APEXNOTE', object_name => 'ORDERS', policy_name => 'ORDERS_SEP', function_schema => 'APEXNOTE', policy_function => 'FRANCHISE_POLICY', statement_types => 'select, insert, update, delete', update_check => true, policy_type => dbms_rls.shared_static); end; / col object_name format a16 col policy_name format a20 col function format a17 select object_name, policy_name, function, sel, ins, upd, del, enable from user_policies order by object_name;
PL/SQL procedure successfully completed. OBJECT_NAME POLICY_NAME FUNCTION SEL INS UPD DEL ENA ---------------- -------------------- ----------------- --- --- --- --- --- FRANCHISE_NOTES FRANCHISE_NOTES_SEP FRANCHISE_POLICY YES YES YES YES YES ORDERS ORDERS_SEP FRANCHISE_POLICY YES YES YES YES YES
grant execute on dbms_rls both tables have a policy for all four statement types. On Oracle AI Database 26ai Free this works without any extra option.Three settings matter here.
- The predicate fails closed. Without a logged-in APEX user the subquery finds no franchise, and the condition matches no row. A forgotten session means no data, not all data.
update_check => truealso checks new and changed rows. Nobody can write a row into another franchise.shared_staticruns the policy function once and caches the text. That is fine because the text itself never changes, only the value ofsys_contextat runtime.
Two Franchise Users
-- Two end users of the Task Board app, one per franchise. set lines 150 pages 50 feedback on begin apex_util.set_workspace(p_workspace => 'APEXNOTE'); for u in (select username from franchise_users) loop apex_util.create_user( p_user_name => u.username, p_web_password => '<a_strong_user_password>', p_change_password_on_first_use => 'N'); end loop; commit; end; / col user_name format a20 select user_name, account_locked from apex_workspace_apex_users where workspace_name = 'APEXNOTE' order by user_name;
PL/SQL procedure successfully completed. USER_NAME ACC -------------------- --- APEXNOTE_ADMIN No DEMO_DEV No FRANKFURT_MANAGER No HAMBURG_MANAGER No
Same Query, Three Users
This is the moment of truth. The query from step 5, not changed by a single character. For SQL*Plus I simulate the APEX login with apex_session.create_session, which fills the same APEX$SESSION context a real page request does.
-- The same query as in step 5, not changed by one character. Only the logged-in APEX user changes. set lines 160 pages 50 feedback on col franchise format a10 col customer format a26 col note format a62 trunc col match_pct format 990 variable q varchar2(200) exec :q := 'customer received the same invoice twice' prompt === 1. No APEX session (a script, a report tool, a forgotten job) with question as ( select vector_embedding(apexnote_vec.all_minilm_l12_v2 using :q as data) as v from dual ) select f.name as franchise, n.customer, n.note, round((1 - vector_distance(n.note_vec, question.v, cosine)) * 100) as match_pct from franchise_notes n join franchises f on f.id = n.franchise_id, question order by vector_distance(n.note_vec, question.v, cosine) fetch first 4 rows only; prompt === 2. Same query, logged in as FRANKFURT_MANAGER exec apex_session.create_session(p_app_id => 100, p_page_id => 1, p_username => 'FRANKFURT_MANAGER') / prompt === 3. Same query, logged in as HAMBURG_MANAGER exec apex_session.delete_session exec apex_session.create_session(p_app_id => 100, p_page_id => 1, p_username => 'HAMBURG_MANAGER') / prompt === 4. Hamburg asks for Frankfurt rows on purpose select count(*) as frankfurt_rows_visible from franchise_notes where franchise_id = 1; prompt === 5. Hamburg tries to write a note into Frankfurt insert into franchise_notes (franchise_id, customer, note) values (1, 'Westend Law Office', 'test'); rollback; exec apex_session.delete_session
=== 1. No APEX session (a script, a report tool, a forgotten job) no rows selected === 2. Same query, logged in as FRANKFURT_MANAGER FRANCHISE CUSTOMER NOTE MATCH_PCT ---------- -------------------------- -------------------------------------------------------------- --------- Frankfurt Mainblick Dental Practice The customer says the invoice for September arrived twice and 66 Frankfurt Bockenheim Kita Customer pays always 30 days late. Send a friendly reminder be 38 Frankfurt Westend Law Office Customer wants a fixed quote for window cleaning twice a year. 32 Frankfurt Sachsenhausen Bakery Contract renewal for next year agreed by phone, waiting for th 23 === 3. Same query, logged in as HAMBURG_MANAGER FRANCHISE CUSTOMER NOTE MATCH_PCT ---------- -------------------------- -------------------------------------------------------------- --------- Hamburg Alster Tax Advisors Customer reports that the October invoice was sent two times b 68 Hamburg Speicherstadt Gallery Last two invoices are still open, the customer asked for a pay 42 Hamburg Altona Co-Working Customer wants to double the cleaning hours, ask for a new off 38 Hamburg Alster Tax Advisors Cleaner came one hour late, the office was already full of cli 29 === 4. Hamburg asks for Frankfurt rows on purpose FRANKFURT_ROWS_VISIBLE ---------------------- 0 === 5. Hamburg tries to write a note into Frankfurt ERROR at line 1: ORA-28115: policy with check option violation
Who Can Still See Everything?
VPD binds every user, even the owner of the table. I ran all the tests above as APEXNOTE, the owner, and still got no rows without a session. Two kinds of users are not bound. SYS, and everybody with the system privilege EXEMPT ACCESS POLICY. Know that list.
-- VPD binds everybody, the table owner too. Only SYS and users with EXEMPT ACCESS POLICY see everything. set lines 150 pages 50 feedback on select count(*) as all_notes_seen_by_sys from apexnote.franchise_notes; col grantee format a20 select grantee from dba_sys_privs where privilege = 'EXEMPT ACCESS POLICY';
ALL_NOTES_SEEN_BY_SYS --------------------- 20 GRANTEE -------------------- SYS GSMUSER_ROLE GSMADMIN_INTERNAL OGG_CAPTURE
-- What the database really runs: the policy predicate shows up in the plan, the query text never had it. set lines 150 pages 80 feedback off explain plan for select customer, note from franchise_notes; select plan_table_output from table(dbms_xplan.display(format => 'BASIC +PREDICATE'));
-------------------------------------------------------- | Id | Operation | Name | -------------------------------------------------------- | 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS FULL | FRANCHISE_NOTES | | 2 | TABLE ACCESS BY INDEX ROWID| FRANCHISE_USERS | |* 3 | INDEX UNIQUE SCAN | SYS_C0019002 | -------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("FRANCHISE_ID"= (SELECT "FU"."FRANCHISE_ID" FROM "APEXNOTE"."FRANCHISE_USERS" "FU" WHERE "FU"."USERNAME"=SYS_CONTEXT('APEX$SESSION','APP_USER'))) 3 - access("FU"."USERNAME"=SYS_CONTEXT('APEX$SESSION','APP_USER'))
select customer, note from franchise_notes. The plan shows what the database really runs, with the franchise filter added by the policy.The APEX Page
In the app the page is a copy of the Smart Search page from part 3, pointed at the notes. One display item shows the user's franchise, and the report query has, again on purpose, no franchise filter.
-- no franchise filter here on purpose: the VPD policy adds it to every query with question as ( select vector_embedding(apexnote_vec.all_minilm_l12_v2 using :P5_SEARCH as data) as v from dual ) select n.customer as list_title, n.note as list_text, to_char(round((1 - vector_distance(n.note_vec, question.v, cosine)) * 100), 'fm990') || ' %' as list_badge, 'fa fa-comment-o' as icon_class, 'u-info' as icon_color_class, null as link, null as link_class, null as link_attr, null as list_class from franchise_notes n, question where vector_distance(n.note_vec, question.v, cosine) <= 0.75 order by vector_distance(n.note_vec, question.v, cosine) fetch first 5 rows only
select f.name from franchise_users fu join franchises f on f.id = fu.franchise_id where fu.username = :APP_USER
frankfurt_manager. "Customer received the same invoice twice" finds Mainblick Dental Practice with 66 %. No Hamburg customer anywhere.
hamburg_manager. Alster Tax Advisors with 68 %. The page did not change, the user did.Part C. "Who Was My Strongest Customer?"
The third question. A manager does not want to search notes, they want numbers. Who was my strongest customer in the last three months, and how much did we make with them? Let me ask the semantic search first, because that is what people expect the "AI" to do.
Ask the Search, Then Ask SQL
-- "Who was my strongest customer?" First as a semantic search, then as plain SQL. Logged in as FRANKFURT_MANAGER. set lines 160 pages 50 feedback off exec apex_session.create_session(p_app_id => 100, p_page_id => 1, p_username => 'FRANKFURT_MANAGER') col customer format a26 col note format a70 trunc col match_pct format 990 with question as ( select vector_embedding(apexnote_vec.all_minilm_l12_v2 using 'who was my strongest customer in the last three months' as data) as v from dual ) select n.customer, n.note, round((1 - vector_distance(n.note_vec, question.v, cosine)) * 100) as match_pct from franchise_notes n, question order by vector_distance(n.note_vec, question.v, cosine) fetch first 3 rows only; col revenue format 999,990.00 select customer, count(*) as orders, sum(amount) as revenue from orders where order_date >= add_months(trunc(sysdate, 'MM'), -3) group by customer order by revenue desc fetch first 3 rows only; exec apex_session.delete_session exec apex_session.create_session(p_app_id => 100, p_page_id => 1, p_username => 'HAMBURG_MANAGER') / exec apex_session.delete_session
CUSTOMER NOTE MATCH_PCT -------------------------- ---------------------------------------------------------------------- --------- Sachsenhausen Bakery Floor in the sales room was still wet when the shop opened. A customer 31 Mainblick Dental Practice The customer says the invoice for September arrived twice and was paid 30 Bockenheim Kita Customer pays always 30 days late. Send a friendly reminder before the 29 CUSTOMER ORDERS REVENUE -------------------------- ---------- ----------- Ostend Fitness Studio 4 12,710.00 Westend Law Office 4 9,840.00 Mainblick Dental Practice 4 7,585.00 -- same SQL, logged in as HAMBURG_MANAGER CUSTOMER ORDERS REVENUE -------------------------- ---------- ----------- Altona Co-Working 4 17,630.00 Alster Tax Advisors 4 11,275.00 Eppendorf Physio Centre 4 8,610.00
The search is not broken. The question is simply not a search. "Strongest customer" means sum the orders per customer for a period and sort. That is arithmetic, and an embedding model does not do arithmetic. SQL does.
To ask that in plain language you need a second kind of AI, a language model. It reads the question, writes the SQL, the database runs it, and the model phrases the answer. This is often called text-to-SQL. There are two ways to run one.
| Option | Good | Watch out |
|---|---|---|
| External model (OpenAI, Anthropic, Google and others) | Very good SQL, nothing to host, works today | The question, the table structure and often the result rows leave your server. That is a processor and possibly a third-country transfer again. |
| Self-hosted model (open models on your own server) | Everything stays in-house, same as the embedding model | Needs real hardware, ideally a GPU, and smaller open models write weaker SQL. Plan for testing. |
Whatever writes the SQL, a developer or a language model, the SQL must run with the rights of the logged-in user, read-only, on tables protected by VPD. Then even a perfectly wrong or manipulated query can only ever see the user's own franchise. The model never gets rows it was not allowed to read, so it can not tell anybody about them.
Troubleshooting. What Went Wrong
| Error | Where | Real cause | Fix |
|---|---|---|---|
| PLS-00201 identifier 'DBMS_RLS' must be declared | dbms_rls.add_policy as the app schema | No execute right on DBMS_RLS | grant execute on dbms_rls to apexnote as SYS |
| ORA-24247 network access denied by access control list | utl_http.request as the app schema | No entry in dba_host_aces. Here it is the point of the test. | Nothing. Grant a host ACE only for hosts you really need. |
| ORA-28115 policy with check option violation | Insert into another franchise | update_check => true | Works as designed |
| ORA-28115 on every row of a seed script | Re-running the sample data script after the policy was active | No APEX session, so no franchise, so no row is allowed | Load data with a real user session, or from a separate admin process you trust |
| A test row landed in the wrong franchise | SQL*Plus / right after exec | / re-runs the last SQL statement in the buffer, which was my failed insert, now as the other user | Never use / after exec in test scripts. I deleted the row and rewrote the script. |
Checklist Before You Go Live
- Every tenant table has a policy. Notes, orders, invoices, attachments, and every table with a vector column. A forgotten table is a forgotten
WHEREagain. - Fail closed. Without a known user the predicate must match nothing. Test it with a session that has no user.
- Know who bypasses. Check
EXEMPT ACCESS POLICYand keep application code away fromSYSand DBA accounts. - Other entry points. REST endpoints, scheduler jobs and reports run without an APEX page request. They need the same context, or they correctly see nothing.
- Outgoing network. Read
dba_host_aces. Only hosts you need, only for the schemas that need them. - Vector indexes. With an approximate index the policy filter is applied on top of the index search. Test with real data per tenant that you still get enough results.
Semantic search, duplicate detection and smart suggestions that run inside your Oracle database, with tenant separation built in. For franchises, branches and every app where customers must never see each other's data. Get in touch.
What Comes Next
- A context for everything else. An own application context, set by REST handlers and jobs, so the same policy protects ORDS endpoints too.
- Language model on top. Text-to-SQL that runs as the logged-in user, read-only, on VPD tables. External or self-hosted, the rule from part C stays the same.
- Audit. Unified auditing on the policy tables, so you can show who searched what, if a customer ever asks.
Final Thoughts
The most useful thing I learned building this is where the risk really sits. People are afraid of the AI. The AI in this post is a 127 MB file that turns text into numbers and forgets everything right after. The risk was always the query around it, and that risk existed long before anybody said "AI".
Virtual Private Database moves the franchise filter from every single query into one place in the database. It took one grant, one function and two policy calls, and it works on the Free edition. After that the leak from step 5 was impossible, not just unlikely.
If you run a franchise or a multi-tenant APEX app and want to try this on your own data, write to me. I read every message.
{fullWidth}