Database Authentication Roles and Window\Controls Security

Ignition
1

I am trying to prove user roles can be used to easily permint functionality by assignment under the security option.

Setup:
Database olny authentication (No failover)
-Only modified the default authentication table by adding UserId
-I prepopulated a few entries to use
This appeared to cause a little problem with logging in due to the USER_ROLE_MAPPING table.
-Initially it only had two columns UserId and Rolename where I assume UserId would be mapped between the USERS and USER_ROLE_MAPPING table.
-With these pre-built table Ignition complained

[color=#FF00FF]GatewayException: Error creating authentication profile.
caused by Exception: SQL exception during authentication

Ignition v7.4.1 (b906)
Java: Sun Microsystems Inc. 1.6.0_24[/color]

-This was worked around by adding the column Username to USER_ROLE_MAPPING and filling in accordingly

See Image for tables
Problem:
-The project sees all three roles and I can map accordingly but when I log in using any account, the Window or control item never becomes enabled as if the roll is not recognized with the user account
-Under the System Tags, RolesString is empty
-I have also tried adding required roles under the configuration properties without any lunck

Any help is appreciated


SecuritySettings.png747×522 15.6 KB



2

First thing, you got the “Error creating authentication profile” because the “Auto Create” checkbox was checked. If you unchecked it you don’t need the username column in the USER_ROLE_MAPPING table.

In this case I would use the “Expert Configuration” under the “Advanced Properties”. Again make sure the “Auto Create” checkbox is unchecked. That way you can control the queries to authenticate. Here are the three queries:

Authentication Query SELECT Username FROM USERS WHERE Username = '$username$' AND Password = PASSWORD('$password$')

Role List QuerySELECT Rolename FROM Roles

User’s Roles QuerySELECT r.Rolename FROM USER_ROLE_MAPPING r JOIN Users u ON u.UserId = r.UserId WHERE u.Username = '$username$'

After you put those in press Save. Click on the “Verify an Authentication Profile…” to see if it gives back the right roles for your login. Let me know the results of that.

3

Besides a case difference on Users and password being MD5, that seemed to fix everytrhing. Thanks for the input.