Search the collections from another schema

Measure and tune the search settings

By the end of this lesson, you can let an agent in another schema search the course collections through one granted package that searches as a fixed ADM user or as the signed-in user, show that the package gives the caller its own ADM user back on every path, and read the run of that agent in the trace of its own schema.

Lesson 7 of 7~45 minScript: 22_aic_app_schema.sql, 22b_aic_app_application.sql, 23_aic_rag_bridge_api.pks, 23_aic_rag_bridge_api.pkb, 24_aic_bridge_setup.sql, 25_aic_bridge_check.sql, 26_aic_app_tools_api.pkb, 27_aic_app_register.sql, 28_aic_app_run.sql, 29_aic_app_per_user.sql, 98_aic_app_teardown.sql, 97_aic_app_schema_drop.sql

Plan a search from another schema

In lessons 1 to 6, the agent, its tools and ADM are in one schema. Often the application that wants the answers has its own schema, its own UC AI installation and its own agent. In this lesson, a purchasing application in the schema AIC_APP asks the contract and policy collections of the course.

The search needs an ADM user. adm_ai_rag_api.prepare_sources checks every chunk against the ADM user of the session, the ADM context. A session of another application has no ADM user. Then prepare_sources returns no rows and no error, and the agent of that application answers “nothing found” for every question.

Two ways to give that session an ADM user are wrong:

An execute grant on adm_context_api to the application schema. With it, any code in that schema can log in as any ADM user, administrators included.
An execute grant on adm_ai_rag_api alone. It does not help, because the session still has no ADM user.

This lesson puts the ADM user into one package in the ADM schema, the bridge. The application schema gets execute on this package and on nothing else:

Object	Schema	What it does
`aic_rag_bridge_api`	ADM	Searches one collection as the fixed ADM user `AIC_SERVICE`, or as a named user, then gives the caller its own ADM user back
`AIC_SERVICE`	ADM user	Role `VIEWER`, member of the group `AIC_LEGAL` only. It decides what the other application sees
`aic_app_tools_api`	`AIC_APP`	The two search tools of the agent. They call the bridge through a synonym
`aic_app_assistant`	`AIC_APP`	The agent, with its prompt profile and its trace in the UC AI tables of `AIC_APP`

Choose the ADM user that the other application searches as

The bridge searches as one fixed ADM user. Every user of the other application gets the passages that this one ADM user may see, no more and no less. This is a design decision, and the lesson makes it on purpose:

The other application has its own users. They are not ADM users, so ADM cannot check them one by one.
A dedicated user is better than a person’s account. Its rights do not change when the person changes jobs, every audit row of ADM names it, and an ADM administrator can deactivate it to stop the bridge at once.
AIC_SERVICE is not an administrator, and it is not in the group AIC_HR. So it does not see the salary bands, and no user of the purchasing application can get them.

If the users of the other application are ADM users with different rights, the search can run as the signed-in user instead. That option costs trust, and Search as the signed-in user shows it at the end of this lesson. The main path of the lesson uses the fixed user.

Prepare the application schema

The application schema needs three things:

UC AI, the same release as in the ADM schema. The recording uses UC AI 26.4 in both schemas. exec dbms_output.put_line(uc_ai.c_version) prints the release.
A place in the APEX workspace of the ADM schema, because the agent calls the model through the web credential OPENAI, which belongs to that workspace.
An APEX application of its own. UC AI runs the code of a tool in the parsing schema of the APEX application of the session. If no APEX session exists, execute_agent creates one in any application of the workspace, which can belong to the ADM schema. In the recording, that happened, and the first agent run failed with PLS-00201: identifier 'AIC_APP_TOOLS_API.SEARCH_CONTRACTS' must be declared.

An application schema that already has UC AI and an APEX application in that workspace is ready. Use it, and set the define aic_app_schema in 24_aic_bridge_setup.sql to its name.

Create the demo schema AIC_APP (needs a DBA)

If you have no such schema, create the demo schema AIC_APP. Do these steps:

Ask a DBA to run @22_aic_app_schema.sql, or run it with a DBA connection yourself. It creates the user AIC_APP with a demo password that is written in the script, Aic-App-Demo-2026, so use it on a local database only. It grants the role DB_DEVELOPER_ROLE of Oracle 23ai, and adds AIC_APP to the APEX workspace of the ADM schema:
```
create user aic_app identified by "Aic-App-Demo-2026" quota unlimited on users;
grant db_developer_role to aic_app;
apex_instance_admin.add_schema(p_workspace => l_workspace, p_schema => 'AIC_APP');
```
```
1. user created: AIC_APP

2. AIC_APP is in the APEX workspace ADM
```
Connect as AIC_APP, and run install_uc_ai_complete.sql of the UC AI release from the UC AI releases page. The install took less than 10 seconds in the recording. It needs no network ACL, because APEX makes the HTTP calls of UC AI.

As AIC_APP, run @22b_aic_app_application.sql. It creates the application “AIC App” with no pages, with the same calls that an APEX export file makes. The agent runs in an APEX session of this application.

API Last Extended: 20260407
Your Current Version: 20260407
This import is compatible with version: 20241130
COMPATIBLE (You should be able to run this import without issues.)
ID offset during import: 67772995060093256
New ID offset for application: 0
... elapsed: ,4 sec
Application 158 "AIC App" created in the workspace ADM

The Makefile of the example has the targets app-schema and app-uc-ai for these steps.

Create the service user and the bridge

The bridge has two functions. This section shows search_passages, which searches as the fixed user. It returns the passages of one collection as a pipelined table, so that SQL can read them:

function search_passages (p_collection_name in varchar2, p_query in varchar2) return t_passages pipelined as
  l_passages t_passages;
begin
  -- 1. the name check, then 2. the search as AIC_SERVICE, with the context of the caller back afterwards
  l_passages := fetch_as(p_username      => c_service_user          -- 'AIC_SERVICE'
                       , p_collection_id => collection_id(p_collection_name)
                       , p_query         => p_query);
  -- 3. the rows, only after the caller has its own ADM user back
  for i in 1 .. l_passages.count loop
    pipe row (l_passages(i));
  end loop;
  return;
end search_passages;

function fetch_as (p_username in varchar2, p_collection_id in number, p_query in varchar2) return t_passages as
  l_caller   r_context_type;
  l_passages t_passages;
begin
  l_caller.username      := sys_context(adm_context_api.c_ctx_namespace, adm_context_api.c_ctx_username);
  l_caller.role          := sys_context(adm_context_api.c_ctx_namespace, adm_context_api.c_ctx_role);
  l_caller.access_source := sys_context(adm_context_api.c_ctx_namespace, adm_context_api.c_ctx_access_source);
  begin
    adm_context_api.system_user_login(p_username => p_username);
    if sys_context(adm_context_api.c_ctx_namespace, adm_context_api.c_ctx_role) = adm_context_api.c_role_admin then
      refuse_user(p_username, 'the user has the role ADMIN');
    end if;
    select document_name, avg_score, chunk_count, source_text
      bulk collect into l_passages
      from table(adm_ai_rag_api.prepare_sources(p_rag_collection_id => p_collection_id, p_query => p_query))
     order by avg_score desc;
  exception
    when others then
      adm_error.log_error(p_scope => c_scope_prefix || 'fetch_as', p_extra => 'user ' || p_username);
      restore_caller(l_caller);
      raise;
  end;
  restore_caller(l_caller);
  return l_passages;
end fetch_as;

The order of the steps is the design:

