Admin vs. App User Installation: Choosing the Right Path for Secure, Efficient Deployments

Choosing the right deployment approach is essential when deploying changes to Oracle Database and APEX applications. One key decision involves selecting between an admin (privileged) user installation and an app (object owner) user installation. Each approach has distinct advantages, security considerations, and compatibility factors, which can impact CI/CD workflows, Liquibase management, and overall flexibility.

Note: In this post, "app user" does not refer to an application's end users; it refers to the database users who own the application objects in the database.

Admin User Deployment: Streamlined with Security at the Core

Admin user deployment allows teams to control and limit the privileges required by app users. With this approach, the installer uses a highly privileged user, typically one that has grants to create and manage application-related objects across schemas. This means the app users—responsible for runtime operations—don't need extensive privileges like CREATE TABLE, helping to minimize SQL injection attack vectors.

Benefits of Admin User Deployment

Enhanced Security Controls: Admin user deployment minimizes the risk associated with privilege abuse by restricting app user privileges. Even if an attacker exploits the application, the reduced permissions of the app user will help limit their actions.
Complete Installation Workflows: Admin users streamline deployment, allowing the installer to create app users, grant necessary privileges, and configure cross-schema objects in one pass. This approach reduces manual setup steps, making it ideal for CI/CD pipelines.

Admin User Options

For Autonomous Database (ADB), the ADMIN user account is a common choice.
The SYSTEM account can be used for non-ADB instances. Note that SQLcl doesn't support using the SYS account for Liquibase operations.
For additional compile-time security, teams can create a custom admin user with a select set of privileges. However, this can be complex to maintain in evolving environments, as a DBA may need to manually add new grants to this user before deployment pipelines can use them.

App User Deployment: Focused and Compatible with Shared Environments

App user deployment might be the only option for shared databases or environments with restricted privileges. In this approach, DBAs manually create app users with only the necessary permissions before the installer runs. The installer then connects with these users to update the user's schema.

This approach is often used with databases that host many distinct applications, where deploying with admin privileges isn't permitted because the installer for one application could affect schemas for another application.

Considerations for App User Deployment

Shared Database Compatibility: This setup aligns well with shared environments, where connecting as the object owner is required to avoid cross-project interference. This is especially true before Oracle Database 23ai (see "Application Scoped Admin Users with Oracle 23ai" below for a new option).
Pre-Configured Users and Privileges: App users and their permissions must be in place before installation, making this setup less amenable to automated pipelines as it requires manual pre-configuration that may require revisiting each time a user needs a new grant.
Single-schema limitation with SQLcl Projects: The SQLcl Projects installer makes a connection to the database and runs the Liquibase changelogs/sets. If the connected user is not an admin user, then the changelogs/sets must be scoped to single app user. While one could conceivably come up with work arounds, such as having multiple SQLcl Projects projects for each app user, this will ultimately complicate development and deployment. A better option would be to move to Scoped Admin Users with Oracle 23ai (see below) when you’re able to.

Application Scoped Admin Users with Oracle 23ai

Before Oracle 23ai, DBAs sometimes considered app user deployment necessary for shared databases because they couldn't scope admin users to a single application. For example, imagine application Foo has the following schemas:

FOO_INSTALL
FOO_MAIN
FOO_ACCESS

DBAs would use the following grant to allow FOO_INSTALL to create tables in FOO_MAIN:

grant create any table to foo_install

But with that grant, FOO_INSTALL could create tables in any schema, not just FOO_MAIN and FOO_ACCESS.
With Oracle 23ai, however, new schema-level privileges can be scoped to a select schema:

grant create any table on foo_main to foo_install

This ability bridges some gaps, enabling a more controlled use of admin users across multiple schemas for a single app. DBAs now have the ability to create admin users on shared databases that can only affect a select set of application-related schemas.

Liquibase Logs and Deployment Approach

The choice between admin and app user deployments also influences Liquibase object management. When using an admin user, Liquibase can use the connected (admin) user to log changes across multiple schemas. When using app user deployment, the Liquibase objects are often created within the app users' schemas.

In either case, SQLcl's Liquibase commands accept a parameter named -liquibase-schema-name, which allows you to specify the schema for the Liquibase objects. As long as the connected user has the grants necessary to work with the schema you pass, then that’s where the Liquibase objects will be created.

Choosing the Right Path

Deciding between an admin and app user deployment comes down to security needs, operational requirements, and compatibility with your environment:

Admin user deployment is often the best fit for secure, CI/CD-friendly deployments prioritizing minimal privilege exposure.
App user deployment may be the only option for shared environments (before Oracle Database 23ai) or restricted systems where more granular control is needed.
Custom admin users with Oracle Database 23ai's schema-level grants may be the best way to achieve balance, especially on shared databases.

In summary, the various deployment methods have their advantages and trade-offs. By understanding these, teams can make informed decisions that align with their security policies, deployment automation, and operational constraints, leading to smoother, safer application deployments.

This article discussed the database users used for application installation/updates (compile-time users). I can think of at least two related topics worth examining in future posts:

Schema arrangement for control and security: How app users are arranged and used at runtime has various control and security-related implications. For example, connecting as an object owner comes with certain privileges that don't require grants (e.g., the ability to drop tables you own). How can proper schema arrangement help ensure apps are used as intended? I'll discuss this topic in a future article on SmartDB. Until then, here's a link where you can learn more.
Admin vs. App User Development (as opposed to deployment): Developers need to connect to the database to build out app schemas. Should they connect as an admin or app user to do the work? Hint: the choice for development is related to the choice for deployment.