Configuring Access to Database Views

This page is for administrators who need to configure direct access to the PhixFlow database.

It is possible for database users who have sufficient permission to access the PhixFlow database tables directly. This can be useful if they want to use database queries to generate reports.

We recommend you configure a database role to restrict database users to see views on steam data tables only. This prevents the database user having direct access to all data tables such as those with operational or configuration data.

Before you start, we recommend you have a list of any the tables that are already configured to create a database view. In the repository right-click the Tables heading and select View Detailed List. You can filter the detailed list to show those with a database view.

In the PhixFlow Database

1. Create a role in the PhixFlow database.

create role myrole;

2. Grant this role to any database users that require access to the database views. The following SQL command also sets the role as the default.

--MariaDB
grant 'myrole' to 'myuser'@'%';
set default role 'myrole' for 'myuser'@'%';
--Oracle
grant myrole to myuser;
alter user myuser default role myrole; 
--SQL Server
alter role myrole add member myuser;

For a MariaDB database: set the GRANT OPTION privilege for the PhixFlow database user; see the MariaDB documentation on The Grant Option Privilege.

In the PhixFlow Application

3. Set up the specific tables that need to be available to generate database views. In the Table properties → Advanced → Database View, enter a string in the format v_xxxx. When the table is published to the database, PhixFlow creates a view for the table data.

If you have already created database views, they will only become available to database users after you republish the table data.

4. In the System Configuration→ Advanced → Database View Role, enter the name of this database role.

This option refers to a role in the PhixFlow database not in the PhixFlow application.

Test the configuration

After configuring Database View Role, you must test that it is correctly configured by triggering PhixFlow to publish a database view.

6. Find table properties → Advanced → Database View set, untick the Enabled box and save the table.

7. Tick the Enabled box again and resave the table.

8. Access the table data, so that PhixFlow publishes it and recreates its database view. To access table data do one of the following:

Open a view.
Run analysis on a model that contains the table.
In the Administration menu, select Other Options → Publish Tables. This publishes all pending changes and may take some time.

If the configuration is incorrect, publishing a table with a database view will fail. Check the console for error messages to help you correct the configuration.

When the configuration is correct, publishing will complete successfully.

Republish existing database views

8. To republish existing database views, you can either follow steps 6 to 8 to trigger republishing. Alternatively, you can open any view for the table.

Remember that the Administration → Other Options → Publish Tables option only publishes changed table data. You need to use the disable/enable method or opening a view to "touch" the data, so that PhixFlow knows to republish it.

If publishing fails, PhixFlow will not attempt to republish until the table configuration changes (steps 6-8 above).

Managing the Database Role

When the Database View Role is set, database views are available to database users with this role. This is equivalent to running:

grant select on v_myview to myrole;

When new database views are created, they are automatically made available to the database users.

Once the database role is created, making changes to the System Configuration→ Advanced → Database View Role name does not update existing views or remove them from the previous role.