DEV Community: Mark Nelson

Evaluate Oracle Database Operator for Kubernetes: A Practical First Run for Platform Teams

Mark Nelson — Wed, 03 Jun 2026 14:43:50 +0000

Key Takeaways

Start with the control loop, not production architecture. A first run should prove that Kubernetes accepts an Oracle Database custom resource and that Oracle Database Operator for Kubernetes reconciles it into observable resources, status, events, and logs.
Use one release tag and one installation path consistently. This walkthrough uses the Oracle Database Operator for Kubernetes v2.1.0 release tag, the manifest-based installation path, and a supported SingleInstanceDatabase sample from the same release-tagged repository.
Treat storage, image access, secrets, and webhooks as first-class prerequisites. Common first-run blockers include missing storage classes, Oracle Container Registry access problems, cert-manager issues, RBAC, and secrets in the wrong namespace.
A healthy demo is not a production approval. A successful first run gives platform engineers and DBAs enough evidence to continue evaluation, not enough evidence to certify backup, recovery, HA, security, performance, or operating support.

Platform teams evaluating databases on Kubernetes usually start with a practical question: will an operator improve the operating model, or will it add another controller, webhook, CRD set, and upgrade path to maintain?

Oracle Database Operator for Kubernetes extends the Kubernetes API with custom resources and controllers for supported Oracle Database lifecycle management tasks. In platform terms, it lets you express database intent as Kubernetes API objects, then inspect how the operator reconciles that intent into related resources such as pods, services, persistent volume claims, events, and status.

This article gives you a lean first run. You will install or verify the operator using the manifest-based path, apply one supported SingleInstanceDatabase sample, validate reconciliation with kubectl, troubleshoot common first-run failures, and clean up the evaluation namespace. The goal is to prove the control loop in a test environment, not to certify the database workload, storage design, backup process, or operating model for production.

This walkthrough uses the Oracle Database Operator for Kubernetes v2.1.0 release tag for install files, CRDs, samples, and API fields. The v2.1.0 manifest currently references the floating operator image container-registry.oracle.com/database/operator:latest. For a disposable first run, that follows the release manifest. For reproducible or long-lived environments, verify the resolved image version or pin an approved image tag or digest according to your platform policy.

Start by proving the operator control loop

The operator model changes where database lifecycle intent lives. Instead of starting with hand-written pods, services, PVCs, and one-off scripts, a platform engineer applies a custom resource such as SingleInstanceDatabase. The Kubernetes API stores that desired state. Oracle Database Operator for Kubernetes watches the resource and reconciles related Kubernetes objects.

That gives platform engineers and DBAs a shared inspection surface. You can inspect the custom resource, status fields, conditions, events, operator logs, pods, services, PVCs, and referenced secrets. Those are familiar Kubernetes signals, but the object being reconciled represents database intent rather than a generic workload.

That distinction matters. The operator can make supported lifecycle tasks easier to express and inspect, but it does not move every database decision into Kubernetes. DBAs and platform teams still own recovery goals, backup validation, patch policy, storage engineering, security posture, capacity planning, performance, and production runbooks.

Figure 1. Oracle Database Operator for Kubernetes turns database intent into a Kubernetes reconciliation loop. A first evaluation should prove that loop before production architecture decisions.

For this first run, success means your cluster accepts a supported Oracle Database custom resource, the operator reacts to it, related Kubernetes resources appear, and status, events, or logs explain progress or failure. That is enough for a first pass. It is not enough for production approval.

Operators can make stateful workloads look deceptively simple. A pod appears, a PVC binds, and a status field moves forward. Those are useful first-run signals. By themselves, they do not prove backup readiness, security compliance, performance, high availability, or operational supportability.

Check the prerequisites before installing anything

Use a non-production Kubernetes cluster and a Linux/Bash shell with kubectl, git, and curl installed. You need cluster administrator or equivalent permissions for this walkthrough because the default manifest-based installation creates CRDs, RBAC, webhooks, and operator resources.

Start with basic cluster checks:

kubectl version
kubectl get nodes
kubectl get storageclass

kubectl version confirms that your client can reach the API server. kubectl get nodes tells you whether the cluster has schedulable nodes. kubectl get storageclass shows whether dynamic persistent storage is available. If the cluster has no default storage class, choose an explicit storage class for the sample manifest later in the walkthrough.

A local cluster can prove the API and reconciliation path, but many local defaults are too small for Oracle Database images and persistent volumes. The SingleInstanceDatabase sample in this article requests a 50Gi persistent volume claim. If your cluster has no suitable storage class, or if quotas prevent a 50Gi PVC, fix that before applying the database sample.

Also confirm image access early. The sample used in this article pulls an Oracle Database Free Lite image from Oracle Container Registry. Depending on your organization’s policy and network setup, you may need registry login, license acceptance, egress allow rules, image pull secrets, or an approved private registry mirror.

If your organization requires image scanning or mirroring, complete that process before the demo. Treat an ImagePullBackOff as an image access problem until you have ruled out registry, network, policy, and pull-secret causes.

Create a dedicated namespace for the database resource

Keep the database evaluation separate from the operator installation. The default operator namespace is oracle-database-operator-system. The sample database resource in this article will live in oracle-db-operator-demo.

Create the demo namespace:

kubectl create namespace oracle-db-operator-demo

This separation makes inspection and cleanup easier. It also makes the scope visible: the operator runs in its own system namespace, while the database custom resource lives in a namespace that represents the evaluation workload.

This walkthrough uses the default cluster-scoped installation path, which means the operator can watch resources across namespaces. That scope is broader than the demo namespace and should be approved by the platform team. If your platform requires namespace-scoped installation, do not mix that model into this walkthrough. Follow the namespace-scoped installation instructions for your selected operator release from start to finish, including the required namespace role bindings and WATCH_NAMESPACE configuration.

Install or verify cert-manager first

Oracle Database Operator for Kubernetes uses admission and conversion webhooks with TLS certificates, so cert-manager must be healthy before you apply database resources.

First, check whether your cluster already has cert-manager:

kubectl get namespace cert-manager
kubectl get pods -n cert-manager
kubectl get crds | grep -i cert-manager

If cert-manager is already installed and managed by your platform team, use that installation. Confirm that it is approved for your cluster and compatible with the operator release you are evaluating. Do not install a second copy into a shared cluster.

If this is a disposable lab cluster and cert-manager is not installed, use cert-manager v1.16.2 for this v2.1.0 walkthrough:

kubectl apply -f https://github.com/cert-manager/cert-manager/releases/download/v1.16.2/cert-manager.yaml
kubectl get pods -n cert-manager

Wait for the cert-manager deployments to become available:

kubectl wait --for=condition=Available deployment/cert-manager \
  -n cert-manager \
  --timeout=180s

kubectl wait --for=condition=Available deployment/cert-manager-webhook \
  -n cert-manager \
  --timeout=180s

kubectl wait --for=condition=Available deployment/cert-manager-cainjector \
  -n cert-manager \
  --timeout=180s

In a shared or production-like cluster, do not install cert-manager from this article. Use the cert-manager installation approved by your platform team.

If the cert-manager pods do not move toward Running, inspect the namespace events before continuing:

kubectl get events -n cert-manager --sort-by=.lastTimestamp

Keep cert-manager troubleshooting separate from database troubleshooting. Admission webhook and certificate problems are much easier to isolate before database resources are involved.

Install Oracle Database Operator for Kubernetes from a release tag

Install the operator from a release tag rather than from the default branch so the installation manifests, samples, and API fields come from the same repository version.

Clone the v2.1.0 repository tag:

git clone --branch v2.1.0 --depth 1 https://github.com/oracle/oracle-database-operator.git
cd oracle-database-operator

For the default cluster-scoped path, apply the cluster role binding and operator manifest:

kubectl apply -f rbac/cluster-role-binding.yaml
kubectl apply -f oracle-database-operator.yaml

The release-tagged manifest is the right starting point for this first run, but remember that it references container-registry.oracle.com/database/operator:latest for the controller image. If your platform requires repeatable builds, image attestations, or explicit rollback targets, resolve and pin the approved operator image before using the manifest in a long-lived environment.

Wait for the operator deployment to roll out:

kubectl rollout status deployment/oracle-database-operator-controller-manager \
  -n oracle-database-operator-system \
  --timeout=300s

The project README also links to an OperatorHub installation path for environments that standardize on Operator Lifecycle Manager. For this first run, stay with one path so troubleshooting remains clear.

Confirm that the operator and CRDs are present

Before applying a database resource, confirm both parts of the operator model: the Kubernetes API extension and the controller workload.

Check the operator namespace:

kubectl get pods -n oracle-database-operator-system
kubectl get deployments -n oracle-database-operator-system

You should see the operator controller manager deployment in oracle-database-operator-system. Pods may take a short time to become ready while images pull and webhook certificates settle.

Next, confirm that the Oracle Database custom resources are registered with the Kubernetes API:

kubectl get crds | grep -i 'database.oracle.com'
kubectl api-resources --api-group=database.oracle.com
kubectl api-resources | grep -i 'singleinstance'

CRDs prove the Kubernetes API server recognizes the custom resource type. A running operator workload proves the controller is available to watch and reconcile resources. You need both. CRDs alone do not prove reconciliation works.

For this first run, verify that singleinstancedatabases appears as an API resource. That tells you Kubernetes can accept the custom resource kind used by the sample.

Figure 2. A first run is complete when you can validate each checkpoint and explain what the operator handled versus what the platform still owns. It is not a production-readiness checklist.

Create the admin password secret

The SingleInstanceDatabase sample expects an admin password secret named freedb-admin-secret with the key oracle_pwd. Create it in the same namespace where the database custom resource will live:

kubectl create secret generic freedb-admin-secret \
  -n oracle-db-operator-demo \
  --from-literal=oracle_pwd='<choose-a-strong-password>'

The angle-bracket placeholder is not part of the password. Replace the entire quoted value with a demo password that satisfies your organization’s password policy.

Confirm the secret exists without printing its value:

kubectl get secret freedb-admin-secret -n oracle-db-operator-demo

Command-line literal secrets are acceptable for a scoped demo only. For production, use your approved secret-management approach, such as a vault-backed workflow or another platform-approved secret delivery pattern. The first-run point is simple: the custom resource references a Kubernetes Secret, and the password should not be embedded in the database manifest or exposed in logs, screenshots, or terminal captures.

Apply one supported `SingleInstanceDatabase` sample

Use the release-tagged sample manifest instead of building a custom resource from memory. Download the v2.1.0 Free Lite sample:

curl -L -o singleinstancedatabase_free-lite.yaml \
  https://raw.githubusercontent.com/oracle/oracle-database-operator/v2.1.0/config/samples/sidb/singleinstancedatabase_free-lite.yaml

Before applying it, edit the file. The release-tagged sample uses metadata.namespace: default and spec.persistence.storageClass: "oci-bv". Change the namespace to oracle-db-operator-demo, and change the storage class to one that exists in your evaluation cluster.

The relevant fields should look like this after editing:

apiVersion: database.oracle.com/v4
kind: SingleInstanceDatabase
metadata:
  name: freedb-lite-sample
  namespace: oracle-db-operator-demo
spec:
  edition: free
  adminPassword:
    secretName: freedb-admin-secret
  image:
    pullFrom: container-registry.oracle.com/database/free:latest-lite
  persistence:
    size: 50Gi
    storageClass: "<your-storage-class>"
    accessMode: "ReadWriteOnce"
  replicas: 1

If your cluster requires credentials for Oracle Container Registry or for an internal mirror, add the image pull secret field supported by the SingleInstanceDatabase API for the operator release you are using, or follow your platform’s approved registry-mirroring process before applying the resource. The release sample shown here does not include a pull secret by default, so an otherwise correct manifest can still fail with ImagePullBackOff if registry access is not ready.

The sample requests 50Gi and uses ReadWriteOnce. The oci-bv storage class in the raw sample is appropriate for OCI Block Volume on Oracle Kubernetes Engine, but it will fail in clusters where that class does not exist.

The sample also uses the floating database image tag latest-lite. That is acceptable only for a tightly scoped evaluation where your team understands the reproducibility and security tradeoff. For retained, shared, or production-like environments, use a platform-approved, scanned, and pinned image tag or digest according to your release policy.

Before applying the manifest, check the fields you changed:

grep -E 'namespace:|storageClass:|pullFrom:' singleinstancedatabase_free-lite.yaml

Apply the edited manifest:

kubectl apply -f singleinstancedatabase_free-lite.yaml

The expected API acceptance message looks similar to this:

singleinstancedatabase.database.oracle.com/freedb-lite-sample created

At this point, you have expressed database intent through Kubernetes. API acceptance does not mean the database is ready. The next checks show whether the operator observes and reconciles that intent.

Validate reconciliation through status, events, and resources

Successful reconciliation is more than a running pod. Look for an accepted custom resource, operator activity, related Kubernetes resources, and useful status or events.

Start with the custom resource:

kubectl get singleinstancedatabase -n oracle-db-operator-demo
kubectl describe singleinstancedatabase freedb-lite-sample -n oracle-db-operator-demo

kubectl get confirms that the resource exists in the demo namespace. kubectl describe gives you the operator-facing story: status, conditions, and events associated with the resource.

Now inspect the related Kubernetes resources:

kubectl get pvc,pod,svc -n oracle-db-operator-demo
kubectl get events -n oracle-db-operator-demo --sort-by=.lastTimestamp

A healthy path should show a PVC, a database pod, and a service appearing as reconciliation proceeds. The events stream is often more useful than a raw pod list because it tells you whether the cluster is scheduling the pod, provisioning storage, pulling the image, or waiting on another dependency.

Check the concise status field:

kubectl get singleinstancedatabase freedb-lite-sample \
  -n oracle-db-operator-demo \
  -o "jsonpath={.status.status}{'\n'}"

For SIDB, when .status.status returns Healthy, the database is open for connections. For this article, treat Healthy as an operator-reported first-run readiness signal, not as production validation.

You can also inspect the connect string fields when the operator reports them:

kubectl get singleinstancedatabase freedb-lite-sample \
  -n oracle-db-operator-demo \
  -o "jsonpath={.status.connectString}{'\n'}"

kubectl get singleinstancedatabase freedb-lite-sample \
  -n oracle-db-operator-demo \
  -o "jsonpath={.status.pdbConnectString}{'\n'}"

If the status is still progressing, keep watching events. Oracle Database images can be large, volume provisioning can take time, and database initialization is not instantaneous. A first-run evaluation is successful when the signals are explainable, not when every step finishes quickly.

Troubleshoot first-run failures in the smallest scope possible

Common first-run failures are platform prerequisites surfacing through normal Kubernetes signals. Start with the part of the system that failed, and avoid changing the manifest until you know what the cluster is reporting.

If the operator pod is not running, start in the operator namespace:

kubectl get pods -n oracle-database-operator-system
kubectl describe pod <operator-pod-name> -n oracle-database-operator-system
kubectl logs -n oracle-database-operator-system <operator-pod-name> --all-containers=true

Likely causes include cert-manager readiness, RBAC problems, image pull issues, resource pressure, or webhook certificate problems. When admission fails or custom resources cannot be created, check cert-manager and webhook resources:

kubectl get pods -n cert-manager
kubectl get certificates -n oracle-database-operator-system
kubectl get validatingwebhookconfiguration | grep -i oracle
kubectl get mutatingwebhookconfiguration | grep -i oracle

If CRDs are missing, stop before applying the sample:

kubectl get crds | grep -i 'database.oracle.com'
kubectl api-resources --api-group=database.oracle.com

Missing CRDs usually mean the manifest did not apply successfully, the installing user lacked cluster-level permissions, or the files do not match the release you intended to install.

If reconciliation stalls after the custom resource is accepted, inspect the database namespace:

kubectl describe pod <database-pod-name> -n oracle-db-operator-demo
kubectl get events -n oracle-db-operator-demo --sort-by=.lastTimestamp
kubectl get pvc -n oracle-db-operator-demo
kubectl describe pvc <claim-name> -n oracle-db-operator-demo

A PVC stuck in Pending points toward storage class, quota, access mode, capacity, or provisioner issues. An ImagePullBackOff points toward registry access, image name, image tag, network egress, license acceptance, or pull-secret requirements. A missing secret or wrong key often appears through custom resource status, events, or pod startup errors.

Confirm the secret exists in the same namespace and exposes the expected key without printing the secret value:

kubectl get secret freedb-admin-secret -n oracle-db-operator-demo
kubectl describe secret freedb-admin-secret -n oracle-db-operator-demo
kubectl describe singleinstancedatabase freedb-lite-sample -n oracle-db-operator-demo

kubectl describe secret shows metadata and key sizes, not the decoded password. Do not run commands that decode or print the Secret value in screenshots, shared logs, or tickets.

Operator logs are useful when status and events do not explain enough:

kubectl logs -n oracle-database-operator-system \
  deployment/oracle-database-operator-controller-manager \
  --all-containers=true

If deployment-level logs are not enough, inspect pods directly:

kubectl get pods -n oracle-database-operator-system
kubectl logs -n oracle-database-operator-system <operator-pod-name> --all-containers=true

The practical default is to inspect status, events, PVCs, image pulls, secrets, RBAC, and operator logs before editing the custom resource. Random manifest changes can hide the real platform dependency that failed.

Clean up the evaluation resources

Clean up the database resource before deleting the namespace when possible:

kubectl delete -f singleinstancedatabase_free-lite.yaml
kubectl delete namespace oracle-db-operator-demo

Then confirm the namespace is gone or terminating:

kubectl get namespace oracle-db-operator-demo

Check whether any persistent volumes or retained storage still need attention:

kubectl get pv
kubectl get pvc -A | grep oracle-db-operator-demo || true

Storage cleanup depends on your storage class reclaim policy and whether dynamic or static provisioning was used. A namespace deletion may remove namespaced PVC objects, but the underlying persistent volume behavior depends on the storage backend and reclaim policy. Do not assume that all data-bearing resources are gone until you have checked the PVs and the backing storage system.

Keep operator uninstall separate from this first-run cleanup. If custom resource instances still exist, operator-managed resources may have finalizers that need the operator to complete cleanup.

Decide what the first run tells you

A first run is successful only if your team can explain what happened. You should be able to identify the CRD you applied, the namespace containing the database resource, the namespace containing the operator, the related Kubernetes resources that appeared, and where status, conditions, events, and logs live.

You should also be able to answer practical ownership questions. Which storage class did the database use? Which secret did the resource reference? Which image did the cluster try to pull? Did the PVC bind? Did a service appear? Did the operator write status? If the run failed, did the failure point to storage, image access, webhook admission, RBAC, secret handling, scheduling, or database initialization?

That is the operating-model value of the first run: it turns a vague success or failure into specific Kubernetes and database-operator signals that platform teams and DBAs can review together.

The first run proves the control loop. It does not prove backup and restore, HA, Data Guard, RAC, patching, upgrades, observability, network policy, security compliance, cost efficiency, or production SLOs. Those are separate decisions.

Compare the operator path with practical alternatives

Oracle Database Operator for Kubernetes is most relevant when Oracle Database is already the required database engine and your team wants to test whether Kubernetes custom resources and reconciliation improve the operating model for supported Oracle Database scenarios.

This evaluation is not a claim that every database belongs in Kubernetes. If the application can use a managed Oracle Database service, evaluate that path because managed services can shift many infrastructure and database administration tasks to the provider, depending on service tier and configuration. For many teams, that operating model is the stronger alternative when the organization values service-level ownership over platform-level control.

Manual Kubernetes resources are also a valid baseline. A hand-written StatefulSet, service, PVC, secret, and DBA script can teach you what Oracle Database needs from the cluster. The operator should earn its place by improving repeatability, inspection, and operational handoff, not by hiding the underlying dependencies.

If your application can choose another database engine, evaluate that path on its own terms. The operator path should be judged against the workload, the required database engine, the platform team’s Kubernetes maturity, and the operating responsibilities your organization is willing to own.

First-run questions platform teams usually ask

Is Oracle Database Operator for Kubernetes a replacement for DBA ownership?

No. Oracle Database Operator for Kubernetes can automate supported lifecycle tasks through Kubernetes, but DBAs and platform teams still need to define storage, backup, recovery, security, observability, upgrade, and support runbooks.

Does a successful first run prove production readiness?

No. A successful first run proves the operator model and basic reconciliation path in one environment. Production readiness requires separate validation for storage, backup and recovery, security, monitoring, failure handling, upgrades, and support boundaries.

What should I inspect first when reconciliation stalls?

Start with the custom resource status, namespace events, operator logs, PVC state, image pull status, secrets, and RBAC errors. These signals usually show whether the issue is schema validation, permissions, registry access, storage, namespace mismatch, or cluster capacity.

Should the first evaluation cover every custom resource?

No. The first evaluation should cover only the resource needed for a lean control-loop test. After that, study the custom resource model in depth and evaluate additional topologies or lifecycle operations only when the next operating question is clear.

Continue only when the next question is clear

If your first run succeeded, continue by studying the custom resource model in more depth. The next useful question is not “can I apply another YAML file?” It is “what belongs in spec, what should I expect in status, and how does reconciliation change the way platform teams and DBAs collaborate?”

If the first run failed, keep the environment small and inspect prerequisites, image access, storage class behavior, secrets, events, webhooks, RBAC, and operator logs before expanding scope. Common first-run failures are easier to solve before you add more resources or topology choices.

If your platform team cannot support storage, security, observability, backup, recovery, upgrades, and runbook ownership, treat the demo as a learning exercise rather than production evidence. A successful first run means the team has enough evidence to evaluate the next layer, not enough evidence to declare the platform production-ready.

The trust layer for enterprise AI on Oracle: prove identity, scope, policy, and evidence for every assistant action

Mark Nelson — Tue, 02 Jun 2026 13:27:23 +0000

This is article 8 of 8 in my Oracle AI Database Skills series.

Key Takeaways

Three receipts from independent sources — result rows, a session tag in V$SESSION, and a row in the audit trail or MCP log — prove what happened without relying on the chat transcript. Trust is easier to defend when independent sources tell the same story.
Oracle's existing controls — VPD, Data Redaction, TDE, Unified Auditing, network ACLs — apply to AI-assisted actions without modification. There is no separate AI trust layer to build; the database you already operate is the control plane.
EXEMPT ACCESS POLICY bypasses VPD for any session that holds it. Assign that privilege intentionally, audit who has it, and treat it as a deliberate design decision rather than a routine grant.
Turning the mini-demo into a runbook — documenting the connection name, restrict level, SHOWSQL step, and the three receipts — converts a one-time proof into repeatable operational practice that the whole team can follow.

When you put an AI assistant in front of enterprise data, "we intended to be safe" is not enough. Security teams want receipts. They will ask three blunt questions: Who acted? Over what scope? Under which policies? If your answer is a shrug or a log row that says "user: ai-service," your rollout will stall.

Oracle gives you a procedural way to answer those questions. Route assistants using a repository "skills" convention (for example, a db/SKILL.md index you maintain) so they discover only the next relevant instruction. Let them act only through a bounded MCP (Model Context Protocol) surface—SQLcl MCP on a workstation or the Managed MCP Server on Autonomous Database. Keep Oracle governance in the path: database users and roles; outbound Network ACLs and Private Endpoints; Select AI profiles with enforced object scope; VPD and Data Redaction for runtime policy; TDE for at rest protection; and Unified Auditing (plus DBTOOLS$MCP_LOG) for traceability. Wire those pieces together deliberately and every assistant action becomes something you can prove, not just something you hope was safe.

Version scope

Identity, auditing, VPD, Data Redaction, and TDE apply to Oracle Database 19c and later.
SQLcl MCP requires SQLcl 25.2+; confirm restrict-level defaults on your installed version (see SQLcl 25.4 docs for current defaults).
Select AI profiles and SHOWSQL are available on Oracle Database 26ai and on Autonomous Database where Select AI is enabled; confirm attribute names and required roles for your target release in current documentation.
On Autonomous Database, direct access to UNIFIED_AUDIT_TRAIL can be restricted by service configuration; use DBTOOLS$MCP_LOG where appropriate.

References: SQLcl MCP restrict levels (25.4), Using/Monitoring MCP, Select AI and DBMS_CLOUD_AI, Autonomous MCP, VPD, DBMS_REDACT, TDE, Unified Auditing (links in Further reading).

Why governance is the trust layer

Assistants are good at surprising you. That’s useful in exploration and dangerous in production. The practical way to keep surprises tolerable is to shrink the decision surface and add proof points at each hop.

Routing is the first shrink. Use a progressive discovery pattern. Instead of handing the assistant your entire repo, maintain a small index file (for example, db/SKILL.md) that links to the next needed skill. Load only that next file. This is a repository convention you implement, not a built-in Oracle feature.

Action comes next—and only through a surface with defined verbs. SQLcl MCP exposes a small tool set over stdio (list-connections, connect, disconnect, run-sql, run-sqlcl) and favors saved connections instead of inline credentials (see Using the SQLcl MCP Server). In SQLcl 25.4 documentation, the default restrict level is the most restrictive (level 4); verify this on your installed version and set it explicitly if needed (see SQLcl MCP restrict levels in Further reading).

Finally, let the database do what it’s built to do: govern. Identities and roles decide who you are; Network ACLs and Private Endpoints set where you can call; Select AI profiles with enforce_object_list pin the objects an NL2SQL call is allowed to touch; VPD filters rows at query time; Data Redaction masks sensitive values; TDE protects data at rest; and Unified Auditing records what actually happened. These aren’t add-ons; they’re the operating environment.

Identity and least privilege you can defend

Give each assistant a real database identity—per assistant or per capability—and default to read-only while you explore. With SQLcl MCP, avoid credential sprawl by saving connections and having the assistant connect by name, not by password (see "Preparing Your Environment" in SQLcl docs). The MCP server’s surface area is intentionally small, and restrict levels let you disallow risky paths such as arbitrary shell access. In SQLcl 25.4 docs, the default restrict level is most restrictive (level 4); check your installed version and set it explicitly if needed (see SQLcl MCP restrict levels).

