Automaticaly creating users in Oracle Apex based on table values-CodePudding

Is there a way to automatically create a user in my application based on values in a table?

So I have a table named EMP as employee in that table I have attributes name, surname, username and password. What do I need to do to automatically create a user account in my application when a new employee is inserted into the database?

I have no idea where to start, is that done in processes or is there a pre-built feature that covers this? Is there a sample code that everyone just uses and adapts to their needs?

CodePudding user response：

In such a case, maybe it would be better to use custom authentication scheme instead of default, built-in Apex authentication.

How to create new users? Just insert them into the table. It would be a good idea to create a package which contains procedures used to manipulate with users' data.

You shouldn't store passwords as text for security reasons.

For example:

procedure p_create_user (p_name     in varchar2, p_surname  in varchar2, 
                         p_username in varchar2, p_password in varchar2) 
is
  l_hash raw (2000);
begin
  l_hash :=
    dbms_crypto.hash (
      utl_i18n.string_to_raw (p_password, 'AL32UTF8'),
      dbms_crypto.hash_sh1);

  insert into emp (name, surname, username, password)
    values
    (p_name, p_surname, p_username, l_hash);
end;

In Apex, authentication scheme requires you to create your own function (and set its name into the "Authentication Function Name" property) which accepts username/password combination and returns Boolean: TRUE (if credentials are valid) or FALSE (if not). Note that parameters' names MUST be p_username and p_password.

Use code similar to that in previous procedure:

function f_auth (p_username in varchar2, p_password in varchar2)
return boolean
is
  l_pwd emp.password%type;
begin
  select password
    into l_pwd
    from emp
    where username = p_username;

  return l_pwd = dbms_crypto.hash (
                   utl_i18n.string_to_raw (p_password, 'AL32UTF8'),
                   dbms_crypto.hash_sh1);

end;

That's all you need for basic functionality. You can add another procedure to let users change their password etc.