Oracle apex authentication schemes login errors

Question

FUNCTION authenticate(p_username IN VARCHAR2,p_password IN VARCHAR2) RETURN 
BOOLEAN 
is
l_count integer;
begin
select count(*)
into   l_count
from   STUDENT, ADMIN, ORGANISATION
WHERE upper(Student.STUDENT_ID, ADMIN.ADMIN_ID, ORGANISATION.ORG_ID) = 
upper(p_username)
AND upper(Student.STUDENT_PASSWORD, ADMIN.ADMIN_PASSWORD, 
ORGANISATION.ORG_PASSWORD) = upper(p_password);
return (l_count > 0);
end;

above is authentication code i have made to get information from multiple tables and use them to authenticate a login. if i just do it for students it works fine but i need multiple types of user to be able to access the software and i cant make multiple authentication schemes run at the same time all table name and column names are correct below is the error i get ORA-06550: line 9, column 7: PL/SQL: ORA-00909: invalid number of arguments ORA-06550: line 6, column 1: PL/SQL: SQL Statement ignored

Answer 1

Wrong syntax. Try

select count(*) 
into l_count
from student,
     admin,
     organisation
where upper(p_username) in (upper(student.student_id),
                            upper(admin.admin_id),
                            upper(organisation.org_id)
                           )
  and upper(p_password) in (upper(student.student_password),
                            upper(admin.admin_password),
                            upper(organisation.org_password)
                           );

Answer 2

With valid_student as (
select count(*) as student_result 
from student 
where upper (p_username) = upper(student_id)
And upper(p_password) = upper(student_password)
),
valid_Admin as (
select count(*) as admin_result 
from admin
where upper (p_username) = upper(admin_id)
And upper(p_password) = upper(admin_password)
),
valid_org as (
select count(*) as org_result 
from organisation 
where upper (p_username) = upper(org_id)
And upper(p_password) = upper(org_password)
)
Select “Valid” as access_allowed
From valid_student s, valid_admin a, valid_org o
 Where s.student_result = 1 or a.admin_result = 1 or o.org_result = 1

Answer 3

I'd like to try break this down, and work the problem through. There are a number of things to address.

Logic

How are you going to control uniqueness across the tables? There might be an ALBERT in both students and administration. It might even be the same person, a student and employee of the facility. I've been such a person.

Suggested SQL

There are no joins between your tables, this would turn the result into a cartesion product, and I'm pretty sure you could return true to ALBERT logging in with NIKOLA's password.

I think you probably meant to use a set operator

select ...
from student 
where ...
union all
select ...
from admin
where ...

The UNION ALL means there is no need to check for uniqueness, no need for an extra sort.

Password protection

To be able to UPPER a password means you're storing it clear text. People today should inherit enough digital fluency that passwords should not be stored clear text. Ever.

See this article for an example of how to set up custom authentication in APEX by hashing passwords. A rare one amongst a disappointing number that did not hash passwords. This one also hashes with the username and some salt, which is better. http://barrybrierley.blogspot.com.au/2015/05/custom-authentication-in-apex.html

It also starts to cover your needs regarding user type.

I'm sure there was an example in the APEX documentation, but I couldn't find it.

User Type - Authorisation

Once you have established you have a valid user, you can determine what type of user they are, then control their access to various components using Authorization Schemes.

For a more flexible system, I would abstract this further and use authorisation schemes to control privileges to certain components, and allocate these to business roles, which are in turn granted to users.

This serves your "multiple types of people".

'Record Exists' check

From early on I learned something at AskTom regarding checking for existence of rows, which seems to hold up well across all versions

declare
  ln_exists pls_integer;
begin
  select count(*)
  into ln_exists
  from dual
  where exists (
     select null 
     from your_table -- whatever you're looking for
     where id = p_id
  );
  return ln_exists = 1; -- true if exists
 end;

Oracle knows just how to spend the least effort in solving this problem.

Many other variations just select too many rows from the database.

Shared applications

You can actually define multiple entry points using different authentication to the same application. http://www.grassroots-oracle.com/2014/04/shared-authentication-across-multiple-apex-apps.html