How to automatically assign USER ROLES in Oracle Apex-CodePudding

I'm trying to create an authorization scheme that creates users based on a table in my database named DJELATNIK that has attributes name, surename, korisnicko_ime, lozinka. korisnicko_ime is the username for the employee and lozika it's the employees password. I created a Authentication Scheme with the following Authentication Function:

CREATE OR REPLACE FUNCTION 
    prijava_custom(p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN
AS
    v_korisnicko varchar2(100);
    v_lozinka varchar2(100);
BEGIN
    SELECT KORISNICKO_IME, LOZINKA
    INTO v_korisnicko, v_lozinka
    FROM DJELATNIK
    WHERE UPPER(KORISNICKO_IME) = UPPER(p_username)
    AND LOZINKA = p_password;
    RETURN TRUE;
EXCEPTION 
   WHEN NO_DATA_FOUND THEN
   RETURN FALSE;
END;

Everything works, all the employees now have an "account" created but i still need to manually assign them a role before they can actually login.

What i want is to set all roles of the accounts generated from this scheme to be a contributor. And somehow set my account to be administrator. Is that even possible ?

I understand that i could add a new column in my DJELATNIK table to select role in the form for DJELATNIK but i don't know how to write the code needed to actually make it work.

CodePudding user response：

It is not clear from your question if you are using your own roles or the native "Application Access Control".

If you're using "Application Access Control" then there is an API available to grant a user a specific role.

There are 2 options:

Grant the user the "Contributor role" when the account is created.
Have an after authentication application process that checks if the current user has the Contributor role and if not, then grant the user the role

Update: an alternative solution is to modify the authorisation scheme for contributor so it just checks if the user is logged on.