Unlimited passwords in Oracle

Forget about renewing your passwords in Oracle. But use these hints with care. Why bother? Well, just assume you imported the sample data for your new project. And all the passwords should have been renewed. Or you have a continuous integration server that no one really wants to update every month for a new database password. If that’s the case read on.

Quick approach

For you local Oracle XE you know beforehand that all users share the same profile and that it is called default:

alter profile default limit password_life_time unlimited;
alter user <user> identified by <password>;

Lazy approach

Well, if you want to spare the hassle and re-use the existing accounts and passwords, you can run the following script as sys using the procedures created on the bottom of the page. But be beware, this modifies all accounts!

call unlimited_passwords();

General approach

To alter the password expiry policy for a certain user profile in Oracle first check which profile the user is using:

select profile from DBA_USERS where username = <username>

Then you can change the limit to never expire using:

alter profile <profile_name> limit password_life_time UNLIMITED;

Alternatively you can switch off all password verification using (use on development only):

alter profile default limit password_verify_function NULL;

If you want to previously check the limit you may use:

select resource_name,limit from dba_profiles where profile='<profile_name>';

As the database copies the policy when an account has been created or updated check with:

select username,expiry_date,account_status from dba_users;

Unlock the account

After you applied the new policy or an account was locked, you need to set a new password and unlock the account. If the account is not locked you can miss the last part:

alter user user_name identified by new_password account unlock;

Unlimited password for known users w/o password reset

CREATE OR REPLACE PROCEDURE UNLOCK_USER
(
  A_USER IN VARCHAR2 DEFAULT USER
) AS
  -- Create a SQL*Plus variable to hold the hash of existing password
  -- OLD_PASSWORD sys.user$.password%type;
  OLD_PASSWORD VARCHAR2(500);
BEGIN
  -- Select the old hash password as a delimited string
  SELECT '''' || PASSWORD || '''' INTO OLD_PASSWORD
  FROM   SYS.USER$
  WHERE  NAME = UPPER(A_USER);

  -- Reset the password
  EXECUTE IMMEDIATE 'ALTER USER ' || A_USER || ' IDENTIFIED BY VALUES ' || OLD_PASSWORD || ' ACCOUNT UNLOCK';
  DBMS_OUTPUT.PUT_LINE('Unlocked user ' || a_user || '.');
END UNLOCK_USER;
/

CREATE OR REPLACE PROCEDURE UNLIMITED_PASSWORDS
AS
  CURSOR database_user IS
    SELECT USERNAME
    FROM   dba_users
    WHERE  account_status = 'OPEN'
     AND   USERNAME<> 'ANONYMOUS'
  ;
BEGIN
  -- As you should use this on development we assume profile "DEFAULT"
  EXECUTE IMMEDIATE 'ALTER PROFILE DEFAULT LIMIT password_life_time unlimited';

  FOR username IN database_user
  LOOP
    unlock_user(username.username);
  END LOOP;
END UNLIMITED_PASSWORDS;
/