Skip to Main Content

SQLcl

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Handling Insufficient Privileges for SYS Grants in project deploy

KeepItSimple01Jan 24 2025

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

This post has been answered by danmcghan-Oracle on Jan 28 2025
Jump to Answer

Comments

BluShadow Mar 18 2024

Oracle support documents are available to paying customers through the support portal: support.oracle.com

If you are not a paying customer then I'm afraid you won't have access to the document, and it is a breach of any customer's agreement with Oracle to supply such support documents to others.

1 - 1

Post Details

Added on Jan 24 2025
5 comments
152 views