How do I unlock a large group of Oracle users that have been locked by VigilEnt Password Manager for (NETIQKB30502)

  • 7730502
  • 02-Feb-2007
  • 14-Aug-2007

Resolution

goal
How do I unlock a large group of Oracle users that have been locked by VigilEnt Password Manager for Oracle?

fact
VigilEnt Password Manager for Oracle

fact
VigilEnt Password Manager for Oracle 6.x

fact
VigilEnt Security Agent for Oracle 1.x

fix

The script(sqlcqr_unlock_accounts.sql) below can be used to unlock a group of users all at once.

Steps for running this unlock script are:

  1. Log in as SQLCQR to a SQL prompt.
  2. Open/Run "sqlcqr_unlock_account.sql".
  3. Find the output file "unlockaccts.sql".
  4. Edit the "unlockaccts.sql" file to remove excess code.
  5. Remove all data found before and including the "Statement processed" message and after including the "SQLCWKS>spool off" message. Everything in between will be left, one line is a comment and the other is an ALTER statement for each user being unlocked.

    *******SAMPLE**********
    Statement processed.
    /*Attempting to unlock JONES*/
    ALTER USER JONES ACCOUNT UNLOCK ;
    /*Attempting to unlock WINTER*/
    ALTER USER WINTER ACCOUNT UNLOCK ;
    SQLWKS> spool off

    ------------------------------------
    -- sqlcqr_unlock_accounts.sql
    -- This script will spool all Oracle locked accounts to a file called "unlockaccts.sql".
    -- Find and run the "unlockaccts.sql" to unlock all locked accounts.

    set serverout on
    spool unlockaccts.sql

    declare cursor lockstats is
    select usr.username,usr.account_status
            from sys.dba_users usr where usr.username = usr.username;

    begin
    for lockrec in lockstats loop
            if lockrec.account_status like 'LOCKED' then
            dbms_output.put_line('/*Attempting to unlock '||lockrec.username||'*/');
            dbms_output.put_line('ALTER USER ' ||lockrec.username||' ACCOUNT UNLOCK ;');
    end if;
    end loop;
    end;
    /
    spool off


Additional Information

Formerly known as NETIQKB30502