Issue running sqlplus as sys

I’m using 4.3.4 Liquibase Pro, trying to run my build scripts as sys (dba) as that is how our scripts are run locally in a docker container. I can connect for status using a command as follows:
Liquibase --changeLogFile=masterchangelog.xml --url="jdbc:oracle:thin:@localhost:1521/orclpdb1" --username='sys as sysdba' --password=pw status

but when the files actually run with the above parameters and update, they fail with sqlplus returned a code of 1.

In the debug logs, the ORA error being thrown is:

ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

I’m using SQL changesets with this header at the top:

--liquibase formatted sql
--changeset ***:*** runWith:sqlplus

I’ll add that these commands work when fired against an AWS RDS Oracle database, using a sys equivalent user since AWS doesn’t allow you to use sys directly.

Thank you for any suggestions as to what’s going on here.

Hi @cjs321,

The error you are getting is of Oracle (as you can see in the error code is ORA-28009, which is an Oracle error). In this scenario, this error is coming from Liquibase running SQLPlus. When you make a connection via SQLPlus as SYS, the Liquibase only connects as the user SYS and does not allow to connect SYS as SYSDBA , because of spaces in between.

The solution for this, and the best practice is to connect as a non SYS user. I would suggest you to create an automation user, e…g, LB_USER and give the user permissions to deploy to all the schemas. This way, you will have better control over what’s allowed to be deployed to automation.

1 Like

Thanks @aditi - are there any good guides on how to establish this LB_USER or equivalent and give it sys-like powers? I would need to establish this user first before executing all my datascripts.