Two habits make your audit trail readable. First, tag sessions. Use DBMS_APPLICATION_INFO.SET_MODULE/SET_ACTION after you connect; SQLcl MCP also sets identifiable MODULE/ACTION values that show up in V$SESSION and in audit rows (see SQLcl MCP "Using" and "Monitoring"). Second, name connections clearly so a human reviewer can reconstruct intent later. If a person must review production actions, include their initials in the connection name or in the module/action tag and correlate as you go. Note that querying V$SESSION requires appropriate privileges (for example, SELECT_CATALOG_ROLE or a direct grant on V_$SESSION).

On Autonomous Database (Serverless), the Managed MCP Server gives you a per-database endpoint governed by your database’s identities, roles, network policy, and auditing (see Autonomous Database MCP docs). Enablement requires appropriate OCI permissions and follows the procedure in the Autonomous Database "Use MCP Server" docs; in many tenancies this involves setting a free-form tag (key adb$feature, value JSON {"name":"mcp_server","enable":true}), but the exact key/value and UI can vary by region and service version. Choose a public posture or a Private Endpoint for isolation. Authentication is via OAuth or a short-lived bearer token; treat tokens like database passwords and confirm current token lifetimes in documentation.

The network path is part of your security posture

When Select AI reaches an external provider, outbound connectivity must be explicitly permitted. On self-managed databases, create database Network ACL entries for approved hosts. On Autonomous, use Private Endpoints and VCN rules to keep your blast radius small and your audit scope clean (see Autonomous Database ACL/VCN/Private Endpoint docs). The goal is simple: if data can’t leave the VCN except by policy, you can prove it didn’t.

Data minimization and enforceable scope

The fastest way to make NL2SQL safe enough to try in a mixed-sensitivity schema is to reduce the surface area. Select AI Profiles bind reasoning to specific objects: pin the provider, credential, and model; list the allowed objects; and set "enforce_object_list": true so generated SQL can’t wander to tables you didn’t name (see Select AI and DBMS_CLOUD_AI docs). Attribute names and boolean formats can vary by release; match the names and types shown in your target version’s docs. That simple move—paired with a least-privilege user—prevents most of the early "oops" moments.

Inspection comes next. SELECT AI SHOWSQL turns NL2SQL into a human-in-the-loop conversation: ask a question, inspect the candidate SQL, nudge it if needed, then run it intentionally (see Select AI examples). When you need narrative only—say you’re brainstorming a KPI definition—use DBMS_CLOUD_AI.DISABLE_DATA_ACCESS to block sending table data to the LLM. Data-dependent features (including certain RAG/narration/synthetic flows) will return an error until you re-enable data access; check your release docs for exact behavior (DBMS_CLOUD_AI package).

Row-level policy and masking push the same discipline deeper into runtime. VPD (via DBMS_RLS) attaches predicates that filter rows per user, context, or session attribute; it works with ordinary SQL and with generated SQL because enforcement happens in the engine. Data Redaction (DBMS_REDACT) masks sensitive columns at query time; even if a careless prompt tries to dump a column, the runtime policy wins. Remember that users with EXEMPT ACCESS POLICY can bypass VPD; design policies and auditing with that in mind (see VPD and Data Redaction docs).

Encryption and key posture you can explain in a review

At rest, Transparent Data Encryption (TDE) removes a class of risk from the conversation. It encrypts tablespaces or columns so that stolen media or mishandled backups don’t expose readable data. For most teams, the "receipt" here is straightforward: document that TDE is enabled for production databases, the keystore location and protection method, and the key-management process (including rotation cadence and audit hooks). This won’t solve every problem, but it makes risk discussions faster and clearer (see TDE overview docs).

Traceability you can hand to auditors

When the review board asks "what happened, exactly," you should be able to tell one story that matches from three angles and lines up in time. Session identity and tags provide the first angle: V$SESSION shows MODULE and ACTION, so you can corroborate user context and timing (requires appropriate privileges). Unified Auditing adds the second angle: a durable record of who touched what and when, based on policies you configure for sensitive tables or operations. On managed services where direct access to unified audit views may be restricted, the SQLcl MCP activity log (DBTOOLS$MCP_LOG) provides a third path: model details, endpoint type, and messages from the MCP side. The overlap is intentional. Trust is easier to defend when independent sources tell the same story (see Unified Auditing and SQLcl MCP Monitoring docs).

Mini demo: prove scope → govern → audit

In about fifteen minutes on a non-production database, you can gather three receipts: a concrete result set from a scoped query, an audit or MCP log entry that corroborates the action, and a V$SESSION tag that ties the session back to the assistant. The point isn’t to show everything Oracle can do; it’s to practice the muscle memory of route → act → govern → evidence.

Prerequisites

Oracle Database 19c+ (self-managed or Autonomous Database). For the Select AI step, use Oracle Database 26ai or an Autonomous Database with Select AI enabled.
SQLcl 25.2+ installed locally.
A read-only database user with SELECT on a demo schema (for example, HR). Ensure this user can see the demo objects via grants; otherwise ALL_TABLES won’t list HR objects.
For Select AI steps, ensure the calling user has privileges to execute DBMS_CLOUD_AI and to create/use AI Profiles (see DBMS_CLOUD_AI/Select AI docs for role/grant details).
Ensure the provider credential referenced by your profile (for example, GENAI_CRED) already exists and is configured for your chosen provider. Follow the credential-creation steps in the Select AI/DBMS_CLOUD_AI docs for your environment.
Optional: Unified Auditing enabled and privilege to query UNIFIED_AUDIT_TRAIL. On managed services, this may be restricted; use DBTOOLS$MCP_LOG instead.
Optional (for V$SESSION): privileges to read V$SESSION (for example, SELECT_CATALOG_ROLE or a direct grant on V_$SESSION).

1) Save a least-privilege connection in SQLcl
Avoid putting raw credentials into prompts or MCP envelopes.

sql /nolog
conn -save ro_demo -savepwd ro_user/ro_password@//host:1521/service

Security note: -savepwd stores credentials for reuse. Use this only on a trusted workstation and follow your organization’s secrets policies.

2) Start SQLcl MCP with a conservative posture
Recent docs (SQLcl 25.4) describe the default restrict level as most restrictive (level 4). Verify on your version and set it explicitly if needed.

# default
sql -mcp

# example of explicitly setting a restrict level (check your version/flags)
# sql -mcp --restrict-level 4

3) Option A — Prove scope via MCP tools
Connect with the saved connection, tag the session for later correlation, then query. Tagging must occur in an active session.

{"tool":"list-connections","arguments":{}}
{"tool":"connect","arguments":{"connection_name":"ro_demo"}}
{"tool":"run-sql","arguments":{"sql":"BEGIN DBMS_APPLICATION_INFO.SET_MODULE('assistant','schema-read'); END;"}}
{"tool":"run-sql","arguments":{"sql":"SELECT owner, table_name FROM all_tables WHERE owner='HR' ORDER BY table_name FETCH FIRST 5 ROWS ONLY"}}

3 alt) Option B — Show generated SQL, but only for allowed objects (Select AI)
Note: This step assumes you are on Oracle Database 26ai or an Autonomous Database with Select AI enabled, that your user has privileges to execute DBMS_CLOUD_AI and create/use profiles, and that a provider credential named GENAI_CRED already exists and is bound to your intended provider (see docs in Further reading). If not, create or reference an appropriate credential first.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'HR_RO',
    attributes   => '{
      "provider": "oci",
      "credential_name": "GENAI_CRED",
      "object_list": [ { "owner": "HR", "name": "EMPLOYEES" } ],
      "enforce_object_list": true
    }');
END;
/
EXEC DBMS_CLOUD_AI.SET_PROFILE('HR_RO');
-- Tag the session for traceability (run after you are connected)
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('assistant','hr-ro-showsql');
END;
/
-- Inspect the candidate SQL without running it
SELECT AI SHOWSQL 'list the 3 most recent employees hired';
-- Optional: disable data access to limit to reasoning-only features
EXEC DBMS_CLOUD_AI.DISABLE_DATA_ACCESS;

If the generated SQL references objects outside your profile’s object_list with "enforce_object_list": true, the call will be rejected. Attribute names and boolean formats can vary by release; match what your version’s docs show. If you disable data access, features that require data will return an error until you re-enable it.

4) Show evidence
If you have Unified Auditing access:

SELECT event_timestamp, dbusername, action_name, object_schema, object_name
FROM   unified_audit_trail
ORDER  BY event_timestamp DESC
FETCH FIRST 10 ROWS ONLY;

If not, use the SQLcl MCP activity log:

-- Object presence, schema, and columns vary by version and setup; consult docs.
SELECT *
FROM   DBTOOLS$MCP_LOG
FETCH FIRST 10 ROWS ONLY;

Note: Access to DBTOOLS$MCP_LOG may be restricted by schema/role in your environment. If the object is not visible, consult the SQLcl MCP Monitoring docs for the expected owner, grants, and pruning guidance.

Optionally, confirm your tag:

SELECT module, action
FROM   v$session
WHERE  module = 'assistant'
FETCH FIRST 5 ROWS ONLY;

You should leave with three receipts that line up in time: result rows from the HR query demonstrating scoped access; either audit rows or MCP log rows corroborating who did what and when; and a V$SESSION row with MODULE='assistant' and a matching ACTION. If you used Select AI, keep the SHOWSQL text that proves enforced object scope.

Small policy add-ons (optional; non-prod only)
To see runtime policy in action, attach a simple VPD policy (this requires creating a policy function first; test the predicate separately):

-- Example: attach policy after creating HR_SEC_PKG.F_REGION_PREDICATE(schema_name, object_name) RETURN VARCHAR2
-- Ensure the calling user is not granted EXEMPT ACCESS POLICY, which bypasses VPD.
BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'HR',
    object_name     => 'EMPLOYEES',
    policy_name     => 'HR_REGION_FILTER',
    policy_function => 'HR_SEC_PKG.F_REGION_PREDICATE',
    statement_types => 'SELECT',
    policy_type     => DBMS_RLS.CONTEXT_SENSITIVE);
END;
/

Or add Data Redaction to a sensitive column to see masking in real time (minimal working example using FULL redaction; choose function types and parameters to match application behavior):

-- Minimal, non-production demo using a common sample schema/column.
-- Requires privileges to execute DBMS_REDACT and to add a policy on the target table.
BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    column_name   => 'EMAIL',
    policy_name   => 'HR_EMAIL_REDACT_ALWAYS',
    function_type => DBMS_REDACT.FULL,
    expression    => '1=1'  -- apply to all sessions for demo
  );
END;
/

Caution: Substitute an appropriate table/column if HR is not available. Test application behavior—FULL redaction replaces values and can affect app logic. Users granted EXEMPT REDACTION POLICY can bypass redaction.

What can go wrong—and how to fix it

"I can’t see UNIFIED_AUDIT_TRAIL on Autonomous." That’s expected in many managed postures. Use DBTOOLS$MCP_LOG for your demo and work with your DBA on a unified audit policy and an access pathway that fits your tenancy. When you move to production, confirm where audit data is stored and how your team retrieves it (see Unified Auditing docs).

"Restrict levels don’t match the blog example." SQLcl evolves. In SQLcl 25.4 docs, the default is most restrictive (level 4). Check the defaults on your installed version and configure them explicitly so you’re not relying on memory (see SQLcl restrict levels).

"My VPD policy didn’t fire." Users with EXEMPT ACCESS POLICY can bypass VPD. Confirm that your policy function compiles, that any application context is set, and that you attached the policy to the base table(s) the query actually hits rather than to a view the optimizer can bypass (see VPD docs).

"Select AI tried to reach a provider and failed." On self-managed estates, add outbound ACLs for the hostnames you intend to call; on Autonomous, verify Private Endpoint setup and security list rules (see Autonomous ACL/VCN docs; Select AI external-call examples).

"We masked the column but the audit shows object names only." That’s normal—audits capture action metadata, not full result sets. If you need extra forensic detail for sensitive operations, add Fine-Grained Auditing (FGA) for targeted captures or instrument at the application layer (see Unified Auditing and FGA docs).

Make it a runbook, not a one-off

Treat "route → act → trust" as change control. Give assistants dedicated least-privilege users or roles and require SQLcl MCP to use saved connections with a verified restrict level at startup. Validate the network path for any assistant that can call outside the database by explicit policy—ACL entries, VCN rules, or a Private Endpoint. Narrow Select AI with profiles and enforce_object_list, start with SHOWSQL, and switch off data access when you only need narrative reasoning. Confirm your TDE posture in production and capture key-management audit hooks. And set DBMS_APPLICATION_INFO early so your team can tie sessions to intent while a unified audit policy or DBTOOLS$MCP_LOG preserves the durable record.

Conclusion: bring receipts to every AI/database conversation

Enterprise AI earns trust one verified action at a time. Oracle’s advantage is not that it hides your database behind a magic assistant; it’s that the database you already trust can stay in charge while the assistant works. Route using a repository "skills" convention (for example, a db/SKILL.md index you control) so the model asks better, context-specific questions. Act only through SQLcl MCP or the Managed MCP Server so you know which verbs were allowed. Enforce scope and policy with Select AI profiles, VPD, and Redaction. Prove what happened with Unified Auditing and DBTOOLS$MCP_LOG.

Run the mini demo in a sandbox and stash three receipts—result rows, an audit or MCP log row, and a V$SESSION tag—into your team wiki. From there, expand the same pattern to the workflows that matter. When someone asks, "How do you know the assistant didn’t touch X?" you’ll be able to show the answer on one page.

Agent‑safe change delivery on Oracle: discovery, online mechanics, idempotent migrations, and provable rollbacks

Mark Nelson — Thu, 28 May 2026 15:04:13 +0000

This is article 7 of 8 in my Oracle AI Database Skills series.

Key Takeaways

The safety net is the process, not the model. Discovery before proposal, preview before apply, session-scoped verification before broad exposure, and a prepared rollback path — these steps protect against risk regardless of how confident the assistant sounds.
Invisible indexes let you test the impact of a change in one session before committing it to all queries. A change that can be reversed with a single statement is the safest kind to ship.
Liquibase updateSQL and Flyway dry-run output play the same role as SHOWSQL for migrations: they show exactly what SQL would execute before anything changes, giving a human an explicit approval moment.
DBMS_REDEFINITION and Edition-Based Redefinition reduce blast radius for structural and code changes by keeping the previous version available as a rollback window through the entire cutover period.

Change is where ambiguity turns into outages. If you let an assistant propose schema or index changes on Oracle Database, slow the work down just enough to make it safe. The pattern across this series is route → act → trust: route proposals through Oracle Database Skills so the assistant performs discovery and establishes intent; act only through a bounded, inspectable MCP surface (SQLcl MCP, where MCP is Model Context Protocol—locally, or a managed MCP in Autonomous AI Database when available); and deliver changes with Oracle‑native online and editioning mechanics, inside a migration workflow that’s idempotent on rerun and leaves a trail you can prove later.

This piece shows that posture in motion. We’ll take one additive change an assistant proposes, preview it, apply it safely, verify it helped, and capture the evidence—with a rollback ready if it didn’t.

Version scope and expectations

Most examples target Oracle Database 19c and later. We rely on invisible indexes and session testing via the OPTIMIZER_USE_INVISIBLE_INDEXES parameter (settable at the session or system level) as documented in the 19c Database Reference. For index maintenance, we use online index rebuilds where the 19c SQL Language Reference documents them for your index type and release (ALTER INDEX ... REBUILD ONLINE). For structural table changes under DML load, we point to DBMS_REDEFINITION (19c PL/SQL Reference). For view/package evolution, we use Edition‑Based Redefinition (EBR). Migration previews use Liquibase or Flyway state tables and dry‑run modes. Evidence and rollback rely on session tagging, SQLcl MCP logging, and (optionally) Unified Auditing.

If you’re on Oracle Database 26ai or Autonomous AI Database, you can also evolve vector indexes with a build‑new → validate → drop‑old pattern and measure recall/latency with DBMS_VECTOR. Always confirm exact syntax and capabilities for your Release Update (RU) and service tier.

ALTER INDEX (ONLINE for REBUILD): 19c SQL Language Reference
OPTIMIZER_USE_INVISIBLE_INDEXES: 19c Database Reference
Managing invisible indexes: 19c Admin Guide
DBMS_REDEFINITION: 19c PL/SQL Reference
CREATE VECTOR INDEX, VECTOR_DISTANCE, DBMS_VECTOR: 26ai docs
SQLcl MCP restrict levels, tools, monitoring: 25.x SQLcl docs
Liquibase and Flyway: vendor docs

Why agent‑safe change matters most

Assistants are good at pattern matching; they are bad at implicit context. Database change has a lot of implicit context: constraints you can’t see from one table, write paths you forgot exist, and optimizer behavior that swings when you add the “perfect” index. The way to make an assistant useful here is to force it to surface what it thinks it knows before it acts. That’s what Oracle Database Skills are for: schema discovery, intent disambiguation, destructive‑operation guards, and idempotency patterns live right next to delivery mechanics—migrations, online operations, EBR, and testing—so the work becomes a sequence you can reason about, not a free‑form prompt.

The assistant can still draft the change. Your job becomes review and control: you see the SQL (Article 5’s SHOWSQL discipline), you preview the migration (dry run), you apply through a constrained channel (MCP), and you verify impact before broad exposure (invisible index testing, or EBR for code). When that loop is the norm, teams stop arguing about whether to “let the model write DDL” and start shipping low‑risk improvements steadily.

Safety before execution: discovery, intent, and rerunnable SQL

Change work starts with a map. If the assistant suggests an index for DOCUMENTS, its first step isn’t to create it; it is to list what already exists and establish the blast radius.

Inventory what’s there (use USER_* views in‑schema; ALL_*/DBA_* if querying across schemas):

SELECT index_name, visibility, status
FROM   user_indexes
WHERE  table_name = 'DOCUMENTS';

Also list indexed columns so you don’t reinvent an existing index:

SELECT index_name, column_name, column_position
FROM   user_ind_columns
WHERE  table_name = 'DOCUMENTS'
ORDER  BY index_name, column_position;

If the assistant claims “add an index to speed source and published date filters,” make it specify the target predicates and expected plan effect. If it can’t state the WHERE clause and the rows accessed in plain terms, it doesn’t understand the workload yet. Article_06 showed why this matters: you don’t expose a new index broadly until you’ve seen it influence a plan the way you expect.

For DML, destructive‑operation guards are non‑negotiable. Updates and deletes happen only with a WHERE clause you can count. Preview the scope:

SELECT COUNT(*)
FROM   documents
WHERE  source = 'kb'
AND    published >= DATE '2025-01-01';

Oracle doesn’t enforce WHERE clauses on UPDATE/DELETE; enforce guardrails in tooling (preview gates) and code review.

Reruns should be safe by default. On the data side, MERGE turns brittle “insert then update” sequences into idempotent steps:

MERGE INTO config t
USING (SELECT 'max_connections' AS key, '100' AS value FROM dual) s
ON (t.key = s.key)
WHEN MATCHED THEN UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN INSERT (key, value) VALUES (s.key, s.value);

On the DDL side, migration tools carry the idempotency for you with state tables and checksums. Liquibase tracks applied changeSets in DATABASECHANGELOG and coordinates with DATABASECHANGELOGLOCK, and Flyway uses flyway_schema_history. Crucially, both can emit exactly what they would execute. Liquibase’s updateSQL and Flyway’s dryRunOutput are the human gate that pairs well with Article 5’s SHOWSQL: you inspect the proposed SQL first, then approve the apply.

Preview‑gate example (Liquibase):

# Print the SQL that would run, without changing the database
liquibase --changelog-file=changelog.xml updateSQL

# Example output (truncated)
-- Changeset 20250108-idx-docs:1
CREATE INDEX docs_src_pub_idx ON documents(source, published) INVISIBLE;
-- Changeset 20250108-config-merge:2
MERGE INTO config ...

Liquibase prints the SQL it would execute against the current database state, based on DATABASECHANGELOG and checksums.

Alternatively, with Flyway (Teams/Enterprise for dry‑run variants):

# Generate a dry-run script of pending migrations
flyway -locations=filesystem:sql -dryRunOutput=preview.sql migrate

dryRunOutput is not available in Community; use Teams/Enterprise for dry‑run script generation.

From preview to apply: bounded MCP, not ad‑hoc credentials

Once you’ve reviewed a change, you still need a controlled way to act. That’s the MCP layer: the SQLcl MCP Server locally, or a managed MCP experience in Autonomous AI Database if your tier and RU expose one.

SQLcl MCP exposes a deliberately small tool surface—connect, disconnect, list saved connections, run SQL—over a stdio server. In the 25.3 docs, Level 4 is the most restrictive. Check your installed version’s “Restrict levels” page for the default, and keep the highest level practical in shared environments. The recommended posture is to use pre‑saved least‑privilege connections; restrict levels can be configured to disallow ad‑hoc credentials and keep the tool surface small. The server tags sessions (V$SESSION.MODULE and .ACTION) and writes activity to DBTOOLS$MCP_LOG once used, so DBAs can see who did what and when. This is what “bounded action” looks like: the assistant can only do the few things the server allows, with least privilege and a record behind it. See “Using the SQLcl MCP Server,” “Restrict levels,” and “Monitoring” in the SQLcl docs for the precise tools, defaults, and logs for your version.

If your Autonomous AI Database service and RU expose a managed MCP endpoint (see your service’s Help Center “Use MCP Server” page), you can integrate assistant actions with database identity, network controls, and auditing. Capabilities and APIs vary by service tier and RU; verify availability and procedures in your tenancy documentation. If a managed MCP endpoint isn’t available for your tier/RU, use the SQLcl MCP Server approach above.

Reduce blast radius with Oracle’s online mechanics

Not every change needs a window. Oracle has first‑party features for evolving structures while the system stays available.

Structural changes under write load use DBMS_REDEFINITION. The flow is predictable—eligibility check, start, copy dependent objects and constraints, sync the interim table, finish, and clean up—and the documentation spells out object and LOB rules per release (19c PL/SQL Reference).

A compact, typical plan:

Eligibility (choose key strategy; use CONS_USE_PK when a primary key exists, or CONS_USE_ROWID otherwise—confirm constraints and object types supported in your version):

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
    uname        => 'APP_OWNER',
    tname        => 'ORDERS',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
END;
/

Create interim table with desired structure (required unless supplying a column mapping to START_REDEF_TABLE):

-- Example: like-for-like copy you will later adjust as needed
CREATE TABLE app_owner.orders_int
AS SELECT * FROM app_owner.orders WHERE 1=0;

Start:

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname     => 'APP_OWNER',
    tname     => 'ORDERS',
    int_table => 'ORDERS_INT'
    -- , col_mapping => '...'  -- use when structures differ
  );
END;
/

Copy dependents (constraints, triggers, indexes, grants) and track errors:

DECLARE
  v_errs PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname            => 'APP_OWNER',
    tname            => 'ORDERS',
    int_table        => 'ORDERS_INT',
    num_errors       => v_errs,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_indexes     => TRUE,
    copy_statistics  => TRUE
  );
END;
/

Sync:

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('APP_OWNER', 'ORDERS');
END;
/

Finish and swap:

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('APP_OWNER', 'ORDERS');
END;
/

Cleanup: reconcile stats/grants, then drop interim artifacts as appropriate.

LOBs, materialized view logs, row movement, and editioning interactions have version‑specific rules—confirm them in your release documentation before use.

When your change is in editioned code rather than the table itself, Edition‑Based Redefinition lets you run two versions at once and cut over on your terms. Tables are not editioned, so the safe pattern is to shield them behind editioning views and synonyms, evolve the views or packages in a child edition, test in that edition, then switch references.

Prerequisites (typically applied by a DBA):

System privileges: CREATE ANY EDITION (to create editions), DROP ANY EDITION (to retire), and the ability to ALTER USER ... ENABLE EDITIONS.
Object privileges in the target schema to create editioning views and editioned synonyms.

Build‑plan: EBR cutover (compact)

Enable editions for your user:

ALTER USER app_owner ENABLE EDITIONS;

In ORA$BASE (or your current default edition), create an editioning view to shield the underlying table. The view name is editioned; the table is not:

CREATE EDITIONING VIEW ev_orders AS
SELECT order_id, customer_id, order_ts, status
FROM   orders;

Create a child edition:

CREATE EDITION app_v2 AS CHILD OF ORA$BASE;

Test in the child edition and evolve the API surface:

ALTER SESSION SET EDITION = app_v2;

-- Evolve API in the child edition (example: add a computed projection)
CREATE OR REPLACE EDITIONING VIEW ev_orders AS
SELECT order_id,
       customer_id,
       order_ts,
       status,
       CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END AS is_cancelled
FROM   orders;

Cutover: change which edition clients use (via connection‑level edition, services, or app routing). Keep ORA$BASE as a rollback window.
Rollback: switch sessions back to the previous edition while you investigate; retire the old edition once stable.

Indexes get two useful mechanics. First, invisible indexes let you test plan influence without affecting other sessions. You can create a new index INVISIBLE or set an existing index invisible, then in your session enable it:

ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

If plans improve (verified via EXPLAIN PLAN and DBMS_XPLAN, and ideally timed execution), you can make the index visible for everyone. Second, ALTER INDEX ... REBUILD ONLINE is documented in 19c for supported index types, letting DML continue during maintenance with some restrictions. Note the careful wording: in 19c the ONLINE clause is documented for REBUILD; do not assume CREATE INDEX ONLINE for B‑tree indexes unless your exact release and index type say so.

The same mindset for vector and relational indexes

Oracle Database 26ai adds native vector indexes—HNSW (Hierarchical Navigable Small World) and IVF (Inverted File)—and vector distance operators. The safe evolution pattern is the same as for relational indexes: build a tuned index in parallel, validate it with representative queries, then drop the old one after a soak period.

-- Check your exact RU/service tier for required/optional clauses and defaults.
-- HNSW example (common defaults shown; tune per workload)
CREATE VECTOR INDEX docs_hnsw_new
ON documents(embedding)
ORGANIZATION HNSW
DISTANCE COSINE;

-- IVF example (parameter defaults vary by RU)
CREATE VECTOR INDEX docs_ivf_new
ON documents(embedding)
ORGANIZATION IVF
DISTANCE COSINE;

If your RU requires additional parameters (e.g., neighborhood or partition knobs), supply them as documented; prefer a build‑new → validate → drop‑old evolution unless your docs explicitly state online/rebuild semantics. Validate using workload queries or DBMS_VECTOR advisers/accuracy checks, watching both recall and latency.

