Automatically Manage Permissions and Synonyms Using Liquibase

There are situations where grants or synonyms need to be created for all objects that meet certain criteria, one example is when they belong to a specific schema. You could rely upon code reviews to make sure that developers remember to include the grants and synonyms in their scripts. However, wouldn’t it be nice to have them generated automatically during each release? This post will show you how to configure your Liquibase project to reliably generate the required grants and synonyms.

Concept

The concept is to write one or more SQL scripts that dynamically create missing grants and synonyms. Then, configure the Liquibase project to execute the scripts as the last step of each deployment. In this example, we use an Oracle database. However, the same process will work for other database platforms such as Postgres and DB2.

An example scenario

Our Oracle database contains two schemas: MFG and REPORTER.

The MFG schema owns all tables, but the REPORTER schema has READ access to the tables via the READ_ONLY role. To make access easier, the REPORTER schema also has synonyms for all the tables in the MFG schema.

We need to ensure that whenever a new table is created in the MFG schema, READ access is granted to the READ_ONLY role, and SYNONYMS are created for the tables in the REPORTER schema.

How to automatically manage permissions and synonyms for each deployment
You will need to have a Liquibase project setup to deploy your application changes. If you have not created a project, start here to install Liquibase and configure your project. After your project is configured, follow these steps to automate the creation of grants and synonyms for each deployment.

1. Create scripts to generate grants and synonyms.

Two simple scripts will create the required grants and synonyms. By using a scripting language, such as PL/SQL, you have the flexibility to implement complex logic to create grants and synonyms.

• auto_permissions.sql
• auto_synonyms.sql

auto_permissions.sql

declare
  v_statement varchar2(512);
 
  cursor grants is
  select o.owner, o.object_name
  from dba_objects o
  where o.owner = 'MFG'
  and o.object_type = 'TABLE'
  minus
  select owner, table_name  /* exclude objects that have grants */
  from dba_tab_privs
  where grantee = 'READ_ONLY'
  and owner = 'MFG'
  and privilege='SELECT';
 
begin
-- Create Grants
  for obj in grants loop
    v_statement := 'grant select on '||obj.owner || '.' ||obj.object_name ||' to READ_ONLY';
    begin
    execute immediate v_statement;
    end;
  end loop;
end;
/

auto_synonyms.sql

declare
  v_statement varchar2(512);
 
  cursor synonyms is
  select o.owner, o.object_name
  from dba_objects o
  where o.owner = 'MFG'
  and o.object_type = 'TABLE'
  minus
-- synonyms that already exist
  select s.table_owner, s.table_name
  from dba_synonyms s
  where s.owner = 'REPORTER';
 
begin
 
-- Create Synonyms
  for obj in synonyms loop
    v_statement := 'create synonym REPORTER.' || obj.object_name || ' for ' || obj.owner || '.' ||obj.object_name;
    begin
    execute immediate v_statement;
    end;
  end loop;
end;
/

2.  Configure a changelog to run the scripts.

To keep our changelogs organized, we will create a separate changelog for the Auto Permission/Synonym scripts. Some special attributes for the changesets are needed to let Liquibase know that they should be run for every deployment.

• runAlways=”true”  — This tells Liquibase to run the script for every deployment.
• runOnChange=”true” — This tells Liquibase to run the script after it is updated. If this is not set, you will get an error if the script is modified.
• failOnError=”false”  — This tells Liquibase not to fail the deployment if the script fails. This is optional depending upon how you want your deploy process to handle failures of grants and synonyms.

The change type for the changeset will be sqlFile. Set the “path” to the location of the SQL file which will create the grants or synonyms. Since PL/SQL contains semi-colons ;, we need to set the Liquibase delimiter to a different value using the endDelimiter attribute.  If you have a Liquibase Pro license, you can also use the runWith changeset attribute to execute the scripts using SQLPlus. Using SQLPlus to execute your scripts avoids problems with processing highly-specialized SQL.

Here is an example of the changelog that defines changesets to execute the two SQL scripts.

<?xml version="1.0"  encoding="UTF-8"?> <databaseChangeLog
          
          xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi_schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
          http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.10.xsd">
<changeSet  author="mmb"  id="auto_permissions" runAlways="true" runOnChange="true" failOnError="false">
    <sqlFile  dbms="oracle"
            encoding="UTF-8"
            endDelimiter="n@"
          path="/opt/liquibase/projects/lb_auto/scripts/auto_permissions.sql"
            relativeToChangelogFile="false"
            splitStatements="true"
            stripComments="true"/>
</changeSet>
<changeSet  author="mmb"  id="auto_synonyms" runAlways="true" runOnChange="true" failOnError="false">
    <sqlFile  dbms="oracle"
            encoding="UTF-8"
            endDelimiter="n@"
            path="/opt/liquibase/projects/lb_auto/scripts/auto_synonyms.sql"
            relativeToChangelogFile="false"
            splitStatements="true"
            stripComments="true"/>
</changeSet>
</databaseChangeLog>

3. Incorporate the new changelog into your existing changelog.

Use an include tag to add the new change log to the END of your main changelog. By placing the changelog at the end of the file, the scripts to execute the grants and synonyms will always run after all other changes have been deployed.

<?xml version="1.0"  encoding="UTF-8"?> <databaseChangeLog
          
          xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi_schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
          http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.10.xsd">
  <include  file="/opt/liquibase/projects/lb_auto/release1_0.xml"/>
  <include  file="/opt/liquibase/projects/lb_auto/release2_0.xml"/>
  <include  file="/opt/liquibase/projects/lb_auto/auto_perms_syns.xml"/>
</databaseChangeLog>

4. Deploy your changeset.

Run the Liquibase update command. This will execute the scripts to generate the required permissions and synonyms. If you need to troubleshoot the executed SQL, set the logLevel to info, as follows.

liquibase --logLevel=info update

Summing it up

Automatically managing permissions and synonyms is a great way to reduce development time and increase the reliability of deployments. If you have questions or run into issues, be sure to check out our forum and our chat room. If you’re looking for more dedicated support options, consider starting a free trial of Liquibase Pro and trying out our support (and more advanced features).