Hello,
While integrating version control and CI/CD into our Oracle DB and Oracle APEX development lifecycle, I've encountered a challenge regarding the use of a dedicated deployment user for granting execute privileges on SYS.xxx
to other_schema
(specifically when using the project deploy
command).
Details
While trying to deploy, I was using a dedicated deployment user based on the following 4.4.2 Administrator Exports HR to Production Schema and encountered an insufficient privileges error Reason: liquibase.exception.DatabaseException: ORA-01031: insufficient privileges
when trying to perform GRANT EXECUTE ON sys.dbms_crypto TO other_schema
, same for GRANT EXECUTE ON sys.javascript TO other_schema
.
So I searched the web and, based on this blog post Admin vs. App User Installation: Choosing the Right Path for Secure, Efficient Deployments by @danmcghan-oracle, I tried using the SYSTEM
user for non-ADB environments. It worked to an extent, as the GRANT EXECUTE ON sys.javascript TO other_schema
command succeeded, but GRANT EXECUTE ON sys.dbms_crypto TO other_schema
still resulted in insufficient privileges.
Main Question
- How can I handle insufficient privileges for SYS grants, given that both the
SYSTEM
user and a dedicated deployment user encounter issues? (Maybe some grants are missing even though I tried to grant the deployment user DBA
, EXECUTE on sys.dbms_crypto
and on sys.javascript
maybe this would stop the insufficient privileges errors in the project deploy
, but didn't work.)
Related Sub-Question
- Is it a best practice to use a dedicated deployment user instead of
SYSTEM
? And if yes, how should one configure this user to be able to run the project deploy
without encountering insufficient privileges errors since clearly SYSTEM
is more privileged and I would need to have the deployment user even more privileged in some cases like the for the GRANT EXECUTE ON sys.dbms_crypto TO other_schema
?
Any guidance, examples, or best practices would be greatly appreciated!
Thank you for your help!
PS: Oracle SQLDeveloper Command-Line (SQLcl) version: 24.3.2.0 build: 24.3.2.330.1718