Script for database task.

Shell Programming and Scripting
1

Hi,

I need help in creating script for "User password reset in database" by logging into database from linux server and resetting the user password.
Could you please provide the script for this task?

Steps are given below.

  1. Login into database from server
sqlplus maddy/maddy123@DBname
  1. Execute the below command in database to reset password.
alter user maddy identified by new password

Regards,
Maddy

2

Any attempts / ideas / thoughts from your side?

3

Hi RudiC,

  1. Here is the content of script.
oracle@testserver:~> cat usercreation.sh
echo "Enter the username to create"
  read Username
   echo "enter the password for the user"
    read Password
     echo "Enter the SID "
      read SID
       echo "create user $Username identified by $Password;
        grant connect,resource to $Username;
         exit
          " | sqlplus / as sysdba@$SID
  1. This is how I execute the script.
oracle@testserver:~> ./usercreation.sh
Enter the username to create
maddy                              -- Here I enter the username 
enter the password for the user
maddy123                           --- Here I enter the new password 
Enter the SID
Demo                                --- Here I enter the database name

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 12 14:24:56 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
User created.                      --- Here the user got created.

SQL>
Grant succeeded.                   --- Here it grants privileges (connect,resource) to user.

Regards,
Maddy

4

OK, that's user creation, not password change. What keeps you from adapting this script to you new requirement?

5

Hi,

The script works fine in a server where it is created.

What I need is we are maintaining more than 50 Unix servers including hp-ux and linux server and each server has multiple database running in it.

I want to modify the script in such a way for example when I execute the script in server A ( where it is created) , it should be able to access other servers of databases and reset the user password.

The script will create new user, reset the password for user and grant connect,resource privilege to user.

6

If you give it $SID for a remote database, does it connect?

If so, simply wrap your code in a shell loop. It would probably be better if you can read a list of SIDs from a file so you can maintain that more simply than editing the script each time you need to add/remove a SID.

Robin