Thinking Out Loud

April 27, 2010

Email password change from Oracle

Filed under: oracle — mdinh @ 8:32 pm

I get requests for password changes frequently.

I would try to figure out a password, change the password, and email the password to the user.

This has become a bit cumbersome.

CREATE OR REPLACE PROCEDURE change_pw (pi_user VARCHAR2)
IS
c_profile CONSTANT VARCHAR2(30) := 'PROFILE_NAME';
c_domain CONSTANT VARCHAR2(30) := '@domain.com';
c_sender CONSTANT VARCHAR2(50) := SYS_CONTEXT ('USERENV', 'DB_NAME')||c_domain;
c_recipients CONSTANT VARCHAR2(30) := REGEXP_SUBSTR(pi_user,'[^_]+',1,2)||c_domain;
c_cc CONSTANT VARCHAR2(30) := 'dba'||c_domain;
c_subject CONSTANT VARCHAR2(50) := 'Password Changed For: '||pi_user;
c_pw CONSTANT VARCHAR2(10) := DBMS_RANDOM.STRING ('A', 9) || TRUNC (DBMS_RANDOM.VALUE (0, 10));
c_message CONSTANT VARCHAR2(50) := 'New Password: '||c_pw;
l_sql VARCHAR2(100);
BEGIN
FOR x IN (
SELECT u.name
FROM sys.user$ u, sys.profname$ p
WHERE u.resource$ = p.profile# AND p.name = c_profile
AND u.name=UPPER(pi_user))
LOOP
l_sql := 'ALTER USER '|| x.name || ' IDENTIFIED BY ' || c_pw || ' ACCOUNT UNLOCK PASSWORD EXPIRE';
EXECUTE IMMEDIATE(l_sql);
UTL_MAIL.SEND(sender=>c_sender,recipients=>c_recipients,cc=>c_cc,subject=>c_subject,message=>c_message);
END LOOP;
END;
/

The above stored procedure allows me to create a random password and emails the user.

You can find article – How To Send Email From 10g Oracle Database (UTL_MAIL) – open in new window

Advertisements

1 Comment »

  1. […] Read full story […]

    Pingback by Chirpir News | Email password change from Oracle « Thinking Out Loud — April 28, 2010 @ 1:36 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: