Re: Changing Of Password In Oracle Application

From: <warkr_at_vanadium.brooks.af.mil>
Date: 1995/12/26
Message-ID: <4bpe3q$4bl_at_xenon.brooks.af.mil>

mcblohkc_at_leonis.nus.sg (Loh Kir Chern) wrote:

Loh,

  Try a variation of the following using "Forms_DLL", this version creates a user, alters or deletes. I have seperate, but less straight forward ways to change passwords. For your purposes, you should only need, 'ALTER USER IDENTIFIED BY ' || :Block:new_password || ';' and makesure to play with the display properties for Block:new_password.

Good luck:

Richard Wark    					Brooks AFB
Oracle DBA					rwark_at_txdirect.net

http://www.txdirect.net/users/rwark

code sample follows - used on-mouse-click of a button: (sorry about the formatting..)

Begin

 IF :USER_INFO_BLOCK.action_item
 is null then

         :USER_INFO_BLOCK.action_item
 := 'ADD'; END IF;
  IF :USER_INFO_BLOCK.action_item = 'ADD'

        THEN :USER_INFO_BLOCK.SQL_MESSAGE := 'CREATE USER ' ||
:USER_INFO_BLOCK.USERNAME ||

 		' IDENTIFIED BY ' || :USER_INFO_BLOCK.PASSWORD;
		FORMS_DDL(:USER_INFO_BLOCK.SQL_MESSAGE);
		IF NOT Form_Success THEN 
    		    Message ('User Creation Failed'); 
		ELSE 
		   null;/* Message ('User ' || :USER_INFO_BLOCK.USERNAME || '
Created');  */
		END IF; 

		:USER_INFO_BLOCK.SQL_MESSAGE := 'ALTER USER ' ||


:USER_INFO_BLOCK.USERNAME ||


 		' DEFAULT TABLESPACE USERS';
		FORMS_DDL(:USER_INFO_BLOCK.SQL_MESSAGE);
		IF NOT Form_Success THEN 
    		    Message ('User Default Tablespace Failed'); 
		ELSE 
		  /*  Message ('User ' || :USER_INFO_BLOCK.USERNAME || ' Tablespace
defined');  */
		NULL;
		END IF;

		:USER_INFO_BLOCK.SQL_MESSAGE := 'ALTER USER ' ||


:USER_INFO_BLOCK.USERNAME ||


 		' TEMPORARY TABLESPACE TEMP';
		FORMS_DDL(:USER_INFO_BLOCK.SQL_MESSAGE);
		IF NOT Form_Success THEN 
    		    Message ('User Temporary Tablespace Failed'); 
		ELSE 
		  /*  Message ('User ' || :USER_INFO_BLOCK.USERNAME || ' Temp defined
as TEMP');  */
		NULL;
		END IF;
		

		:USER_INFO_BLOCK.SQL_MESSAGE := 'ALTER USER ' ||


:USER_INFO_BLOCK.USERNAME ||


 		' QUOTA UNLIMITED ON TEMP';
		FORMS_DDL(:USER_INFO_BLOCK.SQL_MESSAGE);
		IF NOT Form_Success THEN 
    		    Message ('User TS Quota FAILED'); 
		ELSE 
		 /*   Message ('User ' || :USER_INFO_BLOCK.USERNAME || ' TS Quota
set'); */
		NULL;
		END IF;
		
		:USER_INFO_BLOCK.SQL_MESSAGE := 'ALTER USER ' ||


:USER_INFO_BLOCK.USERNAME ||


 		' DEFAULT ROLE ALL';
		FORMS_DDL(:USER_INFO_BLOCK.SQL_MESSAGE);
		IF NOT Form_Success THEN 
    		    Message ('User Default Role not set'); 
		ELSE 
		   /* Message ('User ' || :USER_INFO_BLOCK.USERNAME || ' Default Role
set');  */
		NULL;
		END IF;
		GO_BLOCK('NEW_ROLES');
		FIRST_RECORD;
	LOOP
		:USER_INFO_BLOCK.SQL_MESSAGE := 'GRANT '|| :ROLES_TO_GRANT || ' TO '