Proving what happened—and preparing to undo it

If an assistant participates in change, you need clean answers to who did what and how to reverse it. Start by tagging sessions at the application level:

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('mcp-agent','index-additive-change');
END;
/

DBAs can then look in V$SESSION for that module/action pair, and SQLcl MCP adds its own evidence in DBTOOLS$MCP_LOG with each tool‑mediated request. With Unified Auditing enabled, you can go further: define policies for specific DDL or DML and query UNIFIED_AUDIT_TRAIL to show who executed which statement and when. Access to dynamic performance views or audit trails depends on privileges; if your role is narrower, MCP logs still give you a reliable record. If you lack access to V$SESSION, ask a DBA to expose a filtered view you can query.

Rollback should be proportional to the change. For an index, the fastest backout is to mark it invisible; the hard backout is to drop it. For redefinition or EBR, the rollback is to keep the original structure or edition until final cutover and switch back if something misbehaves. For migrations, Liquibase can roll back by tag/changeSet/date when you’ve authored rollbacks (for supported change types); Flyway supports undo with authored scripts (Teams/Enterprise editions). The operative word is “authored”: write the backout path when you write the forward path.

A quick Unified Auditing check (if enabled and you have privileges):

SELECT event_timestamp, dbusername, action_name, object_schema, object_name, sql_text
FROM   unified_audit_trail
WHERE  action_name IN ('CREATE INDEX','ALTER INDEX','DROP INDEX')
AND    object_schema = 'APP_OWNER'
ORDER  BY event_timestamp DESC
FETCH FIRST 10 ROWS ONLY;

Mini‑demo: propose, review, apply, verify, prove

Let’s run one small, additive change that an assistant proposes. We’ll add a composite index to speed a common filter on DOCUMENTS. The mechanics are deliberately conservative: INVISIBLE first, session‑level test, then visible or back out.

Prerequisites for this demo:

Oracle Database 19c or later.
Privileges: CREATE INDEX on DOCUMENTS owner; ALTER SESSION; EXPLAIN PLAN; EXECUTE on DBMS_XPLAN and DBMS_APPLICATION_INFO.
Access to V$SESSION requires SELECT_CATALOG_ROLE or equivalent; if not available, rely on DBTOOLS$MCP_LOG for evidence.
SQLcl MCP Server running with a pre‑saved least‑privilege connection to the target schema.

1) Preflight discovery. If an assistant suggests an index, ask it to verify there isn’t already one that covers the same columns:

SELECT index_name, visibility, status
FROM   user_indexes
WHERE  table_name = 'DOCUMENTS';

SELECT index_name, column_name, column_position
FROM   user_ind_columns
WHERE  table_name = 'DOCUMENTS'
ORDER  BY index_name, column_position;

(Outside the owner schema, use ALL_INDEXES/ALL_IND_COLUMNS.)

2) Proposal. The assistant drafts an index for source and published. Keep it INVISIBLE so only a session that opts in can use it:

CREATE INDEX docs_src_pub_idx
ON documents(source, published)
INVISIBLE;

3) Apply through a bounded surface. Approve the statement and run it via SQLcl MCP’s SQL tool using a least‑privilege, pre‑saved connection. Configure restrict levels to keep the tool surface small and auditable; MCP logs the call and tags the session automatically in V$SESSION (subject to your privileges).

4) Test before broad exposure. Enable invisible indexes only in your session and check plan shape and timing:

ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM   documents
WHERE  source='kb'
AND    published >= DATE '2025-01-01';

SELECT plan_table_output
FROM   TABLE(DBMS_XPLAN.DISPLAY());

For final validation, prefer executed plans via DBMS_XPLAN.DISPLAY_CURSOR with ALLSTATS LAST after running the query, as EXPLAIN PLAN can differ from runtime under binds or adaptive features. If PLAN_TABLE isn’t present in your schema, either create it (DBA task) or use DBMS_XPLAN.DISPLAY_CURSOR, which doesn’t rely on PLAN_TABLE.

Also capture actual timings on representative data:

-- Show the last executed statement's plan with runtime stats (if available)
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

If the plan and timings improve, make the index visible:

ALTER INDEX docs_src_pub_idx VISIBLE;

5) Evidence. Tag the module/action explicitly and pull recent MCP entries:

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('mcp-agent','index-additive-change');
END;
/

-- Evidence in V$SESSION (requires privileges)
SELECT module, action
FROM   v$session
WHERE  module = 'mcp-agent';

-- Evidence in MCP logs (column names can vary by version)
-- DESCRIBE DBTOOLS$MCP_LOG;
SELECT *
FROM   DBTOOLS$MCP_LOG
FETCH FIRST 5 ROWS ONLY;

-- If you need ordering, first identify a timestamp or sequence column via DESCRIBE/USER_TAB_COLUMNS,
-- then apply ORDER BY accordingly.

If logs appear empty, run a simple MCP run-sql request and retry—the log table populates after the first tool interactions. Querying V$SESSION requires SELECT_CATALOG_ROLE or equivalent; if you don’t have it, rely on MCP logs and application‑level evidence or ask a DBA to expose a filtered view.

6) Rollback if needed. If the index hurts a minority of queries or adds unacceptable write overhead, back it out quickly:

ALTER INDEX docs_src_pub_idx INVISIBLE; -- tactical backout
-- or
DROP INDEX docs_src_pub_idx;            -- hard revert

That’s the loop: propose through discovery, preview the SQL, act through MCP, verify locally via INVISIBLE, and record the proof.

Delivery mechanics that hold up in real pipelines

The same loop translates cleanly to CI/CD and change boards. Migration tools handle state and retries; MCP provides execution discipline; Oracle features reduce blast radius.

In practice, you keep DDL/DML changes in version control and insist on previews. Liquibase’s updateSQL prints exactly what would run for a given changelog; Flyway’s dryRunOutput does the same for pending migrations. Checksums and state tables prevent accidental reruns. Avoid hand‑run DDL outside the tool, which can desynchronize history tables from reality. For table changes that must happen while writes continue, DBMS_REDEFINITION is the predictable route; for code and view evolution, EBR gives you reversible cutovers. For online index maintenance, REBUILD ONLINE helps keep writes flowing, while INVISIBLE indexing keeps tests local until you’re ready.

Testing belongs inside this posture, not as an afterthought. For PL/SQL and schema‑anchored behavior, utPLSQL lets you write fast, repeatable tests and run them in CI. When you’re changing query paths, pair DBMS_XPLAN plan displays with simple timing harnesses and capture them in artifacts the same way you capture dry‑run SQL. This is how agents and humans occupy the same workflow: the agent proposes, the pipeline proves.

A short view for migration leads

If you’re modernizing a system or switching database platforms, treat “agent‑safe change” as the daily rhythm and migrations as a route through it. The sequence has four stages:

1 — Assess. Inventory every object by type and complexity before writing a single migration script. Oracle’s skills repo includes a migration-assessment.md skill that routes this step. In practice, complexity falls into three tiers: simple objects (tables with standard types, views, sequences) that translate with minimal manual effort; moderate objects (stored procedures, triggers, basic PL/SQL packages) that need construct-by-construct review; and complex objects (advanced types, Java stored procedures, database links, Oracle-specific partitioning strategies, or heavy use of DBMS_* packages) that require hands-on expert review. An assistant can draft the inventory query and score the tiers—your job is to sanity-check the classification before committing to a timeline.

A useful starting inventory:

SELECT object_type, COUNT(*) AS cnt
FROM   all_objects
WHERE  owner = UPPER(‘<SOURCE_SCHEMA>’)
GROUP  BY object_type
ORDER  BY cnt DESC;