The ADM user is a constant, c_service_user, not a parameter. The caller cannot choose another one.
The bridge reads all rows before it pipes the first one. A pipelined function stops where its caller stops fetching. If the function pipes rows while it holds AIC_SERVICE, a caller that reads one row and stops keeps AIC_SERVICE for the rest of its session.
restore_caller runs on every path out, also after an error. It calls adm_context_api.restore_context, which puts back the user, the role and the access source of the caller, or clears the context when the caller had none. If the restore itself fails, restore_caller logs that error, clears the context and raises. Then the session has no ADM user, not the bridge user.
The handler logs the original error before the restore. If the restore fails, its error replaces the original one, and the log still has the original. The bare raise comes last, because an error that is raised and handled inside the restore would clear the original error, and raise then fails with ORA-21002.
The bridge refuses an identity with the role ADMIN. If an ADM administrator gives AIC_SERVICE the role ADMIN later, the bridge stops searching instead of searching with more rights.
Only names that start with aic_ pass. The bridge compares the text with substr, not with like, because _ in a like pattern matches any character. The prefix is a naming convention, not a security boundary: any ADM administrator can create a collection with the name aic_.... What a search returns is limited by the rights of AIC_SERVICE, not by the name.

The bridge resolves the collection by its name, not by its ID, because the IDs differ between databases. If no collection has the name, the error lists the collections that the bridge can search, so that a renamed collection is found from one call.

Declare the errors of the bridge

The bridge raises its errors in the range -20700 to -20999, which ADM leaves for customer code. Each error has three declarations in the package spec: the number, the message and an exception that is bound to the number. A caller in another schema can then catch the error by name:

c_err_user_refused constant number := -20752;
c_msg_user_refused constant varchar2(500 char) := 'The search bridge does not search as "%0": %1.';
e_user_refused     exception;
pragma exception_init(e_user_refused, -20752);

The number is written twice, because pragma exception_init accepts only a literal. The spec has three errors: -20750 for a refused collection name, -20751 for a collection that does not exist, and -20752 for a refused user.

The body raises them with adm_error.raise_error, the procedure that ADM uses for its own errors. It accepts a number from the customer range when you pass the message template in p_message. It fills %0 and %1, and writes the error to the APEX debug log before it raises. A plain raise_application_error with the same constants also works, as the guide section Raising errors from your own code shows. raise_error adds the log entry:

adm_error.raise_error(
  p_error_code => c_err_collection_refused
, p_message    => c_msg_collection_refused
, p_scope      => c_scope_prefix || 'collection_id'
, p0           => c_collection_prefix
, p1           => p_collection_name
);

c_scope_prefix is 'aic_rag_bridge_api.', so the log names the procedure. The handlers call adm_error.log_error and then raise. They never put sqlerrm into a message, because the message can reach a user.

Run @24_aic_bridge_setup.sql as the ADM schema owner. It creates the ADM user AIC_SERVICE with the role VIEWER in the group AIC_LEGAL, installs the bridge, and grants execute on it to AIC_APP. It calls no model:

=== 1. the service user AIC_SERVICE ===
=== 2. the package aic_rag_bridge_api ===
=== 3. the grant ===


WHAT                                DETAIL
___________________________________ __________________________________________
service user                        AIC_SERVICE (VIEWER), groups: AIC_LEGAL
package                             PACKAGE VALID
package                             PACKAGE BODY VALID
grants on ADM objects to AIC_APP    AIC_RAG_BRIDGE_API (EXECUTE)

The last row lists every grant of the ADM schema to AIC_APP. It is the bridge and nothing else.

Check the bridge without a model

25_aic_bridge_check.sql calls prepare_sources and the bridge directly, as the ADM schema owner. It runs there because only in the ADM schema can a script give the caller an ADM user, which shows that the bridge gives that user back. It makes about twelve search embeddings and one embedding call that fails on purpose. It calls no answer model, and it commits nothing. Run @25_aic_bridge_check.sql:

--- prepare_sources without an ADM user ---
context:                (none) / - / -
prepare_sources:        0 rows
context:                AIC_SERVICE / VIEW / DB
prepare_sources:        2 rows contract_alpenwerk_maintenance.pdf, scan_nordlicht_amendment.pdf

