Greetings. I am having trouble with the execution order in stage.changelog.xml. I am using SQLcl 25.1 and connecting to an Oracle 19c database.
I follow the following steps:
1. I connect to sqlcl using an elevated user with sys-like permissions.
2. I use this elevated user to initialize a new project with multiple schemas (the elevated schema is not one of them).
3. The remaining steps are all executed as the elevated user.
4. I commit these changes to the master branch.
5. I create and checkout a new feature branch.
6. I do a project export.
7. This creates the desired schemas along with a folder for the elevated user, although the elevated user was not listed as one of the schemas for the project.
8. All of the object grants for the entire project are stuck inside the elevated user's folder. This seems a little janky but I am okayish with it.
9. I commit these changes on the local feature branch.
10. I do a project stage.
11. When I check the resulting stage.changelog.xml file I notice that all object grants for the project are run first, before any objects are even created.
Any idea why this is happening? I thought the whole point of the project feature was so that we would not have to go in and manage the changelogs manually, as was previously common with the Liquibase approach. But if all object grants are listed first, then it now becomes a manual task to move them to the appropriate position in the change log. And when there are hundreds (or even thousands) of object grants this isn't a small task.
Also, I noticed that the object grants are named with a particular prefix - “object_grants_as_grantor”. This suggests that the object grants are generated at the time that the “grantor” schema is processed. But what if we want the grants to be created along with the “grantee” schema instead? Is there a way to have the grants generated as “object_grants_as_grantee”?