# Database Authentication Roles and Window\\Controls Security

**URL:** <https://forum.inductiveautomation.com/t/database-authentication-roles-and-window-controls-security/4437>\
**Category:** Ignition\
**Created:** [May 9, 2012, 7:15pm UTC](https://forum.inductiveautomation.com/t/database-authentication-roles-and-window-controls-security/4437 "2012-05-09T19:15:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![moralesm](https://avatars.discourse-cdn.com/v4/letter/m/3da27b/32.png) [@moralesm](https://forum.inductiveautomation.com/u/moralesm)\
**Post date:** [May 9, 2012, 7:15pm UTC](https://forum.inductiveautomation.com/t/database-authentication-roles-and-window-controls-security/4437/1 "2012-05-09T19:15:50Z")

</div>

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  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/f/fd8b30d9c9a1f29d129bb70479bd1cf207f7e344.png)

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/c/ce36064fb2c74e225ad3f997e3ed0825dd708ee8.png)  
  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/f/f9a086be57cd6e834c4a00d7005b857891d29167.png)

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [May 10, 2012, 12:27am UTC](https://forum.inductiveautomation.com/t/database-authentication-roles-and-window-controls-security/4437/2 "2012-05-10T00:27:48Z")

</div>

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 Query`SELECT Rolename FROM Roles`

User’s Roles Query`SELECT 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.

---

<div class="post-metadata">

**Author:** ![moralesm](https://avatars.discourse-cdn.com/v4/letter/m/3da27b/32.png) [@moralesm](https://forum.inductiveautomation.com/u/moralesm)\
**Post date:** [May 10, 2012, 5:07pm UTC](https://forum.inductiveautomation.com/t/database-authentication-roles-and-window-controls-security/4437/3 "2012-05-10T17:07:57Z")

</div>

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