A look at OAuth Identity, SYS_CONTEXT, and Row-Level Security via OCI AI Database MCP Servers
The Model Context Protocol (MCP) has defined a very simple way to allow AI Agents to interact with external resources. HTTPS streaming MCP Servers are implemented via OAuth2, and this allows a business user, identified using say Azure Entra ID or Active Directory, to provide their work credentials to access protected systems – such as your AI Chat agents talking to your Oracle databases.
That means you could have anyone in your organization chat with your business data – that super important, sensitive data living in your Oracle AI Databases!
You must protect that data. Some people should have ZERO access, some people should have limited access, and a very few people might have FULL (but audited!) access. But if our MCP Server is using the same database connection to sort all those NL2SQL questions from all those people and their agents, how can we manage this?
Oracle Database’s decades of investment in Security makes this super easy to implement.
If you’re not interested in the technical implementation of how this works, I wrote a high level overview on LinkedIn. And if you need a bit more catching up, I did a companion post covering another new feature (Reports) here.
One of the quiet superpowers of our OCI Database MCP Servers is something you don’t even have to think about: when an AI client connects and runs a query, the database knows who they are.
Sorry, if you’re wondering what I’m talking about when I say “OCI Database MCP Servers,” we just added this feature to our free developer service in the Oracle Cloud, called ‘Database Tools.’ You can read all about it here.
But to sum up in a nutshell, it’s serverless and Oracle managed – you simply define the database connection, the identity domain, and the tools you want – we handle the rest!
Our MCP Server brings the identity to the database
You’re not JUST the database user being used to create a database connection. And that’s especially true if you’re using an AI Agent with one of our MCP Servers. If your organization has federated your existing SSO identity management into OCI, then the OAuth properties might be VERY important in terms of how your session is controlled in the database.
Let me show a super quick example.
One of the features of our MCP Server is you can create custom Tools. This means the same SQL or PL/SQL will always be executed when the Tool is called. It can supplement or replace our built-in Tools like ‘run-sql’ if you don’t want to enable ad-hoc, LLM-generated SQL.
I have a tool called ‘Who’ that runs this code block –
SELECT
SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS CURRENT_SCHEMA,
SYS_CONTEXT('USERENV', 'SESSION_USER') AS SESSION_USER,
SYS_CONTEXT('USERENV', 'PROXY_USER') AS PROXY_USER,
SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') AS AUTHENTICATED_IDENTITY,
SYS_CONTEXT('USERENV', 'AUTHENTICATION_METHOD') AS AUTHENTICATION_METHOD,
SYS_CONTEXT('USERENV', 'ENTERPRISE_IDENTITY') AS ENTERPRISE_IDENTITY,
SYS_CONTEXT('USERENV', 'PROXY_ENTERPRISE_IDENTITY') AS PROXY_ENTERPRISE_IDENTITY,
SYS_CONTEXT('CLIENTCONTEXT', 'OAUTH_SUB_TYPE') AS OAUTH_SUB_TYPE,
SYS_CONTEXT('CLIENTCONTEXT', 'OAUTH_SUB') AS OAUTH_SUB,
SYS_CONTEXT('CLIENTCONTEXT', 'OAUTH_USER_OCID') AS OAUTH_USER_OCID,
SYS_CONTEXT('CLIENTCONTEXT', 'OAUTH_CLIENT_OCID') AS OAUTH_CLIENT_OCID,
SYS_CONTEXT('CLIENTCONTEXT', 'OAUTH_CLIENT_NAME') AS OAUTH_CLIENT_NAME,
SYS_CONTEXT('CLIENTCONTEXT', 'OAUTH_CA_OCID') AS OAUTH_CA_OCID,
SYS_CONTEXT('CLIENTCONTEXT', 'OAUTH_CA_NAME') AS OAUTH_CA_NAME,
SYS_CONTEXT('CLIENTCONTEXT', 'OAUTH_DOMAIN_ID') AS OAUTH_DOMAIN_ID,
SYS_CONTEXT('CLIENTCONTEXT', 'OAUTH_DOMAIN_NAME') AS OAUTH_DOMAIN_NAME,
SYS_CONTEXT('CLIENTCONTEXT', 'IAM_DOMAIN_APP_ROLES') AS IAM_DOMAIN_APP_ROLES,
SYS_CONTEXT('CLIENTCONTEXT', 'RESOURCE_OCID') AS RESOURCE_OCID,
SYS_CONTEXT('CLIENTCONTEXT', 'RESOURCE_COMPARTMENT_OCID') AS RESOURCE_COMPARTMENT_OCID
FROM
DUALNow, when my agent calls this tool, here’s what it receives back –
With this information I could do a lot of nice things on the security side of my database house.
But firstly, just who is this user? I can pull them up directly in the console by their OCID –
CLIENTCONTEXT.OAUTH_SUB is me, the authenticated human (or agent) on the other end of the OAuth flow. Our MCP layer set that into the session’s client context for me. I didn’t write a logon trigger, I didn’t create a context package, I didn’t call DBMS_SESSION.SET_CONTEXT. It’s just there, populated per request from the token.
Docs: OCI Database Tools MCP Server Security: Identity Propagation
That’s the whole trick, and everything below is built on it.
Why this matters
In a common app-tier setup, your database might see one pooled service account for everybody. “Who’s running this query?” is unanswerable at the data layer — the app knows, the database doesn’t. So all your access rules live in application code, and anything that bypasses the app (a reporting tool, an ETL job, an over-eager AI agent with SQL access) bypasses your rules too.
When the identity rides along in CLIENTCONTEXT, that flips. The database can answer “who is this?” on every single statement. And Oracle has a feature that’s been waiting 20+ years for exactly this: Virtual Private Database (VPD), a.k.a. Row-Level Security. You attach a policy to a table, Oracle silently rewrites every query against it to add a WHERE clause your policy function returns — before the optimizer even sees it. Users literally cannot read rows they aren’t entitled to, no matter how the query arrives.
Identity in the session + VPD = data protection that doesn’t care how someone got to the table.
Ok let’s make a ‘rule’: only users with an oracle.com domain email can see all of our customers, otherwise you can only see customers who have opted-in for SMS contacts.
It’s an arbitrary rule, but the idea is your business logic can be expressed in a set of conditions that end of the day cause a WHERE clause predicate to be applied to any SQL being executed for a given table, or group of tables.
I’ve got a loyalty CUSTOMERS table. Each customer has a CONTACT_PREFERENCES column — a native JSON column — that looks like this:
CUSTOMER_ID CONTACT_PREFERENCES
----------- -----------------------------
1 {"sms":true,"email":false}
2 {"sms":true,"email":true}
3 {"push":true}
4 {"sms":false,"email":true}
5 {"email":true,"push":true}If there isn’t a “sms”:true value for a customer record, then if I’m authenticated with my personal vs work (oracle.com) user account, I won’t see that row.
Policy:
- Internal Oracle staff (anyone whose
OAUTH_SUBends in@oracle.com) sees all customers. - Everyone else — external identities, partner agents, or anything with no
OAUTH_SUBat all — only sees customers who’ve explicitly opted into SMS contact ("sms": true).
In other words: if I can’t prove you’re internal, you only get the rows where the customer has actively said “yes, text me.”
Building it
We’re going to have three pieces, and since the OAuth subject already lives in CLIENTCONTEXT, I don’t need a custom application context or a logon trigger. The policy PL/SQL function can access this information live at query time.
Piece 1: the policy function
CREATE OR REPLACE FUNCTION jeff.customers_sms_policy (
p_schema IN VARCHAR2,
p_object IN VARCHAR2
) RETURN VARCHAR2 AS
v_oauth_sub VARCHAR2(4000) := SYS_CONTEXT('CLIENTCONTEXT','OAUTH_SUB');
BEGIN
-- Internal Oracle identities see everything.
IF v_oauth_sub IS NOT NULL
AND LOWER(v_oauth_sub) LIKE '%@oracle.com' THEN
RETURN NULL; -- no predicate => all rows
END IF;
-- Everyone else (incl. NULL/absent OAUTH_SUB): SMS opt-ins only.
RETURN q'[JSON_VALUE(contact_preferences, '$.sms') = 'true']';
END customers_sms_policy;
/Piece 2: Attaching the policy
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'JEFF',
object_name => 'CUSTOMERS',
policy_name => 'CUSTOMERS_SMS_VISIBILITY',
function_schema => 'JEFF',
policy_function => 'CUSTOMERS_SMS_POLICY',
statement_types => 'SELECT, UPDATE, DELETE',
update_check => TRUE,
policy_type => DBMS_RLS.DYNAMIC,
enable => TRUE
);
END;
/A couple of things worth saying out loud:
statement_types => 'SELECT, UPDATE, DELETE'— this isn’t just about reads. An external identity can’t update or delete a row it isn’t allowed to see, either.update_check => TRUE— stops someone from editing a visible row in a way that pushes it out of their own visibility (e.g. flippingsmsto false on a customer they can currently see).DYNAMIC— re-evaluated per query. My function does nothing but read a context value and run aLIKE, so it’s cheap, andDYNAMICkeeps me safe from a cached predicate from one identity leaking onto a reused, pooled session belonging to another. On a security policy over pooled connections, correctness wins over a micro-second in response time, every time.
Testing it
First, baseline — there are 75 customers total, and 30 of them have "sms": true. So internal should see 75, external should see 30.
Running as myself ([email protected]):
Except, I didn’t run the query, per se, I had my agent use our Reports feature and our associated MCP Reporting Tools.
So my LLM didn’t generate any SQL. It just called the report, which will always end up with this being executed –
By the way, in this instance, my Agent has been authorized to act on my behalf (OBO) using my [email protected] domain credentials. That’s why I get to see ‘all the customers.’
Using my other agent, where I have authenticated to the SAME MCP server, talking to the same database, with the same tables and data – but only my OAuth2 credentials have changed (my personal vs work email account)…
This OBO stuff, you’ll generally just walk this flow once, as you register a new MCP Server with your agent. You’ll be asked to login using your domain user and then grant access to the KNOWN client:
After I say ‘Allow’, the Cline extension running in my IDE can now act on my behalf, and that includes getting access tokens to talk to my OCI MCP Server, and then say, run tools.
So running the reporting tools, I get back this response –
💡 As my work identity (jeff @ oracle.com), the report returns all 75 customers. Through my personal identity (thatjeffsmith @ mail.com) authorized agent against the same server and table, the same report returns 30 customer— only those who have SMS opt-ins.
Why is the count different? Because the row level security policy function saw that my identity wasn’t in the approved list to see ALL the records, and so when the report ran and executed the SELECT * FROM CUSTOMERS query, the DATABASE added the policy predicate to that query, turning it into –
SELECT *
FROM CUSTOMERS
WHERE JSON_VALUE(contact_preferences, '$.sms') = 'true';Takeaways
The headline isn’t really “Oracle has Row Level Security” — it’s had that forever. The headline is that our OCI AI Database MCP Servers hand the database a real, authenticated identity on every request, with nothing for you to set up. The token becomes a SYS_CONTEXT value, and from there you’re in familiar Oracle territory: read it, branch on it, and let VPD do the enforcement at the database level – vs you needing to implement that FOR EVERY SINGLE CLIENT/APPLICATION.
For anyone wiring AI agents up to a database, this is exactly the control you want. You’re not trusting the agent to behave. You’re not bolting security onto the app tier and hoping every path goes through it. You’re identifying the caller in the session and protecting the data right where it lives — so a SELECT, a report, or an agent all play by the same rules.
