fein0015
asked on
USERNAME PASSWORD PACKAGE/Procedure in PL/SQL
I have oracle 9.2.01 release 2. Im looking to find pl/sql code that will let users set a username and password. I would like to store that uname and pword into a table. Then if the user comes back, they will be able to use the uname and pword to log back in to the gateway to the web system. ANY HELP IS APPRECIATED>> SAMPLE CODE OF SOMETHING SIMILAR WOULD BE GREAT!!!!
Do you need something more ? Is this working for you ?
Is that was you needed ?
ASKER
IT NEEDS TO BE IN PL/SQL!!!
Ok, It's just because I read in the post
ANY HELP IS APPRECIATED>> SAMPLE CODE OF SOMETHING SIMILAR WOULD BE GREAT!!!!
I thought you were able to convert this logic to pl/sql.
ANY HELP IS APPRECIATED>> SAMPLE CODE OF SOMETHING SIMILAR WOULD BE GREAT!!!!
I thought you were able to convert this logic to pl/sql.
Here's a pl/sql one :
REM alterpwd.sql
create or replace procedure CHANGE_MY_PASSWORD(NewPass
Cursor_Name INTEGER;
String VARCHAR2(100);
cursor USERNAME_CURSOR is
select User from DUAL;
UserNm VARCHAR2(32);
BEGIN
open USERNAME_CURSOR;
fetch USERNAME_CURSOR into UserNm;
close USERNAME_CURSOR;
String:= 'alter user '||UserNm||' identified by '||NewPass ;
Cursor_Name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(Cursor_Name
DBMS_SQL.CLOSE_CURSOR(Curs
END;
/
You could use the insert command also to insert the username and password in a table if you want.
With 2 input variables
create or replace procedure CHANGE_MY_PASSWORD(Newuser
Cursor_Name INTEGER;
String VARCHAR2(100);
UserNm VARCHAR2(32);
PassNm VARCHAR2(32);
BEGIN
String:= 'alter user '||UserNm||' identified by '||NewPass ;
Cursor_Name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(Cursor_Name
DBMS_SQL.CLOSE_CURSOR(Curs
String:= 'insert into yourtab select '''||UserNm||''','''||NewP
Cursor_Name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(Cursor_Name
DBMS_SQL.CLOSE_CURSOR(Curs
END;
/
REM alterpwd.sql
create or replace procedure CHANGE_MY_PASSWORD(NewPass
Cursor_Name INTEGER;
String VARCHAR2(100);
cursor USERNAME_CURSOR is
select User from DUAL;
UserNm VARCHAR2(32);
BEGIN
open USERNAME_CURSOR;
fetch USERNAME_CURSOR into UserNm;
close USERNAME_CURSOR;
String:= 'alter user '||UserNm||' identified by '||NewPass ;
Cursor_Name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(Cursor_Name
DBMS_SQL.CLOSE_CURSOR(Curs
END;
/
You could use the insert command also to insert the username and password in a table if you want.
With 2 input variables
create or replace procedure CHANGE_MY_PASSWORD(Newuser
Cursor_Name INTEGER;
String VARCHAR2(100);
UserNm VARCHAR2(32);
PassNm VARCHAR2(32);
BEGIN
String:= 'alter user '||UserNm||' identified by '||NewPass ;
Cursor_Name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(Cursor_Name
DBMS_SQL.CLOSE_CURSOR(Curs
String:= 'insert into yourtab select '''||UserNm||''','''||NewP
Cursor_Name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(Cursor_Name
DBMS_SQL.CLOSE_CURSOR(Curs
END;
/
ASKER
I WOULD WORSHIP ANYONE WHO CAN HELP WITH THE FOLLOWING: I need to translate this small piece of php into something that would work with an oracle DB. As it reads right now, it is setup to interface with mySQL. I have been using the OCI and ORA functions through PHP and I cant get it to work right. SOMEONE HELP PLEASE
<?PHP
/*
***********
User Authentication-Version 1.3
Written and designed by Jaime R. Gouveia-Snap Survey Software 2003
This page is designed for authenticating users to log into specific surveys.
The only configurations that may need to be adjusted are for the validation of username and password lengths.
Nothing else should need to be modified.
By default, the data entered for the password is required to be 5 characters. Username has no restriction on the length. However, the username can only be alphanumeric characters, spaces and underscores.
***********
Coding log
10/22/03-Basic design of form.
10/23/03-Started coding data cleaning and db queries.
10/24/03-Added validation of data. Need to include a validation for illegal characters in the username.
10/27/03-Added validation for illegal characters. Starting to tweak query for db.
11/12/03-Added update query to detect for multi-response surveys
11/13/03-Added update query to be sure that a survey can't be taken more than once, if the db is marked as userMulti "1"
12/16/03-improved data validation and converted HTML tags to uppercase for readability.
12/17/03-Added flag for passing id through URL. Determined in mySQL database
***********
*/
$log_in = $_POST['Submit'];
$FormUserName = $_POST['FormUserName'];
$FormUserPass = $_POST['FormUserPass'];
//--Clean Data
$FormUserName = trim($FormUserName);
$FormUserPass = trim($FormUserPass);
$FormUserName = rtrim($FormUserName);
$FormUserPass = rtrim($FormUserPass);
//--Extra validation measuring length of data fields. If used, uncomment the section in the below switch($log_in).
//--Be sure to set the length in the $UserNameLength and $UserPassLength below. Password is set to validate at a length of 5 by default.
$LFormUserName = strlen($FormUserName);
$LFormUserPass = strlen($FormUserPass);
$UserNameLength = '5';
$UserPassLength = '5';
$authorized = '0';
//-Check for existence of input from user and validate data
if($log_in)
{
if(ereg('[^a-z0-9_ ]', $FormUserName))
{
$error = "You have used an invalid character in your user name.<BR> Valid characters consist of upper or lower case letters (a-z), numbers (0-9), spaces or underscores (_).";
}
switch($log_in)
{
case $FormUserName && !$FormUserPass:
$error = "Please enter your password";
break;
case !$FormUserName && $FormUserPass:
$error = "Please enter your username.<BR>Your password has been cleared for security reasons.";
break;
case !$FormUserName || !$FormUserPass:
$error = "Please fill out both fields";
break;
//--Leave commented out unless there is a length restriction on user input --//
//case $LFormUserName < $UserNameLength:
// $error = "Your username is too short";
//break;
case $LFormUserPass < $UserPassLength:
$error = "Your password is too short.<BR>The required length is five characters.";
break;
case $FormUserName && $FormUserPass:
if(ereg('[^a-z0-9_ ]', $FormUserName))
{
$error = "You have used an invalid character in your user name.<BR> Valid characters consist of upper or lower case letters (a-z), numbers (0-9), spaces or underscores (_).";
}else{
require_once("dbincludes/d
mysql_connect($db_server, $db_user, $db_password) or die("Cannot connect");
mysql_select_db($db_databa
$query = mysql_query("SELECT * FROM $db_user_table
WHERE userName = '$FormUserName'
AND userPass = '$FormUserPass' ");
$result = mysql_num_rows($query);
if(!$result)
{
$error = "We could not log you in with the information provided.<BR>Please try again.";
}
while($userInfo=mysql_fetc
{
$DBUserID = $userInfo['userID'];
$DBUserName = $userInfo['userName'];
$DBUserPass = $userInfo['userPass'];
$DBUserURL = $userInfo['userURL'];
$DBUserMulti = $userInfo['userMulti'];
$DBUserTaken = $userInfo['userTaken'];
$DBpassID = $userInfo['passID'];
$DBUserUID = $userInfo['userUID'];
}
$authorized='1';
}
}
}
if($authorized=='1')
{
if($DBUserMulti=='1' && $DBUserTaken=='1')
{
header("Location: $DBUserURL"."?id="."$DBUse
}
elseif($DBUserMulti=='1' || $DBUserMulti=='0' && $DBUserTaken=='0')
{
if($DBpassID=='0')
{
mysql_query("UPDATE $db_user_table SET userTaken=1 WHERE userID=$DBUserID ");
$result = mysql_affected_rows();
header("Location: $DBUserURL");
}
else
{
mysql_query("UPDATE $db_user_table SET userTaken=1 WHERE userID=$DBUserID ");
$result = mysql_affected_rows();
header("Location: $DBUserURL"."?id="."$DBUse
}
}
elseif($DBUserMulti=='0' && $DBUserTaken=='1')
{
header("Location: error.php");
}
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> Log In: </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="Jaime R. Gouveia">
<LINK HREF="style.css" REL="stylesheet" TYPE="text/css">
</HEAD>
<BODY>
<FORM NAME="form1" METHOD="post" ACTION='<?php $_SERVER['PHP_SELF']?>''>
<TABLE WIDTH="200" BORDER="0" ALIGN="center" CELLPADDING="0" CELLSPACING="0">
<TR>
<TD>
<TABLE WIDTH="200" BORDER="0" CELLSPACING="0" CELLPADDING="0">
<TR>
<TD><IMG SRC="images/top.gif" WIDTH="343" HEIGHT="24"></TD>
</TR>
</TABLE>
</TD>
</TR>
<TR>
<TD>
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0">
<TR>
<TD CLASS="boxborder">
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="2">
<TR ALIGN="CENTER">
<TD COLSPAN="3">Please enter your login information.</TD>
</TR>
<TR>
<TD WIDTH="35%"> </TD>
<TD WIDTH="65%" COLSPAN="2"> </TD>
</TR>
<TR>
<TD ALIGN="RIGHT" VALIGN="MIDDLE">User Name:</TD>
<TD COLSPAN="2"><INPUT NAME="FormUserName" TYPE="text" ID="userName" SIZE="20" MAXLENGTH="20" VALUE='<?php echo $FormUserName; ?>'></TD>
</TR>
<TR>
<TD ALIGN="RIGHT" VALIGN="MIDDLE">Password:<
<TD COLSPAN="2"><INPUT NAME="FormUserPass" TYPE="password" ID="userpass" SIZE="20" MAXLENGTH="5"></TD>
</TR>
<TR>
<TD COLSPAN="3"> <TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="2">
<TR>
<TD WIDTH="50%" ALIGN="RIGHT" VALIGN="MIDDLE"><INPUT TYPE="RESET" NAME="Submit2" VALUE="Reset"></TD>
<TD WIDTH="50%" ALIGN="LEFT" VALIGN="MIDDLE"><INPUT TYPE="SUBMIT" NAME="Submit" VALUE="Submit"></TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
<TR>
<TD>
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0">
<TR>
<TD><IMG SRC="images/bottom.gif" WIDTH="343" HEIGHT="24"></TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
<TABLE ALIGN="CENTER">
<TR>
<TD ALIGN="CENTER" CLASS="error"><?php echo $error; ?></TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>
<?PHP
/*
***********
User Authentication-Version 1.3
Written and designed by Jaime R. Gouveia-Snap Survey Software 2003
This page is designed for authenticating users to log into specific surveys.
The only configurations that may need to be adjusted are for the validation of username and password lengths.
Nothing else should need to be modified.
By default, the data entered for the password is required to be 5 characters. Username has no restriction on the length. However, the username can only be alphanumeric characters, spaces and underscores.
***********
Coding log
10/22/03-Basic design of form.
10/23/03-Started coding data cleaning and db queries.
10/24/03-Added validation of data. Need to include a validation for illegal characters in the username.
10/27/03-Added validation for illegal characters. Starting to tweak query for db.
11/12/03-Added update query to detect for multi-response surveys
11/13/03-Added update query to be sure that a survey can't be taken more than once, if the db is marked as userMulti "1"
12/16/03-improved data validation and converted HTML tags to uppercase for readability.
12/17/03-Added flag for passing id through URL. Determined in mySQL database
***********
*/
$log_in = $_POST['Submit'];
$FormUserName = $_POST['FormUserName'];
$FormUserPass = $_POST['FormUserPass'];
//--Clean Data
$FormUserName = trim($FormUserName);
$FormUserPass = trim($FormUserPass);
$FormUserName = rtrim($FormUserName);
$FormUserPass = rtrim($FormUserPass);
//--Extra validation measuring length of data fields. If used, uncomment the section in the below switch($log_in).
//--Be sure to set the length in the $UserNameLength and $UserPassLength below. Password is set to validate at a length of 5 by default.
$LFormUserName = strlen($FormUserName);
$LFormUserPass = strlen($FormUserPass);
$UserNameLength = '5';
$UserPassLength = '5';
$authorized = '0';
//-Check for existence of input from user and validate data
if($log_in)
{
if(ereg('[^a-z0-9_ ]', $FormUserName))
{
$error = "You have used an invalid character in your user name.<BR> Valid characters consist of upper or lower case letters (a-z), numbers (0-9), spaces or underscores (_).";
}
switch($log_in)
{
case $FormUserName && !$FormUserPass:
$error = "Please enter your password";
break;
case !$FormUserName && $FormUserPass:
$error = "Please enter your username.<BR>Your password has been cleared for security reasons.";
break;
case !$FormUserName || !$FormUserPass:
$error = "Please fill out both fields";
break;
//--Leave commented out unless there is a length restriction on user input --//
//case $LFormUserName < $UserNameLength:
// $error = "Your username is too short";
//break;
case $LFormUserPass < $UserPassLength:
$error = "Your password is too short.<BR>The required length is five characters.";
break;
case $FormUserName && $FormUserPass:
if(ereg('[^a-z0-9_ ]', $FormUserName))
{
$error = "You have used an invalid character in your user name.<BR> Valid characters consist of upper or lower case letters (a-z), numbers (0-9), spaces or underscores (_).";
}else{
require_once("dbincludes/d
mysql_connect($db_server, $db_user, $db_password) or die("Cannot connect");
mysql_select_db($db_databa
$query = mysql_query("SELECT * FROM $db_user_table
WHERE userName = '$FormUserName'
AND userPass = '$FormUserPass' ");
$result = mysql_num_rows($query);
if(!$result)
{
$error = "We could not log you in with the information provided.<BR>Please try again.";
}
while($userInfo=mysql_fetc
{
$DBUserID = $userInfo['userID'];
$DBUserName = $userInfo['userName'];
$DBUserPass = $userInfo['userPass'];
$DBUserURL = $userInfo['userURL'];
$DBUserMulti = $userInfo['userMulti'];
$DBUserTaken = $userInfo['userTaken'];
$DBpassID = $userInfo['passID'];
$DBUserUID = $userInfo['userUID'];
}
$authorized='1';
}
}
}
if($authorized=='1')
{
if($DBUserMulti=='1' && $DBUserTaken=='1')
{
header("Location: $DBUserURL"."?id="."$DBUse
}
elseif($DBUserMulti=='1' || $DBUserMulti=='0' && $DBUserTaken=='0')
{
if($DBpassID=='0')
{
mysql_query("UPDATE $db_user_table SET userTaken=1 WHERE userID=$DBUserID ");
$result = mysql_affected_rows();
header("Location: $DBUserURL");
}
else
{
mysql_query("UPDATE $db_user_table SET userTaken=1 WHERE userID=$DBUserID ");
$result = mysql_affected_rows();
header("Location: $DBUserURL"."?id="."$DBUse
}
}
elseif($DBUserMulti=='0' && $DBUserTaken=='1')
{
header("Location: error.php");
}
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> Log In: </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="Jaime R. Gouveia">
<LINK HREF="style.css" REL="stylesheet" TYPE="text/css">
</HEAD>
<BODY>
<FORM NAME="form1" METHOD="post" ACTION='<?php $_SERVER['PHP_SELF']?>''>
<TABLE WIDTH="200" BORDER="0" ALIGN="center" CELLPADDING="0" CELLSPACING="0">
<TR>
<TD>
<TABLE WIDTH="200" BORDER="0" CELLSPACING="0" CELLPADDING="0">
<TR>
<TD><IMG SRC="images/top.gif" WIDTH="343" HEIGHT="24"></TD>
</TR>
</TABLE>
</TD>
</TR>
<TR>
<TD>
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0">
<TR>
<TD CLASS="boxborder">
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="2">
<TR ALIGN="CENTER">
<TD COLSPAN="3">Please enter your login information.</TD>
</TR>
<TR>
<TD WIDTH="35%"> </TD>
<TD WIDTH="65%" COLSPAN="2"> </TD>
</TR>
<TR>
<TD ALIGN="RIGHT" VALIGN="MIDDLE">User Name:</TD>
<TD COLSPAN="2"><INPUT NAME="FormUserName" TYPE="text" ID="userName" SIZE="20" MAXLENGTH="20" VALUE='<?php echo $FormUserName; ?>'></TD>
</TR>
<TR>
<TD ALIGN="RIGHT" VALIGN="MIDDLE">Password:<
<TD COLSPAN="2"><INPUT NAME="FormUserPass" TYPE="password" ID="userpass" SIZE="20" MAXLENGTH="5"></TD>
</TR>
<TR>
<TD COLSPAN="3"> <TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="2">
<TR>
<TD WIDTH="50%" ALIGN="RIGHT" VALIGN="MIDDLE"><INPUT TYPE="RESET" NAME="Submit2" VALUE="Reset"></TD>
<TD WIDTH="50%" ALIGN="LEFT" VALIGN="MIDDLE"><INPUT TYPE="SUBMIT" NAME="Submit" VALUE="Submit"></TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
<TR>
<TD>
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0">
<TR>
<TD><IMG SRC="images/bottom.gif" WIDTH="343" HEIGHT="24"></TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
<TABLE ALIGN="CENTER">
<TR>
<TD ALIGN="CENTER" CLASS="error"><?php echo $error; ?></TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
WHENEVER SQLERROR EXIT
PROMPT
PROMPT Oracle Password Modification Script
PROMPT ==========================
PROMPT
ACCEPT PARAM1 CHAR PROMPT 'Oracle Username => '
ACCEPT PARAM2 CHAR PROMPT 'Old Password => '
ACCEPT PARAM3 CHAR PROMPT 'Connect String => '
ACCEPT PARAM4 CHAR PROMPT 'New Password => ' HIDE
ACCEPT PARAM5 CHAR PROMPT 'Confirm Password => ' HIDE
SET PAGES 50000
SET LINES 132
SET PAUSE OFF
SET HEAD OFF
SET VERIFY OFF
SET FEED OFF
set arraysize 1
COLUMN SEQ NOPRINT
COLUMN TNAME NOPRINT
COLUMN INAME NOPRINT
PROMPT
PROMPT Working ......
PROMPT
SET TERM OFF
SPOOL d:\pass.log
SET TERM ON
connect &PARAM1/&PARAM2@&PARAM3
SELECT 'We could not confirm your password ... Try again.' from dual
where '&PARAM4' != '&PARAM5';
SELECT 'You password has been modified.' from dual
where '&PARAM4' = '&PARAM5';
INSERT into TABLE_NAME
values ('&PARAM1', '&PARAM4' );
rem alter user &PARAM1 identified by &PARAM4;
SPOOL OFF
SET TERM ON
PROMPT
ACCEPT PROP CHAR PROMPT 'Press <RETURN> to continue ...'
SET TERM OFF
SPOOL d:\pass.sql
SELECT 'EXIT' from dual where '&PARAM4' != '&PARAM5';
SELECT '' from dual where '&PARAM4' = '&PARAM5';
select 'alter user &PARAM1 identified by &PARAM4;' from dual;
select 'exit' from dual;
spool off
@d:\pass.sql
EXIT