Can't log in as SYS to Oracle 21.

[bennyr1003]

Which version and edition of Flyway are you using?

8.5.8

If this is not the latest version, can you reproduce the issue with the latest one as well? (Many bugs are fixed in newer releases and upgrading will often resolve the issue)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Maven plugin

Which database are you using? (Type & version)

Oracle Express 21C

Which operating system are you using?

Windows

What did you do? (Please include the content causing the issue, any relevant configuration settings, the SQL statement(s) that failed (if any), and the command you ran)

I can't seem to log in as SYS (maybe it's because I'm using Oracle 21) I've tried to use the pom.xml to store the user and password, I've tried in a configuration file. I've tried to pass it in as maven parameters. But every time I get

org.flywaydb.core.internal.exception.FlywaySqlException: Unable to obtain connection from database (jdbc:oracle:thin:@localhost:1521/XEPDB1) for user 'SYS as sysdba': ORA-01017: invalid username/password; logon denied

tried this in pom.xml

            <user>SYS as SYSDBA</user>
            <password>db</password>

tried this in configuration

flyway.url=jdbc:oracle:thin:@localhost:1521/XEPDB1
flyway.user=SYS as sysdba
flyway.password=db

tried this in configuration
flyway.url=jdbc:oracle:thin:sys/db@//localhost:1521/XEPDB1

tried running maven like this
mvn '-Dflyway.user=sys as sysdba' '-Dflyway.password=db' flyway:migrate
with configuration like this
flyway.url=jdbc:oracle:thin:@localhost:1521/XEPDB1

When I create my own users manually I can use them to log in successfully.

Just for context, I want to run a migration using sys to create the schema and grant the user the roles it needs. Then I run another instance of flyway to run the actual migrations using the new schema created.

For the sys migration, the defaultSchema is set to the schema I want to create.

What did you expect to see?

Log in as SYS successfully

What did you see instead?

org.flywaydb.core.internal.exception.FlywaySqlException: Unable to obtain connection from database (jdbc:oracle:thin:@localhost:1521/XEPDB1) for user 'SYS as sysdba': ORA-01017: invalid username/password; logon denied

Am I just using Oracle 21 too soon?

Thanks.

[DoodleBobBuffPants]

Flyway uses JDBC to connect to the database, and the syntax for proxy users is slightly different
Could you try specifying the user as sysdba[sys]?

This should work in most cases, but if it doesn't, there is an alternative approach documented here

[bennyr1003]

I was using this placeholder in a script
flyway.placeholders.user=my_new_schema
And the script had
ALTER USER ${user} IDENTIFIED BY ${password};

The exception stated that
org.flywaydb.core.internal.exception.FlywaySqlException: Unable to obtain connection from database (jdbc:oracle:thin:@//localhost:1521/XEPDB1) for user 'sysdba[sys]': ORA-01017: invalid username/password; logon denied

But that was a lie. I monitored the failed login attempts on the database and found the logins were actually attempted by user
my_new_schema

I changed the placeholder to
flyway.placeholders.user_name=my_new_schema

And used
flyway.user=sys as sysdba

And now it's successful.

I guess I broke it by using a placeholder called user.

[DoodleBobBuffPants]

Glad to hear it's working

Flyway passes the placeholders to the driver as additional properties. Placeholders with reserved names can then override some values, which is what was happening with user in this case

[bennyr1003]

The error message is displaying the wrong user that attempted login. Wouldn't you want to correct that?

[DoodleBobBuffPants]

Instead we're thinking that the current behaviour is incorrect

The placeholder behaviour won't change, however when connecting to the DB, flyway.user should take precedence over a user set via a placeholder

I'll reopen this issue to track that change