Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
   
 

Using the DBMS_SQL Package
Oracle Tips by Burleson
 

A common request from users is the ability to change their password. Unfortunately, Oracle does not allow a user to change an account password without having the ALTER USER privilege. This privilege also allows users to change information other than the password and for any user. So, unless you want to grant this level of access to users, the DBA has the responsibility of assigning and changing passwords.

Letís use the DBMS_SQL package to create a Change_Password() function, which the DBA can compile and make accessible to all users. This function can be called from a systemís front end, usually via a button on a form or a menu item, to allow the user to change a password (and only a password). The Change_Password() function is shown in Listing 9.3.

Listing 9.3 The Change_Password() function.

CREATE OR REPLACE
FUNCTION Change_Password (vUsername IN    varchar2,
                          vPassword IN    varchar2)
 
RETURN integer
 
IS
 
   iCursorID           integer;
   vCommand            varchar2 (80);
   iReturned           integer;
 
   xMISSING_PARAMETER  EXCEPTION;
 
BEGIN
   IF (vUserName IS NULL OR vPassword IS NULL) THEN
      RAISE xMISSING_PARAMETER;
   END IF;
 
   vCommand := 'ALTER USER '    ||
               vUsername        ||
               'identified by ' ||
               vPassword;
 
   iCursorID := DBMS_SQL.Open_Cursor;
   DBMS_SQL.Parse (iCursorID,
                   vCommand,
                   DBMS_SQL.v7);
 
   iReturned := DBMS_SQL.Execute (iCursorID);
   DBMS_SQL.Close_Cursor (iCursorID);
 
   RETURN 1;
 
EXCEPTION
   WHEN OTHERS THEN
        RETURN 0;
END Change_Password;
/

This function must be run by a user who has the ALTER USER privilege. In this implementation, the function allows any userís password to be changed. This particular implementation assumes that you will pass only the login name of the current user as a parameter.

The function first builds the proper SQL*Plus command and stores it in the vCommand string. The function then calls functions and procedures in the DBMS_SQL package to parse and execute the command.

This function requires a username and password to be passed as parameters. If the userís password is successfully changed, the function returns 1. Otherwise, the function returns 0.


               
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter