I am attempting to implement a form on APEX that allows the user (in this case dba's) to select a database and then change their username and password.
I was able to set up the input fields correctly but I am totally lost on how to tie it in with the actual database. I am assuming I will have to use a database link to access different databases.
Is this possible?
I don't think you will be able to do something like this:
alter user xyz@remote_database identified by new_password
You will probably need to write a stored procedure on the remote database that accepts the username and password and alters that users password for you. Then you can execute:
begin
change_password('xyz','new_password')@remote_database;
end;
I devised a solution that does not require stored procedures or elevated access for a single user. Its a bit hacked together but it works!
The user inputs their credentials and desired new password through a regular APEX form.
The form data gets processed in an HTML region with an iframe in it using substitution strings. i.e:<iframe height='300' width='100%' scrolling="no" src="http://***SERVER***/cgi-bin/chguserpwd.cgi dbname=&DATABASE_NAME.&username=&USERNAME.&oldpwd=&CURR_PASSWORD.&newpwd=&NEW_PASSWORD."></iframe>
The cgi script does the work and returns the result through the iframe
#!/usr/bin/ksh
my_input=$QUERY_STRING;
dbname=`echo "$QUERY_STRING" |awk '{FS="&"} {print $1}'|awk '{FS="="} {print $2}'`
username=`echo "$QUERY_STRING" |awk '{FS="&"} {print $2}'|awk '{FS="="} {print $2}'`
oldpwd=`echo "$QUERY_STRING" |awk '{FS="&"} {print $3}'|awk '{FS="="} {print $2}'`
newpwd=`echo "$QUERY_STRING" |awk '{FS="&"} {print $4}'|awk '{FS="="} {print $2}'`
print "Content-Type: text/html\n\n"; print " Database Name is $dbname ............ User is $username ";
cd **dir**
./chguserpwd $dbname $username $oldpwd $newpwd
Execute the SQL from the cgi-script ./chguserpwd
set markup HTML on
prompt .
alter user &1 identified by &2;
prompt Password has been changed
