Making a custom authentication based on 2 different types of users in apex

Question

I have an authentication scheme that currently only verifies whether the user is in one table, namely the employer table. Now I want this authentication scheme to verify whether the user is an employer OR a employee, and based on whether they are an employer or employee it should redirect them to different pages.

First of all how would I include a second table in the authentication script? And my biggest problem is would I need a second application that links to the first application for when an employee logs in? How else could this be done? I currently only have employer forums and pages for when an employer logs in. If an employee logs in how would I hide all the employer reports/forms and only let them see employee forms/reports?

My current authentication scheme is shown below.

FUNCTION authenticate(p_username IN VARCHAR2
  ,p_password IN VARCHAR2) RETURN BOOLEAN IS
   l_value       NUMBER;
   l_returnvalue BOOLEAN;
 BEGIN
   BEGIN
     SELECT 1
       INTO l_value
       FROM employer
      WHERE 1 = 1
        AND upper(employer.username) = upper(p_username)
        AND upper(employer.passwords) = upper(p_password);
   EXCEPTION
     WHEN no_data_found
          OR too_many_rows THEN
       l_value := 0;
     WHEN OTHERS THEN
       l_value := 0;
   END;
   l_returnvalue := l_value = 1;
   RETURN l_returnvalue;
 END;     

Answer 1

This is an 'Authorisation Scheme' problem, not a 'Authentication Scheme' issue.

The authentication scheme is the gatekeeper to your application. If the user is allowed in, then consider what type of user they are.

You can use 'Authorisation Schemes' to do this, deriving true/false depending on how you identify them as employer or employee. You can then associate these authorisation schemes to various components in your application - page, menu items/links, reports, buttons, .. branches - which you could define on the home page of your application.

eg: If the user has authorisation scheme x, then send then to page 2, else send them to page 3.

But if you want this concept to scale, I recommend defining authorisation schemes for features, then allocate those features to business roles, via your custom tables. Then business roles (employees, employers) can be associated to specific :APP_USER.

Your sample code also suggest your store clear text passwords. Don't do that. Not even for examples. Hashing passwords should be a default thing - Dimitri Gielis has a good example of how to set up custom authentication here.

And/or review the example in the attribute help: