Select AI / Natural-Language-to-SQL Concepts, Governance, Validation, and Prompt/Data Security

Learning outcomes

A ServiceHub operations manager asks, “Show overdue high-priority work orders by region,” and wants the database to translate natural language to SQL. Oracle Select AI uses an AI profile and a selected Large Language Model (LLM) provider to generate/run/explain SQL from prompts. The productivity gain does not change the trust model: LLM output is probabilistic application-generated SQL, and database privileges/VPD/views remain the security boundary.

1. Select AI is a generation gateway, not a new SQL privilege model

An AI profile tells Select AI which provider/model/credential and database objects may be used for a natural-language interaction. SELECT AI SHOWSQL ... asks the LLM to show generated SQL; RUNSQL runs generated SQL and is the default action; EXPLAINSQL explains the generated statement. The AI keyword itself cannot run DDL, DML or PL/SQL—its NL2SQL execution surface is SELECT—but a SELECT can still expose everything the current database identity can read.

2. Current 26ai Select AI availability is provider/profile dependent

Current 26ai documentation describes Select AI running natively in Oracle AI Database and Autonomous AI Database. It uses DBMS_CLOUD_AI, supported external providers or private/local AI infrastructure depending on capability, database/network credentials and Access Control List (ACL) privileges. Some Autonomous documentation requires an Autonomous instance plus provider account; self-managed Oracle AI Database can integrate provider/private services under the 26ai Select AI capability set. Always check the current capability matrix for the exact deployment.

3. Build a read-only AI-facing view

CREATE OR REPLACE VIEW sh25_ai_work_orders_v ASSELECT  w.work_order_id,  w.status_code,  t.technician_id,  t.technician_name,  r.region_id,  r.region_nameFROM work_orders wLEFT JOIN technicians t  ON t.technician_id=w.technician_idLEFT JOIN service_regions r  ON r.region_id=t.region_id;

The AI-facing object contains only columns the natural-language use case needs. It hides payload JSON/other sensitive columns by design and creates a stable semantic contract for prompts.

4. Create a least-privilege NL2SQL execution identity

BEGIN EXECUTE IMMEDIATE 'DROP USER sh25_ai_reader CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/CREATE USER sh25_ai_reader NO AUTHENTICATION;GRANT CREATE SESSION TO sh25_ai_reader;GRANT SELECT ON servicehub_owner.sh25_ai_work_orders_v  TO sh25_ai_reader;PASSWORD sh25_ai_reader-- Set the lab credential interactively.

Do not grant SELECT ANY TABLE, DBA, table-owner login, or DML merely because “the AI might need it.” Generated SQL should hit the same database authorization wall as any human/application query.

5. Provider-free local simulation: preview candidate SQL

Assume an LLM proposed the following SQL for “Show active work orders by region.” First inspect it as text/code. Then run only through the read-only identity:

SELECT  region_name,  COUNT(*) AS active_work_ordersFROM servicehub_owner.sh25_ai_work_orders_vWHERE status_code='OPEN'GROUP BY region_nameORDER BY active_work_orders DESC;

CONNECT sh25_ai_reader@//localhost:1521/FREEPDB1SELECT  region_name,  COUNT(*) AS active_work_ordersFROM servicehub_owner.sh25_ai_work_orders_vWHERE status_code='OPEN'GROUP BY region_nameORDER BY active_work_orders DESC;

Validate business semantics too: “active” may mean OPEN only, or OPEN + HOLD in the actual domain. A syntactically valid query can be semantically wrong.

6. Deliberate malicious/wrong candidate hits a privilege wall

SELECT payload_jsonFROM servicehub_owner.work_orders;-- As SH25_AI_READER:-- Expected ORA-00942 (table or view does not exist)-- because only the curated view was granted.

This is why least privilege is the durable control. A regex prompt filter such as “never mention payload_json” is not a security boundary; prompts and LLM output can be manipulated.

7. Real Select AI profile shape: narrow object list

