Hi Team,
I’m encountering an issue where system privileges and role grants are not exported correctly when both export and deployment are performed by a privileged admin account rather than by the application schema user.
In the example below, I use the INSTALLER user (with DBA privileges) to deploy an application that has two schemas: FOO and BAR.
--- Connect as SYS
conn -name vm_sys
show user
--- Create the Installer
create user installer identified by oracle;
grant dba to installer;
grant execute on sys.javascript to installer with grant option;
--- Connect as Installer
connect installer/oracle@//localhost:1521/freepdb1
conn -save inst -savepwd
--- Create application schemas and objects
create user foo;
create table foo.t (
id number(10,0) generated always as identity not null enable,
document_type_valid_yn varchar2(1 char) default 'Y' not null enable
);
grant connect, resource to foo;
grant execute on sys.javascript to foo;
grant select any table to foo;
create user bar;
create table bar.q (
id number(10,0) generated always as identity not null enable,
name varchar2(30)
);
grant select on foo.t to bar;
create synonym bar.t for foo.t;
grant execute on sys.javascript to bar;
grant select_catalog_role to bar;
grant create public synonym to bar;
project init -name foobar -directory foobar -schemas foo,bar
cd foobar
!git init -b main
/*
Go to .dbtools/filters/project.filters and
comment out this line to allow priveleges and roles export:
-- export_type not in ('USER','USER_SYS_PRIVS','USER_ROLE_PRIVS'),
*/
Pause Click Enter when done …
!git add .
!git commit -m "initial commit"
!git checkout -b feature-1
project export
Review the files generated under src/database/sys:
src/database/sys/object_grants/object_grants_as_grantor.sys.mle_language.javascript.sql:
grant execute on sys.javascript to bar;
grant execute on sys.javascript to foo;
This is correct and expected - table grants are working OK
src/database/sys/object_grants/user_role_privs.sql:
grant dba to installer;
This is incorrect. Expected result:
grant connect to foo;
grant resource to foo;
grant select_catalog_role to bar;
src/database/sys/object_grants/user_sys_privs.sql:
grant unlimited tablespace to installer;
This is incorrect. Expected:
grant select any table to foo;
grant create public synonym to bar;
This feature is critical for our project.
At the moment, I’m working around the issue by manually editing the generated files in the sys folder — which is not a sustainable solution.
Could you please confirm if this behavior is expected, or if there’s a way to export user-specific system and role grants correctly when exporting as a DBA/admin user?
Thank you,
Alex Kluev