oracle apex 18.2 custom authentication and authorization

Question

I am Developing a Small ERP in oracle Apex 18.2 I want to Implement a Custom Security as Listed Below

1) User Should Authenticate from Custom Table

2) After Authentication User should be authorize to application as per Assigned Role in Custom Table

3) User Must have Authorize for READ,INSERT,UPDATE and DELETE Operation from Table

As Example Image

I have done some googling but lot of confusing options as per my little knowledge , need recommendations and Suggestion.

Answer 1

1) User Should Authenticate from Custom Table

• Create an Authentication Scheme and set Scheme Type to Custom.
• Implement the Authentication Function and place the code in the PL/SQL Code section. The function is supplied with the username and password entered from the login page. Use that to verify a match. ALWAYS encrypt users' passwords. Function returns true or false depending on the result.

Below is the sample code available and copied from the help text in the Page Builder:

function my_authentication (
    p_username in varchar2,
    p_password in varchar2 )
    return boolean
is
    l_user my_users.user_name%type := upper(p_username);
    l_pwd  my_users.password%type;
    l_id   my_users.id%type;
begin
    select id  , password
    into l_id, l_pwd
    from my_users
    where user_name = l_user;
    return l_pwd = rawtohex(sys.dbms_crypto.hash (
                    sys.utl_raw.cast_to_raw(p_password||l_id||l_user),
                    sys.dbms_crypto.hash_sh512 ));
exception
    when NO_DATA_FOUND then return false;
end;

• Set the Authentic Function to my_authentication

2) After Authentication User should be authorize to application as per Assigned Role in Custom Table

• Again in the PL/SQL code region, implement a post-authentication procedure, e.g. post_login, that retrieves the user's role and either adds them to an Application Item or to an APEX collection.
• Add the procedure name to the field Post-Authentication Procedure Name, e.g. post_login.
• Create Authorization Schemes based on the values above.

OR

Simply create an Authorization Scheme that simply queries the same information on the fly, though there's probably a small performance hit depending on how often these authorizations are called.

3) User Must have Authorize for READ,INSERT,UPDATE and DELETE Operation from Table

• You can limit pages, page items (read-only or editable), buttons and processes based on the Authorization Schemes.
• Depending on your Oracle License, you can also implement Oracle Real Application Security (RAS) or Virtual Private Databases (VPD) to manage access at the "database-level" and that is definitely more secure.