unlock and unexpire oracle accounts through script: shell script, oracle 11, scripting languages

by: schwertnerPosted on 2009-04-13 at 12:11:52ID: 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;
 
/

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:

Select all Open in new window

by: itkamarajPosted on 2009-04-13 at 12:26:19ID: 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

by: nav_kum_vPosted on 2009-04-14 at 02:24:32ID: 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.

by: MikeOM_DBAPosted on 2009-04-15 at 09:21:40ID: 24149843

Why not just create an Oracle profile which does not expire/lock accounts and then assign this profile to the accounts you select?

by: itkamarajPosted on 2009-04-15 at 10:35:08ID: 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

by: nav_kum_vPosted on 2009-04-16 at 03:37:10ID: 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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:

Select all Open in new window

by: itkamarajPosted on 2009-04-16 at 21:34:28ID: 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

by: nav_kum_vPosted on 2009-04-16 at 21:37:44ID: 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 ';

Question

unlock and unexpire oracle accounts through script

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

shell script

oracle 11

scripting languages

KornShell (ksh)

Scripting Languages

Oracle 11.x Database

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press