Database validation equal / not equal to

Howdy everyone.

Can we have server connect validation that can check if a row equal to another row or equal to a custom literal text? I can achieve what I want using walkaround like by using condition then manipulate other validation to output the message but I wish it can be done in a proper way.

Usage case:

I want to use validation to disable login to account with status inactive, so that I can output the message ‘account is inactive’ on the spot without having the login page to load or redirect.

Let me know if this feature already available or consider it as a feature request.

Thank you.

I believe you can do this with the Security Provider option in the Server Actions. You could create a Permission entitled Inactive and utilize it that way.

In the alternative, you can do what I did and create a table for users and a table for permissions and then a third table to join the two. I did a third table to join the two because my users may have multiple permissions and it was easier for me to manage that way.

Thanks for your suggestion. Will this step load the page, because as far as I’m aware, security provider will load the page.

What I do is a combination of Sever Side Security Provider and Security Login and then on the client side, I use Security Provider Enforcer. I also add an additional layer of protection on the Server Side by adding Security Restrict on all my database queries.

Here is a video that might help you.

Additionally, there is some documentation that can help as well.

Hi. I understand the proper way to do it according to the docs is by using Security - Restricting Access to Your Page. But this step will load the page in order to work. In my project, I have a login modal that will load the existing page if login success with particular sessions been set. It is a SPA sort of. Using security step, it will run server connect security provider that will go to or load the particular page.

What I actually want to achieve is by using database validation, I want the security login to run only when the validation passed, i.e when column user_level == active. If user level != active, the security login step should not run at all.

@transcoderm you can use a condition step to check if user is active.
In the ‘then’ step add the steps that need to be executed if user is active.
In the ‘else’ step add a response (for example 403) and show a notification on the page then.

1 Like

Alright. Understood. Thanks again for your response. :+1:

Hi @transcoderm,

Did you get this working correctly?

I’m also trying to add a check for user status. I’m storing the status value in a table separate to the main user login and password table (referenced by a foreign key on user_id).

In the user_login server action, only Security Provider and Security Login have been added. There’s no database query to evaluate the status of a user and then add a condition based on that query. I tried to add this query and evaluate status in a condition but the login action stopped working altogether.

Could you please share how you added this condition if it’s working correctly for you?

Thank you :slight_smile:

I know 2 ways to achieve this, 1st using method as Teodor explained and another using validation check - this way you can output validation message directly onto the form field, but it’s quite hacky. Both methods work for me.

For the 1st method, you can use conditional step to check the value based on single query (e.g. with status == ‘active’) filtered by email field value. Then, create a response on the catch or else step and assign error code (e.g. 403). On your web page, use dynamic attribute to show error item (e.g. alert or whatever) when the particular server connect returns 403 code.

Sorry I’m currently not able to assist in detail at the moment.

Could you create a view based on a table join of the two tables and validate against the view?

Hi @transcoderm and @Hyperbytes

Thank you for the responses. I have created a single query, joined by u_id with an equal condition set to Security Provider: identity. Then I have set a condition to check the user status == 1 and go to the Step Security Login when this condition is met; else display a comment. But this has stopped the login server action to work completely. This is how I have added the user_login server action:

Screen Shot 2020-10-12 at 11.39.57 am

I’m not sure whether I’m adding the Query and Condition in the correct order or a completely different set up is required to get this working.

Thank you for your assistance.

Although this is a solution, but not a good one in terms of logic and maintenance.

Eg: if a user is logged in, when status was 1, and later you cange it to 0, the user will still be able to do all activities on the website until they are logged out.
This is so beacuse wherever you use security restrict, it will NOT check status.

And, it will be a nightmare to include the condition on every secure server action.

But, if you create a view as @Hyperbytes has correctly suggested, both issues are resolved.

Hi @sid,

Thank you for the reply and explain why the set up I have is not a good solution.

If I understand correctly; create a view -> check the User Status there and then use the view as a source to log users in and out.

Hey I would like to learn more about it as well, kind to elaborate on the steps?

Try this out:

1 Like

I have created a view where I am filtering the data by Status.

However, I’m not able to add this view in the Security Provider under Users and Permissions. I am only getting a list of database tables excluding any views. I can see the new view under DB Connections.

I’m trying to modify the existing Security Provider. Is it possible to add a view in the SP?

Thank you for sending the steps. I have added the Condition as per your response and it’s working as expected :+1:t4:

I would certainly like to learn more about the method suggested by @Hyperbytes and @sid as well.

I rechecked the security provider, and I think you won’t need the view.
Try this:

The main users table will be say user_master. And identity will point to the primary key (ideally).
In the permissions section, create a new permission with the other status table (instead of same user table as shown in screenshot above).
And for the important part, use the foreign key column of the status table as identity column (green). This should basically have the same values as the main user table identity column.
Then, set the condition on the status field of the status table.

I think this should work as long as there is one-to-one relation between the two tables.
With this, you should not have to configure anything else at places where you need to use security login and security restrict.

In case this logic does not work, I will try to explain how to do the view thing. That would definitely work.

I see yeah I think this will work, I’m aware of this feature but can’t remember why I resorted to the other method :joy:. What’s the error code does this output when fails?

No error code. As far as I know, security provider does not return any error as such.
Its the security login & redirect steps that actually validates the request and throws 401 when it fails.