BEGIN  DBMS_CLOUD_AI.CREATE_PROFILE(    profile_name => 'SH25_READONLY_AI',    attributes   => '{      "provider":"openai",      "credential_name":"SH25_AI_CRED",      "object_list":[        {          "owner":"SERVICEHUB_OWNER",          "name":"SH25_AI_WORK_ORDERS_V"        }      ],      "enforce_object_list":"true"    }'  );END;/EXEC DBMS_CLOUD_AI.SET_PROFILE('SH25_READONLY_AI');

object_list supplies target metadata to generation; enforce_object_list=true further restricts generated SQL to those listed objects. Database privileges must still be least-privilege—profile metadata restriction and SQL authorization are two independent layers.

8. SHOWSQL before RUNSQL

SELECT AI SHOWSQL  show open work orders by region;-- Review generated SQL, referenced objects, joins, filters,-- aggregation, row limits, and expected cardinality first.SELECT AI EXPLAINSQL  show open work orders by region;-- RUNSQL/default execution only after policy permits it.

For automated low-risk use cases, policy can allow execution after static/semantic checks; for sensitive/ad-hoc analytical requests, keep human approval or a server-side query gateway.

9. SELECT AI cannot issue DML/DDL—but read exposure is still serious

The AI keyword is supported only in SELECT and cannot run PL/SQL, DDL or DML. That removes one class of destructive output, but a SELECT can scan regulated tables, infer sensitive counts, create expensive Cartesian joins, call allowed functions, or exfiltrate data to the client. Resource governance, row security, query timeouts and curated views still matter.

10. Prompt and metadata security

Select AI augments prompts with database schema metadata from target objects. External-provider use therefore creates a data-flow boundary even if table rows are not always sent. RAG/chat features may send retrieved content to the provider depending on configuration. Classify prompts, object comments/metadata and retrieved documents; use approved providers/endpoints, TLS/network ACLs, provider retention settings and secrets management.

11. LLM correctness validation

• Use descriptive curated views/column comments rather than exposing a sprawling owner schema.
• Prefer SHOWSQL for new prompts; keep prompt/SQL/result feedback samples.
• Validate joins, time zones/NLS, NULL semantics, definitions such as “active,” and row-level authorization.
• Apply statement timeout/resource controls for ad-hoc generated queries.
• Use test questions with known answers and track NL2SQL execution/correctness rates by model/profile version.

12. Local private-model alternative

26ai also documents Oracle Private AI Services Container for local/private embedding and LLM inference, and supports private endpoint patterns. That is additional infrastructure—not part of Oracle Free itself. A lighter learning alternative is to generate SQL in an external local LLM manually, display it to the user, and run it only through the same curated least-privilege database account after review.

13. Cleanup

DROP USER sh25_ai_reader CASCADE;DROP VIEW sh25_ai_work_orders_v;-- On a provider-configured lab, also clear/drop the test AI profile-- using DBMS_CLOUD_AI after verifying it is not shared.

14. Production judgment

Select AI is appropriate when natural-language productivity is worth model/provider cost and the target query domain can be governed. Curate the SQL surface with views, object lists and least-privilege users; start with SHOWSQL and known-answer evaluation; audit prompt/profile/model changes; budget generated-query resource use; never treat an LLM response as trusted SQL because it came from Oracle tooling.

Select AI in 26ai is capability/provider/deployment dependent, and external provider credentials/network ACLs can be required. The local Free exercise needs none of them. No restart or COMPATIBLE increase is required by the simulation; VECTOR/RAG portions retain the >=23.4.0 vector prerequisite. Lesson 5 turns these one-query mechanisms into a production lifecycle where embeddings/models/indexes can drift independently of the relational source.

Authoritative references

• Select AI — current Select AI guide
• Use AI Keyword — SHOWSQL/RUNSQL/EXPLAINSQL and restrictions
• DBMS_CLOUD_AI — profile/generation API
• Examples — Restrict Object Access — object_list/enforce_object_list
• Private AI Services Container — private/local AI infrastructure