A VMS script to check user, change password.

Hello Oracle DBAs,

Here is a VMS command procedure to check existence of a userid in a given database and/or to change his/her password. A help file is also attached. The appropriate rdbms is version 6 series.

$ save_verify = f$verify(0) ! QUTDCL std+
$ on control_y then exit 1554.or.(f$verify(save_verify).and.0)

$!-------------------------------------------------------------
$! Procedure name:	CHECK_USER_CHANGE_PW.COM
$! Purpose:		To check existence of a userid in a given database
$!			and/or to change his/her password.
$! QUT Standard Used:	QUTDCL 2.01
$!-------------------------------------------------------------


$	save_message	= f$environment("message")

$	full_proc_name	= f$environment("procedure")

$	proc_name	= f$parse(full_proc_name,,,"name")

$	proc_depth	= f$environment("DEPTH")

$	_ = f$verify(f$trnlnm("''proc_name'$debug").or.f$trnlnm("com$debug"))

$	_ = f$environment("CONTROL")

$	proc_ctly = (_ -"Y") .nes. _

$	tracing = f$trnlnm("$trace$debug")

$	on control_y then goto y_exit


$	if save_message .nes. "/FACILITY/SEVERITY/IDENTIFICATION/TEXT"-


	  then set message/facility/severity/identification/text


$	if tracing then write sys$output -


	  "*** TRACE *** ENTER (depth ",proc_depth,") **** ",full_proc_name


$	exit_status = 1


$	if p1.nes."?" then goto start


$ help:


$	set noon

$	_at_gtl:info 'full_proc_name'


$	goto exit


$!
$!
$ start:
$!
$!	QUTDCL	User Uninterruptable Code			
$!							! QUTDCL std-


$	set nocontrol=y					! QUTDCL opt 1

$	on control_y then goto y_end			! QUTDCL std+

$	if proc_ctly then set control=y			! QUTDCL opt 1

$	on warning   then goto on_end


$	set on


$!
$!	QUTDCL	General user code				
$!							! QUTDCL std-
$!
$ 	SQUOTE[0,7] = 39
$ 	DQUOTE[0,7] = 34


$	TIME_STAMP = F$TIME()


$	ASK = "READ SYS$COMMAND/PROMPT="


$!
$ASK_DATABASE_ID:


$	ASK "Which database id ([DPT], GEN, CSD)? " DBID


$!	IF DBID .EQS. "" THEN GOTO ASK_DATABASE_ID


$	IF DBID .EQS. "" THEN DBID = "DPT"

$	DBID = F$EDIT (DBID, "UPCASE, TRIM")

$	IF DBID .EQS. "DPT" THEN NODE_ID = "ACACIA"

$	IF DBID .EQS. "GEN" THEN NODE_ID = "REDGUM"


$	IF DBID .EQS. "CSD" THEN NODE_ID = "REDGUM"


$!
$       NODE  = F$EDIT(F$GETSYI("SCSNODE"),"COLLAPSE")


$	IF NODE_ID .NES. NODE

$	THEN

$	   WRITE SYS$OUTPUT "The database is not on this node ''NODE' but on ''NODE_ID'."

$	   WRITE SYS$OUTPUT "Re-execute this script on the node ''NODE_ID'."

$	   GOTO USER_END


$	ENDIF


$!
$ASK_USER_ID:


$	ASK "Type in the oracle userid? " USER

$	IF USER .EQS. "" THEN GOTO ASK_USER_ID


$	USER = F$EDIT (USER, "UPCASE, TRIM")


$!


$	OPEN/WRITE COMFIL SYS$LOGIN:CHECK_USER.COM


$ 	WRITE COMFIL "$IF (F$TRNLNM (", DQUOTE, "ORA_SID", DQUOTE, ",", DQUOTE, "LNM$PROCESS", DQUOTE, ") .NES. ", DQUOTE, "''DBID'", DQUOTE, ") THEN _at_ora_root_v6:[db_''DBID'.qutsys_''DBID']oraus$	WRITE COMFIL "$SQLDBA"


$	WRITE COMFIL "CONNECT INTERNAL"


$!	WRITE COMFIL "SELECT USERNAME,CONNECT_PRIV,RESOURCE_PRIV ", -
$!           "DEFAULT_TABLESPACE,CREATED ", - 
$!           "FROM SYS.DBA_USERS WHERE USERNAME = ", -
$!           SQUOTE, USER, SQUOTE, ";"


