SQLcl: MCP Server & SQL Prompt

Hi Team,

This is a follow-up to my earlier post:
“Unable to export system/role grants for app schemas when connected as installer”
( https://forums.oracle.com/ords/apexds/post/unable-to-export-system-role-grants-for-app-schemas-when-co-8109)

In the first post I described the problem where exporting grants from an installer/admin user does not correctly export system privileges and role grants for individual application schemas.

Since then, I performed additional tests and discovered a second significant issue, this time occurring when exporting schema-by-schema using the schema’s own credentials.

It's also important scenario because not all developers will have access to DBA-like installer/admin even in development environment, and the Project should support multi-schema projects that will use schema-by-schema exports.

So, here is the issue:

Exports From Multiple Schemas Overwrite Each Other

Test Setup

connect -name vm_sys -- connect as a DBA

drop user foo cascade;
drop user bar cascade;

create user foo identified by oracle;
grant connect, resource to foo;
grant execute on sys.javascript to foo;
grant select any table to foo;

create user bar identified by oracle;
grant connect to bar;
grant select_catalog_role to bar;
grant create public synonym to bar;
grant execute on sys.javascript to bar;

project init -name foobar -directory foobar1 -schemas foo,bar
cd foobar1
!git init -b main

Important Setup Step (to enable grant export)

Before exporting, edit:

foobar1/.dbtools/filters/project.filters

Comment out the following line:

--export_type not in ('USER','USER_SYS_PRIVS','USER_ROLE_PRIVS'),

This allows SQLcl Project to export system privs and role grants.

Step 1 — Export as FOO

connect foo/oracle
project export

Export will correctly generate these three files:

src/database/sys/object_grants/object_grants_as_grantor.sys.mle_language.javascript.sql
src/database/sys/user_sys_privs.sql
src/database/sys/user_role_privs.sql

All containing FOO-specific grants. All look good.

Step 2 — Export as BAR

connect bar/oracle
project export

After exporting as BAR, two of the three files were overwritten:

• user_sys_privs.sql
• user_role_privs.sql

Only one file preserved both schemas’ grants:

• object_grants_as_grantor.sys.mle_language.javascript.sql

After BAR export:

• user_sys_privs.sql now contains only BAR’s system privileges

• user_role_privs.sql now contains only BAR’s role grants

• FOO’s grants are lost

  SQL> !cat ./src/database/sys/object_grants/user_sys_privs.sql
  grant create public synonym to bar;
  
  
  -- sqlcl_snapshot {"hash":"b60ae3449f06a02a6ad94cde2fd0831353f88ace","type":"OBJECT_GRANT","name":"SYS_PRIVS","schemaName":"SYS","sxml":""}
  
  SQL> !cat ./src/database/sys/object_grants/user_role_privs.sql
  grant connect to bar;
  
  grant select_catalog_role to bar;
  
  
  -- sqlcl_snapshot {"hash":"eb728d535376ce7e82c85c9de7e6b3be9bd11bd7","type":"OBJECT_GRANT","name":"ROLE_PRIVS","schemaName":"SYS","sxml":""}
  
  

This makes it impossible to manage or version control system privileges and role grants for a multi-schema application.

Summary of Current Problems

1. Using a privileged installer user fails

(as described in the original post)

2. Exporting schema-by-schema also fails

Because files under src/database/sys/ are shared, exporting grants from a second schema overwrites the previous schema’s grant files.

Proposed Solution: Add Schema-Level Namespacing Under /src/database/sys

To avoid overwriting, the structure should include schema names:

src/database/sys/
   ├── foo/
   │     ├── object_grants/
   │     │       └── <foo grants>.sql
   │     ├── user_role_privs.sql
   │     └── user_sys_privs.sql
   │
   ├── bar/
   │     ├── object_grants/
   │     │       └── <bar grants>.sql
   │     ├── user_role_privs.sql
   │     └── user_sys_privs.sql
   │
   └── (schemaN)

This would:

• Prevent overwriting between schema exports
• Allow schema-level privilege tracking
• Properly support multi-schema applications
• Allow both installer-based and schema-based workflows

Enhancement Request:

Could the team consider implementing schema-specific subfolders under **/src/database/sys** so that grants can be exported and versioned independently for each schema?

At the moment, even with the correct filter settings, multi-schema grant management is not possible without manual edits.

Thank you,
Alex