• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 768
  • Last Modified:

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!!!!
0
fein0015
Asked:
fein0015
  • 6
  • 2
1 Solution
 
peterside7Commented:
Here's a SQL script for you to adapt to pl/sql, i've included the part you need to insert the us/pw into a table  :
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
0
 
peterside7Commented:
Do you need something more ? Is this working for you ?
0
 
peterside7Commented:
Is that was you needed ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
fein0015Author Commented:
IT NEEDS TO BE IN PL/SQL!!!
0
 
peterside7Commented:
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.
0
 
peterside7Commented:
Here's a pl/sql one :

REM alterpwd.sql
create or replace procedure CHANGE_MY_PASSWORD(NewPass IN VARCHAR2) AS
    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, String, dbms_sql.Native);
   DBMS_SQL.CLOSE_CURSOR(Cursor_Name);
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 IN VARCHER2 NewPass IN VARCHAR2) AS
    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, String, dbms_sql.Native);
   DBMS_SQL.CLOSE_CURSOR(Cursor_Name);
   String:= 'insert into yourtab select '''||UserNm||''','''||NewPass||'''||' from dual' ;
   Cursor_Name := DBMS_SQL.OPEN_CURSOR;  
   DBMS_SQL.PARSE(Cursor_Name, String, dbms_sql.Native);
   DBMS_SQL.CLOSE_CURSOR(Cursor_Name);
END;
/

0
 
fein0015Author Commented:
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/db_connect.inc.php");
                  mysql_connect($db_server, $db_user, $db_password) or die("Cannot connect");
                  mysql_select_db($db_database) or die("Could not choose database");
                  $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_fetch_array($query))
                        {
                        $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="."$DBUserUID");
                  }
                  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="."$DBUserUID");
                              }
                        }
                        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%">&nbsp;</TD>
                                                <TD WIDTH="65%" COLSPAN="2">&nbsp;</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>
                                                <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>
0
 
peterside7Commented:
fein0015,

Did my pl/sql code helped you ?
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now