$	WRITE COMFIL "SELECT * ", - 


           "FROM SYS.DBA_USERS WHERE USERNAME = ", -
           SQUOTE, USER, SQUOTE, ";"


$	WRITE COMFIL "EXIT"

$	CLOSE COMFIL


$	_at_SYS$LOGIN:CHECK_USER


$       ASK "Do you see meaningful values on the output? (Y/[N]) " CONTIN


$	IF CONTIN .EQS. "" .OR. CONTIN .EQS. "N" .OR. CONTIN .EQS. "n" THEN GOTO USER_END


$       ASK "Do you want to change the password? (Y/[N]) " CONTIN


$	IF CONTIN .EQS. "" .OR. CONTIN .EQS. "N" .OR. CONTIN .EQS. "n" THEN GOTO USER_END


$!
$ASK_USER_PW:


$	ASK "Type in his/her new password? " USERPW

$	IF USERPW .EQS. "" THEN GOTO ASK_USER_PW


$	USERPW = F$EDIT (USERPW, "UPCASE, TRIM")


$!


$	OPEN/WRITE CUPFIL SYS$LOGIN:CHANGE_USER_PW.COM


$ 	WRITE CUPFIL "$IF (F$TRNLNM (", DQUOTE, "ORA_SID", DQUOTE, ",", DQUOTE, "LNM$PROCESS", DQUOTE, ") .NES. ", DQUOTE, "''DBID'", DQUOTE, ") THEN _at_ora_root_v6:[db_''DBID'.qutsys_''DBID']oraus$	WRITE CUPFIL "$SQLDBA"


$	WRITE CUPFIL "CONNECT INTERNAL"

$	WRITE CUPFIL "ALTER USER ", USER, " IDENTIFIED BY ", USERPW, ";"

$	WRITE CUPFIL "EXIT"

$	CLOSE CUPFIL


$	_at_SYS$LOGIN:CHANGE_USER_PW


$!


$	goto user_end					! QUTDCL std+


$!
$ y_end:


$	set nocontrol=y

$	exit_status = 1554  ! SS$_CONTROLY severity=E	! QUTDCL std-


$	goto user_end					! QUTDCL std+


$!
$!
$ on_end:


$	exit_status = '$status'				! QUTDCL std-

$	set noon					! QUTDCL opt 2


$	goto user_end					! QUTDCL std+


$!
$!
$ user_end:


$	set nocontrol=y

$	set noon					! QUTDCL opt 3


$	set symbol/scope=(nolocal,noglobal)		! QUTDCL opt 4


$!	QUTDCL	User cleanup code and environment
$!		restoration	   			! QUTDCL std-


$	goto exit					! QUTDCL std+


$!
$!
$ y_exit:


$	set nocontrol=y


$	exit_status = 1554 ! SS$_CONTROLY with severity=E 


$!
$!
$ exit:


$	set nocontrol=y


$	if tracing then write sys$output -


	  "*** TRACE *** EXIT  (depth ","''proc_depth'",") **** ",-
	  "''full_proc_name'"


$	if save_message .nes. f$environment("MESSAGE") -


		then set message 'save_message'
$ exit1: on control_y then goto exit1


$	if proc_ctly then set control=y


$	exit 'exit_status'.or.(f$verify(save_verify).and.0)


$!
$!	QUTDCL	User Subroutines
$!
$!							! QUTDCL std-
$!							! QUTDCL std+
$!----  QUTDCL	Documentation  -----
$!
$!  Procedure name:	CHECK_USER_CHANGE_PW.COM
$!  Author:		How Hie Ling
$!  Date written:	3 February 1994	
$!  QUT Standard Used:	QUTDCL 2.01
$!  Purpose:		To check existence of a userid in a given database
$!			and/or to change his/her password.
$!
$!  Update history
$!	Rev.	Who When	Description
$!	----	--- ----	-----------
$!  	No Revisions
$!
$!  Input items
$!     Parameters:	
$!     Files:		
$!     Logical Names:
$!     Local Symbols:
$!     Global Symbols:
$!
$!  Returned items
$!     Files:		
$!     Logical Names:	
$!     Global Symbols:	
$!     Exit status:	
$!
$!  Temporaries:
$!    Files:		SYS$LOGIN:CHECK_USER.COM
$!			SYS$LOGIN:CHANGE_USER_PW.COM
$!    Logical Names: 	COMFIL, CUPFIL
$!    Globals Symbols:
$!    Non-Std Local Symbols:  NODE_ID, DBID, USER, USERPW
$!
$!  Subroutine Names:
$!  Com files invoked:	ora_root_v6:[db_''DBID'.qutsys_''DBID']orauser_qutsys_''DBID'"
$!    			SYS$LOGIN:CHECK_USER.COM
$!			SYS$LOGIN:CHANGE_USER_PW.COM
$!  EXE files invoked:	SQLDBA
$!
$!
$!  Misc:
$!  		Nil