|| :USER_INFO_BLOCK.USERNAME;
		FORMS_DDL(:USER_INFO_BLOCK.SQL_MESSAGE);
		IF NOT Form_Success THEN 
    		    Message ('User '|| :ROLES_TO_GRANT || ' Role Failed'); 
		ELSE 
		  /*  Message ('User ' || :USER_INFO_BLOCK.USERNAME || ' GRANTED '||


:ROLES_TO_GRANT);  */


		NULL;
		END IF;
		NEXT_RECORD;
		IF :ROLES_TO_GRANT is null THEN EXIT;
		END IF;
	END LOOP;
	GO_BLOCK('NEW_ROLES');
	Clear_BLOCK;
	GO_BLOCK('DBA_ROLE_PRIVS');
	EXECUTE_QUERY;

     ELSIF :USER_INFO_BLOCK.action_item = 'ALTER'
	THEN GO_BLOCK('NEW_ROLES');
		FIRST_RECORD;
	LOOP
		:USER_INFO_BLOCK.SQL_MESSAGE := 'GRANT '|| :ROLES_TO_GRANT || ' TO '
|| :USER_INFO_BLOCK.USERNAME;
		FORMS_DDL(:USER_INFO_BLOCK.SQL_MESSAGE);
		IF NOT Form_Success THEN 
    		    Message ('User '|| :ROLES_TO_GRANT || ' Role Failed'); 
		ELSE 
		  NULL; /*  Message ('User ' || :USER_INFO_BLOCK.USERNAME || ' GRANTED
'|| :ROLES_TO_GRANT);  */
		END IF;
		NEXT_RECORD;
		IF :ROLES_TO_GRANT is null THEN EXIT;
		END IF;
	END LOOP;
	GO_BLOCK('NEW_ROLES');
	Clear_BLOCK;
	GO_BLOCK('DBA_ROLE_PRIVS');
	EXECUTE_QUERY;

     ELSIF :USER_INFO_BLOCK.action_item = 'DELETE'
	THEN :USER_INFO_BLOCK.SQL_MESSAGE :=
		'DROP USER ' || :USER_INFO_BLOCK.USERNAME || ' CASCADE';
		FORMS_DDL(:USER_INFO_BLOCK.SQL_MESSAGE);
		IF NOT Form_Success THEN 
    		    Message ('User Drop Failed'); 
		ELSE 
		   GO_BLOCK('NEW_ROLES');
		   Clear_BLOCK;
		    Message ('User ' || :USER_INFO_BLOCK.USERNAME || ' Dropped'); 
		END IF; 
	GO_BLOCK('USER_INFO_BLOCK');
	Clear_BLOCK;
	GO_BLOCK('DBA_ROLE_PRIVS');
	Clear_BLOCK;
	GO_BLOCK('NEW_ROLES');
	Clear_BLOCK;
	/* GO_BLOCK('LOGIN_BLOCK');  */

  END IF;
END;
>Dear World,  

>We developed a MIS application using Forms 4.0.13.20.0, is there anyway 
>that we can build a friendly and convenient way for users to change their 
>password in the application, besides having to exit the application and 
>going via sqlplus, teaching them "alter user .... identified by ... "? It 
>should be GUI, password would not be echoed when typing etc. Does anyone 
>have such experience ? Many Thanks and A Merry Christmas to all !
 
>Rgds
>Kir Chern, Loh
>(Inst Of Molecular And Cell Biology

>National University Of Singapore
>Email : mcblohkc_at_leonis.nus.sg)

Richard A. Wark                                warkr_at_vanadium.brooks.af.mil
DBA - Computer Sciences Corp.                  http://www.txdirect.net/users/rwark

Brooks AFB, San Antonio TEXAS Received on Tue Dec 26 1995 - 00:00:00 CET