--- The bridge searches as AIC_SERVICE, whoever calls it ---
caller:                 AIC_HR / EDIT / DB
Helios, direct:         0 rows
Helios, bridge:         1 rows contract_helios_distribution.pdf (.675)
salary bands, direct:   1 rows salary_bands.pdf
salary bands, bridge:   0 rows
caller after the calls: AIC_HR / EDIT / DB

--- The bridge refuses names outside its prefix ---
hr_salaries:            error ORA-20750: The search bridge only searches collections whose name starts with "aic_". "hr_salaries" is not one of them.
caller after the error: AIC_HR / EDIT / DB

--- A name that does not exist ---
aic_handbook:           error ORA-20751: No RAG collection is named "aic_handbook". Collections that the search bridge can search: aic_contracts, aic_policies, aic_policies_large.
caller after the error: AIC_HR / EDIT / DB

--- A failure inside the search ---
empty query:            error ORA-20302: Error response from provider OpenAI: HTTP 400 from provider
caller after the error: AIC_HR / EDIT / DB

--- A caller that stops after the first row ---
first row:              contract_alpenwerk_maintenance.pdf
caller after the fetch: AIC_HR / EDIT / DB

--- The per-user function gives the caller back, too ---
AIC_LEGAL, salary bands: 0 rows
AIC_HR, salary bands:    1 rows salary_bands.pdf
ADMIN:                   error ORA-20752: The search bridge does not search as "ADMIN": the user has the role ADMIN.
caller after the calls:  AIC_HR / EDIT / DB

The output shows these results:

Without an ADM user, prepare_sources returns 0 rows and no error. As AIC_SERVICE, the same call returns the Alpenwerk contract and its amendment. This is what the session of AIC_APP gets without the bridge.
The bridge searches as AIC_SERVICE, whoever calls it. The caller is AIC_HR, who cannot see the Helios contract and can see the salary bands. Through the bridge, the result is the other way round, because AIC_SERVICE is in the group AIC_LEGAL. These two rows show the decision of Choose the ADM user that the other application searches as.
The bridge refuses hr_salaries before it looks for the collection, and it names aic_handbook as not found with the list of collections that it can search. The list depends on your schema. It contains aic_policies_large only if you kept that collection in lesson 6.
After every call, the caller is AIC_HR / EDIT / DB again: after a search, after a refusal, after an error, and after a caller that read only the first row. An empty search text fails at the embedding call, after the bridge has logged in as AIC_SERVICE, so that row tests the error path.
The per-user function also gives the caller back. It searches as AIC_LEGAL and as AIC_HR with different results, and it refuses an administrator. The script picks the first active administrator of your schema, ADMIN in the recording. Search as the signed-in user explains this function.

Register the tools and the agent in the application schema

The application schema calls the bridge through a synonym. Its tool package keeps the status contract of lesson 2, with one change: there is no no_identity, because the bridge always searches as its own ADM user.

Status	Meaning
`ok`	The search found passages that `AIC_SERVICE` may see.
`no_passages`	The search ran and found nothing above the score threshold.
`error`	Anything else. For a collection name that does not exist, the message of the bridge, with the list of collections. Other details go to the APEX debug log.

select json_arrayagg(
         json_object('document' value document_name, 'score' value round(score, 3)
                   , 'matched_chunks' value matched_chunks, 'text' value passage_text returning clob)
         order by score desc returning clob)
  into l_passages
  from table(aic_rag_bridge_api.search_passages(p_collection_name => p_collection_name, p_query => p_query));
...
exception
  when aic_rag_bridge_api.e_collection_not_found
    or aic_rag_bridge_api.e_collection_refused
    or aic_rag_bridge_api.e_user_refused
  then
    -- the message of the bridge, without "ORA-nnnnn: "
    return status_json('error', p_collection_name
      , regexp_substr(sqlerrm, '^ORA-[0-9]+: ([^' || chr(10) || ']*)', 1, 1, null, 1)
        || ' Tell the user that the search is unavailable.');

