As your own question answers: "Oracle Applications is an [ERP]." An "Oracle Apps DBA" will be someone who is experienced in working with the particular schema and tooling around this ERP. There are hundreds (if not thousands) of packages, tables, stored procedures, and interfaces that are specifically built for the ERP. It is not a trivial database to admin. You can get a brief glimpse into some of the things in their ERP by looking around - for example, this blog - or by logging into a Metalink account as someone who is a customer of their ERP solution, where you can get ERDs and other documentation.
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''';
Since 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.
Best Answer
There should not be any problems. Nobody should use this password for application purposes. In case of RAC cluster do not forget to change the password on each node. This password is not stored in the database but in local node's password file.
Unless you use Oracle Data Guard (physical replication). For some reason having the same password on each side is not sufficient and you must copy the amended password file to the other site too.