Skip to main content
HashnodeMore or Less About Oracle Database

Command Palette

Search for a command to run...

The Likely Obvious Oracle Tip #7

Using SQLcl MCP server to check schema grants provided when creating new APEX workspace.

Published
•6 min read
The Likely Obvious Oracle Tip #7
W
Wojciech Sowa
I started working with Oracle during my studies, and in 2017, I joined Pretius as Oracle APEX Developer. Here, I work on international projects across various industries like healthcare, banking and public sector. I enjoy sharing my knowledge with others, so I write blog posts in my free time. You can read my articles on Pretius blog and on my personal blog page on Hashnode. My mission is to tell you about complex things in simple words. I also volunteered in the 'Rewild Earth' APEX project led by Steven Feuerstein as a Google Drive integration expert. I am a conference speaker on events like APEX Meetup Poland and APEX World 2025. I have been awarded the Member of the Month December 2020 title by apex.world portal. In my free time, I enjoy driving sports cars and trying to make the best espresso ever.

Introduction

From time to time, I log into the INTERNAL workspace to set up a new APEX workspace. In most cases, it is based on an existing database schema. In some situations, I need to create a new schema. Here, APEX offers us two options: creating the schema ourselves and configuring it as we wish, or creating the schema directly through the APEX wizard when setting up a new workspace.

The first major test of my schema is installing database objects and data, such as from Sample Datasets. This is when I verify if my schema can host these objects with the current level of permissions. For existing schemas or schemas created manually, the situation is not 100% clear, and any installation errors indicate which permissions might be missing. Of course, I assume that my code do not cause compilation problems.

💡
What's more, even a successful installation of the Sample Dataset might not give us 100% certainty - how about the execution of APEX Automations or Background Processes in my application?

However, when I use the wizard to create the schema and then the workspace, installing objects from the Sample Datasets section is not a problem. Moreover, APEX does not suggest any additional permissions that need to be added under specific conditions. Therefore, I don't need to know the list of permissions necessary for a correct installation - APEX ‘knows‘ this list for us.

What permissions does APEX provide?

The task I decided to solve with the help of AI can be approached in many "standard" ways. I could probably find the appropriate section in the APEX documentation or develop the right query myself. But since we have AI and the appropriate MCP server available, it's worth trying a simple training exercise to get to know this technology a bit better. A simple example is easier for us to control.

💡
Another thing is that this approach is becoming more popular. It's worth knowing, especially since APEXlang is getting closer.

My Approach

Here is a bullet list of assumptions for my Tip:

  • I am on my local Docker environment: Oracle Database 26ai + APEX 24.2 + SQLcl 25.4.1

  • I used the wizard to create the schema and workspace.

  • I have the SQL Developer Extension in my VS Code.

  • I created a connection in this tool to my new schema.

  • I will use cline (with claude-sonnet) and the SQLcl MCP server so that AI can find the answer to my question.

  • In a project situation, I should exercise particular caution and follow the guidelines from Jeff Smith blog.

💡
Check Jeff Smith’s posts - LINK. You will find complete knowledge there about working with the MCP server, SQLcl, ORDS and much more.

Let’s get this done

What’s my prompt?

You are an Oracle database specialist and familiar with SQLcl MCP Server.

I created a new database schema while setting up a new APEX workspace. I used the interactive wizard available in the INTERNAL workspace.

The schema is named HR and is saved as a connection called HR@FREEPDB1-DOCKER. Only the standard connection is available (without SYSDBA role or similar).

Use the SQLcl MCP server to complete the task:

  1. Develop a list of grants given to the HR schema (CREATE TABLE, CREATE VIEW, etc.)

  2. Develop a list of roles assigned to the HR schema (CONNECT, RESOURCE, etc.)

I am not interested in permissions for objects from other schemas.

Verify the results to ensure they are correct.

💡
If you don't feel confident writing useful prompts that clearly and precisely tell the AI what to do, you can always ask the AI to... write the prompt for you :-)

Agent's Way of Thinking

Let's see how my agent will think. The results of each interaction might differ - it really depends on the LLM used and the mode of interaction, like Agent, Plan, or Edit.

💡
I accept this step.

💡
For me, it's okay - the agent found the correct connection and will attempt to connect to that database.
### DATABASE CONNECTION ESTABLISHED ###
Successfully connected to: **HR@FREEPDB1-DOCKER**


### DATABASE ENVIRONMENT CONTEXT ###


**1. Oracle Database Version:** 
23.26.0.0.0

**2. Access Mode:** 
The Oracle database is currently in 'null' mode

