Solved

unlock and unexpire oracle accounts through script

Posted on 2009-04-13
8
6,905 Views
Last Modified: 2013-12-21
Hi all,

I like to create a small unix shell script to unlock and unexpire all the expired and locked user accounts.

example:

>./myscript.sh

the above command has to unlock and unexpire the expired and locked user accounts

i like a interaction script also,

example :

>./myscript.sh -i

then it has to ask like this for all users who's account is locked and expired.

ABC account is locked.
Do you want to unlock ?  - waiting for user input ( if it is y then it should unlock)

ABC account is expired.
Do you want to unexpire ? - waiting for user input ( if it is y then it should unlock)

Any idea how to implement this.

thanks
kamaraj.s
0
Comment
Question by:Kamaraj Subramanian
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 24132053
PL/SQL is not interactive language.
I have a script that encounters all passwords that will soon expire, but this is only a warning. There is no action.
et pagesize  500
set linesize  200
set trimspool on
set feedback  off 
set heading off
column "EXPIRE DATE" format a20
select TO_CHAR(STARTUP_TIME, 'Month dd, YYYY HH24:MI:SS') "StartUp" from v$instance;
select username as "USER NAME", 
       TO_CHAR(expiry_date,'DD-MON-YYYY') as "EXPIRE DATE",
       account_status
from dba_users
where expiry_date < sysdate+30
and account_status IN ( 'OPEN', 'EXPIRED(GRACE)' );
/
 
select username as "USER NAME", 
       account_status
from dba_users
where  
  account_status LIKE ( '%LOCKED%' ) 
order by username;
 
/

Open in new window

0
 
LVL 23

Author Comment

by:Kamaraj Subramanian
ID: 24132154
But i need a shell script to interact with oracle.

we can execute the the queries via shell script right ?

so, i am plannig to do like this

first, get all the user name from the database ( already locked and expired users)
store it in array.

and just loop the array to unlock and unexpire it.

that's all

thanks
kamaraj.s
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 24136231
we can even do that by having a temporary table which can be populated by username , account status and whether it needs unlock or not.

Then you can take all those which needs unlocking and then proceed accordingly. before this you need to ensure that you have marked those accounts which need unlock by some flag as 'Y' or 'N'.

Y means needs unlocking account.
N means we do not need to unlock the account.

pseudocode will look something like...

you have to manually create the table once
-------------------------------------------------------
create table my_temp_table
( username varchar2(60),
  account_status varchar2(32),
  unlock_required varchar2(1));

this insert statement needs to be run once to populate the my_temp_table with the details
which require unlocking...

insert into my_temp_table select username, account_status, 'Y'
from dba_users
where account_status ='LOCKED';

-- you need to manually update the above populated records which do not require unlocking
-- with 'N' though they are in locked status for the unlock_required column in the my_temp_table

commit;
 
the script will run the below to do the activity ...
-------------------------------------------------------------
begin
for cur1 in ( select * from my_temp_table where unlock_required ='Y' )
loop
execute immediate 'alter user ' ||  cur1.username || ' account unlock ' ;
end loop;

-- finally cleanup the temp table
delete my_temp_table;
commit;

end;
/

i do not have a unix shell script which can do this by accepting values from you for each locked account. It is very much possible like as you wanted, so just wait for some unix shell script gurus to respond to your question.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 24149843
Why not just create an Oracle profile which does not expire/lock accounts and then assign this profile to the accounts you select?
 
 
0
 
LVL 23

Author Comment

by:Kamaraj Subramanian
ID: 24150544
Yes, everything is ok. but i need it via shell script.

My client expects in shell script only

that's why i stick with shell script

thanks
kamaraj.s
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 24156140
i am not good in unix stuff as when i compare myself to in oracle..... but came up with the below.. which is working for me. Can you give it a try :

note:
 1) comment out all the debug statements which i had put there for understanding which you think that they are not needed in your actual script...
 2) also the below code connects to sqlplus once to unlock each account which are marked with Y as response which is not 100% right. but it does what you wanted as per your requirement. I think the perfect code should connect to database once to unlock all accounts which have Y in one shot.  I tried for this one but could not get it working.
3) also change this code "scott/tiger@testdb" accordingly with right username/pwd@database in the below code in two places.
#!/usr/bin/ksh
 
i=0
echo "
set feedback off
set heading off
set pagesize 0
select username
from dba_users
where account_status = 'LOCKED';
exit
" | sqlplus -s scott/tiger@testdb  | \
while read line
do
arr[$i]="$line"
i=$i+1
done
 
echo "locked user list is given below : "
i=0
echo "Total no. of users locked ${#arr[*]} "
while [ $i -lt ${#arr[*]} ]
do
print ${arr[$i]}
(( i=i+1 ))
done
echo "After Array Print"
 
i=0
while [ $i -lt ${#arr[*]} ]
do
  while [ 1 -eq 1 ]
  do
  echo 'Do you want to unlock account ' ${arr[$i]} : '.Enter only Y/N:'
  read arr_flag[$i]
  if [ ${arr_flag[$i]} = 'Y' -o ${arr_flag[$i]} = 'N' ]
  then
     break
  fi
  done
  #echo ${arr_flag[$i]} is the given flag
i=$i+1
done
 
i=0
echo "Below is the content after user response:"
while [ $i -lt ${#arr[*]} ]
do
print ${arr[$i]} : ${arr_flag[$i]}
(( i=i+1 ))
done
 
echo "Now unlocking the locked accounts which are with Y response"
i=0
while [ $i -lt ${#arr[*]} ]
do
 if [ ${arr_flag[$i]} = 'Y' ]
 then
    print executing unlock for  ${arr[$i]} : ${arr_flag[$i]}
    echo "
           set feedback off
           set heading off
           set pagesize 0
           set serveroutput on
begin
execute immediate 'alter user ' || '${arr[$i]}' || ' account unlock ';
exception when others then
  dbms_output.put_line('exception raised :' ||sqlcode );
  dbms_output.put_line('exception raised :' ||sqlerrm );
end;
/
exit
" | sqlplus -s scott/tiger@testdb
 else
    print ${arr[$i]} : ${arr_flag[$i]} - NO UNLOCK required
 fi
(( i=i+1 ))
done

Open in new window

0
 
LVL 23

Author Comment

by:Kamaraj Subramanian
ID: 24164941
Hi, thanks for your script.

But i see there is not code for unexpire the expired account.

thanks for your code

bye
kamaraj.s
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 24164953
see line no. 66 in the above code.... i am again just pasting the same thing here again... ( only line 66 )

execute immediate 'alter user ' || '${arr[$i]}' || ' account unlock ';
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question