I would give the Database Link primitive a shot. Though it would involve some DDL. For example, you could create a private fixed user link as follows:
create database link check_subm_credentials
connect to scott
identified by password using 'service_name';
In your PL/SQL code it might look something along the lines of:
EXECUTE IMMEDIATE 'create database link check_subm_credentials'
|| ' connect to ' || submitted_user_name
|| ' identified by ' || submitted_password
|| ' using ''orcl''';
ALTER DATABASE LINK has some limitations:
You cannot use this statement to change the connection or authentication user associated with the database link.
You must re-create the database link to use different connection user. You would just issue
DROP DATABASE LINK every time before you re-create the database link. You might also need to close the database link with
CLOSE DATABASE LINK clause before you can drop it:
alter session close database link check_subm_credentials;
Once the database link is created, you could query some
USER_* view or
DUAL table to check if the user submitted correct credentials:
select * from dual@check_subm_credentials;
If the query returned some result then the connection was established successfully and the credentials provided by the user are valid. Otherwise Oracle will raise an exception.
Remember that the statements to create and drop the database links is DDL, and DDL statements implicitly commit current transaction, therefore all the changes you made before DDL will become permanent, and you will be unable to
ROLLBACK them after DDL is executed.