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.
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 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
We need to ensure that whenever a new table is created in the
READ access is granted to the
READ_ONLY role, and
SYNONYMS are created for the tables in the
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.
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; /
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="http://dtorg.liquibase.com/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://dtorg.liquibase.com/xml/ns/dbchangelog http://dtorg.liquibase.com/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="http://dtorg.liquibase.com/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://dtorg.liquibase.com/xml/ns/dbchangelog http://dtorg.liquibase.com/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
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).