Wednesday, 4 April 2012

Unexpire passwords of Meta Data Service Repository

Today I ran into the unability to deploy my SOA/BPM project to my development SOASuite installation in my Virtual Machine. I haven't used it for a while, and it turns out that the passwords of my MDS users in the database are expired.

Since it is my personal dedicated development server I don't want passwords to expire at all. So I found out that I have to update the default profile to have the password expiration turn of:

ALTER PROFILE DEFAULT LIMIT
  FAILED_LOGIN_ATTEMPTS UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED; 
To check this you can use the following query:
select LIMIT from dba_profiles where RESOURCE_NAME ='FAILED_LOGIN_ATTEMPTS' 
     and PROFILE = (select profile from dba_users where username like 'DEV_%');
But still the passwords are expired... You can alter user every MDS user one by one. But you can also use the following piece of plsql (run as System):
declare 
  cursor c_ddl
    is select 'alter user '||username ||' identified by welcome1' ddl
    from all_users
    where username like  'DEV_%';
begin
  for r_ddl in c_ddl loop
     execute immediate r_ddl.ddl;
   end loop;
end;
You should change the 'DEV_%' in the where clause of the cursor to reflect the prefix of the MDS users in your system. And to possible show errors you might want to catch exceptions within a local block around the execute immediate.

No comments: