Use SQL Plus and Oracle Proxy User

You can use the SQL Plus proxy user feature with Liquibase Pro 4.4.1 and later versions if you have multiple users sharing the same schema and do not want to provide the schema credentials to each user.

Using a proxy user to connect to a schema, you do not need to share the password, only the username. It means that a proxy user is a user who connects to the database on behalf of another user, usually a client user. A proxy user connects with the username and password set for them without knowing the client user password.

Note: For more information about the Oracle proxy user setup, see Proxy Authentication.

With Liquibase Pro, you can use the runwith:sqlplus attribute and connect to the environment that requires a proxy user. You can set URL properties which contain your proxy user configuration to deploy SQL Plus changesets to a database.

To run SQL Plus via a proxy user, follow these steps:

  1. Add an SQL Plus changeset to your changelog file:
    2. --liquibase formatted sql
--changeset user:45679-runWith_sqlplus-example runwith:sqlplus
CREATE TABLE mytable (
id           NUMBER         NOT NULL,
description  VARCHAR2(50)   NOT NULL
);
  1. Set proxy values as your username in the Liquibase properties file, environment variables, or on the command line at runtime:

    2. Example Liquibase properties file

    url: jdbc:oracle:thin:proxy_user[client_user]/@host:port/servicename
username: proxy_username[client_username]
password: proxyuserpass

    Environment variables example

    LIQUIBASE_COMMAND_URL="url=jdbc:oracle:thin:proxy_user[client_user]/@host:port/servicename"
LIQUIBASE_COMMAND_USERNAME="proxy_username[client_username]"
LIQUIBASE_COMMAND_PASSWORD="proxyuserpass"

    CLI example

    liquibase update --url jdbc:oracle:thin:proxy_user[client_user]/@host:port/servicename --username proxy_username[client_username] --password proxyuserpass --changelog-file changelog.sql

    Note: The proxy_user is the user that will initiate the connection and emulate the client_user. The client_user is the user whose privileges, schema, objects will be available for the session.

  1. Run the update command to apply changes:
    2. liquibase update --changelog-file changelog.sql

Related Links