Maven integration is missing executeSql goal

Liquibase Development
1

There is an open issue for this: Maven integration is missing executeSql goal · Issue #3793 · liquibase/liquibase · GitHub

My question:
After applying the changelog (which is actually supplied by 3rd party) as the ‘admin’ user, we need to update the (Oracle) permissions so that the ‘app’ user has CRUD access to all the tables (and similarly the ‘readonly’ user has readonly permissions to all the tables). Applying this permissions is totally idempotent, so we don’t need to keep track of it as a separate DB change.

Is there another, better way of achieving this other than using execute-sql ?

2

I would create additional changesets that contain the required GRANT statements. At my company all SQL is apply via Liquibase, that way all SQL required for a rebuild is in the changelogs.

3

Thanks for the advice.

I should point out that our permission update is actually a PLSQL block which is doing a select, a loop and executing dynamically created SQL. We can apply it to any schema and it will apply the permissions to all tables in that schema.

FOR R IN (SELECT table_name from user_tables) LOOP
    EXECUTE IMMEDIATE 'grant select on ' || R.table_name || ' to app_ro_role';
END LOOP

Let me test my understanding, as I am not sure how your suggestion would work in practice

  1. We apply 3rd party changelog (which, say, consists of a baseline and 7 updates)
  2. We apply our permissions as an additional changeset (history will show a baseline and 8 updates)
  3. We upgrade 3rd party lib - which brings in an 8th update to their schema (we load the changelog from their jar)
  4. Liquibase detects the schema update and applies it
  5. We need to re-apply our permissions to any new tables created, but our previous changeset has already been applied, so will not get applied again. Do we need to create a new changeset to re-apply the same SQL ? I guess it means we need know ahead of time the jar upgrade is bringing in a schema upgrade.

Many thanks for the help & advice

4

You should put the changelog that applies your grants permanently at the bottom of your master changelog, so it always runs last. Then add the “runAlways=true” to the changeset so it runs at every execution. Here is an example:

--changeset BOB:execute_recompile_invalid endDelimiter:/ runAlways:true
--comment: Recompile all invalid objects
BEGIN
  DBMS_UTILITY.COMPILE_SCHEMA(schema=>'XYZ',compile_all=>FALSE);
END;
/  
--rollback empty