I have an Ignition project where I need to read permissions from a SQL database for a specific user, then access them across several views. The permissions are per user, meaning one user's permissions could be different than another's. More information can be found in this post I previously made: Permissions on custom session properties - #7 by parker.williams
It seems that the custom session properties sometimes disappear. I'm aware this is a known bug, but from my attempts to find a fix in other posts, it doesn't look like it's been solved. It seems to happen completely at random so I'm not sure if I could replicate it for a bug report. Some posts suggested reopening the project within the designer, unfortunately the issue happens both in a browser session and in the designer.
Is there any other way I can store the permissions of the logged in user? Specifically so that I don't have to keep querying the database every time I need to check the user's permissions.
I am using Ignition 8.3.2 on Windows 11 for context.
I would use my Integration Toolkit's sessionVarMap() expression function (value lookup) and the corresponding system.perspective.sessionVarMap() scripting function (value assignment and propagation).
In a shared docked view, binding the session user ID information to one or more view custom properties, and use a property change script to run your permissions calculation. Save that result in the sessionVarMap and use its refresh method to propagate it to any views that have the expression function already running.
I've seen session properties disappear, but don't recall seeing the bindings for those disappear. Typically, the binding will execute and recreate the prop at runtime. Also, bindings are more likely to 'disappear' when they are set to private (there's a chance that you are trying to access a property by executing logic in a context that cannot access those properties).
I'd strongly recommend you find a way to properly authenticate a user at the Identity Provider level. Else, there are too many places that you'll be adding unnecessary logic to your workflow in order to permit & restrict access.
This is sort of what I am already doing. I have an Identity Provider setup purely to authenticate the user using the database, and after the user logs in, I have a script that executes that populates the permissions. Below is code pulled from the post I linked that does this.
from util.auth import get_login_row
from util.const import LOGIN_COLUMNS # a list of column names from the db as strings
username = "testuser"
data = get_login_row(username) # pulls the row (all columns) from the database
user_globals = self.session.custom.userGlobals
for column in LOGIN_COLUMNS:
user_globals[column] = data[column]
I looked into using the Identity Provider to store permissions retrieved from a SQL query, but based on the way the database is setup, it's going to be a headache. The developer before me stored permissions as "Yes" and "No" rather than a boolean.
I think the binding might be the way to go. If I remember correctly, there is a scripting function that I can call to refresh the binding. I could have that executed after the user logs in as well. I think the reason behind my issue is that when the userGlobals object disappears, my script errors because it can't access it. I don't think I ever had it create that object if it's missing.
I urge you to add the requirement to return roles at the Identity Provider step, at login, not after. Without roles maintained at the Identity Provider level, you're likely to multiply your efforts to develop a secure app, and additional headaches to maintain it. You're likely to overlook someone's ability to write to specific tags, access projects, execute named queries, etc.
With the Database Authentication, I believe you are able to use Manual Mode in order to configure the exact queries used for returning user roles. I assume you've already developed these queries (within your get_login_row function), perhaps only a slight tweak will be necessary. However, this option would require (I think) that the user's passwords are stored in the database (encrypted). If I understand your workflow correctly, this appears to be the case. If not, you might need to pivot...
Within your existing Identity Provider, there is a section under User Attributes called Roles, you should be able to customize this field in your existing IdP via an expression which calls your existing script. If your script can be tweaked to return the results in a (complicated) SQL query, then this is an unnecessary step, and you should focus on a User's Roles Query within the Manual Mode of DB Auth.
Are you able to share the table schema for the user database (perhaps include a sample row with column headers)? Also, include specifics of your get_login_row function?
x2 what Chris said. Create custom security levels for various tasks, like if they can change a setpoint then in the IDP under roles you assign them from there. Then your on tag expressions can just use the isAuthorized syntax.
Your second paragraph is entirely true. I already have the queries set up using Manual Mode. I do in fact have encrypted passwords in the database.
After thinking about this for a few minutes, I'm starting to understand your statement about multiplying efforts. It is far easier just to lock a view behind a role requirement, than it is to manually check a user's permissions every single time they access the specific page.
Below is the table schema, a sample row, (the data and some of the column names are confidential so I've just replaced it with generic data that is easy to understand), and the relevant python code.
Username
EncryptedPassword
FirstName
LastName
Permission1
Permission2
jdoe
sha1-encrypted-password-goes-here
John
Doe
Yes
No
"""User related operations."""
from java.lang import Throwable
from app.auth.errors import UserNotFoundError
from lib.const import GENERIC_ERROR_MESSAGE
from lib.util import logger, show_message_box
USER_NOT_FOUND_ERROR = "No data was found for that user!"
def _get_login_row(username):
"""Get all permissions for a user.
Args:
username (str): The username to query with.
Returns:
list[str]: All permissions for a user.
"""
params = {"username": username}
data = system.db.execQuery("Authentication/GetLoginRow", params)
if not data or len(data) == 0:
return None
return data
def _populate_user_globals(username, session):
"""Get all user permissions and add them to a Python dictionary.
Args:
username (str): The username to query the database with.
session (Session): The current Perspective session.
Raises:
UserNotFoundError: Raised if the user cannot be found.
"""
if hasattr(session.custom.userGlobals, "Username"):
if session.custom.userGlobals.Username:
return
else:
data = _get_login_row(username)
if not data:
raise UserNotFoundError(USER_NOT_FOUND_ERROR)
ds = system.dataset.toDataSet(data)
columns = system.dataset.getColumnHeaders(ds)
session.custom.userGlobals = {}
user_globals = session.custom.userGlobals
for column in columns:
user_globals[column] = ds[0][column]
def set_user_globals(session):
"""Set a user's permissions globally across the project.
Args:
session (Session): The current Perspective session.
"""
try:
_populate_user_globals(session.props.auth.user.userName, session)
except UserNotFoundError as u:
show_message_box("info", "User permissions were not found, please check logs!")
logger.warnf("User permissions population failed: %s", u)
except Exception as e:
show_message_box("info", GENERIC_ERROR_MESSAGE)
logger.warnf("User permissions population failed: %s", e)
except Throwable as t:
show_message_box("info", GENERIC_ERROR_MESSAGE)
logger.warn("User permissions population failed", t)
I'm aware that the "Yes" / "No" permission columns aren't good practice. The goal of this project is purely moving a web app into Ignition, so unfortunately overhauling the database is not within scope.
You're passing in a single username param to that named query, which is exactly what the User's Roles Query is expecting to pass. The difference is that it is expecting a list of roles to be returned in a single column. Something like the following might work (disclaimer - untested...):
SELECT PermissionName
FROM YourTableName
CROSS APPLY (
VALUES
('Permission1', Permission1),
('Permission2', Permission2)
) AS Permissions(PermissionName, PermissionValue)
WHERE Username = ?
AND PermissionValue = 'Yes';
You'll want to update the column names for your specific permissions, table name, etc.
