How to Use the Custom SQL Editor Securely

Security Best Practice: How to Use the Custom SQL Editor Securely

Introduction

The Custom SQL Editor gives a lot of power and flexibility to allow access to your data in any way that is supported by the database engine. As a Laserfiche administrator, you may want to maintain control over your database while also allowing access to custom queries. The following principles will help you create secure data sources and secure custom data queries for use in your workflows and business processes.

Using a Secure Data Source

Create your data source with a limited access user. When creating your data source, you will need to provide a service account user to access your database. This user’s rights will limit what your data source can be used for. Using a service account with only the necessary rights will help make sure no unwanted changes occur.

Example: Our IT Admin is setting up a data source that will be used to look up customer information in the database across tables. Because this data source will only be used for reading data, the IT Admin creates the data source with a user who only has rights to read data, not insert new data or perform table manipulations.

Use the ‘Allow Custom SQL’ checkbox. When creating a direct data source, you have the option to allow Custom SQL queries. If this box is not checked, only the SELECT, INSERT, and UPDATE query editors will be available to use with this data source.

Using Laserfiche User Rights

Configure user rights to process automation. Refer to Managing Accounts in Process Automation to make sure only users who need to be developing processes have rights to access Process Automation and create rules there.

Using Projects and Teams

Teams and Projects are the main way to secure resources inside of Process Automation. By using these tools we can make sure only users we expect have rights to use Custom Queries.

Use Projects to group similar processes and security levels. By default creating projects for resources, we can control the security for those resources more easily while also grouping similar resources together.

Example: Accounting and Human Resources both want to create Business Processes that use custom queries to select, update, and insert items. By creating a separate project for Accounting and Human Resources, we can provide access to only the database that is relevant to each department.

Use Teams security features to further secure access to resources. Refer to Team Security Features and Determining Access Rights to use Team Roles within projects to secure access to your resources.

Use the Global project to create secure resources and then share them with team projects. By putting most regular users and resources inside a team, we can use the Global project as a place to put secure resources and share them with individual projects that need them. By giving most users access within a Team but not to general Process Automation access rights, users will only be able to access their team’s resources, not those in global. These global resources can then be shared to other projects for use within processes, but not viewable or editable by default.

Example: The Accounting Department wants to be able to perform a complex query which updates internal employee information. This will require a custom query, but we don’t want to give general access to this database. Our IT Admin instead creates a data source in the global project which can access this database. Then they create a custom query rule on the database within the Global project as well. They then share that query to the Accounting project. The Accounting workflow designer will then be able to use that query within their workflow, but not view or edit it.