Kenneth_Barber

A spreadsheet to convert access control lists (ACLs) to tables

Blog Post created by Kenneth_Barber Champion on Sep 30, 2020

Answer these questions based on the table below:

  • Which PI Identities have access to the PIPOINT table and what type of access do they have?
  • To which tables do PI Connectors have write access?

 

The table below is a snippet of a Database Security table from PI System Management Tools.

 

Table NameAccess Control List (ACL)
PIDBSECPI Vision: A(r) | PI Administrator: A(r,w) | PI Data Collection Manager: A(r)
PIDSPI Point Creator: A(r,w) | PI Administrator: A(r,w) | PI Connector: A(r,w) | PI Connector Relay: A(r,w) | PI Data Collection Manager: A(r)
PIMAPPINGPI Reader: A(r) | PI Administrator: A(r,w) | PI Vision: A(r)
PIModulesPI Point Creator: A(r,w) | PI Administrator: A(r,w)
PIMSGSSPI Administrator: A(r,w) | PI Connector: A(r) | PI System Health Connector: A(r)
PIPOINTPI Reader: A(r) | PI Point Creator: A(r,w) | PI Administrator: A(r,w) | PI Vision: A(r) | PI Buffer Subsystem: A(w) | PI Connector: A(r,w) | Buffered PI Interface: A(r) | PI Connector Relay: A(r,w) | PI Data Collection Manager: A(r) | PI System Connector: A(r) | PI Analysis: A(r,w)
PIReplicationPI Administrator: A(r,w) | PI Data Collection Manager: A(r)
PIUSERPI Administrator: A(r,w) | PI Connector Relay: A(r) | PI Data Collection Manager: A(r) | PI Vision: A(r)

 

The 1st question is easy. Find the PIPOINT table in the Table Name column and the answer is in the Security column.

 

The 2nd question is much harder. Because the table is small, we can take a minute to eyeball where PI Connectors have write access. However, this approach does not scale well if we were to deal with the permissions of hundreds of PI Points and elements instead.

 

Why is the 2nd question so much harder? It's because all of the PI Identities and their permissions are all crammed into a single cell for each Table Name. The table is grouped by Table Name and does not allow grouping, sorting, or filtering by PI Identity or access (read or write).

 

It would be much easier to answer the question if the table was arranged like this:

 

Table NamePI IdentityRead Access?Write Access?
PIDBSECPI Web API Indexed Search CrawlerYesNo
PIDBSECPI AdministratorYesYes

 

or like this:

 

Table NamePI IdentityAccess
PIDBSECPI Web API Indexed Search CrawlerRead
PIDBSECPI AdministratorRead
PIDBSECPI AdministratorWrite

 

This is precisely the point that I was trying to make in this suggestion. Please show it some love. The access control list (ACL) format is difficult to read and use, and, in my opinion, PI should have an option to export permissions in a purely tabular format wherever ACLs are currently used.

 

To be more proper, we would say that:

  • the ACL version of the table is unnormalized (i.e. it crams a bunch of values into a single cell)
  • the "pure tables" are in 1st normal form (i.e. it doesn't cram a bunch of values into a single cell)

 

A temporary solution

 

I've attached a spreadsheet that converts the unnormalized ACL tables into normalized tables.

 

It supports ACLs from PI Builder (from both PI Points and PI Asset Framework elements) and from a CSV export of the Database Security table in PI System Management Tools. It also supports both of the proposed table formats above. You can easily adapt the Power Query code in the spreadsheet to suit your own projects.

 

I hope that you find it useful and let me know of any feedback that you have for it. My hope is that PI will natively support a better format than ACLs and therefore make my spreadsheet useless.

Outcomes