Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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. However this is insecure, as it allows the database user to see all PhixFlow data. To restrict access to specific data, you can configure a role to access database views

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.

Tip

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

...

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.

Code Block
languagesql
--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;

...

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

Tip

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

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

Note

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

...

Tip

Remember that the 

Insert excerpt
_administration
_administration
nopaneltrue
 →  Other Options → Publish Streams option only publishes changed stream data. You need to use the disable/enable method or opening a stream view to "touch" the data, so that PhixFlow knows to republish it.

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

...

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. 

...