$!-------------------------------------------------------------

--------------------------------------------------------------------------------
CHECK_USER_CHANGE_PW -	To check existence of a userid in a given database
			and/or to change his/her password.

Syntax: _at_TTL:CHECK_USER_CHANGE_PW

Command procedure to check existence of a userid in a given database and/or to change his/her password. Note: the account needs special rights identifiers ala ORA_xxx_DBA.

It will first prompt for the database id:

        Which database id ([DPT], GEN, CSD)?

The default database id is DPT, which is the one used by the Faculty of Information Technology on the TEACHING (ACACIA) node. The GEN database on the ACADEMIC (REDGUM) node is used by other faculties such as Business, Engineering, Arts, etc.

The database id you enter will be used to check that you are executing this DCL procedure on the appropriate node. If not, a message will appear:

	The database is not on this node <A-NODE> but on <B-NODE>.
	Re-execute this script on the node <B-NODE>.

It then prompts for the user id that you want to check or to change his/her password:

        Type in the oracle userid? xxxxxxxxx

At this point, the control is passed into the oracle database, as follows:

        SQL*DBA: Version 6.0.36.5.0 - Production on Fri Feb 4 09:56:46 1994

        Copyright (c) Oracle Corporation 1979, 1992. All rights reserved.

	ORACLE RDBMS V6.0.36.5.0 (6.2), transaction processing option - Production
	PL/SQL V1.0.35.2.0 - Production

	SQLDBA> Connected.
	SQLDBA> USERNAME                       USER_ID    PASSWORD
	 CONNECT
	------------------------------ ---------- ------------------------------ -------
	_PR RESOURCE_P DBA_PRIV   DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
	--- ---------- ---------- ------------------------------ -----------------------
	        CREATED   EXPIRES
	------- --------- ---------
	xxxxxxxxx                              11 2462A780990530CA
	  1          0          0 yyyyyyTS                       TMPSEGTS
	        01-FEB-93
	1 row selected.
	SQLDBA> SQL*DBA complete.

Now check that:

  . the USERNAME prefix (if available) matches the DEFAULT_TABLESPACE prefix.
  . the TEMPORARY_TABLESPACE must be TMPSEGTS
  . the CONNECT_PR must be 1
  . the RESOURCE_P must be 0
  . the DBA_PRIV must be 0
  . the message must be "1 row selected."

If any of the above is not as stated, then answer N to the next prompt and see the Systems Programmer.

The control is back to the procedure with the prompt:

        Do you see meaningful values on the output? (Y/[N])

If you answer N, the procedure will stop here. If Y, then it will proceed:

        Do you want to change the password? (Y/[N])

If you don't want to change the password, enter N. If you enter Y, then you will see the final prompt:

        Type in his/her new password?

Control is passed into the database to effect the password change.

        SQL*DBA: Version 6.0.36.5.0 - Production on Fri Feb 4 10:15:46 1994

        Copyright (c) Oracle Corporation 1979, 1992. All rights reserved.

	ORACLE RDBMS V6.0.36.5.0 (6.2), transaction processing option - Production
	PL/SQL V1.0.35.2.0 - Production

	SQLDBA> Connected.
	SQLDBA> Statement processed.
	SQLDBA> SQL*DBA complete.

Make sure you see the following message. It means the change is successful.

       SQLDBA> Statement processed.

Author: How-Hie Ling February 1994

How-Hie Ling.
(h.ling_at_qut.edu.au)
Queensland University of Technology,
Brisbane, Queensland, Australia. Received on Fri Jun 10 1994 - 02:38:10 CEST