Loading Security Levels other than Roles from Database

I am looking to allow users to only access projects that I have explicitly allowed them to access.

I believe that the best way to do this would be to have a security level that is essentially parallel to roles as shown in the image below.

image

I am using a SQL database with manual queries for my user source. Is there a way to link a table\tables in a database to these security levels when a user logs in? I am pretty sure that I cannot write to session.auth.user.roles post login.

What I want to do works if I create user grants, but I am looking for a more automated way to do this.

Welcome @Jkhadley, If you are using security levels I assume you are using an IdP based authentication strategy.

Security levels are mapped to roles either by placing them in the Roles security level and naming them exactly the same as the role (it implicitly maps them), or you can use Security Level Rules and explicitly map security levels to roles through expressions (this gives the most flexibility and gives the ability to have quite complex security architectures).

I don't fully understand what you are trying to achieve, but hopefully the above helps. If not, could you provide a more detailed example so we can work through it further?

Yes I am using idP with a SQL database as the user source. I have the Role security levels working correctly. I believe the queries below map the roles to the roles security levels.

image

My security levels would more realistically look like this

image

The database schema below may also help clear things up.

I would like to make it so if a user doesn't have access to a facility and/or isn't part of a company that has access to that facility, they can't load views related to that facility. This is easy enough to do with view permissions and works if I create user grants, but I would prefer to map the security levels to my database like with the roles if that makes sense.

The issue with the security level rules is that I don't think that I can get SQL data with an expression.

I think that I need to somehow use the "Extra Properties" query in the user source to do this mapping somehow or load the security levels after the user is logged in. I haven't been successful in finding where the extra properties data is loaded and I don't believe that I can modify the security levels once the user is authenticated.

Thanks for your help!