CODEWITHSUNDEEP
oracle-databasecommand-lineflyway
Elvar
Elvar

Reputation: 21

Error when using "connect sys as sysdba" in FlywayDb migration

I am trying to convert our Oracle database creation scripts to use Flyway so that distributed development on the database becomes easier. These scripts consists mostly of sql commands and require almost no change so that is fine. I am using the command line version of Flyway(4.0.3) against an Oracle 11.2 Express database.

However there is one script that I am having some trouble with, namely when creating the schemas, the roles and granting privileges to those schemas/roles.

Here is an example how the sql migration file looks like:

alter user SAMPLEDB identified by ${schemaPassword};
alter user LOCDB identified by ${schemaPassword};
grant create session,create view,resource to SAMPLEDB;
grant create session,create view,resource to LOCDB;

connect sys/PASSWORD@xe as sysdba;

grant EXECUTE ON SYS.DBMS_PIPE to SAMPLEDB;

The file is of course much larger but this should be enough to give the general idea on how it looks like.

So when I run flyway migrate on those sql commands I get the following error message:

SQL State  : 42000
Error Code : 900
Message    : ORA-00900: invalid SQL statement
Statement  : connect sys/PASSWORD@xe as sysdba

If I remove the connect sys/PASSWORD@xe as sysdba line then I get this error:

SQL State  : 42000
Error Code : 1031
Message    : ORA-01031: insufficient privileges
Statement  : grant EXECUTE ON SYS.DBMS_PIPE to SAMPLEDB

I am using the Oracle "system" user that is configured in the flyway.conf config file.

So the question is, can I somehow change the user to the sysdba user in the sql file or should I create the schema users/roles and grant privileges to them manually before running the flyway migrate command?

This task would only be done when creating a new database which should not happen very often.

Hope you can help me with this :)

Upvotes: 2

Views: 806

Answers (1)

Jan
Jan

Reputation: 109

I don't think that you can use a "connect"-SQL-Statement in a Flyway migration, as this is SQL*Plus-Syntax. What you could do is migrating the first few migrations that need a more privileged user via the command line before you start the migration using the config-file:

flyway -configFile=path/to/flyway.conf -user="SYS as sysdba" -password=<password> -target=<#ofInitialMigration> migrate

The command line parameters would then override the parameters specified in the config file and thus you could execute your first Migration as SYS. After that you can change back to your previous way of initiating the flyway migration and it should then just skip the first migration, as it was already applied before. Both calls could be combined in a shell-script as to minimize manual effort.

Of course, this only works if your initial Migration are really the very first Migrations. It may not be ideal but I think it is better than granting everything manually beforehand.

Upvotes: 2

Related Questions