How to handle Oracle synonyms with Flyway 2.0.1?

Question

I'm using flyway for a long time now. Very nice/complete tool!

I'm actually facing an unexpected situation... I have two schemas:

• An Owner pocessing the tables and sequences
• A User using synonyms to access the Owner objects

The custom does not want me to give the 'grant create any synonym / drop any synonym' rights to the Owner. But I can provide the 'grant create synonym' to the User.

So I need to

1. Create the tables/sequences (connected with the Owner)
2. Grant Select, Delete... to my User schema (connected with the Owner)
3. Create a synonym for the User to access the Owner objects (connected with the User)

The point 3 is my problem.

If I give the 'grant any synonym' to the owner and using the flyway placeholders, I can do something like that:

CREATE OR REPLACE SYNONYM ${user}.WORKITEMINFO FOR WORKITEMINFO;

BUT I cannot ;)

So the solution I implemented is to use java migrations using another DataSource connected to the User schema. (My MigrationUtils.replacePlaceholders allows me to get access to any of the flyway.properties properties as placeholder)

private final static String[] queries =
        {"CREATE OR REPLACE SYNONYM TASK_COMMENT FOR ${flyway.user}.TASK_COMMENT"}

@Override
public void migrate(final Connection connection) throws Exception {

    final DataSource dataSource = MigrationUtils.getUserDataSource();

    final Connection connectionForMigrations = dataSource.getConnection();

    final JdbcTemplate jdbcTemplate = new JdbcTemplate(connectionForMigrations);
    new TransactionTemplate(connectionForMigrations).execute(new TransactionCallback<Void>() {

        @Override
        public Void doInTransaction() {

            try {
                for (final String query : queries) {
                    final String replacedQuery = MigrationUtils.replacePlaceholders(query);
                    LOG.debug("Executing SQL: " + replacedQuery);
                    jdbcTemplate.executeStatement(replacedQuery);
                }
            } catch (final SQLException exc) {
                throw new FlywayException("Could not drop synonym", exc);
            }
            return null;
        }
    });

}

Is there another way to resolve this situation?

Thank you!

Answer 1

The best way would be to qualify the schema name in the application.

If that's not possible and the application uses only SQL (not PL/SQL) to access the "Owner" objects, you could avoid synonyms at all by changing the default schema in an after-logon trigger:

create or replace trigger USERX.a_logon_USERX
after logon on USERX
BEGIN
  EXECUTE IMMEDIATE ('ALTER SESSION SET current_schema=standard');
END;

However, it's somewhat hard that to grasp that this technique fails when the application sends PL/SQL blocks ("begin ... end;") to the database. PL/SQL is compiled and therefore cannot consider the current schema.

Answer 2

Use -Dflyway.schemas=OWNER_NAME while you run flyway.