**3. NLS Parameters:** 
The current Oracle database uses the following NLS configuration:
Database character set:{"results":[{"columns":[{"name":"PARAMETER","type":"VARCHAR2"},{"name":"VALUE","type":"VARCHAR2"}],"items":
[
{"parameter":"NLS_CHARACTERSET","value":"AL32UTF8"}
]}]}

 Full NLS parameters: 
{"results":[{"columns":[{"name":"PARAMETER","type":"VARCHAR2"},{"name":"VALUE","type":"VARCHAR2"}],"items":
[
{"parameter":"NLS_RDBMS_VERSION","value":"23.0.0.0.0"}
,{"parameter":"NLS_NCHAR_CONV_EXCP","value":"FALSE"}
,{"parameter":"NLS_LENGTH_SEMANTICS","value":"BYTE"}
,{"parameter":"NLS_COMP","value":"BINARY"}
,{"parameter":"NLS_DUAL_CURRENCY","value":"$"}
,{"parameter":"NLS_TIMESTAMP_TZ_FORMAT","value":"DD-MON-RR HH.MI.SSXFF AM TZR"}
,{"parameter":"NLS_TIME_TZ_FORMAT","value":"HH.MI.SSXFF AM TZR"}
,{"parameter":"NLS_TIMESTAMP_FORMAT","value":"DD-MON-RR HH.MI.SSXFF AM"}
,{"parameter":"NLS_TIME_FORMAT","value":"HH.MI.SSXFF AM"}
,{"parameter":"NLS_SORT","value":"BINARY"}
,{"parameter":"NLS_DATE_LANGUAGE","value":"AMERICAN"}
,{"parameter":"NLS_DATE_FORMAT","value":"DD-MON-RR"}
,{"parameter":"NLS_CALENDAR","value":"GREGORIAN"}
,{"parameter":"NLS_NUMERIC_CHARACTERS","value":".,"}
,{"parameter":"NLS_NCHAR_CHARACTERSET","value":"AL16UTF16"}
,{"parameter":"NLS_CHARACTERSET","value":"AL32UTF8"}
,{"parameter":"NLS_ISO_CURRENCY","value":"AMERICA"}
,{"parameter":"NLS_CURRENCY","value":"$"}
,{"parameter":"NLS_TERRITORY","value":"AMERICA"}
,{"parameter":"NLS_LANGUAGE","value":"AMERICAN"}
]}]}
20 rows selected. 


 This is important for correctly interpreting data formats, encodings, and locale-specific behavior.


Here’s the Oracle database context you need: version, open mode, and NLS parameters. Use this information to run the following queries accurately and ensure they align with the database’s current environment and settings.
💡
Looks good, I'm following the plan given by the agent.

💡
The query the agent wants to execute is as follows - I allow this operation.
SELECT privilege 
  FROM user_sys_privs 
 ORDER BY privilege;
💡
Here are the results.
"PRIVILEGE"
"CREATE CLUSTER"
"CREATE DIMENSION"
"CREATE INDEXTYPE"
"CREATE JOB"
"CREATE MATERIALIZED VIEW"
"CREATE MLE"
"CREATE OPERATOR"
"CREATE PROCEDURE"
"CREATE PROPERTY GRAPH"
"CREATE SEQUENCE"
"CREATE SESSION"
"CREATE SYNONYM"
"CREATE TABLE"
"CREATE TRIGGER"
"CREATE TYPE"
"CREATE VIEW"
"EXECUTE DYNAMIC MLE"

17 rows selected.
💡
I checked this query - it works and returns identical results.

💡
The query the agent wants to execute is as follows - I allow this operation.
SELECT granted_role 
  FROM user_role_privs 
 ORDER BY granted_role;
💡
I checked this query - no data returned.
"GRANTED_ROLE"
no rows selected
💡
But the agent wants to take it a step further:

💡
The query the agent wants to execute is as follows. The agent has already checked this object, but this time wants to retrieve more columns.
SELECT privilege, admin_option, common, inherited 
  FROM user_sys_privs 
 ORDER BY privilege;
💡
The above query returns the following results.
"PRIVILEGE","ADMIN_OPTION","COMMON","INHERITED"
"CREATE CLUSTER","NO","NO","NO"
"CREATE DIMENSION","NO","NO","NO"
"CREATE INDEXTYPE","NO","NO","NO"
"CREATE JOB","NO","NO","NO"
"CREATE MATERIALIZED VIEW","NO","NO","NO"
"CREATE MLE","NO","NO","NO"
"CREATE OPERATOR","NO","NO","NO"
"CREATE PROCEDURE","NO","NO","NO"
"CREATE PROPERTY GRAPH","NO","NO","NO"
"CREATE SEQUENCE","NO","NO","NO"
"CREATE SESSION","NO","NO","NO"
"CREATE SYNONYM","NO","NO","NO"
"CREATE TABLE","NO","NO","NO"
"CREATE TRIGGER","NO","NO","NO"
"CREATE TYPE","NO","NO","NO"
"CREATE VIEW","NO","NO","NO"
"EXECUTE DYNAMIC MLE","NO","NO","NO"

17 rows selected.

It seems that APEX assigns only specific grants to the new schema. Despite significant similarities (but not identical), typical roles like CONNECT or RESOURCE are not used. In my case, the agent never made a mistake when referring to DBA_% tables/views, which was my intention to avoid them.

💡
What do you think about this exercise and the results? If I made a mistake anywhere, let me know.

Thanks for reading this article! Feel free to use it and try it on your own!

Check also my other publications!

#OracleDatabase26ai #OracleAPEX #MCP #PromptEngineering

#oracle-apex#mcp#oracle-database

Comments

Join the discussion

No comments yet. Be the first to comment.

More from this blog

More or Less About Oracle Database

15 posts