 |
|
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.
|