The package has one tool for each collection, as in lesson 3: search_contracts and search_policies. The collection names are constants in the package, so the model cannot choose a collection that the agent does not offer. One tool for each body of text also lets the answer say where a passage came from.

Connect as AIC_APP, and run @27_aic_app_register.sql. It creates the synonym aic_rag_bridge_api for ADM.aic_rag_bridge_api, installs the tool package, and registers the tools AIC_APP_SEARCH_CONTRACTS and AIC_APP_SEARCH_POLICIES with the tag aic_app, the prompt profile AIC_APP_ASSISTANT and the agent aic_app_assistant. The tools take only the parameter query, as after lesson 6. The prompt has the six rules of lesson 2, and rule 5 names only no_passages and error. If your ADM schema has another name, change the define adm_schema first. The script calls no model:

=== 1. the synonym for the bridge ===
=== 2. the package aic_app_tools_api ===
=== 3. the two tools ===
=== 4. the prompt profile and the agent ===

Registered in this schema:

KIND       CODE                        DETAIL
__________ ___________________________ ____________________________________
synonym    AIC_RAG_BRIDGE_API          ADM.AIC_RAG_BRIDGE_API
package    AIC_APP_TOOLS_API           PACKAGE VALID
package    AIC_APP_TOOLS_API           PACKAGE BODY VALID
tool       AIC_APP_SEARCH_CONTRACTS    tags: aic_app
tool       AIC_APP_SEARCH_POLICIES     tags: aic_app
profile    AIC_APP_ASSISTANT           active, openai/gpt-6.1-sol
agent      aic_app_assistant           active, profile AIC_APP_ASSISTANT

Run the agent in the application schema

28_aic_app_run.sql runs as AIC_APP. It calls the two tools by hand, and then runs the agent once. Before the run, it creates an APEX session in the application of AIC_APP, so that UC AI runs the tool code in this schema:

select min(application_id) into l_app_id from apex_applications where owner = user;
apex_util.set_workspace(l_workspace);
apex_session.create_session(p_app_id => l_app_id, p_page_id => 0, p_username => 'AIC_APP_DEMO');

l_result := uc_ai_agents_api.execute_agent(
  p_agent_code       => 'aic_app_assistant'
, p_input_parameters => json_object_t('{"question":"How do we end the Alpenwerk maintenance contract, and what may I spend on a hotel when I visit them?"}')
, p_session_id       => uc_ai_agents_api.generate_session_id
);

In a page of your application, the session exists already, and you do not create it. Run @28_aic_app_run.sql. The tool calls make three search embeddings. The agent run uses gpt-6.1-sol and about 3,300 input tokens in the recording.

=== The tools, called directly ===
ADM user of this session: (none)
search_contracts, "Alpenwerk notice period termination"
  status:      ok, searched_as: AIC_SERVICE
  passages:    ["contract_alpenwerk_maintenance.pdf","scan_nordlicht_amendment.pdf"]
search_policies, "salary band senior engineer"
  status:      no_passages, searched_as: AIC_SERVICE
  message:     No passage in the collection aic_policies matches this query. Say so; do not answer from memory. You may try other words.
search('aic_handbook'), a collection that does not exist
  status:      error, searched_as: AIC_SERVICE
  message:     No RAG collection is named "aic_handbook". Collections that the search bridge can search: aic_contracts, aic_policies, aic_policies_large. Tell the user that the search is unavailable.
ADM user of this session: (none)

The session of AIC_APP has no ADM user before and after the calls, and every result says searched_as: AIC_SERVICE. The salary bands give no_passages, because AIC_SERVICE may not see them.

=== The agent run ===
Answer:
### Ending the Alpenwerk contract
Give **written notice, with 30 days’ notice to the end of a calendar month**: “Either party may terminate this Contract with a notice period of 30 days to the end of a calendar month. The notice must be in writing.” [contract_alpenwerk_maintenance.pdf]

### Hotel budget
The **2026 policy replaces the 2025 policy**: “Valid from 1 January 2026. This version replaces the Travel Policy 2025.” [travel_policy_2026.pdf]

- **2026:** “Hotel, per night, large cities EUR 165”; “Hotel, per night, other places EUR 120.” [travel_policy_2026.pdf]
- **2025 had lower limits:** “Hotel, per night, large cities EUR 140”; “Hotel, per night, other places EUR 110.” [travel_policy_2025.pdf]

I can’t determine which location category applies to your visit from these passages.

If the **total trip exceeds EUR 750**, obtain approval before booking: “A trip that costs more than EUR 750 needs the approval of the line manager before booking.” [travel_policy_2026.pdf]

Tool calls:   3
Tokens:       {"prompt_tokens":3331,"completion_tokens":355,"reasoning_tokens":9,"total_tokens":3686}
Seconds:      13.8
Execution id: 42


=== The trace of the run, from uc_ai_agent_messages of this schema ===

   SEQ ROLE           TOOL_NAME                   WHAT
______ ______________ ___________________________ ___________________________________________________________________________________________________________________________________________________
     1 user                                       How do we end the Alpenwerk maintenance contract, and what may I spend on a hotel when I visit them?
     2 tool_call      AIC_APP_SEARCH_CONTRACTS    {"query":"Alpenwerk maintenance termination notice"}
     3 tool_call      AIC_APP_SEARCH_POLICIES     {"query":"travel hotel nightly limit"}
     4 tool_result    AIC_APP_SEARCH_CONTRACTS    {"status":"ok","collection":"aic_contracts","searched_as":"AIC_SERVICE","passages":[{"document":"contract_alpenwerk_maintenance.pdf","score" ...
     5 tool_result    AIC_APP_SEARCH_POLICIES     {"status":"ok","collection":"aic_policies","searched_as":"AIC_SERVICE","passages":[{"document":"travel_policy_2026.pdf","score":0.44,"matche ...
     6 reasoning
     7 tool_call      AIC_APP_SEARCH_CONTRACTS    {"query":"Alpenwerk maintenance amendment termination"}
     8 tool_result    AIC_APP_SEARCH_CONTRACTS    {"status":"ok","collection":"aic_contracts","searched_as":"AIC_SERVICE","passages":[{"document":"contract_alpenwerk_maintenance.pdf","score" ...
     9 assistant                                  ### Ending the Alpenwerk contract
                                                  Give **written notice, with 30 days’ notice to the end of a calendar month**: “Either party may terminate

=== Who ran it ===

   ID STATUS       DB_USER    APEX_USER          APEX_APP_ID    TOOL_CALLS_COUNT    TOTAL_INPUT_TOKENS    TOTAL_OUTPUT_TOKENS
_____ ____________ __________ _______________ ______________ ___________________ _____________________ ______________________
   42 completed    AIC_APP    AIC_APP_DEMO               158                   3                  3331                    355

Read the trace in the application schema

The trace comes from uc_ai_agent_messages of AIC_APP. The UC AI tables of the ADM schema have no row of this run, because each schema has its own UC AI installation. In the recording, the model called both tools in the first round, one for the contract and one for the travel policy. Then it searched the contracts once more for an amendment, so the run made three tool calls. Each tool result shows "searched_as":"AIC_SERVICE". The answer quotes the contract clause and both travel policies, and says which one is newer, as rules 3 and 4 of the prompt ask. The number of tool calls and the wording differ between runs.

The last table comes from uc_ai_agent_executions of AIC_APP. db_user is AIC_APP, apex_user is the user name of the APEX session, and apex_app_id is the application of AIC_APP. ADM sees AIC_SERVICE for the same search. When you need to know which person of the other application asked, record it in that application, for example as the APEX user of the session, or search as that person with the function of the next section.

Search as the signed-in user

With the fixed user, every user of the purchasing application gets the same passages. Sometimes the users of the other application are also ADM users with different rights, and each one must see only their own documents. For that case the bridge has a second function, search_passages_for_user. It searches as the ADM user whose name the caller passes, with the same restore of the caller’s context as search_passages. ADM then also writes its audit rows for that person.

When to trust a user name from another schema

With this function, the ADM schema believes the user name that the other schema passes. ADM cannot check that the name belongs to the person who is signed in. So every line of code that can run in AIC_APP can act as any ADM user that the guards of the bridge let through: any active user without the role ADMIN in the allowed groups. The APEX user is no proof either: code in AIC_APP can create an APEX session for any name with apex_session.create_session, and 29_aic_app_per_user.sql does exactly that.

The function is acceptable when these conditions are true:

Only code that you control runs in AIC_APP, and it passes only the user that its own APEX application authenticated, APP_USER.
The application signs in its users with the same names that ADM uses, for example with the same identity provider or with APEX accounts of the same names.
The model never chooses the name. The tool reads it from the session. A tool parameter username would let a prompt choose the identity.

Use the fixed user when other teams, generated code or a scheduler job of somebody else can run in AIC_APP, or when the users of the application are not ADM users. The grant on aic_rag_bridge_api covers both functions. If the other schema must only use the fixed user, delete search_passages_for_user from your copy of the bridge.

Limit the users that the bridge searches as

The guards run in the ADM schema, before the login, so code in AIC_APP cannot skip them:

-- c_allowed_groups constant apex_t_varchar2 := apex_t_varchar2('AIC_LEGAL', 'AIC_HR');   (in the spec)
select count(*), count(case when r.role_name = 'ADMIN' then 1 end)
  into l_active, l_admin
  from adm_users u
  join adm_roles r
    on r.role_id = u.role_id
 where u.username  = upper(p_username)
   and u.is_active = 'Y';

if l_active = 0 then
  refuse_user(p_username, 'no active ADM user has this name');
elsif l_admin > 0 then
  refuse_user(p_username, 'the user has the role ADMIN');
end if;
-- then the same for the groups: refuse_user(p_username, 'the user is in none of the groups AIC_LEGAL, AIC_HR')

An unknown or inactive user is refused before system_user_login sees the name. A deactivated ADM user is refused, so that deactivation stops the access at once.
A user with the role ADMIN is refused, so that AIC_APP cannot borrow administrator rights. Today prepare_sources checks every chunk with adm_access_control_api.user_is_allowed_to_view_document_personal and gives an administrator no extra passages. The refusal still matters: an ADM session with the role ADMIN passes the administrator checks of other ADM calls, and a later change to the bridge could add one. fetch_as repeats the check after the login, so it also covers AIC_SERVICE.
A user outside c_allowed_groups is refused. This guard is optional. It limits the people that AIC_APP can act as to the departments that use the purchasing application.

All three refusals raise e_user_refused (-20752) with the reason in the message.

Pass the signed-in APEX user from the tool

The tool in AIC_APP reads the user from the APEX session and passes it to the bridge. The model never sees or chooses the name:

function search_as_app_user (p_collection_name in varchar2, p_query in varchar2) return clob as
  l_app_user varchar2(255 char);
begin
  l_app_user := sys_context('APEX$SESSION', 'APP_USER');
  if l_app_user is null or l_app_user = 'nobody' then
    return status_json('error', p_collection_name
      , 'No user is signed in, so no documents can be searched. Tell the user that the search is unavailable.', null);
  end if;
  return search_passages_json(p_collection_name, p_query, l_app_user);   -- calls search_passages_for_user
end search_as_app_user;

sys_context('APEX$SESSION', 'APP_USER') and v('APP_USER') both returned the APEX user inside a tool run of aic_app_assistant in a test, because UC AI runs the tool in the APEX session of the run. Both also returned it after apex_session.attach in a new database session. The package has two tool functions on top, search_contracts_as_app_user and search_policies_as_app_user. 27_aic_app_register.sql does not register them. To use them, register them as in 27_aic_app_register.sql, with return aic_app_tools_api.search_contracts_as_app_user(:parameters); as the function call.

A chat region of the type ucAiChat in the application of AIC_APP runs the agent in a scheduler job, as in lesson 4. Its Session Init Code joins the APEX session of the user, so that the tool finds APP_USER:

apex_session.attach(
  p_app_id     => :APP_ID,
  p_page_id    => :APP_PAGE_ID,
  p_session_id => :APP_SESSION
);

Lesson 4 adds adm_context_api.establish_from_session as a second line. AIC_APP has no grant on adm_context_api and needs none: the bridge sets the ADM user for each search.

Run the per-user search

29_aic_app_per_user.sql runs as AIC_APP. It creates an APEX session for four user names, one after the other, and calls search_policies_as_app_user with the same query in each. It makes two search embeddings, calls no answer model, and commits nothing. AIC_APP cannot read the ADM tables, so the script cannot look up an administrator. Pass the ADM username of an active administrator of your schema as the argument. To find one, run this query in the ADM schema:

select min(u.username)
  from adm_users u
  join adm_roles r
    on r.role_id = u.role_id
 where r.role_name = 'ADMIN'
   and u.is_active = 'Y';

The recording uses AIC_ADMIN, an administrator who is also a member of the group AIC_HR. Without the argument, the script skips the administrator row and says so. Run @29_aic_app_per_user.sql AIC_ADMIN and replace AIC_ADMIN with your administrator:

Question: {"query":"salary band senior engineer"}
ADM user of this session: (none)
APEX user AIC_LEGAL  status: no_passages, searched_as: AIC_LEGAL
                     message:  No passage in the collection aic_policies matches this query. Say so; do not answer from memory. You may try other words.
APEX user AIC_HR     status: ok, searched_as: AIC_HR
                     passages: ["salary_bands.pdf"]
APEX user AIC_ADMIN  status: error, searched_as: -
                     message:  The search bridge does not search as "AIC_ADMIN": the user has the role ADMIN. Tell the user that the search is unavailable.
APEX user J_DOE      status: error, searched_as: -
                     message:  The search bridge does not search as "J_DOE": no active ADM user has this name. Tell the user that the search is unavailable.
ADM user of this session: (none)

AIC_LEGAL and AIC_HR get different results for the same query, because the bridge searched as each of them. AIC_HR sees the salary bands, and AIC_LEGAL does not.
The administrator is refused, although AIC_ADMIN is in the group AIC_HR. The role check comes before the group check.
J_DOE is refused, because no ADM user has this name. A refused user gets no searched_as, because the bridge did not search.
The session of AIC_APP has no ADM user before and after the four searches.

Remove what the lesson created

The lesson changed three places. Remove them in this order:

As the application schema, run @98_aic_app_teardown.sql. It deletes the agent and its runs, the profile, the tools, the tool package, the synonym and the application of 22b_aic_app_application.sql. UC AI stays.
As the ADM schema owner, run @99_aic_teardown.sql at the end of the course. It now also drops aic_rag_bridge_api, which removes its grant, and deletes the ADM user AIC_SERVICE.
If you created the demo schema, ask a DBA to run @97_aic_app_schema_drop.sql. It removes AIC_APP from the APEX workspace and drops the user with everything in it. It refuses while AIC_APP still owns an APEX application.

make app-teardown runs steps 1 and 3.

Classify uploads with an LLMThe other AI course: let a model classify new uploads, and release the verdicts as annotations that a RAG collection can select.