Solved

unlock and unexpire oracle accounts through script

Posted on 2009-04-13
8
6,698 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
8 Comments
 
LVL 47

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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Recently, an awarded photographer, Selina De Maeyer (http://www.selinademaeyer.com/), completed a photo shoot of a beautiful event (http://www.sintjacobantwerpen.be/verslag-en-fotoreportage-van-de-sacramentsprocessie-door-antwerpen#thumbnails) in An…
This video shows how to recover a database from a user managed backup
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now