2 — Translate with source-specific guidance. Each source platform has predictable problem areas. For PostgreSQL migrations, the most common friction points documented in migrate-postgres-to-oracle.md are: sequence syntax (SERIAL → GENERATED AS IDENTITY), BOOLEAN columns (Oracle has no native BOOLEAN in SQL prior to 23ai; the standard convention is NUMBER(1,0) with a CHECK (col IN (0,1)) constraint), RETURNING clauses (Oracle restricts RETURNING INTO to PL/SQL blocks, requiring application refactoring), ILIKE (rewrite as UPPER(column) LIKE UPPER(pattern) with a supporting function-based index), and DATE semantics (Oracle's DATE stores time-of-day, so equality filters like WHERE created = DATE '2024-01-15' silently miss rows that have a non-midnight time component). An assistant loaded with migrate-postgres-to-oracle.md from the skills repo can flag these automatically when reviewing source DDL—but always verify the translation with a human before running it in staging.

3 — Pilot additive changes in staging. Before cutover, run a representative subset of translated objects through the full discovery → preview → bounded apply → verify loop described above. This is where invisible indexes, preview-gate SQL, and DBMS_REDEFINITION earn their keep. Focus on the moderate and complex tiers first; the simple tier can usually be scripted and batch-applied. Confirm that application queries return the same results on Oracle as they did on the source—row counts, join shapes, and date arithmetic are the usual suspects for silent semantic differences.

4 — Cutover and verify. Keep ORA$BASE or a pre-cutover schema snapshot available as a rollback window. Use Liquibase or Flyway changelogs so the migration state is recorded and reruns are safe. After cutover, run your standard smoke tests plus any Oracle-specific validation queries you assembled during assessment. Tag the migration session with DBMS_APPLICATION_INFO so post-cutover audit queries can isolate migration activity from normal application traffic.

By the time you’re scheduling the production cutover, the mechanics should be boring. The assistant can draft scripts and surface incompatibilities; your tooling and Oracle’s online features make the process predictable and reversible.

What to remember—and what’s next

Agent‑safe change is not a bet on model intelligence; it’s a bet on process. Force discovery before action. Require a previewed SQL artifact. Act only through a bounded server with least privilege. Use Oracle’s online and editioning features to keep availability high. Tag sessions, log activity, and keep a rollback path that matches the change. When you operate this way, you can accept more assistant help without accepting more risk.

In Article 8 we’ll close the loop with governance: identity and least privilege, policy, VPD, data redaction, and Unified Auditing as a procedural trust layer around the same route → act → trust loop.

Sources and further reading

SQLcl MCP Server: tools, restrict levels, monitoring and logs
https://docs.oracle.com/en/database/oracle/sql-developer-command-line/25.2/sqcug/using-oracle-sqlcl-mcp-server.html
https://docs.oracle.com/en/database/oracle/sql-developer-command-line/25.3/sqcug/sqlcl-mcp-server-tools.html
https://docs.oracle.com/en/database/oracle/sql-developer-command-line/25.3/sqcug/configuring-restrict-levels-sqlcl-mcp-server.html
https://docs.oracle.com/en/database/oracle/sql-developer-command-line/25.4/sqcug/monitoring-sqlcl-mcp-server.html
Online operations and index behavior (19c)
ALTER INDEX (ONLINE for REBUILD): https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-INDEX.html
OPTIMIZER_USE_INVISIBLE_INDEXES: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_USE_INVISIBLE_INDEXES.html
Managing invisible indexes: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html
DBMS_REDEFINITION (19c)
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_REDEFINITION.html
EBR (editions and editioning views)
Start with the SQL Language Reference (CREATE EDITION; ALTER SESSION SET EDITION) and the Edition‑Based Redefinition Guide for your release (19c/23ai/26ai).
Vector indexing and functions (26ai)
CREATE VECTOR INDEX: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/create-vector-index.html
VECTOR_DISTANCE: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/vector_distance.html
DBMS_VECTOR: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector1.html
Migrations tooling
Liquibase: https://www.liquibase.org/
Flyway: https://documentation.red-gate.com/fd
Evidence and auditing
DBMS_APPLICATION_INFO: PL/SQL Packages and Types Reference (match your version)
Unified Auditing and UNIFIED_AUDIT_TRAIL: Oracle Database Security Guide (match your version)
Series continuity
Article 5 (Select AI, AI Profiles, SHOWSQL) and Article 6 (index and vector evaluation) provide the inspection and evaluation habits used here.

Notes on differences across releases and editions

Index ONLINE semantics: In Oracle 19c, ONLINE is documented for ALTER INDEX ... REBUILD for supported index types with restrictions; check your exact RU and index type. Do not assume CREATE INDEX ONLINE for B‑tree indexes in 19c.
Invisible indexes: Controlled by OPTIMIZER_USE_INVISIBLE_INDEXES at the session/system level. Validate on your target RU.
Vector indexes: 26ai introduces HNSW and IVF; confirm syntax and capabilities for your RU/tier. Prefer build‑new → validate → drop‑old unless your docs state otherwise.
SQLcl MCP: Tools and restrict‑level defaults are versioned. Verify the tool list and defaults for your installed SQLcl version.

How to Enforce Role-Based Data Access in AI Applications with Oracle Deep Data Security

Mark Nelson — Thu, 28 May 2026 13:54:19 +0000

Key Takeaways

AI assistants should not rely on prompts or application code to enforce sensitive data access rules.
Oracle Deep Data Security keeps row, column, and document authorization in the database before data reaches the LLM.
DEAL uses the same SQL and vector retrieval for Linda and Wendy, while Oracle Database returns different authorized results.
The companion GitHub repository contains the runnable Python demo, including ADB-S setup scripts and an optional OCI GenAI chatbot loop.

AI-powered applications often use two kinds of retrieval at the same time: SQL for structured business records and vector search for contextual documents. A loan assistant might use SQL to list applications under review, then use vector search to retrieve policy guidance about income verification, debt-to-income exceptions, or credit risk. Both paths can reach sensitive data.

That retrieval power creates a security problem. If an assistant can call broad SQL tools or broad RAG retrievers, it might fetch data the current user should not see. Prompt injection, missing metadata filters, a malformed tool call, or a forgotten Python redaction step can turn a useful chatbot into a data exposure path.

The safest place to enforce access rules is the database, where the protected data already lives. Oracle Database security controls have been trusted for decades to enforce application access policies close to the rows and columns being protected. An LLM should not become the system of record for authorization decisions. It is probabilistic, prompt-influenced, and easy to steer into requesting more data than the user should receive.

That distinction matters because data exfiltration is expensive. A single overbroad answer can expose regulated customer data, trigger incident response and disclosure work, create audit findings, violate compliance requirements, and erode trust in the AI application. Database security policy is also more provable and auditable than LLM behavior: you can inspect grants, test queries under known identities, review database audit records, and show that enforcement happens before data leaves the database. The goal is not to make the prompt better at saying no; the goal is to make sure the database only returns data the active end user is allowed to see.

In this tutorial, we build DEAL — Deep Data Security Enabled Assistant for Lending. DEAL is a terminal-based Python loan assistant that connects to Oracle AI Database 26ai on Autonomous Database Serverless with a wallet. The demo uses direct logon as local Deep Sec end users: linda, a loan officer, and wendy, an underwriter.

The proof point is simple: DEAL runs the same broad SQL and the same vector retrieval pattern for both users. Oracle Database enforces the configured data access policies for the demo's SQL and vector retrieval paths before data reaches Python, the tool layer, or the LLM. The Python code does not filter rows by user, redact restricted fields, or filter RAG results by audience.

What Is Oracle Deep Data Security?

Oracle Deep Data Security is a database-enforced data authorization framework in Oracle AI Database 26ai. It lets applications pass end-user security context to the database so that the database can enforce fine-grained access to protected data.

In DEAL, there are four important pieces:

Database owner user: the database account Python uses for setup scripts, such as DEEPSEC.
Application end user: the human using the app. DEAL uses linda and wendy.
Deep Sec end-user context: the database identity that is active while a user-scoped operation runs.
Data roles and data grants: the Deep Data Security objects that define which rows, columns, and operations are available.

The teaching pattern throughout the demo is:

same query shape
same SQL
different Deep Sec end user
different authorized result

The no-token path uses direct logon as local Deep Sec end users. Deep Sec also supports service-user context patterns where the application connects as a service user and sets end-user context before each database operation. Some service-user patterns use external identity tokens, and some use prepared local Deep Sec end-user identities.

Use this table to choose the right identity path:

Path	How the database knows the end user	External IAM or Entra token required?	When to use it
Local Deep Sec direct logon	Python connects directly as a local Deep Sec end user such as `linda` or `wendy`	No	Best for a compact tutorial, lab, or proof where each demo user can have a local Deep Sec password
Service user with local Deep Sec context	Python connects as the application/service schema, then sets context for a prepared local Deep Sec end user	No external IAM token, but requires local end-user setup and context-key handling	Useful when the app should keep a stable service connection while still avoiding an external identity provider for a demo
Service user with OCI IAM or Microsoft Entra	Python connects as the application/service schema, then sets context using a database access token issued by the identity provider	Yes	Best for production-style apps that already authenticate users through OCI IAM, Entra, or a federated identity flow

The rest of the tutorial uses the first path: local Deep Sec direct logon. That keeps the security proof focused on database-enforced row, column, and vector retrieval behavior instead of identity-provider setup.

Deep Data Security requires Oracle Database 26ai and is supported only in python-oracledb Thin mode. Do not call oracledb.init_oracle_client() in this tutorial.

DEAL architecture: the Python assistant connects as local Deep Sec end users, and Oracle Database enforces the configured data access policies for the demo’s SQL and vector retrieval paths.

Key Features of Oracle Deep Data Security

DEAL uses a small lending scenario to demonstrate the Deep Data Security capabilities that matter most to AI application developers.

Data roles and end-user identity

Deep Sec data roles group data access permissions. DEAL uses two data roles:

LOAN_OFFICER_ROLE
UNDERWRITER_ROLE

The prepared Deep Sec end users are:

linda, assigned to LOAN_OFFICER_ROLE
wendy, assigned to UNDERWRITER_ROLE

In the no-token path, Linda and Wendy are local Deep Sec end users who can log on directly after an administrator grants a standard CREATE SESSION role through the Deep Sec data roles. That keeps the tutorial free of external IAM or Entra token setup.

Row-level security

Deep Sec data grants can include row predicates. DEAL uses row rules like these:

Linda sees loan applications where assigned_officer matches the active Deep Sec username.
Wendy sees loan applications where in_underwriting_queue = 'Y'.

The application SQL does not add WHERE assigned_officer = :end_user.

Column-level security

Data grants can restrict column access. DEAL restricts sensitive financial and underwriting fields for Linda, including:

customer_ssn
customer_income
customer_credit_score
underwriting_decision
risk_score
underwriting_notes

In the demo output, restricted values are displayed as NULL. The Python code does not replace values with None or hide fields.

Deep Sec-scoped vector retrieval

Role-scoped vector retrieval matters because RAG systems often search across policy, procedure, and knowledge-base documents that are not equally appropriate for every user. A loan officer might need intake and customer-document guidance, while an underwriter might need credit-risk escalation procedures. If the retriever ranks every nearby document first and asks the LLM to ignore unauthorized context later, sensitive guidance has already left the database. DEAL makes the authorization check happen before vector ranking returns results to the application.

DEAL stores policy documents in Oracle Database with a VECTOR(3, FLOAT32) column. The core demo uses deterministic 3-dimensional demo vectors so the tutorial stays self-contained.

The vector retrieval query runs under Linda’s or Wendy’s Deep Sec context. The application does not add a security filter such as:

WHERE audience = :role

Instead, Deep Sec data grants govern which loan_policies rows are visible to the query; the vector query ranks the rows visible under the active end-user context.

We use VECTOR_DISTANCE() in the SQL because it keeps the distance expression and metric explicit. Oracle AI Vector Search also provides shorthand vector distance operators, but this tutorial intentionally stays with VECTOR_DISTANCE() so the Deep Sec proof is easier to read. The demo uses a small data set and does not create a vector index. A vector index is not useful for proving performance or plans on eight rows; add indexing later when you move to a larger corpus and can verify index metadata and execution behavior for your workload.

Application trust boundary

The assistant is not the security boundary. DEAL’s Python code is responsible for:

Connecting to Oracle AI Database 26ai.
Binding values safely.

The database is responsible for enforcing the configured row, column, and policy-document access rules.

The demo avoids these application-side security patterns:

WHERE assigned_officer = :end_user

WHERE audience IN ('general', :role)

if end_user == "linda":
    row["customer_ssn"] = None

Those patterns can work in one carefully reviewed code path, but they are fragile as the application grows. Every new query, retriever, tool function, report, or chatbot action becomes another place where a developer has to remember the right row predicate, audience filter, and redaction rule. If one path forgets a filter, the database may return too much data before the mistake is noticed. Keeping the rules in Deep Sec data grants makes enforcement centralized, testable, and consistent across the application paths that reach the protected tables.

How to Get Started with Oracle Deep Data Security

This tutorial assumes an administrator-prepared Oracle AI Database 26ai on Autonomous Database Serverless environment where Oracle Deep Data Security is enabled and the required privileges have been granted. The demo targets ADB-S 26ai; it does not use Oracle Database Free or a local Oracle container.

Prerequisites

You need:

Oracle AI Database 26ai on Autonomous Database Serverless.
A downloaded and unzipped Autonomous Database wallet.
A tutorial owner schema user, such as DEEPSEC.
Database password for the tutorial owner schema.
A TNS alias from the wallet, or a connect descriptor using host, port, and service name values from your Oracle AI Database 26ai environment.
Wallet password.
Prepared Deep Sec end users for linda and wendy.
Passwords for the local Deep Sec end users linda and wendy.
Privileges to create Deep Sec data roles and data grants for the tutorial objects, or an administrator who has prepared those objects.
Optional: OCI Generative AI access for the chatbot extension.
Python 3.10 or later.

Prepared Deep Sec identity contract

Before you run the demo, an administrator must create or provide the Deep Sec identity setup. This tutorial uses local Deep Sec users and direct logon, so the setup includes end users such as:

CREATE END USER linda IDENTIFIED BY <password>;
CREATE END USER wendy IDENTIFIED BY <password>;

Those end users are not ordinary database schema users. Because the SQL does not quote linda or wendy, Oracle stores the end-user names using the normal uppercase identifier form: LINDA and WENDY. The setup scripts connect as the tutorial owner schema, such as DEEPSEC, while the user-facing read and RAG scripts connect directly as LINDA or WENDY.

The tutorial owner needs the Deep Sec privileges required to create data roles, grant data roles, create data grants, create end users, and enable mandatory use of data grants for the demo tables. Ask an administrator to grant:

GRANT CREATE DATA ROLE TO deepsec;
GRANT CREATE DATA GRANT TO deepsec;
GRANT CREATE ANY DATA GRANT TO deepsec;
GRANT ADMINISTER ANY DATA GRANT TO deepsec;
GRANT GRANT ANY DATA ROLE TO deepsec;
GRANT CREATE END USER TO deepsec;
GRANT CREATE END USER SECURITY CONTEXT TO deepsec;
GRANT SET USE DATA GRANTS ONLY TO deepsec;

For direct logon without tokens, an administrator also creates a normal database role with CREATE SESSION, then grants that role to the Deep Sec data roles used by the tutorial:

CREATE ROLE deal_direct_logon_role;
GRANT CREATE SESSION TO deal_direct_logon_role;

GRANT deal_direct_logon_role TO loan_officer_role;
GRANT deal_direct_logon_role TO underwriter_role;

If loan_officer_role and underwriter_role do not exist yet, create deal_direct_logon_role first, run Step 5 to create the Deep Sec data roles, and then run the two GRANT deal_direct_logon_role ... statements.

After the tutorial tables exist, the owner enables mandatory data-grant enforcement on the protected tables:

SET USE DATA GRANTS ONLY ON loan_applications ENABLED;
SET USE DATA GRANTS ONLY ON loan_policies ENABLED;

For a service-user context setup that uses an external identity provider, the DEEPSEC_DATABASE_ACCESS_TOKEN value in .env is not a database password and not a convenient static tutorial secret. It is a database access token issued by the configured identity provider, such as OCI IAM or Microsoft Entra ID, and it can expire. Your administrator or identity setup must tell you how to obtain and refresh it.

For the no-token local-user setup, use the direct-logon pattern: Python connects directly as local Deep Sec end users such as LINDA and WENDY. In that mode, the database does not need DEEPSEC_DATABASE_ACCESS_TOKEN, but an administrator must grant a standard CREATE SESSION role to the Deep Sec data roles.

The end_user_identity value passed to oracledb.create_end_user_security_context() depends on the identity model:

For IAM or Entra users, it is typically an identity token string.
For local Deep Sec users in a service-user context pattern, it can be a tuple such as ("LINDA", key) where the key comes from the prepared local-user setup.
For direct logon, the helper does not call create_end_user_security_context(); it opens the connection as the local Deep Sec end user.

The demo helper supports DEEPSEC_CONTEXT_MODE=direct_logon, external_token, or local_tuple. Use direct_logon for this tutorial.

Create the project

You can copy the files from this tutorial, or clone the companion GitHub repository that contains the same runnable demo project:

git clone https://github.com/markxnelson/deep-data-security.git deal-deepsec
cd deal-deepsec

If you are copying the code from the article instead of cloning the repository, create a project directory first:

mkdir deal-deepsec
cd deal-deepsec

python -m venv .venv
source .venv/bin/activate

On Windows PowerShell:

python -m venv .venv
.venv\Scripts\Activate.ps1

If you cloned the companion repository, create and activate the virtual environment inside that cloned directory before installing dependencies.

The companion repository already includes requirements.txt, .env.example, .gitignore, and the Python scripts. If you cloned it, use the file-creation sections below as reference and skip ahead to installing dependencies after your virtual environment is active.

Create requirements.txt:

oracledb
python-dotenv
oci

Install the dependencies:

pip install -r requirements.txt

Expected output:

Successfully installed oracledb ...
Successfully installed python-dotenv ...
Successfully installed oci ...

Add local environment variables

Create .env.example:

# Oracle AI Database 26ai on ADB-S connection.
ADB_USERNAME=DEEPSEC
ADB_PASSWORD=your-database-password

# Use an ADB wallet TNS alias such as yourdb_high,
# or a TCPS connect descriptor with host, port, and service_name values.
ADB_DSN=your-adb-tns-alias-or-connect-descriptor

# Directory containing the unzipped wallet files, including tnsnames.ora and ewallet.pem.
ADB_WALLET_LOCATION=/path/to/unzipped/adb-wallet

# Wallet password created when the wallet was downloaded.
# This is not the database user password.
ADB_WALLET_PASSPHRASE=your-wallet-password

# Deep Sec context mode must match your administrator-prepared identity setup.
# Use direct_logon for local Deep Sec users without IAM/Entra tokens.
# Use external_token for IAM/Entra-style identity strings.
# Use local_tuple when a service connection sets context for local end-user tuples.
DEEPSEC_CONTEXT_MODE=direct_logon

# Required only for service-user context modes.
# direct_logon does not use this value.
DEEPSEC_DATABASE_ACCESS_TOKEN=

# Direct-logon passwords for the local Deep Sec end users.
DEEPSEC_LINDA_KEY=your-linda-end-user-password
DEEPSEC_WENDY_KEY=your-wendy-end-user-password

# Protected object owner used when direct-logon users query the demo tables.
DEAL_OBJECT_OWNER=DEEPSEC

# OCI Generative AI settings for the final chatbot step.
OCI_CONFIG_FILE=~/.oci/config
OCI_PROFILE=DEFAULT
OCI_GENAI_ENDPOINT=https://inference.generativeai.us-chicago-1.oci.oraclecloud.com
OCI_GENAI_COMPARTMENT_ID=your-compartment-ocid
OCI_GENAI_MODEL_ID=your-generative-ai-model-id

Copy it to .env and edit the values:

cp .env.example .env

Never commit .env to source control. Add .gitignore:

.env
.venv/
__pycache__/
*.pyc
.pytest_cache/
.DS_Store

DEAL Demo Project

The demo uses small scripts that build on each other. Run them in order:

python 01_verify_deepsec.py
python 02_create_schema.py
python 03_load_data.py
python 04_configure_deepsec.py
python 05_context_read_demo.py
python 07_vector_basics.py
python 08_secure_rag_retrieval.py
python 08b_keyword_vs_vector_check.py  # optional

After Step 8, the Deep Sec security proof is complete. The remaining scripts wrap the proven operations as DEAL tool functions, print a final terminal session, and optionally connect those tools to OCI Generative AI:

python 09_deal_tools_demo.py
python 10_run_deal_sessions.py
python 11_oci_genai_chatbot.py  # optional, requires OCI GenAI configuration

Step 1: Verify the ADB-S connection and Deep Sec metadata visibility

Start with the smallest useful checkpoint: connect to Oracle AI Database 26ai using the wallet and confirm that python-oracledb is running in Thin mode.

Create 01_verify_deepsec.py:

import os
import sys
from pathlib import Path

import oracledb
from dotenv import load_dotenv


load_dotenv()


def required_env(name):
    value = os.getenv(name)
    if (
        not value
        or value.startswith("replace-")
        or value.startswith("your-")
        or value.startswith("/path/to/")
    ):
        raise RuntimeError(f"Set {name} in .env before running this script.")
    return value


def first_env(*names):
    for name in names:
        value = os.getenv(name)
        if value and not value.startswith("replace-") and not value.startswith("your-"):
            return value
    raise RuntimeError(f"Set {' or '.join(names)} in .env before running this script.")


def optional_env(*names):
    for name in names:
        value = os.getenv(name)
        if value and not value.startswith("replace-") and not value.startswith("your-"):
            return value
    return None


def wallet_dsn(wallet_dir):
    tnsnames = Path(wallet_dir) / "tnsnames.ora"
    if not tnsnames.exists():
        raise RuntimeError("Set ADB_DSN or provide a wallet with tnsnames.ora.")
    for line in tnsnames.read_text(encoding="utf-8").splitlines():
        stripped = line.strip()
        if "=" in stripped and not stripped.startswith("#"):
            return stripped.split("=", 1)[0].strip()
    raise RuntimeError("No TNS aliases found in wallet tnsnames.ora.")


def connect():
    wallet_dir = first_env("ADB_WALLET_LOCATION", "DB_WALLET_DIR")
    dsn = optional_env("ADB_DSN", "DB_DSN") or wallet_dsn(wallet_dir)
    return oracledb.connect(
        user=first_env("ADB_USERNAME", "DB_USER"),
        password=first_env("ADB_PASSWORD", "DB_PASSWORD"),
        dsn=dsn,
        config_dir=wallet_dir,
        wallet_location=wallet_dir,
        wallet_password=optional_env(
            "ADB_WALLET_PASSPHRASE", "ADB_WALLET_PASSWORD", "DB_WALLET_PASSWORD"
        ),
    )


print("DEAL environment check")

with connect() as conn:
    cur = conn.cursor()

    cur.execute("select sys_context('USERENV', 'CURRENT_SCHEMA') from dual")
    print(f"Connected as: {cur.fetchone()[0]}")

    try:
        cur.execute("select banner_full from v$version where rownum = 1")
        version_text = cur.fetchone()[0]
    except oracledb.DatabaseError:
        cur.execute(
            """
            select version_full
            from product_component_version
            where product like 'Oracle Database%'
            fetch first 1 row only
            """
        )
        version_text = cur.fetchone()[0]

    print(f"Database version: {version_text}")

    mode = "Thin" if oracledb.is_thin_mode() else "Thick"
    print(f"python-oracledb mode: {mode}")

    if mode != "Thin":
        print("Deep Data Security requires python-oracledb Thin mode for this demo.")
        sys.exit(1)

    cur.execute(
        """
        select view_name
        from all_views
        where view_name in (
            'DBA_DATA_ROLES',
            'DBA_DATA_ROLE_GRANTS',
            'DBA_DATA_GRANTS',
            'ALL_DATA_GRANTS',
            'USER_DATA_GRANTS'
        )
        order by view_name
        """
    )
    visible_views = [row[0] for row in cur.fetchall()]

    if visible_views:
        print("Deep Sec metadata visible to this schema: " + ", ".join(visible_views))
    else:
        print("Deep Sec metadata views are not visible to this schema.")
        print("Continue only with an administrator-prepared Deep Sec environment.")

Run it:

python 01_verify_deepsec.py

Expected output:

DEAL environment check
Connected as: DEEPSEC
Database version: Oracle AI Database 26ai Enterprise Edition Release 23.26.2.1.0
python-oracledb mode: Thin
Deep Sec metadata visible to this schema: ALL_DATA_GRANTS, USER_DATA_GRANTS

If your schema cannot see Deep Sec metadata views, you may see:

Deep Sec metadata views are not visible to this schema.
Continue only with an administrator-prepared Deep Sec environment.

If this fails:

Confirm ADB_DSN, or let the helper pick an alias from wallet tnsnames.ora.
Confirm ADB_WALLET_LOCATION contains the unzipped wallet files.
Confirm ADB_WALLET_PASSPHRASE is the wallet password, not the database password.
Confirm the database is Oracle AI Database 26ai.
Do not switch to Thick mode.

Step 2: Add a small connection helper

Now that we have seen the full connection code, move the connection boilerplate into a small helper. Do not add authorization logic yet.

Create deal_db.py:

import os
from pathlib import Path

import oracledb
from dotenv import load_dotenv


load_dotenv()


def required_env(name):
    value = os.getenv(name)
    if (
        not value
        or value.startswith("replace-")
        or value.startswith("your-")
        or value.startswith("/path/to/")
    ):
        raise RuntimeError(f"Set {name} in .env before running this script.")
    return value


def first_env(*names):
    for name in names:
        value = os.getenv(name)
        if value and not value.startswith("replace-") and not value.startswith("your-"):
            return value
    raise RuntimeError(f"Set {' or '.join(names)} in .env before running this script.")


def optional_env(*names):
    for name in names:
        value = os.getenv(name)
        if value and not value.startswith("replace-") and not value.startswith("your-"):
            return value
    return None


def wallet_dsn(wallet_dir):
    tnsnames = Path(wallet_dir) / "tnsnames.ora"
    if not tnsnames.exists():
        raise RuntimeError("Set ADB_DSN or provide a wallet with tnsnames.ora.")
    for line in tnsnames.read_text(encoding="utf-8").splitlines():
        stripped = line.strip()
        if "=" in stripped and not stripped.startswith("#"):
            return stripped.split("=", 1)[0].strip()
    raise RuntimeError("No TNS aliases found in wallet tnsnames.ora.")


def connect():
    wallet_dir = first_env("ADB_WALLET_LOCATION", "DB_WALLET_DIR")
    dsn = optional_env("ADB_DSN", "DB_DSN") or wallet_dsn(wallet_dir)
    return oracledb.connect(
        user=first_env("ADB_USERNAME", "DB_USER"),
        password=first_env("ADB_PASSWORD", "DB_PASSWORD"),
        dsn=dsn,
        config_dir=wallet_dir,
        wallet_location=wallet_dir,
        wallet_password=optional_env(
            "ADB_WALLET_PASSPHRASE", "ADB_WALLET_PASSWORD", "DB_WALLET_PASSWORD"
        ),
    )

There is no command to run for this helper. Later scripts import connect().

Before continuing, you should understand:

deal_db.py centralizes only connection setup.
It does not filter data, redact columns, or set user context.
It does not call oracledb.init_oracle_client().

Step 3: Create loan and policy tables

Create two tables:

loan_applications for structured loan data.
loan_policies for policy documents and demo vectors.

Create 02_create_schema.py:

import oracledb

from deal_db import connect


DDL = [
    """
    create table loan_applications (
        id number primary key,
        customer_name varchar2(100) not null,
        loan_amount number(12,2) not null,
        purpose varchar2(100) not null,
        status varchar2(40) not null,
        officer_notes varchar2(4000),
        customer_ssn varchar2(20),
        customer_income number(12,2),
        customer_credit_score number(4),
        underwriting_decision varchar2(40),
        risk_score number(5,2),
        underwriting_notes varchar2(4000),
        assigned_officer varchar2(40) not null,
        in_underwriting_queue char(1) check (in_underwriting_queue in ('Y','N'))
    )
    """,
    """
    create table loan_policies (
        id number primary key,
        title varchar2(200) not null,
        body varchar2(4000) not null,
        audience varchar2(40) not null,
        embedding vector(3, float32)
    )
    """,
]


def drop_if_exists(cur, table_name):
    try:
        cur.execute(f"drop table {table_name} purge")
    except oracledb.DatabaseError as exc:
        error = exc.args[0]
        if error.code != 942:
            raise


with connect() as conn:
    cur = conn.cursor()

    drop_if_exists(cur, "loan_policies")
    drop_if_exists(cur, "loan_applications")
    print("Dropped existing DEAL demo tables if they existed.")

    for statement in DDL:
        cur.execute(statement)

    print("Created loan_applications.")
    print("Created loan_policies with VECTOR(3, FLOAT32) embedding column.")
    print("Schema setup complete.")

Run it:

python 02_create_schema.py

Expected output:

Dropped existing DEAL demo tables if they existed.
Created loan_applications.
Created loan_policies with VECTOR(3, FLOAT32) embedding column.
Schema setup complete.

Before continuing, you should understand:

The policy corpus is stored in Oracle Database, not in a separate vector store.
The vector column is intentionally small: VECTOR(3, FLOAT32).
Security will be added with Deep Sec data grants, not views or Python filters.

Step 4: Load synthetic data and deterministic demo vectors

Load enough data to make security differences visible.

The row design is:

Linda is assigned applications 101, 102, and 105.
Wendy can see underwriting queue applications 102, 103, 105, and 106.
Policy documents are tagged as general, loan_officer, or underwriter, but the application will not filter on that column.

The three-audience policy model is intentional. A simpler all/underwriter model can work, but general, loan_officer, and underwriter makes the policy proof easier to inspect: both roles can receive general guidance, and each role can also receive role-specific documents without any Python-side audience filtering.

Create 03_load_data.py:

from array import array

from deal_db import connect


loans = [
    (101, "Avery Stone", 320000, "Home purchase", "RECEIVED",
     "Missing final pay stub.", "111-22-3333", 128000, 720,
     "PENDING_REVIEW", 41, "Not reviewed yet.", "LINDA", "N"),
    (102, "Noah Rivers", 485000, "Home purchase", "UNDER_REVIEW",
     "Customer uploaded tax documents.", "222-33-4444", 151000, 695,
     "PENDING_REVIEW", 72, "Watch revolving debt.", "LINDA", "Y"),
    (103, "Maya Chen", 210000, "Refinance", "UNDER_REVIEW",
     "Transferred from branch intake.", "333-44-5555", 98000, 681,
     "PENDING_REVIEW", 68, "Check income variability.", "raj", "Y"),
    (104, "Grace Hill", 150000, "Home equity", "NEEDS_DOCS",
     "Awaiting insurance statement.", "444-55-6666", 87000, 735,
     "NOT_STARTED", 35, "Not in queue.", "amir", "N"),
    (105, "Owen Park", 640000, "Jumbo mortgage", "UNDER_REVIEW",
     "Customer requested expedited review.", "555-66-7777", 220000, 705,
     "PENDING_REVIEW", 77, "Large loan amount.", "LINDA", "Y"),
    (106, "Sofia Reyes", 390000, "Investment property", "ESCALATED",
     "Escalated by branch manager.", "666-77-8888", 132000, 660,
     "PENDING_REVIEW", 83, "Exception review needed.", "raj", "Y"),
]

policies = [
    (1, "General lending eligibility",
     "Baseline eligibility requirements for consumer lending applications.",
     "general", array("f", [0.75, 0.20, 0.05])),
    (2, "Income verification basics",
     "Required documents for income verification and employment review.",
     "general", array("f", [0.65, 0.30, 0.05])),
    (3, "Document retention requirements",
     "Retention periods for lending documents and customer communications.",
     "general", array("f", [0.30, 0.20, 0.50])),
    (4, "Loan officer workflow checklist",
     "Loan officer workflow for intake, status updates, and customer follow-up.",
     "loan_officer", array("f", [0.85, 0.10, 0.05])),
    (5, "Officer notes standards",
     "Standards for writing clear customer-facing officer notes.",
     "loan_officer", array("f", [0.80, 0.05, 0.15])),
    (6, "Credit risk escalation policy",
     "When credit risk signals require underwriting escalation.",
     "underwriter", array("f", [0.05, 0.90, 0.05])),
    (7, "Debt-to-income exception review",
     "Underwriting guidance for debt-to-income ratio exceptions.",
     "underwriter", array("f", [0.10, 0.85, 0.05])),
    (8, "Collateral review guidance",
     "Collateral review requirements for underwriting decisions.",
     "underwriter", array("f", [0.05, 0.75, 0.20])),
]


with connect() as conn:
    cur = conn.cursor()

    cur.executemany(
        """
        insert into loan_applications (
            id, customer_name, loan_amount, purpose, status, officer_notes,
            customer_ssn, customer_income, customer_credit_score,
            underwriting_decision, risk_score, underwriting_notes,
            assigned_officer, in_underwriting_queue
        ) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14)
        """,
        loans,
    )

    cur.executemany(
        """
        insert into loan_policies (id, title, body, audience, embedding)
        values (:1, :2, :3, :4, :5)
        """,
        policies,
    )

    conn.commit()

    print(f"Inserted {len(loans)} loan applications.")
    print(f"Inserted {len(policies)} loan policy documents.")
    print("Synthetic data load complete.")

Run it:

python 03_load_data.py

Expected output:

Inserted 6 loan applications.
Inserted 8 loan policy documents.
Synthetic data load complete.

Before continuing, you should understand:

All data is synthetic.
Sensitive columns are populated so column restrictions are visible.
The vectors are deterministic demo vectors, not model-generated embeddings.
The audience column is a database policy anchor, not an application-side filter.

Step 5: Configure Oracle Deep Data Security data roles and data grants

Now create the Deep Sec authorization model.

This step assumes linda and wendy are prepared Deep Sec end users in your environment. The script creates data roles, grants those roles to the end users, and creates data grants on the two tutorial tables.

DEAL access rules: Deep Sec data roles and data grants define which loan rows, restricted columns, and policy documents Linda and Wendy can access.

Create 04_configure_deepsec.py:

from deal_db import connect


with connect() as conn:
    cur = conn.cursor()

    print("Using prepared Deep Sec end users: LINDA, WENDY")

    cur.execute("create data role loan_officer_role")
    print("Created data role: LOAN_OFFICER_ROLE")

    cur.execute("create data role underwriter_role")
    print("Created data role: UNDERWRITER_ROLE")

    cur.execute("grant data role loan_officer_role to linda")
    print("Granted LOAN_OFFICER_ROLE to LINDA.")

    cur.execute("grant data role underwriter_role to wendy")
    print("Granted UNDERWRITER_ROLE to WENDY.")

    grants = [
        """
        create or replace data grant deal_loan_officer_read as
        select (
            all columns except customer_ssn, customer_income,
            customer_credit_score, underwriting_decision,
            risk_score, underwriting_notes
        )
        on loan_applications
        where assigned_officer = ORA_END_USER_CONTEXT.username
        to loan_officer_role
        """,
        """
        create or replace data grant deal_underwriter_read as
        select on loan_applications
        where in_underwriting_queue = 'Y'
        to underwriter_role
        """,
        """
        create or replace data grant deal_policy_general_to_officer as
        select on loan_policies
        where audience = 'general'
        to loan_officer_role
        """,
        """
        create or replace data grant deal_policy_officer as
        select on loan_policies
        where audience = 'loan_officer'
        to loan_officer_role
        """,
        """
        create or replace data grant deal_policy_general_to_underwriter as
        select on loan_policies
        where audience = 'general'
        to underwriter_role
        """,
        """
        create or replace data grant deal_policy_underwriter as
        select on loan_policies
        where audience = 'underwriter'
        to underwriter_role
        """,
    ]

    for grant in grants:
        cur.execute(grant)

    conn.commit()

    print("Created loan application read grants.")
    print("Created loan policy read grants.")
    print("Configured Deep Data Security for DEAL.")

Run it once against a fresh demo schema:

python 04_configure_deepsec.py

Expected output:

Using prepared Deep Sec end users: LINDA, WENDY
Created data role: LOAN_OFFICER_ROLE
Created data role: UNDERWRITER_ROLE
Granted LOAN_OFFICER_ROLE to LINDA.
Granted UNDERWRITER_ROLE to WENDY.
Created loan application read grants.
Created loan policy read grants.
Configured Deep Data Security for DEAL.

If the direct-logon role grants were waiting on the Deep Sec data roles, have the administrator run them now. Then enable mandatory data-grant enforcement on the two demo tables:

GRANT deal_direct_logon_role TO loan_officer_role;
GRANT deal_direct_logon_role TO underwriter_role;

SET USE DATA GRANTS ONLY ON loan_applications ENABLED;
SET USE DATA GRANTS ONLY ON loan_policies ENABLED;

If this fails:

Confirm the tutorial schema has the required Deep Sec privileges.
Confirm linda and wendy exist as Deep Sec end users in your prepared environment.
Confirm your schema owns the demo tables or your administrator has prepared the object-owner model.
Do not replace Deep Data Security with views, triggers, VPD, or Python filters for this tutorial.

Before continuing, you should understand:

Data roles are part of the Deep Data Security authorization model.
Data grants define row and column access in the database.
The loan_policies.audience column is used only inside database policy grants.
Application SQL still remains broad.

Step 6: Set end-user context and prove read and column enforcement

Now add Deep Sec context support to the helper and prove that the same SQL returns different authorized results. In direct_logon mode, the helper connects directly as LINDA or WENDY and qualifies protected objects through the owner schema.

Add this function to the bottom of deal_db.py:

def direct_logon_connect(end_user):
    passwords = {
        "linda": required_env("DEEPSEC_LINDA_KEY"),
        "wendy": required_env("DEEPSEC_WENDY_KEY"),
    }
    if end_user not in passwords:
        raise ValueError(f"Unknown DEAL end user: {end_user}")

    wallet_dir = first_env("ADB_WALLET_LOCATION", "DB_WALLET_DIR")
    dsn = optional_env("ADB_DSN") or optional_env("DB_DSN") or wallet_dsn(wallet_dir)
    return oracledb.connect(
        user=end_user.upper(),
        password=passwords[end_user],
        dsn=dsn,
        config_dir=wallet_dir,
        wallet_location=wallet_dir,
        wallet_password=optional_env(
            "ADB_WALLET_PASSPHRASE", "ADB_WALLET_PASSWORD", "DB_WALLET_PASSWORD"
        ),
    )


def run_for_user(end_user, work):
    if (optional_env("DEEPSEC_CONTEXT_MODE") or "direct_logon") == "direct_logon":
        with direct_logon_connect(end_user) as conn:
            return work(conn)
    raise RuntimeError("This tutorial validates DEEPSEC_CONTEXT_MODE=direct_logon.")


def object_name(name):
    owner = optional_env("DEAL_OBJECT_OWNER") or "DEEPSEC"
    return f"{owner}.{name}"

This helper uses the prepared local-user passwords from .env. The public Python functions still accept friendly names such as "linda" and "wendy", then the helper connects with the unquoted database identifiers LINDA and WENDY. No DEEPSEC_DATABASE_ACCESS_TOKEN is required in direct_logon mode. In a production app, your authentication layer must still establish the real user before choosing which local Deep Sec user or service-user context to use.

Create 05_context_read_demo.py:

from deal_db import object_name, run_for_user


SQL = """
select *
from {loan_applications}
order by id
""".format(loan_applications=object_name("loan_applications"))

FIELDS_TO_SHOW = [
    "customer_ssn",
    "customer_credit_score",
    "underwriting_decision",
    "risk_score",
]


def display_value(value):
    return "NULL" if value is None else value


def rows_for(end_user):
    def work(conn):
        cur = conn.cursor()
        cur.execute(SQL)
        columns = [col[0].lower() for col in cur.description]
        return [dict(zip(columns, row)) for row in cur.fetchall()]

    return run_for_user(end_user, work)


def show(end_user):
    rows = rows_for(end_user)
    ids = [str(row["id"]) for row in rows]

    print(f"\nAs {end_user}:")
    print(f"  Rows returned: {len(rows)}")
    print(f"  Visible application ids: {', '.join(ids)}")

    sample = rows[0] if rows else {}
    print("  Selected sensitive and underwriting fields:")
    for field in FIELDS_TO_SHOW:
        print(f"    {field}: {display_value(sample.get(field))}")


print("Read and column enforcement demo")
show("linda")
show("wendy")

Run it:

python 05_context_read_demo.py

Expected output:

Read and column enforcement demo

As linda:
  Rows returned: 3
  Visible application ids: 101, 102, 105
  Selected sensitive and underwriting fields:
    customer_ssn: NULL
    customer_credit_score: NULL
    underwriting_decision: NULL
    risk_score: NULL

As wendy:
  Rows returned: 4
  Visible application ids: 102, 103, 105, 106
  Selected sensitive and underwriting fields:
    customer_ssn: 222-33-4444
    customer_credit_score: 695
    underwriting_decision: PENDING_REVIEW
    risk_score: 72

The same SQL is used for Linda and Wendy:

SELECT *
FROM loan_applications
ORDER BY id

There is no WHERE assigned_officer = :end_user predicate in the application.

If this fails:

Confirm DEEPSEC_CONTEXT_MODE=direct_logon.
Confirm Linda and Wendy can log on as local Deep Sec end users.
Confirm DEAL_OBJECT_OWNER points to the owner of the demo tables.
Confirm data role grants and data grants exist.

Before continuing, you should understand:

The application does not add a user-specific WHERE clause.
The application does not redact restricted columns.
The database returns the authorized row and column view for the active direct-logon Deep Sec end user.

Step 7: Demonstrate vector distance with manual vectors

Before using vector retrieval as a RAG building block, inspect a simple vector query.

A vector is a list of numbers. In AI applications, embedding models produce vectors that place related concepts near one another. In this tutorial, we use small deterministic vectors so you can see the mechanics directly.

The query vector [0.9, 0.1, 0.0] is closest to loan-officer workflow style documents in our demo data.

Create 07_vector_basics.py:

from array import array

from deal_db import object_name, run_for_user


query_vector = array("f", [0.9, 0.1, 0.0])

sql = """
select title,
       vector_distance(embedding, :query_vector, COSINE) as distance
from {loan_policies}
order by distance
fetch first 3 rows only
""".format(loan_policies=object_name("loan_policies"))

print("Vector warm-up with manual 3-dimensional vectors")
print("Vector warm-up user context: linda")
print(f"Query vector: {list(query_vector)}")

def work(conn):
    cur = conn.cursor()
    cur.execute(sql, query_vector=query_vector)

    print("\nNearest policy vectors:")
    for index, (title, distance) in enumerate(cur, start=1):
        print(f"{index}. {title:<35} distance: {distance:.6f}")


run_for_user("linda", work)

Run it:

python 07_vector_basics.py

Expected output:

Vector warm-up with manual 3-dimensional vectors
Vector warm-up user context: linda
Query vector: [0.8999999761581421, 0.10000000149011612, 0.0]

Nearest policy vectors:
1. Loan officer workflow checklist     distance: 0.001723
2. General lending eligibility         distance: 0.013266
3. Officer notes standards             distance: 0.018206

Your exact distances may vary slightly by database patch level or floating-point formatting; the important result is the role-scoped result set.

A quick metric mental model:

Cosine distance compares vector direction and is common for embedding-style retrieval.
Euclidean distance compares straight-line distance in vector space.
Dot product is often used as a similarity score and is sensitive to vector magnitude.

Production embedding models may normalize vectors or document how they should be compared. Normalization can affect cosine, Euclidean, and dot-product rankings. DEAL uses one explicit VECTOR_DISTANCE() expression so the query shape stays clear.

Oracle AI Vector Search also supports vector indexes for larger corpora, including HNSW-style neighbor graph indexes and IVF/IVFFlat-style partitioned indexes. DEAL does not create an index because this security proof has eight policy rows; when you scale the corpus, validate a CREATE VECTOR INDEX statement for your metric and inspect index metadata such as USER_INDEXES before relying on performance claims.

Before continuing, you should understand:

VECTOR(3, FLOAT32) stores three FLOAT32 values.
Python binds the query vector with array.array("f", [...]).
The primitive vector example comes before any embedding model or chatbot tooling.

Step 8: Run Deep Sec-scoped vector retrieval

Now run a vector query that represents a RAG-style policy search. The search intent is:

unstable cash flow or credit risk

The tutorial maps that intent to a fixed demo vector. In a real AI application, an embedding model such as OCI Generative AI would create the query vector and document vectors at the model’s actual dimension.

Create 08_secure_rag_retrieval.py:

from array import array

from deal_db import object_name, run_for_user


query_text = "unstable cash flow or credit risk"
query_vector = array("f", [0.05, 0.9, 0.05])

sql = """
select id, title, audience,
       vector_distance(embedding, :query_vector, COSINE) as distance
from {loan_policies}
order by distance
fetch first 3 rows only
""".format(loan_policies=object_name("loan_policies"))


def search_as(end_user):
    def work(conn):
        cur = conn.cursor()
        cur.execute(sql, query_vector=query_vector)
        return cur.fetchall()

    return run_for_user(end_user, work)


print("Deep Sec-scoped vector retrieval demo")
print(f"Policy search query: {query_text}")
print("Output below is scoped by the active Deep Sec end user.")

for end_user in ["linda", "wendy"]:
    print(f"\nAs {end_user}:")
    for index, row in enumerate(search_as(end_user), start=1):
        print(f"  {index}. {row[1]:<35} distance: {row[3]:.6f}")

Run it:

python 08_secure_rag_retrieval.py

Expected output:

Deep Sec-scoped vector retrieval demo
Policy search query: unstable cash flow or credit risk
Output below is scoped by the active Deep Sec end user.

As linda:
  1. Income verification basics          distance: 0.529221
  2. Document retention requirements     distance: 0.604677
  3. General lending eligibility         distance: 0.686696

As wendy:
  1. Credit risk escalation policy      distance: 0.000000
  2. Debt-to-income exception review    distance: 0.001895
  3. Collateral review guidance         distance: 0.020925

If this fails:

Confirm Deep Sec grants exist on loan_policies.
Confirm the active end-user context is set.
Confirm the vector query runs against the protected table.
Confirm no application-side audience filter has been added.

Before continuing, you should understand:

The same query vector is used for Linda and Wendy.
The same SQL shape is used for Linda and Wendy.
The SQL does not filter by audience.
The Python code does not filter by audience.
Deep Sec data grants govern which policy rows are available to the vector query.

Optional: Compare keyword and vector-style retrieval

Vector retrieval and keyword retrieval answer different relevance questions. Keyword search finds literal text matches. Vector retrieval can find nearby concepts represented by numbers.

Neither one should become the security boundary. Run both retrieval styles under the same Deep Sec contexts.

Create 08b_keyword_vs_vector_check.py:

from array import array

from deal_db import object_name, run_for_user


query_vector = array("f", [0.05, 0.9, 0.05])

vector_sql = """
select title
from {loan_policies}
order by vector_distance(embedding, :query_vector, COSINE)
fetch first 3 rows only
""".format(loan_policies=object_name("loan_policies"))

keyword_sql = """
select title
from {loan_policies}
where lower(body) like :term
order by id
fetch first 3 rows only
""".format(loan_policies=object_name("loan_policies"))


def run_as(end_user):
    def work(conn):
        cur = conn.cursor()

        cur.execute(vector_sql, query_vector=query_vector)
        vector_titles = [row[0] for row in cur.fetchall()]

        cur.execute(keyword_sql, term="%credit risk%")
        keyword_titles = [row[0] for row in cur.fetchall()]

        return vector_titles, keyword_titles

    return run_for_user(end_user, work)


print("Optional vector-style vs keyword retrieval check")

for end_user in ["linda", "wendy"]:
    vector_titles, keyword_titles = run_as(end_user)
    print(f"\nAs {end_user}:")
    print(f"  Vector-style titles: {', '.join(vector_titles)}")
    print(
        "  Keyword titles for 'credit risk': "
        + (", ".join(keyword_titles) if keyword_titles else "none")
    )

Run it if you want the extra comparison:

python 08b_keyword_vs_vector_check.py

Representative output:

Optional vector-style vs keyword retrieval check

As linda:
  Vector-style titles: Income verification basics, Document retention requirements, General lending eligibility
  Keyword titles for 'credit risk': none

As wendy:
  Vector-style titles: Credit risk escalation policy, Debt-to-income exception review, Collateral review guidance
  Keyword titles for 'credit risk': Credit risk escalation policy

Before continuing, you should understand:

Keyword search and vector retrieval are relevance strategies.
Deep Sec policy still scopes the rows available to the query.
The app still does not use an audience security filter.

Try this next: Wrap the proven operations as DEAL tool functions

Only after proving the database operations directly should we wrap them as assistant tools.

Create deal_tools.py:

from array import array

from deal_db import object_name, run_for_user


def _rows_as_dicts(cur):
    columns = [col[0].lower() for col in cur.description]
    return [dict(zip(columns, row)) for row in cur.fetchall()]


def _run_for_user(end_user, work):
    return run_for_user(end_user, work)


def _query_vector(query):
    text = query.lower()
    if "risk" in text or "credit" in text or "cash flow" in text:
        return array("f", [0.05, 0.9, 0.05])
    return array("f", [0.9, 0.1, 0.0])

The _query_vector() function is relevance logic for this deterministic demo. It is not authorization logic.

Append the read tools:

def get_loan_applications(end_user):
    def work(conn):
        cur = conn.cursor()
        cur.execute(
            f"""
            select *
            from {object_name("loan_applications")}
            order by id
            """
        )
        return _rows_as_dicts(cur)

    return _run_for_user(end_user, work)


def get_application_detail(end_user, app_id):
    def work(conn):
        cur = conn.cursor()
        cur.execute(
            f"""
            select *
            from {object_name("loan_applications")}
            where id = :id
            """,
            id=app_id,
        )
        rows = _rows_as_dicts(cur)
        return rows[0] if rows else None

    return _run_for_user(end_user, work)

The WHERE id = :id predicate is a business lookup. It is not a user-specific authorization filter.

Append the policy-search tool:

def search_policies(end_user, query):
    query_vector = _query_vector(query)

    def work(conn):
        cur = conn.cursor()
        cur.execute(
            f"""
            select id, title, body, audience,
                   vector_distance(embedding, :query_vector, COSINE) as distance
            from {object_name("loan_policies")}
            order by distance
            fetch first 3 rows only
            """,
            query_vector=query_vector,
        )
        return _rows_as_dicts(cur)

    return _run_for_user(end_user, work)

Create 09_deal_tools_demo.py:

from deal_tools import (
    get_loan_applications,
    search_policies,
)


print("Tool demo: linda")
linda_loans = get_loan_applications("linda")
linda_policies = search_policies("linda", "credit risk")
print(f"  get_loan_applications returned {len(linda_loans)} rows.")
print(f"  search_policies returned {len(linda_policies)} policy documents.")

print("\nTool demo: wendy")
wendy_loans = get_loan_applications("wendy")
wendy_policies = search_policies("wendy", "credit risk")
print(f"  get_loan_applications returned {len(wendy_loans)} rows.")
print(f"  search_policies returned {len(wendy_policies)} policy documents.")

Run it:

python 09_deal_tools_demo.py

Expected output:

Tool demo: linda
  get_loan_applications returned 3 rows.
  search_policies returned 3 policy documents.

Tool demo: wendy
  get_loan_applications returned 4 rows.
  search_policies returned 3 policy documents.

Before continuing, you should understand:

Each tool runs database work under the requested Deep Sec end user.
In direct-logon mode, each user-scoped operation opens its own connection as LINDA or WENDY.
Tools do not filter rows by user.
Tools do not redact restricted columns.
Tools do not filter policy documents by audience.

Try this next: Run DEAL sessions and bypass checks

Now run the final terminal assistant session. This script calls the tool functions and prints a short transcript for Linda, Wendy, and a bypass check.

Create 10_run_deal_sessions.py:

from deal_tools import (
    get_application_detail,
    get_loan_applications,
    search_policies,
)


def display_value(value):
    return "NULL" if value is None else value


def titles(rows):
    return ", ".join(row["title"] for row in rows)


def linda_session():
    print("========================")
    print("DEAL session: linda")
    print("========================")

    loans = get_loan_applications("linda")
    ids = [str(row["id"]) for row in loans]
    detail = get_application_detail("linda", 102)
    policies = search_policies("linda", "credit risk")

    print(f"Visible applications: {len(loans)}")
    print(f"Application ids: {', '.join(ids)}")
    print(f"Restricted risk_score: {display_value(detail.get('risk_score'))}")
    print(f"Policy results: {titles(policies)}")


def wendy_session():
    print("\n========================")
    print("DEAL session: wendy")
    print("========================")

    loans = get_loan_applications("wendy")
    ids = [str(row["id"]) for row in loans]
    detail = get_application_detail("wendy", 102)
    policies = search_policies("wendy", "credit risk")

    print(f"Visible applications: {len(loans)}")
    print(f"Application ids: {', '.join(ids)}")
    print(f"Underwriting risk_score: {display_value(detail.get('risk_score'))}")
    print(f"Policy results: {titles(policies)}")

Append the bypass check and runner:

def bypass_check():
    print("\n========================")
    print("Bypass check")
    print("========================")

    linda_rows = get_loan_applications("linda")
    wendy_rows = get_loan_applications("wendy")
    linda_docs = search_policies("linda", "credit risk")
    wendy_docs = search_policies("wendy", "credit risk")

    print(f"Broad loan query returned {len(linda_rows)} Linda-scoped rows for linda.")
    print(f"Broad loan query returned {len(wendy_rows)} Wendy-scoped rows for wendy.")
    print(f"Linda policy titles: {titles(linda_docs)}")
    print(f"Wendy policy titles: {titles(wendy_docs)}")
    print("No application-side row filter, redaction, or audience filter was used.")


linda_session()
wendy_session()
bypass_check()

Run it:

python 10_run_deal_sessions.py

Expected output:

========================
DEAL session: linda
========================
Visible applications: 3
Application ids: 101, 102, 105
Restricted risk_score: NULL
Policy results: Income verification basics, Document retention requirements, General lending eligibility

========================
DEAL session: wendy
========================
Visible applications: 4
Application ids: 102, 103, 105, 106
Underwriting risk_score: 72
Policy results: Credit risk escalation policy, Debt-to-income exception review, Collateral review guidance

========================
Bypass check
========================
Broad loan query returned 3 Linda-scoped rows for linda.
Broad loan query returned 4 Wendy-scoped rows for wendy.
Linda policy titles: Income verification basics, Document retention requirements, General lending eligibility
Wendy policy titles: Credit risk escalation policy, Debt-to-income exception review, Collateral review guidance
No application-side row filter, redaction, or audience filter was used.

Final DEAL session: the same tool functions run under Linda and Wendy’s Deep Sec context, and the database returns user-scoped loan and policy retrieval results.

Before finishing, you should understand the full trust boundary:

DEAL establishes the correct Deep Sec end user for each operation.
The same broad read function returns different rows and restricted values.
The same vector retrieval function returns different policy documents.
The assistant is not trusted to filter or redact protected data.

Optional: Add an OCI GenAI chatbot loop

The terminal session proves the secured tool functions directly. As an optional extension, add a small LLM loop that lets OCI Generative AI choose which DEAL tool to call. The OCI GenAI code path is an integration pattern; the Deep Sec read and vector security proof does not depend on it. Validate the OCI SDK request and response shape in your tenancy before relying on a captured chatbot transcript.

The model is not trusted for authorization; it only selects an action. Every tool call still runs under the chosen Deep Sec end user and lets Oracle Database enforce the policy.

Create 11_oci_genai_chatbot.py:

import json
import os
import re
from pathlib import Path

import oci
from dotenv import load_dotenv
from oci.generative_ai_inference import GenerativeAiInferenceClient
from oci.generative_ai_inference.models import (
    ChatDetails,
    GenericChatRequest,
    Message,
    OnDemandServingMode,
    UserMessage,
)

from deal_tools import (
    get_application_detail,
    get_loan_applications,
    search_policies,
)


load_dotenv()


TOOLS = {
    "get_loan_applications": get_loan_applications,
    "get_application_detail": get_application_detail,
    "search_policies": search_policies,
}


def required_env(name):
    value = os.getenv(name)
    if value:
        value = value.strip().strip('"').strip("'")
    if not value or value.startswith("your-"):
        raise RuntimeError(f"Set {name} in .env before running this script.")
    return value


def oci_client():
    config_file = Path(os.getenv("OCI_CONFIG_FILE", "~/.oci/config")).expanduser()
    profile = os.getenv("OCI_PROFILE", "DEFAULT")
    config = oci.config.from_file(str(config_file), profile)
    return GenerativeAiInferenceClient(
        config=config,
        service_endpoint=required_env("OCI_GENAI_ENDPOINT"),
    )


def message_text(chat_response):
    data = chat_response.data
    if hasattr(data, "chat_response") and hasattr(data.chat_response, "choices"):
        choice = data.chat_response.choices[0]
        return choice.message.content[0].text
    return json.dumps(oci.util.to_dict(data))


def chat_once(client, prompt):
    request = GenericChatRequest(
        api_format=GenericChatRequest.API_FORMAT_GENERIC,
        messages=[
            UserMessage(
                role=Message.ROLE_USER,
                content=[{"type": "TEXT", "text": prompt}],
            )
        ],
        max_tokens=500,
        temperature=0,
    )
    details = ChatDetails(
        compartment_id=required_env("OCI_GENAI_COMPARTMENT_ID"),
        serving_mode=OnDemandServingMode(model_id=required_env("OCI_GENAI_MODEL_ID")),
        chat_request=request,
    )
    return message_text(client.chat(details))


def choose_action(client, end_user, question):
    prompt = f"""
You are DEAL, a lending assistant running as end user {end_user}.
Choose exactly one tool call for the user's request.
Return only JSON with keys tool and arguments.

Available tools:
- get_loan_applications: {{}}
- get_application_detail: {{"app_id": number}}
- search_policies: {{"query": string}}

User request: {question}
"""
    raw = chat_once(client, prompt).strip()
    try:
        return json.loads(raw)
    except json.JSONDecodeError:
        fenced = re.search(r"```

(?:json)?\s*(\{.*?\})\s*

```", raw, re.DOTALL)
        if fenced:
            return json.loads(fenced.group(1))
        start = raw.find("{")
        end = raw.rfind("}")
        if start >= 0 and end > start:
            return json.loads(raw[start : end + 1])
        raise


def run_tool(end_user, action):
    tool_name = action["tool"]
    arguments = action.get("arguments", {})
    if tool_name not in TOOLS:
        raise ValueError(f"Unknown tool requested by model: {tool_name}")
    return TOOLS[tool_name](end_user, **arguments)


def answer_with_result(client, end_user, question, action, result):
    prompt = f"""
You are DEAL, a lending assistant running as end user {end_user}.
The database has already enforced Oracle Deep Data Security.
Answer the user briefly from the tool result only.
If the tool result is a list of loan applications, count the list and list the id values exactly.
If the tool result is a list of policies, list only the returned policy titles.
Do not say that no rows or policies were found unless the tool result is an empty list.

User request: {question}
Tool action: {json.dumps(action)}
Tool result: {json.dumps(result, default=str)}
"""
    return chat_once(client, prompt)


def run_session(client, end_user, questions):
    print(f"\n========================")
    print(f"OCI GenAI DEAL session: {end_user}")
    print(f"========================")
    for question in questions:
        action = choose_action(client, end_user, question)
        result = run_tool(end_user, action)
        answer = answer_with_result(client, end_user, question, action, result)
        print(f"\nUser: {question}")
        print(f"Tool: {action['tool']} {action.get('arguments', {})}")
        print(f"DEAL: {answer}")


client = oci_client()

run_session(
    client,
    "linda",
    [
        "Which loan applications can I see?",
        "Find policy guidance about credit risk.",
    ],
)

run_session(
    client,
    "wendy",
    [
        "Which applications are in my underwriting queue?",
        "Find policy guidance about credit risk.",
    ],
)

Run it after the direct tool demo succeeds:

python 11_oci_genai_chatbot.py

Expected output:

========================
OCI GenAI DEAL session: linda
========================

User: Which loan applications can I see?
Tool: get_loan_applications {}
DEAL: You can see 3 loan applications: 101, 102, 105.

User: Find policy guidance about credit risk.
Tool: search_policies {'query': 'credit risk'}
DEAL: Income verification basics, Document retention requirements, General lending eligibility

========================
OCI GenAI DEAL session: wendy
========================

User: Which applications are in my underwriting queue?
Tool: get_loan_applications {}
DEAL: There are 4 applications in your underwriting queue: 102, 103, 105, 106.

User: Find policy guidance about credit risk.
Tool: search_policies {'query': 'credit risk'}
DEAL: Credit risk escalation policy
Debt-to-income exception review
Collateral review guidance

The important part is the tool transcript, not the wording of the generated answer. If the model asks for a broad loan query, broad application detail, or broad policy search, Oracle Database still scopes the result to the active Deep Sec end user.

Troubleshooting

The connection fails

Check:

ADB_DSN matches your ADB-S wallet alias or connect descriptor, or the wallet contains a usable tnsnames.ora alias.
ADB_WALLET_LOCATION points to the unzipped wallet directory.
The directory includes wallet files used by Thin mode, such as tnsnames.ora and ewallet.pem.
ADB_WALLET_PASSPHRASE is the wallet password, not the database password.

Do not call oracledb.init_oracle_client() for this tutorial.

The driver is in Thick mode

Deep Data Security in this Python tutorial uses python-oracledb Thin mode. Remove any call to:

oracledb.init_oracle_client()

Then rerun 01_verify_deepsec.py.

Linda and Wendy see the same loan rows

Check:

DEEPSEC_CONTEXT_MODE=direct_logon.
DEEPSEC_LINDA_KEY and DEEPSEC_WENDY_KEY contain the local Deep Sec end-user passwords.
DEAL_OBJECT_OWNER points to the schema that owns loan_applications and loan_policies.
Data roles were granted to the correct Deep Sec end users.
Data grants reference the expected Deep Sec username.

Vector inserts or queries fail

Check:

The table uses VECTOR(3, FLOAT32).
Every vector bind uses exactly three values.
Python binds vectors with array.array("f", [...]).
The vector query uses the same metric expression consistently.

You plan to use connection pools

The tutorial uses simple standalone connections so the request boundary is obvious. If you later switch to the service-user context path with a pool, set the Deep Sec context after acquiring a connection and clear it before releasing the connection. Keep this pattern:

conn = pool.acquire()
try:
    context = context_for(end_user)
    conn.set_end_user_security_context(context)
    # Run user-scoped database work.
finally:
    conn.clear_end_user_security_context()
    conn.close()

You plan to replace demo vectors with real embeddings

The core tutorial uses deterministic demo vectors so the security proof is self-contained. When you switch to a real embedding API:

Use the model’s actual vector dimension in the table definition.
Batch embedding requests where the provider supports batching.
Track API cost and rate limits.
Store enough metadata to regenerate embeddings when the model changes.
Keep authorization in Deep Sec data grants, not in embedding metadata filters.

Related Resources

Companion DEAL demo code repository — Clone the runnable Python project used in this tutorial.
Oracle Deep Data Security Guide for Oracle AI Database 26ai — Learn the Deep Data Security concepts, objects, and administration model behind the tutorial.
Create Data Grants in Oracle Deep Data Security — Review how data grants define row, column, and operation access for protected tables.
Configure Data Roles in Oracle Deep Data Security — See how Deep Sec data roles are created and assigned to end users.
python-oracledb Deep Data Security — Use the Python driver APIs for creating, setting, and clearing end-user security context.
Paul Parkinson's Deep Data Security Java article — Compare the same Deep Sec application model in a Java application.
Oracle LiveLabs: Getting Started with Oracle Deep Data Security — Try a guided Deep Data Security workshop.
Oracle AI Vector Search User's Guide — Review Oracle AI Vector Search concepts and SQL reference material.
python-oracledb Vector Data Type — Learn how Python applications bind and fetch Oracle Database VECTOR values.

Conclusion

You built DEAL, a Python loan assistant that demonstrates database-enforced access control for structured SQL, vector retrieval, and LLM-driven tool calls. Linda and Wendy use the same broad tool functions, but Oracle Database applies the active Deep Sec end-user identity before returning loan rows, restricted columns, or policy documents.

That matters because AI applications widen the blast radius of small authorization mistakes. A chatbot can call a broad query, a tool can forget a role predicate, and a prompt can ask for data the user should not receive. With Deep Data Security, the tutorial's security boundary sits in Oracle Database instead of in prompts, redaction branches, or application-side filters.

For production, replace the deterministic demo vectors with embeddings from your chosen model, test the workflow in your own ADB-S environment, and add auditing around high-risk read and retrieval paths. Keep the same rule as you evolve the assistant: the model and application can request data, but the database decides what the end user is allowed to see.

Vector‑native RAG on Oracle: embeddings, HNSW/IVF, and hybrid search under database governance

Mark Nelson — Wed, 27 May 2026 10:38:06 +0000

Key Takeaways

Storing vectors in an Oracle VECTOR column alongside content, metadata, and provenance means retrieval happens inside the database. Existing governance — row-level security, auditing, data masking — applies to vector queries the same way it applies to any other query.
Hybrid retrieval is ordinary SQL: VECTOR_DISTANCE handles semantic similarity and WHERE clauses handle business predicates in the same statement. Any reviewer who can read a query can understand what rows qualified and why.
HNSW and IVF are index strategies with real trade-offs in recall, memory footprint, and query latency. Choosing between them requires measuring both on your own corpus — they are not interchangeable defaults.
The answer step can stay inspectable too. Connecting retrieval to a Select AI profile with SHOWSQL means the SQL generation step is reviewable, not just the retrieval that fed it.

Retrieval‑augmented generation works when you can fetch the right context quickly and explain—in plain SQL—why those rows qualified. Splitting storage between a stand‑alone vector store and the database of record speeds early prototypes but makes governance fragile: metadata is duplicated, filters are reimplemented, and audit trails often end at a gateway you do not control. Oracle’s vector‑native approach keeps the entire RAG path inside the database. Embeddings live in a VECTOR column next to the text, JSON, and provenance you already track; you index them with HNSW or IVF using CREATE VECTOR INDEX; you retrieve with SQL that combines semantic similarity and business predicates. Because retrieval runs on database objects, existing policies and audits—when configured on those objects—stay in the path. When you need an LLM to compose the answer, Select AI with AI Profiles lets you scope what the model can see and inspect generated SQL before execution. The result is a retrieval path that is fast enough for interactive use and reviewable under the same governance you already operate.

Figure 1 — Route → act → trust for in‑database RAG. Retrieval is SQL; governance stays in the path.

If you read Article 5, you saw how AI Profiles keep NL2SQL inspectable. This article focuses on retrieval: how to store vectors, choose HNSW or IVF, and write hybrid queries you can defend in a review meeting.

Prerequisites

Oracle AI Database 26ai or Autonomous Database with AI Vector Search enabled for your RU/service tier.
Privileges to CREATE TABLE and CREATE INDEX in a schema with sufficient quota.
For external embeddings or Select AI, configure credentials and network ACLs per your organization’s policy.
Verify CREATE VECTOR INDEX options and the DBMS_* package signatures in your RU before automating builds.

What “vector‑native” means in Oracle

Vector‑native means embeddings are first‑class database data, not a sidecar. You add a VECTOR(dim, element_type) column to the table that holds your chunked content and provenance; you build an approximate nearest‑neighbor (ANN) index—HNSW for interactive recall or IVF for very large corpora—and you query with SQL. The practical effect is governance continuity: when Virtual Private Database (VPD) policies, Unified Auditing, Data Redaction, and Transparent Data Encryption (TDE) are configured on the relevant objects, they continue to filter, record, mask, and protect your retrieval path. When someone asks why a row entered the context window, you can point to the same table‑scoped policies that govern every other query.

For architecture and security details, see the Oracle AI Vector Search overview and the Database Security Guide for VPD, Unified Auditing, Data Redaction, and TDE:

From document to chunk to embedding

RAG quality depends heavily on two choices you control: where you cut chunks and how you embed them. Oracle provides DBMS_VECTOR_CHAIN utilities for deterministic chunking with tunable size and overlap, so the same document splits the same way as you iterate. For embeddings, you can run an in‑database model (see DBMS_VECTOR support for installed models and ONNX where documented for your RU) or call an external provider with credentials stored in the database and governed by network ACLs. Treat those decisions as versioned preferences you can update deliberately.

Keep the storage shape simple: one row per chunk with the content, the provenance you already carry (source, timestamps, tenant), and a VECTOR column for the embedding. That single‑row design makes hybrid retrieval straightforward because eligibility is a WHERE clause and semantic similarity is an ORDER BY, all in one statement.

Docs:

DBMS_VECTOR (embedding/model support) https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector1.html
DBMS_VECTOR_CHAIN (chunking and embedding helpers) https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector_chain1.html

DBMS_HYBRID_VECTOR: keyword + semantic in one call

DBMS_VECTOR_CHAIN and DBMS_VECTOR handle pure vector workflows. When your retrieval also needs full‑text keyword matching—relevance ranking by term frequency, Oracle Text lexer rules, or multi‑language tokenization—DBMS_HYBRID_VECTOR adds that layer. The package provides a JSON‑based SEARCH API that searches by keywords and vectors against hybrid vector indexes. By integrating traditional keyword‑based text search with vector‑based similarity search in a single call, it can improve recall for queries where either approach alone misses relevant results.

Use DBMS_HYBRID_VECTOR when:

Your corpus has mixed-quality writing (exact keywords matter for short queries, semantic similarity matters for paraphrased ones).
You support multiple languages with different stemming rules that Oracle Text already handles.
You want a single managed search API rather than hand-tuned SQL merging of separate text and vector result sets.

For pure vector retrieval with business-predicate filters, the WHERE + VECTOR_DISTANCE pattern in the section below is simpler and sufficient. Reserve DBMS_HYBRID_VECTOR for the cases where keyword recall meaningfully improves results on your corpus—measure both before committing to the more complex path.

Doc:

DBMS_HYBRID_VECTOR https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_hybrid_vector1.html

Choosing an index: HNSW or IVF

HNSW and IVF both deliver sub‑second nearest‑neighbor search, but they trade memory, build time, and recall differently.

HNSW builds an in‑memory navigable small‑world graph. In practice, it often yields high recall at low latency for interactive workloads, at the cost of a memory footprint that grows with corpus size and neighbor degree. Use the documented memory and accuracy advisors in DBMS_VECTOR to size the index pool and validate recall on your corpus before you build. If you are powering a help center, runbooks, or an internal knowledge base where queries arrive piecemeal and you value accuracy, HNSW is a common starting point—measure and tune for your data.

IVF clusters the vector space into partitions and probes only a subset per query. That design can lower memory footprint and sustain good throughput on very large corpora. You trade some recall and tune it by changing how many partitions to probe. If you manage millions of chunks and steady traffic, evaluate IVF operationally using Oracle’s accuracy and performance advisors.

One optimizer detail matters to plans and expectations: the optimizer uses a vector index only when your query’s distance metric matches the index’s metric. If they differ, the index will not be used and an exact search is performed. If your embeddings use cosine similarity (common for text), create the index with DISTANCE COSINE and use the same metric in ORDER BY.

Docs:

CREATE VECTOR INDEX (HNSW/IVF) https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/create-vector-index.html
DBMS_VECTOR advisors and accuracy helpers https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector1.html

Writing hybrid retrieval you can defend

Hybrid search in Oracle is just SQL. Your business predicates—tenant, source system, publish date, access level—are ordinary filters in WHERE, and VECTOR_DISTANCE ranks eligible rows by semantic similarity. Because this happens on a single table with a single row shape, the execution plan can combine a vector index for similarity with conventional indexes for filters. When configured on the relevant objects, VPD policies still apply to the session.

A typical query looks like this:

SELECT id, source, content
FROM   documents
WHERE  source = 'kb'
AND    published >= DATE '2025-01-01'
ORDER  BY VECTOR_DISTANCE(embedding, :qvec, COSINE)
FETCH  FIRST 5 ROWS ONLY;

Ensure the index’s DISTANCE metric matches the query’s metric; otherwise the vector index may not be used.

Two small details avoid surprises in production. VECTOR_DISTANCE returns smaller values for closer vectors, so you sort ascending and keep the metric explicit to match the index. Also, the query vector’s dimension and element type must match the column’s or the statement will error. In plans, you will often see the vector index satisfy similarity ordering while B‑trees or partitions satisfy filters; the optimizer’s choice depends on predicate selectivity, statistics, and metric/index alignment.

If you prefer operator syntax, the <=> operator is the cosine distance operator in 26ai. Use it only if your target version documents it; otherwise prefer VECTOR_DISTANCE(..., COSINE) explicitly:

SELECT id, source, content
FROM   documents
ORDER  BY embedding <=> :qvec
FETCH  FIRST 3 ROWS ONLY;

Docs:

A tiny end‑to‑end demo

This toy example shows the loop from rows to top‑K in a few statements. It runs on Oracle AI Database 26ai or Autonomous Database. Replace the three‑dimensional vectors with your model’s actual dimension (384/768/1536 are common), and use FLOAT32 for typical embeddings. For real data, prefer CLOB when content exceeds 4000 bytes.

1) Create a table that keeps content, provenance, and a vector together:

CREATE TABLE documents (
  id         NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source     VARCHAR2(64),
  published  DATE,
  content    VARCHAR2(4000), -- use CLOB for larger text in production
  embedding  VECTOR(3, FLOAT32)
);

2) Insert a few rows with deterministic toy vectors so results are obvious:

INSERT INTO documents (source, published, content, embedding) VALUES
  ('kb', DATE '2025-01-15', 'How to reset your password',
   TO_VECTOR('[0.10, 0.05, 0.90]', 3, FLOAT32));

INSERT INTO documents (source, published, content, embedding) VALUES
  ('kb', DATE '2025-02-10', 'How to export monthly invoices',
   TO_VECTOR('[0.80, 0.10, 0.10]', 3, FLOAT32));

INSERT INTO documents (source, published, content, embedding) VALUES
  ('runbook', DATE '2025-03-05', 'Rotate API keys every 90 days',
   TO_VECTOR('[0.15, 0.85, 0.10]', 3, FLOAT32));
COMMIT;

3) Create a cosine HNSW index and keep the metric explicit:

CREATE VECTOR INDEX docs_hnsw_idx
ON documents (embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE;

The ORGANIZATION clause names the HNSW structure as documented in the SQL Reference. Verify feature availability and any licensing considerations in your environment, and keep the query metric aligned (COSINE here) for the optimizer to consider this index.

4) Run a top‑K query. VECTOR_DISTANCE returns smaller values for closer matches, so use ascending ORDER BY. Using a literal vector for illustration (in applications, pass vectors as binds rather than literals to avoid hard parsing and ensure type/dimension alignment):

SELECT id, source, content
FROM   documents
ORDER  BY VECTOR_DISTANCE(
           embedding,
           TO_VECTOR('[0.12, 0.04, 0.92]', 3, FLOAT32),
           COSINE
         )
FETCH  FIRST 3 ROWS ONLY;

Using a bind (recommended in applications). Ensure :qvec matches the column’s dimension and element type:

-- Suppose :qvec is a VECTOR(3, FLOAT32) bind variable
SELECT id, source, content
FROM   documents
ORDER  BY VECTOR_DISTANCE(embedding, :qvec, COSINE)
FETCH  FIRST 3 ROWS ONLY;

Operator shorthand (if documented in your RU; <=> is cosine):

SELECT id, source, content
FROM   documents
ORDER  BY embedding <=> :qvec
FETCH  FIRST 3 ROWS ONLY;

5) Add a business predicate to show hybrid eligibility in action:

SELECT id, source, content
FROM   documents
WHERE  source = 'kb'
ORDER  BY VECTOR_DISTANCE(
           embedding,
           TO_VECTOR('[0.12, 0.04, 0.92]', 3, FLOAT32),
           COSINE
         )
FETCH  FIRST 2 ROWS ONLY;

If your corpus grows very large, evaluate IVF. Drop the HNSW index first. Keep a single active vector index per column to simplify planning and maintenance.

Use the documented IVF clause (NEIGHBOR PARTITIONS) in your RU’s SQL Reference:

DROP INDEX docs_hnsw_idx;

CREATE VECTOR INDEX docs_ivf_idx
ON documents (embedding)
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE COSINE;

After creating IVF, verify search‑effort controls and probe counts for your RU in the SQL Reference and DBMS_VECTOR docs; use those documented knobs to balance recall and latency.

Orchestrating the answer: in your app or with Select AI

Retrieval yields top‑K rows with provenance; you still need to compose an answer. Many teams keep this step in the application: run the retrieval SQL, log the query and bind values, and pass the returned snippets—plus id, source, and published—to an LLM. Because the context is explicit, reviewers can reconstruct what the model saw and sign off on the path.

If you prefer to orchestrate inside the database, use Select AI with a narrowly scoped AI Profile. During early review, enable SHOWSQL/EXPLAINSQL so a human can inspect generated SQL before execution. Syntax for USING/profile attributes varies by RU; follow the Select AI examples for your environment:

-- In a session with an AI Profile that scopes access
SELECT AI SHOWSQL 'List the top 3 KB articles about password resets from 2025.'
USING 'profile = <your_ai_profile>';

To prevent table data from being sent to a provider during review, use the data‑access controls documented for your RU/service tier (see Select AI examples). Enable the appropriate session/profile setting rather than relying on defaults.

Select AI can include sources in responses when configured; check the SOURCES option and relevant profile attributes for your RU, and keep SHOWSQL/EXPLAINSQL enabled until stakeholders are comfortable with the generated SQL.

Docs:

Select AI examples (SHOWSQL, EXPLAINSQL, object scoping, data access) https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/examples-using-select-ai.html

Operating in production shape

Demos are short; production is measurement and repeatability. For HNSW, memory depends on graph parameters and corpus size. Use DBMS_VECTOR memory advisors before large builds so you can size the index pool without starving the buffer cache, and measure recall and latency as you adjust graph parameters. For IVF, recall depends on partitioning and probe counts. Use accuracy helpers to compare index settings against exact search on a representative sample, and decide whether a point of recall is worth the latency trade.

Plan hygiene matters. Keep the query metric aligned with the index metric, and gather statistics after bulk loads so the optimizer can choose the intended plan. A minimal stats call looks like this:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'DOCUMENTS');
END;
/

BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(USER, 'DOCS_HNSW_IDX');
END;
/

Gather index stats after index builds or rebuilds so the optimizer can cost similarity ordering accurately.

If you evolve index settings or dimensions, make DDL idempotent and, where your release supports it, prefer online rebuilds to avoid unnecessary outages. Article_07 covers schema and index evolution strategies.

Governance continuity does not require special workarounds. When policies are configured on the relevant objects, VPD continues to filter rows, Unified Auditing can capture activity, and Data Redaction can mask columns that should not appear in clear text. As with any sensitive path, set MODULE and ACTION at session start, run your retrieval, and make evidence easy to find in audit views. Consider tagging vector queries with a distinct ACTION (for example, RAG_RETRIEVAL) to simplify Unified Auditing filters.

Docs:

DBMS_VECTOR advisors and accuracy reporting https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector1.html
Security guide landing (VPD, Unified Auditing, Data Redaction, TDE) https://docs.oracle.com/en/database/oracle/oracle-database/26/dbseg

Version scope and environment notes

The VECTOR data type, VECTOR_DISTANCE, ANN vector indexes (HNSW/IVF via CREATE VECTOR INDEX), DBMS_VECTOR, DBMS_VECTOR_CHAIN, and the Select AI features shown here require Oracle AI Database 26ai or Autonomous Database. If you are on 19c, you can persist arrays as JSON or BLOB and compute distances in the application, but you do not have native vector columns, vector indexes, or VECTOR_DISTANCE. Always verify your exact RU or service tier for option names, index clauses, and package signatures before you automate builds. If you use external embedding providers, configure credentials and network ACLs before calling them from the database. Ensure the query vector dimension and element type match the column definition; otherwise the statement will error.

Out of scope

This article stays focused on storing vectors, building the two ANN index types, writing hybrid retrieval SQL, and keeping the answer path reviewable. It does not cover Oracle Text, summarization prompts, or application frameworks. For NL2SQL hardening and AI Profile design, see Article 5. For safe schema and index evolution, see Article 7.

Try it next

Load 50–100 real chunks from your docs, store embeddings in a VECTOR column, and build an HNSW index; compare recall and latency before and after switching to IVF on the same corpus.
Turn on SELECT AI SHOWSQL for the answer step so reviewers can sign off on generated SQL before execution.

References

Oracle AI Vector Search overview https://docs.oracle.com/en/database/oracle/oracle-database/26/vecse/overview-ai-vector-search.html
VECTOR type, TO_VECTOR, and VECTOR_DISTANCE https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/vector.html https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/to_vector.html https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/vector_distance.html
CREATE VECTOR INDEX (HNSW/IVF) https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/create-vector-index.html
Querying with similarity and hybrid predicates https://docs.oracle.com/en/database/oracle/oracle-database/26/vecse/query-data-similarity-and-hybrid-searches.html
DBMS_VECTOR and DBMS_VECTOR_CHAIN https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector1.html https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector_chain1.html
Select AI examples (SHOWSQL, EXPLAINSQL, object scoping, data access) https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/examples-using-select-ai.html
Security guide landing (VPD, Unified Auditing, Data Redaction, TDE) https://docs.oracle.com/en/database/oracle/oracle-database/26/dbseg

Safer NL2SQL with Select AI and AI Profiles

Mark Nelson — Mon, 25 May 2026 16:30:20 +0000

Key Takeaways

AI Profiles bind the model to a defined list of database objects. With enforce_object_list enabled, generated SQL cannot reach tables you did not explicitly name — scope is a constraint, not just a suggestion.
SHOWSQL returns the candidate SQL without executing it, using schema metadata rather than table data. You see what the assistant intends before any data moves or any query runs.
EXPLAINSQL surfaces the model's reasoning — the join choices, date windows, and grouping logic — so reviewers can catch wrong assumptions before they become surprises in a result set.
DISABLE_DATA_ACCESS keeps early experiments in a prompt-only lane: metadata-driven inspection continues, but actions that would send table data to the model are blocked until you re-enable them.

Natural-language-to-SQL is fast until it guesses your schema, misreads a join, or touches the wrong table. On Oracle, you don’t have to accept that trade-off. AI Profiles bind provider and model to a defined set of database objects; Select AI adds database-native, inspectable actions so you can review intent and generated SQL before anything runs. In practice, you scope a profile, inspect with SHOWSQL and EXPLAINSQL, capture feedback, and only then execute. You keep the speed of NL2SQL without giving up control.

This article builds on the managed MCP posture from Article 4, where we put a governed action surface in front of assistants. Here, we add an Oracle-native reasoning loop: Select AI’s profiles and actions make the model’s intent reviewable and controllable inside the database.

Version scope and sandbox assumptions

Select AI is available in Oracle Database 26ai and in Autonomous Database (Serverless, Dedicated, and Cloud@Customer). The examples assume Select AI is enabled, a provider credential exists (for example, created with DBMS_CLOUD for external providers), and any required network ACLs are in place. The HR sample schema is used for illustration with read-only access, and table/owner casing must match your environment. You’ll need EXECUTE on DBMS_CLOUD_AI and access to the referenced credential. Profiles are created via DBMS_CLOUD_AI.CREATE_PROFILE and take effect for the current session when set with DBMS_CLOUD_AI.SET_PROFILE. Supported providers and models vary by platform and region; consult the Providers and Models page for your environment.

Prompts that include punctuation or special characters parse more reliably when enclosed in single quotes (for example, SELECT AI SHOWSQL '…';). Some prompts work unquoted; if parsing fails, add quotes. All examples below use quoted prompts.

Action tokens and profile JSON keys can be release-sensitive; confirm exact spellings and supported values in your target release. For current behavior, providers/models, action syntax, conversations, and end-to-end examples, see the Select AI docs:

Overview: https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/select-ai-about.html
Providers and models: https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/select-your-ai-provider-and-llms.html
Examples (profiles, actions, scoping, data-access toggle, feedback): https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/select-ai-examples.html
Conversations and action syntax: https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/select-ai-enable-conversations.html

Why reasoning quality matters for database AI

LLMs know SQL patterns; they don’t know your database. Ask, “How many employees were hired last quarter by department?” and an open-ended model can guess a table name, pick the wrong join key, or hard-code a date window that doesn’t match your calendars. The remedy is twofold: make the model schema-aware, and force an inspection step before anything executes.

AI Profiles handle schema awareness by binding the provider, model, and an object list the model may consider. Review then happens inside the database with SELECT AI SHOWSQL (candidate SQL) and SELECT AI EXPLAINSQL (rationale). That combination—scope plus inspection—turns NL2SQL from a clever demo into a workflow you can standardize. Per docs, for generation and inspection, SHOWSQL and EXPLAINSQL use database metadata (schemas/tables/columns/comments) rather than table contents by default. This keeps them safe defaults during review; consult docs for any action-specific exceptions in your release.

AI Profiles in practice: provider, credential, model, and scope

An AI Profile is a database object created with DBMS_CLOUD_AI.CREATE_PROFILE. It specifies which provider and model to use, which credential authorizes access, and which database objects are in bounds. The object_list does double duty: it gives the model the vocabulary it needs and, when enforced, constrains generation to the allowed objects. Profiles are selected per session with DBMS_CLOUD_AI.SET_PROFILE; after that, SELECT AI actions in the session use the bound provider/model and respect your scoping choices.

The following example enables HR headcount questions without inviting schema drift. The model string is illustrative—choose a provider/model combination supported in your environment and release.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'HR_READONLY',
    attributes   => '{
      "provider": "openai",
      "credential_name": "OPENAI_CRED",
      "model": "gpt-4o-mini",
      "object_list": [
        {"owner":"HR","name":"EMPLOYEES"},
        {"owner":"HR","name":"DEPARTMENTS"}
      ],
      "enforce_object_list": true,
      "case_sensitive_values": true
    }'
  );
END;
/
EXEC DBMS_CLOUD_AI.SET_PROFILE('HR_READONLY');

Notes:

Replace provider, model, and credential with values supported in your release. Supported providers vary by platform and region; check the Providers and Models page.
Verify attribute keys and supported values (for example, case_sensitive_values) against your release; use JSON booleans (true/false), not quoted strings.

A few practical notes help avoid surprises. Profiles are session-scoped, so call DBMS_CLOUD_AI.SET_PROFILE in each session that will use SELECT AI (new SQL Developer tabs, pooled connections, and so on). Creating or using profiles requires appropriate privileges (for example, EXECUTE on DBMS_CLOUD_AI), access to the referenced credential, and any needed network ACLs. The object list can span multiple schemas; execution still honors your roles and any VPD policies. Keeping "enforce_object_list": true curbs schema drift, though the constraint isn’t absolute—if your environment uses synonyms or views, validate with SHOWSQL that generated SQL stays within intended objects. The case_sensitive_values attribute steers how string literal comparisons are produced; if your text data is mixed-case and recall matters more than exact match semantics, consider setting it to false while confirming the exact predicate shape your chosen provider/model generates. For example, with "case_sensitive_values": false a generated comparison might use a case-insensitive form (such as UPPER(e.email) = UPPER(:email)), but exact SQL varies by model and release. Finally, attribute keys such as enforce_object_list, case_sensitive_values, and any automated scoping modes are release-sensitive; verify names and supported values against current docs before relying on them.

Inspectable actions: SHOWSQL first, then EXPLAINSQL if needed

Once a profile is active, you can ask natural-language questions with database-native actions designed for review. SHOWSQL returns a candidate SQL statement without running it. Generation uses metadata—schemas, tables, and columns—rather than table contents.

SELECT AI SHOWSQL 'how many employees were hired last quarter by department?';

EXPLAINSQL summarizes the model’s intent so you can catch wrong assumptions before execution. Like SHOWSQL, it relies on metadata, not table data.

SELECT AI EXPLAINSQL 'how many employees were hired last quarter by department?';

Together, these two actions keep review and execution separate. SHOWSQL gives you something concrete to approve or adjust; EXPLAINSQL tells you why the model chose certain filters or join keys. Use them as your default path during early adoption. A third action, NARRATE, answers in natural language and can incorporate data when enabled; reserve it for contexts where your data-sharing posture allows model access to results and you have documented that decision (docs: Examples page).

Hardening the path: scoping, case handling, and data minimization

Start with guardrails. Enforce the object list so the model stays inside approved tables or views. Decide on case handling up front so string comparisons behave predictably. And be explicit about what data, if any, can leave the database during NL2SQL interactions.

Oracle provides an administrative toggle for data transfer. Calling DBMS_CLOUD_AI.DISABLE_DATA_ACCESS blocks actions that would send table data or results to the model. Metadata-only flows—SHOWSQL and EXPLAINSQL—continue to work when disabled. Data-bearing actions (for example, NARRATE, and in some releases certain chat or result-enriched flows) are blocked until re-enabled. See the Select AI docs for the per-release action matrix.

EXEC DBMS_CLOUD_AI.DISABLE_DATA_ACCESS;

-- Still allowed: metadata-driven candidate SQL (no data sent)
SELECT AI SHOWSQL 'total headcount by department?';

-- Expected to raise an error while disabled (data-bearing action)
SELECT AI NARRATE 'total headcount by department?';

EXEC DBMS_CLOUD_AI.ENABLE_DATA_ACCESS;

Treat this toggle as a policy switch you can document and audit. When disabled, reviewers can still shape and approve SQL; when enabled, teams can opt into natural-language answers in contexts where that’s appropriate. Roles, VPD, and auditing continue to apply to any approved SQL you run, regardless of whether the SQL was generated by Select AI (docs: Overview and Examples).

Guardrails to standardize in your runbooks: keep enforce_object_list enabled; choose case_sensitive_values deliberately; default to SHOWSQL/EXPLAINSQL during review; and use the data-access toggle to separate prompt-only review from data-bearing actions.

Quick scope test you can try

With the HR_READONLY profile above (only EMPLOYEES and DEPARTMENTS in scope), ask something that tempts the model to reach for HR.LOCATIONS:

-- LOCATIONS is out of scope; we expect generation to stay within allowed objects
SELECT AI SHOWSQL 'list each department with its city and headcount';

Review the candidate. With enforce_object_list on, generation is constrained to the allowed objects. If the candidate includes a disallowed object (for example, HR.LOCATIONS), do not approve it—either change the question or explicitly add the needed object to the profile and regenerate. Scope isn’t a security bypass; it’s a generation boundary that works best when the object list is precise. Synonyms and views can influence behavior, so verify the metadata your profile exposes. When you want to encapsulate joins and filters deliberately, consider scoping to views rather than base tables.

A small, governed NL2SQL demo

Here is a path your team can adopt without changing how you secure or audit the database. First, create and set a narrow profile; the HR_READONLY example above is sufficient for headcount questions, and keeping enforce_object_list enabled prevents drift to unapproved tables. Next, inspect with SHOWSQL and, when the intent needs clarification, EXPLAINSQL. For the HR schema, a typical review might begin with a candidate:

-- Candidate SQL; not executed
SELECT AI SHOWSQL 'list the number of employees hired in the last 90 days by department with department name';

If the candidate looks structurally right but relies on SYSDATE - 90 and you need calendar quarters instead, either edit the date predicate yourself or nudge the prompt and generate another candidate. When you want to see the model’s assumptions—date windows, join keys, grouping logic—ask for a short rationale:

-- Short rationale to help review
SELECT AI EXPLAINSQL 'list the number of employees hired in the last 90 days by department with department name';

After review, execute the approved SQL manually under a read-only role. Generated or not, it is just SQL, so your roles, VPD, and unified auditing remain in force.

Feedback and evaluation loops

You’ll learn faster if you keep lightweight records of what you approved and why. A tiny table works well:

CREATE TABLE ai_sql_feedback (
  id             NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  asked_at       TIMESTAMP DEFAULT SYSTIMESTAMP,
  profile_name   VARCHAR2(128),
  nl_question    CLOB,
  generated_sql  CLOB,
  corrected_sql  CLOB,
  rating         NUMBER, -- 1–5
  notes          CLOB
);

Record your decision after each review round:

INSERT INTO ai_sql_feedback (profile_name, nl_question, generated_sql, corrected_sql, rating, notes)
VALUES (
  'HR_READONLY',
  'list the number of employees hired in the last 90 days by department with department name',
  :paste_candidate_sql_here,
  :paste_corrected_or_approved_sql_here,
  4,
  'Approved after switching SYSDATE-90 to quarter boundaries'
);

Oracle also exposes a built-in feedback hook so you can record positive or negative signals tied to generated SQL. See DBMS_CLOUD_AI.FEEDBACK in the Select AI docs for the current procedure signature; confirm parameter names such as sql_text versus sql_id and accepted feedback types/ratings for your release (docs: Examples).

-- Example only; verify parameter names/values for your release (e.g., sql_text vs sql_id)
BEGIN
  DBMS_CLOUD_AI.FEEDBACK(
    feedback_type => 'SQL_QUALITY',             -- verify accepted values
    rating        => 4,                         -- verify rating scale
    sql_text      => :paste_candidate_sql_here, -- or use sql_id => '...' if supported
    notes         => 'Good structure; adjusted date window to fiscal quarter'
  );
END;
/

Keep the loop simple: store the question, what Select AI proposed, what you ran, and a short note. In reviews, patterns emerge quickly—repeated date-window mistakes, common join fixes, or object_list gaps you can close in profiles. If you depend on case-insensitive matching, pilot with a small test set that validates generated predicates against your data.

What changes when you do this under managed MCP

In Article 4 we hosted an MCP server on Autonomous Database to make the database the control plane for actions. Select AI fits neatly into that posture. An agent can plan and call tools inside the database, but the way it generates and explains SQL remains inspectable and scoped by the active profile. Your identity, roles, private endpoints, VPD, and auditing don’t get bypassed; they’re the runway. This is series context, not a formal product integration; Select AI behavior and governance follow the documented database APIs. The operational benefit is simple: teams can standardize on "SHOWSQL first," even when an assistant is in the loop, and your audit trail still shows a normal query execution under the right account, module, and action once you approve the run.

Common failure modes, and how the Select AI pattern avoids them

Open-ended NL2SQL tends to fail the same ways: hallucinated table names, ambiguous joins, and fragile filters. An enforced object_list removes most schema drift by design. SHOWSQL gives reviewers something concrete to approve or fix, and EXPLAINSQL surfaces incorrect assumptions before they become surprises in production. When data sharing is sensitive or still under review, DISABLE_DATA_ACCESS keeps experiments in the prompt-only lane. None of this is exotic. It is a repeatable workflow you can hand to a junior developer—or an assistant—and expect the same outcome: first we scope; then we inspect; then we run; and we prove it in logs and policy after the fact.

What comes next

This same discipline carries into retrieval-augmented generation. In Article 6, we will combine Select AI with Oracle AI Vector Search so answers are grounded in embeddings stored next to the business data itself. You’ll use the same pattern: bind provider and model with a profile, scope what’s visible, inspect what will run, and keep governance in the path.

A tiny sequence to keep on a team wall

Closing

NL2SQL doesn’t have to be a black box. On Oracle, you bind the model and the data surface with AI Profiles, inspect what the model intends with SHOWSQL and EXPLAINSQL, and execute only when you’re satisfied—under the same roles, VPD, and auditing you already trust. Start narrow. Keep enforce_object_list on. Use the data-access toggle when you’re in prompt-only mode. Log decisions. The result is faster iteration with guardrails you can explain—and prove.

Managed MCP in Autonomous AI Database: remote, governed tools per database

Mark Nelson — Thu, 21 May 2026 10:32:46 +0000

This is article 4 of 8 in my Oracle Database Skills series.

Key Takeaways

Managed MCP moves the action surface into the database itself. Tools run under real database identities with existing network controls, VPD policies, and audit trails already in force — no separate trust stack to build.
Custom tools are database objects. You define the logic in PL/SQL and register it with DBMS_CLOUD_AI_AGENT.CREATE_TOOL, so governance travels with the tool definition rather than depending on each caller to do the right thing.
The five questions security teams ask — who acted, under which identity, from what network path, against which tool, and where is the audit record — are answerable by design when the endpoint is managed by the database.

Most teams begin where Article 3 ended: a single developer running a local MCP (Model Context Protocol) server to prove out tools and workflows. That’s perfect for a solo experiment and brittle the moment work becomes shared. As soon as you put an IDE on a shared sandbox or add a second teammate, you need identity that isn’t “whoever is at the laptop,” network boundaries that aren’t home Wi‑Fi, and an audit trail your security team can actually review. Autonomous AI Database’s managed MCP server gives you those primitives without rebuilding your trust stack. Each database exposes its own HTTP MCP endpoint. Clients authenticate as a database user. Tools are defined and governed inside the database. Existing controls—roles, Access Control Lists (ACLs) or Private Endpoint reachability, Virtual Private Database (VPD)/redaction, and Unified Auditing—stay in the path for every tool call.

If you liked the local model—small, named tools; least‑privilege credentials; proof after the fact—you keep it. What changes is location and blast radius. The server runs with the database service. Calls execute with a database user’s roles. Network policy and auditing you already rely on continue to apply over streamable HTTP.

Sources: consult the Autonomous Database MCP documentation for concepts, enablement, security, and troubleshooting:

Version scope and assumptions

This article focuses on Autonomous Database Serverless (Autonomous AI Database) with its managed, per‑database HTTP MCP endpoint and on custom tool registration with DBMS_CLOUD_AI_AGENT. It does not cover on‑premises databases, self‑hosted proxies, or alternative deployment topologies. For authentication, follow the documented OAuth and short‑lived bearer token flows supported at publish time. The examples assume you can modify an Autonomous AI Database in Oracle Cloud Infrastructure (OCI) and create PL/SQL in your schema. Private Endpoint deployments require clients to run within, or be routed into, the VCN. Availability is documented as exclusive to Oracle Autonomous AI Database (versions 26ai and 19c) on the “About MCP Server” page; verify your environment against current docs before starting.

What you’ll need (prerequisites)

An Autonomous AI Database (Serverless) you can modify in OCI.
Permission in OCI to update free‑form tags on that database (enablement uses a tag).
A database user to authenticate to MCP (least‑privilege strongly recommended).
Database privileges to create/compile PL/SQL in your schema and to execute DBMS_CLOUD_AI_AGENT (DBA can grant EXECUTE on the package).
Network posture set appropriately:
- Public endpoint constrained by ACLs to known IPs/CIDRs, or
- Private Endpoint with clients running inside or routed/peered into the VCN.
An MCP‑aware client that supports streamable HTTP (e.g., Claude Desktop via mcp-remote; VS Code with Cline). Ensure the client’s transport is set to streamable HTTP per its configuration schema.
To view audit trails, privileges such as AUDIT_VIEWER (or equivalent access to UNIFIED_AUDIT_TRAIL) and any required unified audit policies per your security team.

Why move from local to managed

Local MCP gets you speed but leaves basic enterprise questions unanswered: Who ran this? Under which roles? From where? What policy filtered the result? Where’s the audit record? A managed MCP endpoint answers them by design. Identity is a database user, so tools run with that user’s roles and any VPD or redaction policies. Network is first‑class: public endpoints honor ACLs; Private Endpoint confines reachability to your VCN. Evidence lives with the data in Unified Auditing, and—if your organization uses it—Oracle Data Safe can centralize review. You don’t assemble a separate trust stack for assistants; you reuse the one already protecting your data.

Enable the per‑database MCP endpoint

Enabling MCP is an OCI operation. You turn it on per database with a free‑form tag. After a brief delay, the database details page shows the MCP URL. Use that exact URL; Private Endpoint deployments will reflect your VCN in the hostname. The documented public endpoint pattern is:

https://dataaccess.adb.{region-identifier}.oraclecloudapps.com/adb/mcp/v1/databases/{database-ocid}

For Private Endpoint databases, the documented pattern uses the database Private Endpoint hostname prefix:

https://{hostname_prefix}.adb.{region-identifier}.oraclecloudapps.com/adb/mcp/v1/databases/{database-ocid}

In the OCI Console, open your Autonomous AI Database (Serverless) and add this free‑form tag:

Name: adb$feature
Value: {"name":"mcp_server","enable":true}

Tighten network posture before you connect. For public endpoints, restrict inbound IPs with an ACL. For Private Endpoint, keep the MCP URL accessible only within your VCN; clients must run in or have routed/peered reachability to that network. The enablement and security pages in the docs provide step‑by‑step screens for both configurations.

Authenticate to the managed MCP endpoint

The managed server accepts OAuth or short‑lived bearer tokens as documented. Tokens are issued per database and presented on every request as a bearer header over streamable HTTP.

Token endpoint path (documented): /adb/auth/v1/databases/{database-ocid}/token
Present the token in client requests as: Authorization: Bearer <token>
Tokens are typically valid for approximately one hour; confirm the current duration and request body in the Use MCP Server page before automating refresh.

Client configuration varies slightly:

Claude Desktop via mcp-remote: configure a remote MCP server using your database’s MCP URL and add the Authorization: Bearer ... header in the bridge’s HTTP headers field. Set the transport to streamable HTTP as required by your client (for example, a setting named streamable-http or streamableHttp).
VS Code with Cline: add a remote HTTP MCP server pointing to the MCP URL and set Authorization: Bearer ... in the request headers section. Ensure the transport is explicitly set to streamable HTTP.

A minimal Cline-style configuration looks like this, with placeholders for your database URL and bearer token:

{
  "mcpServers": {
    "adb-managed-mcp": {
      "type": "streamableHttp",
      "url": "https://dataaccess.adb.{region-identifier}.oraclecloudapps.com/adb/mcp/v1/databases/{database-ocid}",
      "headers": {
        "Authorization": "Bearer <token>"
      }
    }
  }
}

If your database uses a Private Endpoint, make sure the client host has network reachability into the VCN. Valid credentials against an unreachable hostname still fail.

Publish a custom tool the database can govern

Unlike local MCP servers that host code beside the client, the managed server looks up tools defined in the database. You create small, purposeful functions in your schema and register them with DBMS_CLOUD_AI_AGENT.CREATE_TOOL. That keeps control centralized: the definitions live in the database, metadata is queryable from views, and execution inherits database roles and policy.

To keep the example easy to reason about, we’ll publish a read‑only tool that returns a paginated JSON array of objects in a given schema that are visible to the executing database user and are not in Oracle‑maintained schemas. This variant excludes Oracle‑maintained schemas using ALL_USERS.ORACLE_MAINTAINED. The Oracle docs also include a sample that filters on ALL_OBJECTS.ORACLE_MAINTAINED = 'N'; either approach works on Autonomous Database.

First, create and compile the function:

CREATE OR REPLACE FUNCTION list_objects_json (
  p_schema IN VARCHAR2,
  p_offset IN NUMBER DEFAULT 0,
  p_limit  IN NUMBER DEFAULT 20
) RETURN CLOB
AS
  v_json  CLOB;
BEGIN
  SELECT COALESCE(
           JSON_ARRAYAGG(
             JSON_OBJECT(
               'SCHEMA_NAME' VALUE owner,
               'OBJECT_NAME' VALUE object_name,
               'OBJECT_TYPE' VALUE object_type
             ) RETURNING CLOB
           ),
           '[]'
         )
    INTO v_json
    FROM (
      SELECT o.owner, o.object_name, o.object_type
      FROM   all_objects o
      WHERE  o.owner = UPPER(p_schema)
      AND    EXISTS (
               SELECT 1
               FROM   all_users u
               WHERE  u.username = o.owner
               AND    u.oracle_maintained = 'N'
             )
      ORDER  BY o.object_name
      OFFSET p_offset ROWS FETCH NEXT p_limit ROWS ONLY
    );
  RETURN v_json;
END;
/

Check for compilation errors before registering a tool:

SHOW ERRORS FUNCTION list_objects_json;

-- Or:
SELECT name, type, line, position, text
FROM   user_errors
ORDER  BY name, sequence;

Next, register the function as an MCP tool. Use the attribute names exactly as shown in the documentation for DBMS_CLOUD_AI_AGENT.CREATE_TOOL.attributes; mismatches can cause registration or invocation errors. Common keys include instruction, function, and tool_inputs. For this example, rely on the PL/SQL defaults defined in the function and do not include unverified default values in the tool_inputs JSON.

BEGIN
  DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
    tool_name  => 'LIST_OBJECTS',
    attributes => q'~{
      "instruction": "Return a JSON array of objects visible to the executing database user for the given schema (excluding Oracle-maintained schemas). Always respect p_limit and p_offset.",
      "function": "LIST_OBJECTS_JSON",
      "tool_inputs": [
        { "name": "p_schema", "type": "string", "required": true,  "description": "Schema name (case-insensitive)" },
        { "name": "p_offset", "type": "number", "required": false, "description": "Row offset for pagination" },
        { "name": "p_limit",  "type": "number", "required": false, "description": "Max rows to return" }
      ]
    }~'
  );
END;
/

Verify that registration succeeded by querying the registry view. Column sets differ by version, so keep the check simple:

SELECT tool_name
FROM   user_cloud_ai_agent_tools
ORDER  BY tool_name;

If you see “insufficient privileges,” ask your DBA to grant EXECUTE on DBMS_CLOUD_AI_AGENT to your user and retry.

Note: DBMS_CLOUD_AI_AGENT supports both custom PL/SQL‑backed tools and built‑in tool types. For the current list of built‑in types and parameters, see the package reference:

https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/dbms-cloud-ai-agent-package.html

Also note: MLE JavaScript is not supported for MCP tools. Java can be used via PL/SQL calling Java stored procedures when JAVAVM is enabled on the database (see the docs for details).

Connect a client and try the tool

Once authentication works and the tool is registered, connect an MCP‑aware client and ask it to use the tool. In Claude Desktop, add a remote MCP server that points to your database’s MCP URL, include the Authorization: Bearer <token> header, and set transport to streamable HTTP per the client’s schema. In a fresh chat, prompt the assistant: “Use the LIST_OBJECTS tool to list 5 objects in the HR schema.” You should see the assistant invoke the tool and stream back JSON. In VS Code with Cline, add the same MCP URL in the extension’s settings, supply the same header, and ensure the streamable HTTP transport is selected; then request LIST_OBJECTS with p_schema=HR and p_limit=5.

A representative response from the tool looks like this (truncated):

[
  { "SCHEMA_NAME": "HR", "OBJECT_NAME": "COUNTRIES", "OBJECT_TYPE": "TABLE" },
  { "SCHEMA_NAME": "HR", "OBJECT_NAME": "EMPLOYEES", "OBJECT_TYPE": "TABLE" },
  { "SCHEMA_NAME": "HR", "OBJECT_NAME": "JOBS", "OBJECT_TYPE": "TABLE" }
]

On Private Endpoint databases, run those clients from a host inside the VCN or with routed/peered access. Lack of reachability is the most common reason a seemingly correct configuration fails.

Observe the audit trail (roles permitting)

Unified Auditing on Autonomous Database records activity under your database user. Exact fields and event names depend on your version and your organization’s audit policy. With appropriate privileges (for example, AUDIT_VIEWER or equivalent access to UNIFIED_AUDIT_TRAIL), you can review a time‑bounded window of recent activity:

SELECT event_timestamp, dbusername, action_name, return_code, obj_privilege, unified_audit_policies
FROM   unified_audit_trail
WHERE  event_timestamp >= SYSTIMESTAMP - INTERVAL '15' MINUTE
AND    UPPER(dbusername) = UPPER(USER)
ORDER  BY event_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

Autonomous Database retains unified audit data for about 14 days by default; if you need longer retention or centralized reporting, integrate Oracle Data Safe. When you author VPD or audit policies for MCP, do not rely on SESSION_USER. Use MCP context attributes instead—for example, sys_context('MCP_SERVER_ACCESS_CONTEXT','USER_IDENTITY') as shown in the Security docs. Some concept pages may refer to the MCP context container using slightly different naming in prose; follow the exact string in the Security examples. Your security team may also enable additional unified audit policies to capture MCP‑specific attributes.

Operate safely and clean up

Treat tools as product surface area. Keep them small and read‑heavy at first; add write paths only when you can articulate least privilege and validate inputs. Prefer schemas you own to avoid accidental privilege sprawl. Version tool registrations as code and require review before deployment.

When a tool is no longer needed, remove both the registration and the underlying function:

BEGIN
  DBMS_CLOUD_AI_AGENT.DROP_TOOL(tool_name => 'LIST_OBJECTS');
END;
/

DROP FUNCTION list_objects_json;

As you expand usage, periodically re‑check ACLs on public endpoints and confirm Private Endpoint posture remains correct, especially after network changes. Rate limiting is enforced by the managed service; consult current release notes for behavior and limits.

Troubleshooting

Most issues fall into a few buckets:

Compilation and registration: fix PL/SQL errors (SHOW ERRORS, USER_ERRORS) before registering. Ensure your user has EXECUTE on DBMS_CLOUD_AI_AGENT. If registry queries fail with unknown columns, select only tool_name and consult versioned docs for metadata.
Connectivity: verify you’re using the MCP URL exactly as shown in the console. For public endpoints, confirm your IP/CIDR is in the database ACL. For Private Endpoint, confirm the client host has routed/peered access to the VCN.
Authentication: obtain tokens from /adb/auth/v1/databases/{database-ocid}/token and present them as Authorization: Bearer <token>. Copy the exact request body and lifetime from the current docs. Place headers where your client expects them.

If a client claims to connect but tool calls fail, double-check that attribute keys in CREATE_TOOL match the documentation exactly; case mismatches cause registration or invocation errors.

What comes next

With a per-database MCP endpoint, one governed tool, and a way to verify activity in Unified Auditing, you can expand safely. Start with a small set of read-heavy tools your team actually needs. Introduce narrow write paths with explicit inputs and clear privileges. Treat tool definitions like code: review them, version them, and track changes.

The next article moves from action surfaces to reasoning scope: Select AI and AI Profiles. That is where you decide what natural-language-to-SQL should be allowed to see, how generated SQL should be inspected, and how to keep an assistant’s query generation inside a deliberate profile instead of a broad database connection.

Freshness note

Content validated against the Autonomous Database MCP documentation on 2026-05-08. Re-check the Use MCP Server and Security pages for any changes to endpoint paths, JSON key names, token lifetimes, and audit details before publishing or automating.

Controlled Oracle Actions with SQLcl MCP

Mark Nelson — Tue, 19 May 2026 12:07:51 +0000

This is article 3 of 8 in my Oracle Database Skills series.

Kay Takeaways

The assistant connects using a saved connection name, not a pasted password. Credentials stay in the SQLcl store and out of prompts, so the model sees the name but never the secret.
Restrict levels define the tool surface at runtime. Starting at the most restrictive setting limits what the assistant can do; you widen the surface deliberately as the use case earns it.
Session tagging and the MCP activity log give a DBA verifiable proof of what ran. Evidence comes from the database, not the chat transcript — that distinction matters when someone asks what the assistant actually did.

In the last piece, you routed Oracle prompts through db/SKILL.md to keep intent explicit (see Article 1 and Article 2 in this series). Now it’s time to act—locally, with guardrails, and with an evidence trail your DBA can verify. SQLcl MCP gives you that posture. You launch it as a local process (sql -mcp); it exposes a small catalog of named tools over stdio; it connects through saved connections you control; it starts in a highly restricted mode by default; and it leaves database‑native traces you can query later. That’s the right shape for early assistant experiments on a developer machine.

Citations for versioned behavior are in Oracle’s SQLcl documentation (for example, “Using SQLcl MCP Server,” “SQLcl MCP Server tools,” “Starting and managing SQLcl MCP Server,” “Restrict Levels,” “Preparing your environment,” and “Saving connections” in the 25.4 and 26.1 editions). Treat any version‑sensitive claim here as “verify against your installed docs.”

What you need before you start

SQLcl 25.2.0 or newer with MCP support
Java 17 or 21 available to SQLcl (per “Preparing your environment”)
A non‑production Oracle Database (19c+ suffices for this demo)
A least‑privilege, read‑only database user for testing
A saved SQLcl connection that includes a stored password, created with -save and -savepwd (required for non‑interactive MCP connects)
Permission to query V$SESSION for evidence, or access to an equivalent DBA‑provided view that exposes MODULE and ACTION for your sessions
If you use TNS aliases, configure Oracle Net as usual (for example, set TNS_ADMIN to your tnsnames.ora directory). If your MCP client launches sql -mcp with a custom environment, pass TNS_ADMIN there as needed.

Security note: Storing passwords is a prerequisite for non‑interactive MCP connects. Keep this to a sandbox, use a read‑only account, and lock down your local SQLcl store (commonly under ~/.dbtools) per your organization’s secrets policy.

Why SQLcl MCP is the right local action layer

A tempting shortcut is to paste credentials into a prompt and let a model “try something.” That’s exactly what you want to avoid. SQLcl MCP flips the pattern: you launch sql -mcp locally and expose a small set of structured tools. Your client can list saved connections, connect by name, run SQL, and disconnect. The MCP connect path uses saved/named connections; the tool does not accept raw credentials in the call. For non‑interactive use, the password must be saved (-savepwd). You decide which saved connections exist and what privileges they carry.

Restrict levels are SQLcl’s runtime safety gates that limit which features and commands are allowed. In MCP mode the server defaults to restrict level 4 (most restrictive) unless you override it with -R, so the surface stays small until you intentionally broaden it. That’s a posture you can explain to a DBA and adjust deliberately as you earn trust.

References: SQLcl MCP overview and startup; Saved connections; Restrict levels (25.4/26.1 docs).

How the server behaves under the hood

When your client launches sql -mcp, SQLcl starts a small server over stdio and advertises a catalog of named tools. Message envelopes vary by client, but tool names and arguments follow the SQLcl MCP documentation. As of the 26.1 docs, commonly listed tools include:

list-connections
connect
disconnect
run-sql
run-sqlcl

Some builds also include schema-information. Tool availability and argument shapes can vary by version/build; verify against your installed docs.

Connections are discovered from your local SQLcl store (for example, ~/.dbtools). To make a connection usable without a prompt, save it with a stored password. Oracle documents this as a prerequisite for hands‑off connection by name.

Restrict levels shape what the process can do. In MCP mode, the server defaults to restrict level 4 (most restrictive) unless you override it with -R. Outside MCP, generic SQLcl defaults to restrict level 0 (unrestricted) unless you pass -R; always verify the “Restrict Levels” page for your exact build.

References: SQLcl MCP tools; Saved connections; Restrict levels and -R flag (25.4/26.1 docs).

Traceability you can prove (monitoring and governance)

Before you run anything, plan how you’ll show what happened. The most portable approach is to tag your session with DBMS_APPLICATION_INFO and then confirm it in V$SESSION. The tag appears in performance tooling and helps a DBA correlate what ran and why. By default, SQLcl MCP records the execution history of requests in DBTOOLS$MCP_LOG (per Oracle docs). Access, owner/schema, and privileges vary by environment, so confirm presence and permissions with your DBA.

Tagging mechanism: DBMS_APPLICATION_INFO.SET_MODULE and SET_ACTION (documented in Oracle Database PL/SQL packages)
Evidence view: V$SESSION (privilege‑gated; ask a DBA for a scoped alternative if you lack access)
Logs: DBTOOLS$MCP_LOG records requests by default; access and retention are environment‑specific

References: DBMS_APPLICATION_INFO; V$SESSION; SQLcl MCP monitoring/logging pages (25.4/26.1 docs).

Version scope and environment notes

Keep SQLcl at 25.2.0+ for MCP features and run it with Java 17 or 21 (per “Preparing your environment”).
The tool catalog and defaults can evolve; the 25.4 and 26.1 guides capture current behavior, but verify against your installed version.
The demo assumes Oracle Database 19c or later and avoids database features tied to 26ai.
Some clients (for example, SQL Developer for VS Code) can launch and manage the MCP server for you; follow your client’s documentation for its configuration format and capabilities, which can change by version.

A small, traceable demo you can run today

This walkthrough saves a single read‑only connection, launches the MCP server, validates that the connection is discoverable, connects by name, tags its session, runs one read‑only discovery query, and disconnects. Each step includes copy‑paste commands and why the step matters.

1) Save a named connection for a read‑only sandbox user

Open SQLcl, connect once interactively, and persist the connection with a stored password so MCP can use it without a prompt.

sql /nolog
# If you connect by service name:
conn demo_ro@//host:1521/service -save demo_ro -savepwd
# If you connect by TNS alias (ensure Oracle Net is configured and tnsnames.ora is discoverable):
# conn demo_ro@ALIAS -save demo_ro -savepwd

When prompted, enter the password interactively. The -save and -savepwd flags persist the named connection (commonly in ~/.dbtools) with a stored password so MCP can use it non‑interactively. Use a sandbox‑only, least‑privilege account and follow your org’s secrets policy for local credential storage.

2) Start the MCP server (keep the default restrict level)

Launch SQLcl in MCP mode and leave the terminal open. Unless your client does this for you, this process is the local server your MCP‑aware client will talk to.

sql -mcp

By default, restrict level 4 applies. If you ever see broader capability than expected, confirm that the process was not started with a lower restrict level (for example, -R 0). For early experiments, stick with the default level 4.

3) Point your MCP‑aware client at SQLcl

Configure your client to launch sql -mcp.

Representative MCP server configuration for a desktop client. Field names and file locations vary by client; follow your client’s documentation for the exact schema.

{
  "mcpServers": {
    "sqlcl": {
      "command": "sql",
      "args": ["-mcp"],
      "env": {
        "TNS_ADMIN": "/path/to/network/admin"
      }
    }
  }
}

4) Verify that the saved connection is discoverable

Before you connect, ask the server to list saved connections. Seeing demo_ro here confirms that the server can use it non‑interactively.

Representative MCP tool call; adapt to your client’s message envelope (for example, a surrounding "type": "call_tool"), server name, and argument key names as defined by your client and the SQLcl MCP tools page for your installed version.

[
  { "tool": "list-connections", "arguments": {} }
]

If demo_ro does not appear, revisit the conn ... -save -savepwd step and make sure you created the connection under the same OS user/profile that SQLcl is using.

5) Connect by name and tag the session

Connect using the saved name, then tag the session with MODULE and ACTION so you can find it later in performance views and, if accessible, MCP logs.

Representative MCP tool call; adapt to your client’s envelope and argument names. Note: some clients use name, others connectionName or connection_name.

[
  { "tool": "connect", "arguments": { "name": "demo_ro" } },
  { "tool": "run-sql", "arguments": { "sql": "BEGIN DBMS_APPLICATION_INFO.SET_MODULE('sqlcl-mcp','schema-discovery'); END;" } }
]

Tagging is optional but strongly recommended. It creates a human‑readable breadcrumb for exactly this experiment.

6) Run one read‑only discovery query

To keep the example universal, start with a query against objects you own:

Representative MCP tool call; adapt to your client’s envelope and argument names.

{ "tool": "run-sql", "arguments": { "sql": "SELECT table_name FROM user_tables ORDER BY table_name FETCH FIRST 5 ROWS ONLY" } }

If your account has cross‑schema visibility, you can adjust the query to ALL_TABLES:

Representative MCP tool call; adapt to your client’s envelope and argument names.

{ "tool": "run-sql", "arguments": { "sql": "SELECT owner, table_name FROM all_tables WHERE owner = UPPER('<YOUR_SCHEMA>') ORDER BY table_name FETCH FIRST 5 ROWS ONLY" } }

Keep the action read‑only while you learn the surface and review the evidence with a DBA partner.

Optional: Some clients also expose run-sqlcl for SQLcl console commands (for example, DESC). At restrict level 4, many SQLcl commands are disabled; simple describe operations may work depending on your build—verify in your environment. If enabled in your version and posture, a representative call looks like:

Representative MCP tool call; adapt to your client’s envelope and argument names.

{ "tool": "run-sqlcl", "arguments": { "command": "DESC USER_TABLES" } }

Treat this as optional; the minimal surface for early tests is run-sql.

7) Disconnect when you are done

Disconnecting is good hygiene and makes your test’s boundaries clear.

Representative MCP tool call; adapt to your client’s envelope and argument names.

{ "tool": "disconnect", "arguments": {} }

Prove what happened

Evidence checks depend on privileges. If your account cannot query performance views, ask your DBA for a scoped alternative that exposes MODULE and ACTION for your sessions.

See the tag in a live session view:

SELECT module, action, username, machine
FROM   v$session
WHERE  module = 'sqlcl-mcp';

By default, SQLcl MCP records requests in DBTOOLS$MCP_LOG. You can review recent entries there as well. Presence is standard per the docs, but owner/schema and access vary by setup; in some environments the table resides under a DBA‑owned schema. Treat the query below as an example and ask your DBA for the owner or a scoped view if needed.

SELECT id, mcp_client, model, end_point_type, end_point_name, log_message
FROM   DBTOOLS$MCP_LOG
ORDER  BY id DESC
FETCH  FIRST 10 ROWS ONLY;

If neither view is available to you directly, a DBA can run the checks or provide a narrow view limited to your test user.

When something goes wrong

If the client prompts for a password, the saved connection likely lacks a stored secret. Recreate it with both -save and -savepwd so MCP can connect non‑interactively. When the tool catalog looks unfamiliar or is missing entries, you may be on a different SQLcl version; check the MCP tools page for your installed build (the 25.4 and 26.1 docs capture current behavior). If you don’t see your session tag in V$SESSION, it’s probably a privilege boundary rather than a failure to tag; ask for a scoped view or a one‑time verification. If the server shows more capability than your risk posture allows, confirm the restrict level and restart without any -R override so the default restrictive posture applies.

Why this posture earns DBA trust

DBAs reasonably resist assistants that can write to production. SQLcl MCP avoids that leap of faith. You connect through named entries you control. You default to a restrictive mode. You tag your sessions with MODULE and ACTION so activity shows up cleanly in performance tooling. And, where available, MCP logs give an after‑the‑fact record. The point is not speed; it’s reversibility and evidence. In a world where models sometimes propose creative fixes, that’s the currency that gets you permission to try again tomorrow.

What’s intentionally out of scope here

This article focuses on local SQLcl MCP on a developer workstation, read‑only sandboxes, and minimal evidence checks. It does not cover managed or remote MCP hosting, enterprise audit/policy, advanced reasoning patterns (for example, Select AI or AI Profiles), vector‑native RAG, or change‑management workflows. Those topics are addressed next when we move beyond a single machine.

Where this leaves you next

You now have a local, developer‑friendly path for safe, traceable Oracle actions. Previously in this series: Article 1 (routing) and Article 2 (route‑to‑action bridge). Next up: Article 4, where we carry these guardrails into a managed path with audit, policy, and team controls so you can scale without losing the evidence and restraint you established here.

Freshness Note: Version‑sensitive behavior (tool catalog, defaults, restrict levels) should be verified against your installed SQLcl documentation (notably the 25.4 and 26.1 editions).

Route, Don’t Flood: Using db/SKILL.md to Steer Oracle‑Aware Assistants

Mark Nelson — Fri, 15 May 2026 12:21:32 +0000

If you’ve tried to make an assistant “Oracle‑aware,” you’ve likely hit the same wall: you paste a stack of links, the model blends vendor‑neutral habits with stale Oracle guidance, and the answers get vague when you need precision. The fix isn’t more context—it’s better routing. One Oracle skill at a time, in the right order, so every next move follows Oracle’s own sequence.

In Article 1 we outlined an operating model for AI on Oracle Database: route → act → trust. This piece goes deep on the first verb. It shows how to use db/SKILL.md in the public Oracle Database Skills repository as your front door; how to route by persona or by task; and how to enforce progressive discovery so your assistant loads exactly one file at a time. This is a no‑execution article; we stay in the routing lane and leave tools to Article 3 (SQLcl MCP). Prerequisite: you only need to browse https://github.com/oracle/skills in a web browser.

Why routing beats context dumping

Dumping a pile of links into a prompt feels thorough, but it blurs version lines and lets generic “SQL” patterns overrule Oracle‑specific guidance. It also bloats token budgets without improving the next decision. db/SKILL.md fixes this by providing a maintained map of Oracle Database skills, pointing you to the right starting file for common jobs, and encoding short, opinionated sequences for multi‑step workflows. The goal is not to read everything; it’s to pick the next file, digest it, and decide whether one more file is warranted. When you’re actually ready to execute a command, you’ve left routing and entered “act” (Article 3 covers SQLcl MCP).

Treat db/SKILL.md as the front door

db/SKILL.md is the router for the Database domain. It opens with a routing table and guidance to keep context tight—begin at the table, then read only the specific file or category you need.

It also shows the shape of the Database skills, including these directories:

db/agent
db/features
db/frameworks
db/performance
db/security
db/devops
db/migrations
db/sqlcl

Two parts matter most on your first pass. First, the file highlights concrete entry points for real jobs—where performance triage begins, where SQLcl basics and MCP server entries sit (setup is deferred to Article 3), how to approach schema migrations, and which agent behaviors (like schema discovery) must be understood before you write anything. Second, it outlines common sequences that act as guardrails. Examples include RAG (skills under db/features: ai‑profiles → vector‑search → dbms‑vector), slow‑query diagnosis (skills under db/performance: explain‑plan → wait‑events → optimizer‑stats → awr‑reports), and agent‑safe schema change (skills under db/agent and db/migrations: schema‑discovery → destructive‑op‑guards → idempotency‑patterns → schema‑migrations). There’s also an MCP setup path you’ll use later when you move from routing to action: sqlcl‑basics → a least‑privilege/privilege‑management skill in db/security → sqlcl‑mcp‑server.

Starting here changes how your assistant behaves. Instead of grabbing whatever looks related on the internet, it asks, “What’s the next Oracle‑authored file?” The result is fewer wrong turns and shorter, more reviewable prompts.

Route by persona when you’re scoping a project

When you’re defining scope rather than responding to a ticket, match your role and load one file. Don’t skim the whole repository first—route, read, and decide.

App developer

If you’re wiring a service to Oracle, start with frameworks and then layer on application‑specific skills. The db/frameworks directory anchors guidance for stacks such as Spring, Django, and SQLAlchemy: connection configuration, driver/dialect selection, data type mapping, and recommended connection patterns. Once the framework is set, dip into app‑dev skills for JSON, Spatial, Text, or pooling nuances you actually need. Doing it in this order avoids needless trial and error—you adopt the Oracle‑tested path before you customize.

AI engineer

Begin in db/agent and db/features. The agent skills cover behaviors that matter when a model touches a database: discover schema before DML, guard against destructive operations, make steps idempotent so retries don’t double‑apply, and identify clients for traceability. The features skills introduce Oracle’s AI‑native building blocks—Select AI and AI Profiles for governed NL2SQL, and AI Vector Search/DBMS_VECTOR for retrieval and RAG. Version gate: check each feature’s documentation. DBMS_VECTOR and many Select AI/AI Profiles examples are documented for 26ai; several AI capabilities are also available in Autonomous Database. Do not assume 19c availability unless a skill or doc explicitly states it.

DBA

The fastest way to get Oracle‑native outcomes from an assistant is to route through db/performance and then db/security. Performance skills teach the assistant to obtain and read the plan that actually executed (for example, via DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')), interpret row sources and cardinality, and pivot at the right moment from plan reading to wait events and then, if warranted, to AWR. Security skills ground privilege design, auditing, and encryption in Oracle’s vocabulary so “least privilege” becomes an actionable design rather than a slogan.

Migration lead

Treat db/migrations and db/devops as two halves of one job. Migration skills help with assessment and translation; devops skills handle delivery mechanics such as schema change workflows, online operations, Edition‑Based Redefinition, and testing. Loading one file at a time keeps heterogeneous estates from collapsing into generic, lowest‑common‑denominator advice.

Route by task when you’re on the clock

When a ticket arrives with a single job, follow the ordered sequences in the repository. Oracle’s diagnostics and defaults are opinionated for a reason; ignoring their order usually costs time.

RAG on Oracle Database

Start with AI Profiles, because that’s where you choose a provider and model and, crucially, define which database objects the model may see. Only after scope and governance are set should you learn retrieval patterns in vector‑search and then orchestrate pipelines with DBMS_VECTOR. The payoff is a governed RAG flow that can be inspected (SHOW SQL) and tightened (OBJECT_LIST and data access controls), rather than a bag of embeddings taped onto a database.

Route (skills under db/features): ai‑profiles → vector‑search → dbms‑vector
Reference: Select AI examples (SHOW SQL, OBJECT_LIST, and data access controls): https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/examples-using-select-ai.html
Version note: Follow the feature docs. DBMS_VECTOR and many Select AI/AI Profiles examples are documented for 26ai; several AI capabilities are also available in Autonomous Database. Do not assume 19c availability unless a skill or doc explicitly states it.

Slow‑query diagnosis

Begin by reading the plan that actually ran. In Oracle that means capturing the executed plan from the cursor (for example, DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')), not relying solely on an EXPLAIN PLAN estimate. If the plan looks sane but runtime stalls, pivot to wait events. If rows and joins are misestimated, fix statistics and cardinality before widening the window with AWR.

Route (skills under db/performance): explain‑plan → wait‑events → optimizer‑stats → awr‑reports
Availability note: AWR/ASH access depends on environment and licensing.

Agent‑safe schema change

Discover what exists before you propose change, add loss‑prevention so a single bad predicate can’t harm a table, and make each step idempotent so a retry won’t double‑apply. Only then move into an audited migration workflow.

Route (skills under db/agent and db/migrations): schema‑discovery → destructive‑op‑guards → idempotency‑patterns → schema‑migrations

A compact prompt that enforces progressive discovery

A small prompt can prevent freelancing by enforcing a loop: route, read one file, summarize, decide. That discipline keeps the context precise and affordable.

Prompt

Open db/SKILL.md. I’m [persona or task].
Which single skill file should I load next, and why?
Summarize that file in 5 bullets, then propose the next decision (read another file or stop).
Do not execute any actions.

Progressive routing loop

Mini demos (no DB access, no execution)

These prompts validate routing behavior before you connect any tools. They’re deliberately constrained to “read and decide.”

Demo A: Persona‑first route for a Spring app developer

Prompt

Open db/SKILL.md. I’m wiring a Spring Boot service to Oracle.
Which single frameworks/appdev skill should I load next, and why?
Summarize that file in 5 bullets, then propose the next decision.
Do not execute any actions.

Example assistant response

Next file: A Spring Data JPA with Oracle skill under db/frameworks/.
Why: It covers Oracle-specific connection configuration, driver/dialect selection, pooling, and type mapping.

Summary:
- Recommended Oracle JDBC URL formats and service naming for Spring Boot
- Dialect/driver selection and common DataSource pitfalls
- Transaction, batching, and fetch-size options tuned for Oracle
- LOB, DATE/TIMESTAMP, and NUMBER mapping considerations
- Starter checks and links to follow-up appdev skills

Next decision:
- If you need pooling/retry/circuit specifics, read a connection/pooling skill next.
- Otherwise, stop and apply these configuration steps before proceeding.

Demo B: Task‑first route for diagnosing a slow query

Prompt

Starting from db/SKILL.md, list the ordered skill files for slow-query triage.
Load only the first file, summarize it in 5 bullets, then recommend whether to read the next file or stop.
Do not execute any actions.

Example assistant response

Ordered files:
1) The explain-plan skill in db/performance/
2) The wait-events skill in db/performance/
3) The optimizer-stats skill in db/performance/
4) The awr-reports skill in db/performance/

Loaded: explain-plan

Summary:
- Obtain the executed plan from the cursor (vs. EXPLAIN PLAN estimates) and where to find it
- Read row sources and cardinality; spot misestimates and join-order issues
- Recognize anti-patterns (forced full scans, skewed histograms, implicit conversions)
- Interpret plan notes for adaptive features and parallel decisions
- Decide when to pivot from plan analysis to wait-event inspection

Recommendation:
- Read wait-events next if the plan looks reasonable but runtime stalls.
- If the plan is clearly wrong, stop and fix plan issues before proceeding.

Notes:

The “explain‑plan” skill in this route teaches capturing and reading the actual cursor plan, not just the EXPLAIN PLAN estimate.
AWR/ASH may not be available in all environments and may require licensing.

Version scope and context‑budget discipline

Assume Oracle Database 19c as the baseline unless a skill states a higher requirement. For AI features, check per‑feature documentation: DBMS_VECTOR and many Select AI/AI Profiles examples are documented for 26ai; several AI capabilities are also available in Autonomous Database. Do not assume 19c availability unless a skill or doc explicitly states it. Label those steps clearly when you route.

Two habits keep assistants accurate and affordable:

Load one file or short index at a time, then summarize in five bullets.
Decide whether to stop, read one more, or hand off to action (SQLcl MCP in Article 3). Don’t blur routing with execution.

Why “route, don’t flood” holds up under pressure

Teams tend to bulk‑load context when deadlines loom. Ironically, progressive discovery pays off most when time is tight. db/SKILL.md compresses Oracle experience into short routes you can trust: read the executed plan before hunting waits, scope an AI Profile before experimenting with retrieval, and prove discovery and guards before you accept any schema change. Staying inside those lanes doesn’t slow you down; it eliminates the detours that burn days.

Make the front door easy to reach. The simplest path is to browse the repository in a web browser and open db/SKILL.md. If you prefer a local copy, install the Database domain directly:

npx skills add oracle/skills/db

This puts the domain files in your working directory so you can open db/SKILL.md immediately. Then set your assistant’s “Oracle Database” system prompt to begin with “Open db/SKILL.md,” and keep the progressive‑discovery loop in place until the next decision is truly to act.

Conclusion

Oracle Database Skills is not a PDF dump for models. It’s an operating method that starts with routing. Use db/SKILL.md as your first touch, load one file at a time, and stop before you wander into execution. In Article 3, we’ll introduce SQLcl MCP as the bounded action surface that pairs naturally with this approach. Until then: route → read one → summarize → decide.

Sources

Oracle Database Skills repository root: https://github.com/oracle/skills
Database router (db/SKILL.md): https://github.com/oracle/skills/blob/main/db/SKILL.md
Frameworks directory: https://github.com/oracle/skills/tree/main/db/frameworks
Performance directory: https://github.com/oracle/skills/tree/main/db/performance
Select AI examples (SHOW SQL, OBJECT_LIST, and data access controls): https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/examples-using-select-ai.html
DBMS_XPLAN.DISPLAY_CURSOR (19c): https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XPLAN.html
Licensing Information User Manual (AWR/ASH availability/licensing): https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/index.html
DBMS_VECTOR (26ai): https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_VECTOR.html

AI agents know SQL. Oracle Database Skills teach them Oracle

Mark Nelson — Wed, 13 May 2026 14:02:12 +0000

This is article 1 of 8 in my Oracle Database Skills series.

Most assistants can write SQL. That’s not the same as knowing Oracle Database. The difference shows up in production. A model that learned generic patterns can assume another vendor’s isolation semantics, borrow date arithmetic that silently miscounts in Oracle, rely on hints that “fix” one plan but fight the optimizer elsewhere, or forget Edition‑Based Redefinition (EBR) during a migration. Give that assistant a live connection and those differences turn into risk.

Oracle Database Skills change the operating model. Instead of hoping a generalist remembers every Oracle rule, you route it—one decision at a time—to Oracle‑authored, source‑backed instructions. When action is appropriate, you expose only small, named tools via Oracle MCP servers (MCP = Model Context Protocol), never a blank connection string. And you keep Oracle’s governance in the path so identity, scope, and evidence ride with every request. In short: route first, act through bounded tools, and let the database prove what happened.

Why generic SQL knowledge fails on Oracle Database

Large models generalize across vendors; your database does not. Oracle’s transaction semantics, time idioms, plan management, and governance are exactly where generic advice goes sideways.

Isolation and consistency are different in Oracle. Many public answers treat isolation levels as dials that change write visibility. Oracle’s multi‑version read consistency gives readers a clean snapshot but doesn’t use the same language or behavior as those engines. Ask a generalist assistant to “make this serializable” and you can get a non‑Oracle explanation—or an irrelevant fix. See Oracle Database Concepts on consistent reads for details.
Time math bites hard. DATE/TIMESTAMP/INTERVAL semantics and NLS/time zone interactions vary by vendor; Oracle requires vendor‑specific idioms. Skills provide tested patterns for Oracle SQL and PL/SQL so an assistant doesn’t have to guess which expression respects your NLS settings, time zones, and interval choices.
Plan stability demands a different order of operations. Oracle recommends maintaining accurate optimizer statistics and using SQL Plan Management (baselines) for stability; avoid over‑reliance on ad‑hoc hints in application code. Skills teach that progression so the assistant recommends fixes that survive the next gather‑stats job.
Governance cannot be an afterthought. “Connect and run” examples usually ignore Virtual Private Dat
abase (VPD), Unified Auditing, Fine‑Grained Auditing, Data Redaction, Transparent Data Encryption (TDE), and network ACLs/private endpoints. In Oracle, those are how you make AI access presentable to a DBA, a security reviewer, or an auditor. If you want to ship, keep those controls in the path.

Route first with Oracle Database Skills (progressive discovery)

The oracle/skills repository is not a doc dump. It’s installable operating instructions, loaded sparingly—one file at a time—so assistants (and humans) can make the next decision correctly without carrying 200 pages of context. The Database domain (oracle/skills/db) is organized the way you work: agent safety; features like Select AI, AI Profiles, and Vector Search; app frameworks; performance; DevOps and migrations; SQLcl; and security. The front door is a routing file: db/SKILL.md.

Progressive discovery narrows the model’s attention to what moves the work forward, preserves version truth, and leaves a clean review trail. Because the assistant reads only the next skill, it doesn’t mix versions or unrelated features. Skills mark Oracle Database 19c as the baseline and flag when you need Autonomous Database (Serverless) or Oracle Database 23ai/26ai features. Teams can see which Oracle sources the assistant consulted, in what order, and why.

A concrete path makes this real. Suppose you’re building retrieval‑augmented generation on Oracle. The route in db/SKILL.md steers you through Select AI profiles (to set provider and scope), then Vector Search basics (to choose the right data model and index), then DBMS_VECTOR (to make retrieval programmable). You don’t need AWR or EBR on day one. The route keeps you focused until you do.

Act through bounded tools: Oracle’s MCP surfaces

Sometimes the assistant must do more than explain. It needs to run a read‑only query, inspect a schema, or check a plan. That’s where Oracle’s MCP servers help: they expose small, named capabilities rather than hand the assistant a general‑purpose connection.

Local development: SQLcl MCP server. Built into SQLcl, it exposes a concise set of named tools—for example, to list saved connections, connect using a saved connection, and run SQL—and supports configurable “restrict levels,” with higher numbers enforcing tighter limits. As of SQLcl 25.4, the MCP server defaults to restrict level 4 (most restrictive). Confirm your version and configuration. Teams can set the most restrictive mode for local experiments. SQLcl MCP can also leave evidence when configured: it can log activity (for example, to a DBTOOLS$MCP_LOG table), set V$SESSION.MODULE and V$SESSION.ACTION to identifiable values (such as the MCP client and a client‑provided model/agent label), and tag LLM‑generated statements with a comment. See the SQLcl MCP docs linked below for version‑specific details.
Managed, governed execution: Autonomous Database (Serverless) MCP server. Enable it on a specific Autonomous Database (via an OCI free‑form tag) and you get a per‑database HTTP endpoint and authentication that serve built‑in tools or custom tools you define with DBMS_CLOUD_AI_AGENT.CREATE_TOOL. Identity and roles apply as usual. So do network ACLs and private endpoints, VPD policies, and Unified Auditing and Fine‑Grained Auditing when configured. If your team wants a controlled tool surface without hosting anything, this is the right shape: the database remains the control plane for what tools exist and who can use them.

The shared property is restraint. In both cases, the assistant gets the minimum set of named actions appropriate for the task, and Oracle‑native controls keep doing their job.

Keep Oracle governance in the path (trust and evidence)

Oracle’s governance features aren’t an appendix; they are the safety net that lets you ship. Roles and least privilege define what an assistant can reach. Network ACLs and private endpoints restrict egress. VPD enforces row‑level policies you already rely on. Unified Auditing and Fine‑Grained Auditing can be configured to record the activity you care about. Data Redaction and TDE protect sensitive data at query time and at rest. These controls are available and can be configured; they are not enabled by default in every environment.

The benefit for AI work is simple once you see it: your proof is in the database. When actions go through SQLcl MCP or the Autonomous Database (Serverless) MCP server, the same identity, scoping, and auditing options that protect your applications also protect your assistant. The question becomes less “Can we trust the agent?” and more “Show me the database evidence.”

Mini demo: install skills and open the routing door

Begin without touching a database or setting up MCP. Fetch the Database Skills domain and open the routing file. From there, pick the path that matches your role and objective.

You only need internet access, Node.js installed, and a text editor. This step does not connect to a database, handle credentials or secrets, or change any MCP restrict levels.

npx skills add oracle/skills/db

This installs the Database domain into your working directory. Open db/SKILL.md in your editor. If you’re an app developer wiring a Spring app, follow the frameworks route. If you’re an AI engineer, start with the agent and features routes (Select AI, AI Profiles, Vector) before you let any assistant write SQL. If you’re a DBA, begin with performance and security so the assistant speaks AWR/ASH/optimizer and respects governance. Keep the habit: load only the next file you need, then stop. That discipline keeps model context clean and your review surface small.

Where to find this in Oracle docs and repos

The instruction layer is the skills repo itself, with db/SKILL.md acting as the router. The controlled action layer is documented for both entry points: SQLcl MCP (restrict levels, tool catalog examples, saved connections, monitoring, activity logging and tagging) and the Autonomous Database (Serverless) MCP server (enablement via OCI free‑form tag, per‑database endpoint and authentication, custom tool publication, and governance integration). The trust layer is the familiar security stack: roles and privileges; network ACLs and private endpoints; VPD; Unified Auditing and Fine‑Grained Auditing when configured; Data Redaction; encryption. Each is a first‑class Oracle feature you can point to in docs and verify in your environment.

Source links:

Skills repo and routing (start at db/SKILL.md)
SQLcl MCP (using the server; logging/tagging; restrict levels)
Autonomous Database (Serverless) MCP server (enable and use)
Oracle Database Concepts — Consistent Reads/MVRC
Oracle SQL Language Reference — Data Types (DATE/TIMESTAMP/INTERVAL and NLS)
Optimizer statistics and SQL Plan Management
Oracle Database Security Guide (19c baseline)

Version scope and environment notes

Most skills target Oracle Database 19c as the baseline. Files that require features from Autonomous Database (Serverless) or newer Oracle Database 23ai/26ai releases—such as Select AI, AI Profiles, Vector Search, or the Autonomous Database (Serverless) MCP server—are labeled in the repo. If you work in a mixed‑version estate, treat those labels as gates and route accordingly. For hands‑on tries in this opener, you need only a local machine with git and a text editor. MCP client steps and database connectivity come later, when action is appropriate.

What’s out of scope (in this opener)

Enabling or configuring the Autonomous Database (Serverless) MCP server
Connecting to any database or handling credentials
Changing SQLcl MCP restrict levels
Configuring Unified Auditing or Fine‑Grained Auditing
Demonstrating Select AI or Vector Search live

A short story from the field

A team asked an assistant to “optimize a daily sales query.” The first answer added an index and a couple of hints that looked fine in isolation. The skills route would have sent it somewhere else: read the explain-plan and optimizer-stats files first; check cardinalities and stale stats; consider a plan baseline if volatility is the real problem. That path took longer but avoided chasing a hint that didn’t survive the next gather‑stats job. The difference wasn’t model IQ—it was having Oracle‑specific operating instructions and the habit of loading only what came next.

Where this series goes from here

Everything you need to begin is in your editor: the skills repo, the routing file, and the mental model—route, act, trust. We’ll build outward from that front door, showing how local and managed MCP surfaces work when action is warranted, and how Select AI and Vector Search improve reasoning close to your data. Expect short, source‑backed posts that follow the same grammar:

Route: pick a path in db/SKILL.md for your role and task.
Act: use SQLcl MCP locally or the Autonomous Database (Serverless) MCP server when you need controlled actions.
Guardrail: keep roles, ACLs/private endpoints, VPD, and auditing in‑path for evidence and scope.

The eight posts are organized into four groups:

Foundation 1 — this post; 2 — Route, Don’t Flood Mental model and db/SKILL.md as routing layer
Action 3 — SQLcl MCP; 4 — Managed MCP in Autonomous AI Database Local and remote bounded action surfaces
Intelligence 5 — Select AI and AI Profiles; 6 — Vector‑Native RAG NL2SQL reasoning quality and in‑database retrieval
Rollout 7 — Agent‑Safe Change Delivery; 8 — The Trust Layer Safe change workflows and enterprise governance

Conclusion: a safer default for Oracle‑backed AI

If your assistant can touch Oracle Database, “just let it connect” isn’t an acceptable plan. Put the skills repo between the model and the problem so it learns to work the Oracle way. When execution is appropriate, send it through SQLcl MCP or the Autonomous Database (Serverless) MCP server so actions are small, named, and governed. Keep Oracle’s controls—roles, ACLs, VPD, auditing, redaction, encryption—in the path so every action is scoped and traceable.

DEV Community: Mark Nelson

Evaluate Oracle Database Operator for Kubernetes: A Practical First Run for Platform Teams

Key Takeaways

Start by proving the operator control loop

Check the prerequisites before installing anything

Create a dedicated namespace for the database resource

Install or verify cert-manager first

Install Oracle Database Operator for Kubernetes from a release tag

Confirm that the operator and CRDs are present

Create the admin password secret

Apply one supported SingleInstanceDatabase sample

Validate reconciliation through status, events, and resources

Troubleshoot first-run failures in the smallest scope possible

Clean up the evaluation resources

Decide what the first run tells you

Compare the operator path with practical alternatives

First-run questions platform teams usually ask

Is Oracle Database Operator for Kubernetes a replacement for DBA ownership?

Does a successful first run prove production readiness?

What should I inspect first when reconciliation stalls?

Should the first evaluation cover every custom resource?

Continue only when the next question is clear

The trust layer for enterprise AI on Oracle: prove identity, scope, policy, and evidence for every assistant action

Key Takeaways

Version scope

Why governance is the trust layer

Identity and least privilege you can defend

The network path is part of your security posture

Data minimization and enforceable scope

Encryption and key posture you can explain in a review

Traceability you can hand to auditors

Mini demo: prove scope → govern → audit

What can go wrong—and how to fix it

Make it a runbook, not a one-off

Conclusion: bring receipts to every AI/database conversation

Further reading

Agent‑safe change delivery on Oracle: discovery, online mechanics, idempotent migrations, and provable rollbacks

Key Takeaways

Version scope and expectations

Why agent‑safe change matters most

Safety before execution: discovery, intent, and rerunnable SQL

From preview to apply: bounded MCP, not ad‑hoc credentials

Reduce blast radius with Oracle’s online mechanics

The same mindset for vector and relational indexes

Proving what happened—and preparing to undo it

Mini‑demo: propose, review, apply, verify, prove

Delivery mechanics that hold up in real pipelines

A short view for migration leads

What to remember—and what’s next

Sources and further reading

Notes on differences across releases and editions

How to Enforce Role-Based Data Access in AI Applications with Oracle Deep Data Security

Key Takeaways

What Is Oracle Deep Data Security?

Key Features of Oracle Deep Data Security

Data roles and end-user identity

Row-level security

Column-level security

Deep Sec-scoped vector retrieval

Application trust boundary

How to Get Started with Oracle Deep Data Security

Prerequisites

Prepared Deep Sec identity contract

Create the project

Add local environment variables

DEAL Demo Project

Step 1: Verify the ADB-S connection and Deep Sec metadata visibility

Step 2: Add a small connection helper

Step 3: Create loan and policy tables

Step 4: Load synthetic data and deterministic demo vectors

Step 5: Configure Oracle Deep Data Security data roles and data grants

Step 6: Set end-user context and prove read and column enforcement

Step 7: Demonstrate vector distance with manual vectors

Step 8: Run Deep Sec-scoped vector retrieval

Optional: Compare keyword and vector-style retrieval

Try this next: Wrap the proven operations as DEAL tool functions

Try this next: Run DEAL sessions and bypass checks

Optional: Add an OCI GenAI chatbot loop

Troubleshooting

The connection fails

Apply one supported `SingleInstanceDatabase` sample