Error ora 28001 the password has expired - Исправление ошибок и поиск оптимальных решений проблем

ORA-28001 means that your password has been expired according to your profile. Normally, you should change the password, but you can still work around it. Don’t be confused with ORA-28000: The account is locked, it’s a different account problem.

SQL> conn hr/hr ERROR: ORA-28001: the password has expired

While ORA-28001 alerts that the password has expired, ORA-28002 warns that the password will expired soon in grace time.

A preventive action is to make PASSWORD_LIFE_TIME into UNLIMITED in the profile, there will be no more expiration in the future. However, at this moment, the password expiration notice has been triggered, we need to solve it.

Solutions to ORA-28001

There’re several ways to solve ORA-28001.

Change Password

The formal way to solve it is to change the password.

Defer Expiration

An unofficial way to solve it is to revert the expiration.

Set Original Password

If you want to set the original password, you can try this way.

Change Password

Of course, user can change the password immediately at connect-time.

Changing password for hr New password: Retype new password: Password changed Connected.

A privileged user can do it too.

SQL> conn / as sysdba Connected. SQL> alter user hr identified by hr;


  User altered.

You can also use various GUI tools to change the password.

Defer Expiration

For senior DBA, you may consider to defer the password expiration.

Set Original Password

In some cases, changing password can not be done because the password is bound to specific application and even worse, no one remember the original one! So is there any way to unexpire the password? No, at least no direct syntax supports that, we have to set the original password for our user.

Here comes a question, where to find the original password? For 10g and early versions, just lookup the PASSWORD column in the dictionary view DBA_USERS, which is the encrypted value of password. But for later versions, the column is deprecated and shows nothing.

Now, let’s see what we can do to find the original password.

1. Generate the user’s DDL.

SQL> conn / as sysdba Connected. SQL> set long 4000; SQL> select dbms_metadata.get_ddl('USER','HR') stmt from dual;


  STMT
 --------------------------------------------------------------------------------
     CREATE USER "HR" IDENTIFIED BY VALUES 'S:E129CFF697C9B08E613D3B22B13DC1124B80
 F9346C79FAECF8426B7F3580;T:3716DFD59DAF47A9DA01E6EAE75FC3A86D4911C6FD494F5517FE1
 4E19F89E781BF193E27338CE1D8F3BEE184B1B22DD1B5C3A6A355E7EB7118E53C30B5CE60E246916
 2F618D658D761207BDF81523871'
       DEFAULT TABLESPACE "EXAMPLE"
       TEMPORARY TABLESPACE "TEMP"
       PASSWORD EXPIRE

2. Set Password by VALUES String

In the output, the string follows IDENTIFIED BY VALUES is the original encrypted password. We can use it to set the password. Just don’t forget to remove line breaks in betweens before using it.

SQL> alter USER "HR" IDENTIFIED BY VALUES 'S:E129CFF697C9B08E613D3B22B13DC1124B80F9346C79FAECF8426B7F3580;T:3716DFD59DAF47A9DA01E6EAE75FC3A86D4911C6FD494F5517FE14E19F89E781BF193E27338CE1D8F3BEE184B1B22DD1B5C3A6A355E7EB7118E53C30B5CE60E2469162F618D658D761207BDF81523871';





  


                       
  




  User altered.

Now the user’s password is unexpired, just like nothing happened.

SQL> conn hr/hr; Connected.

Please note that, changing the profile with unlimited PASSWORD_LIFE_TIME for the user can not save ORA-28001 at this moment. But it makes your password unexpired afterwards.

Источник

Перестало работать приложение, использующее СУБД Oracle XE, в логе сообщение об истечении срока действия пароля: ORA-28001: the password has expired. При чем, было неизвестно под каким пользователем коннектится приложение. Но ясно, что искать надо просроченные пароли.

Приконнектиться к базе как супер админ:

conn / as sysdba

Посмотреть пользователей с истекшими паролями:

SQL> SELECT USERNAME,ACCOUNT_STATUS FROM dba_users WHERE ACCOUNT_STATUS LIKE '%EXPIRED%';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
DIP                            EXPIRED > LOCKED
ORACLE_OCM                     EXPIRED > LOCKED
DBSNMP                         EXPIRED > LOCKED
APPQOSSYS                      EXPIRED > LOCKED
ORAUSER                        EXPIRED > LOCKED

Стало ясно, что искомый пользователь ORAUSER. Теперь надо его разлочить и сменитьпереназначить пароль:

SQL> ALTER USER orauser ACCOUNT UNLOCK;
User altered.
SQL> ALTER USER orauser IDENTIFIED BY newpass;
User altered.

Проверить статус и дополнительно посмотреть дату истечения пароля:

SQL> SELECT USERNAME,ACCOUNT_STATUS,EXPIRY_DATE FROM dba_users WHERE USERNAME LIKE '%ORAUSER%';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA
------------------------------ -------------------------------- ---------
ORAUSER                        OPEN                             03-JAN-15

Видно, что учетка работает, но 3 января срок действия пароля снова истечет. Чтобы этого не происходило надо отключить данную возможность. Для этого надо узнать к какому профилю относится пользователь и отключить истечение времени действия пароля для этого профиля:

SQL> SELECT USERNAME,PROFILE from dba_users WHERE USERNAME LIKE '%ORAUSER%';

USERNAME                       PROFILE
------------------------------ ------------------------------
ORAUSER                        ORAPROFILE

SQL> ALTER PROFILE oraprofile LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.

Убедиться что все сработало. Поле EXPIRY_DATE должно быть пустым:

SQL> SELECT USERNAME,ACCOUNT_STATUS,EXPIRY_DATE FROM dba_users WHERE USERNAME LIKE '%ORAUSER%';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA
------------------------------ -------------------------------- ---------
ORAUSER                        OPEN

Все.

Источник

What This Error Means

You’re trying to login to an Oracle database and you get ORA-28001: the password has expired. Yikes! This means that the user’s profile forces them to periodically change their password, due to a security precaution. So, if you’re getting this error, it means a password change is overdue. Oracle is trying to force you to change your password to something different so that your database remains secure.

There are times when this feature is an annoyance, because sometimes changing your password is more trouble than it’s worth. So, here’s a workaround that will let you unexpire your password without having to change it! Or, even know what the password is!!

How to Fix The Issue Now

In the below block of code, you’ll want to replace PUT_USERNAME_YOU_WANT_TO_UNEXPIRE_HERE with whomever you want to unexpire the password for.

<<unexpire_password>>
declare
  CONST_USERNAME constant varchar2(128) := 'PUT_USERNAME_YOU_WANT_TO_UNEXPIRE_HERE';
  v_unexpire_command varchar2(4000);
begin

  execute immediate ' alter user ' || CONST_USERNAME || ' account unlock';

  select 'alter user ' || name || q'< identified by values '>' || spare4 || ';' || password || q'<'>' into v_unexpire_command
  from sys.user$ 
  where name = unexpire_password.const_username;

  execute immediate v_unexpire_command;

end unexpire_password;
/

Run the block of code, and your account will be unlocked and the password unexpired.

How to Prevent “ORA-28001: the password has expired” From Happening In The Future

You say you want to prevent your password from expiring in the future? Not problem, because all you need is a profile that prevents the password from expiring.

The safest way to do this is to create a new profile that has all the same settings as the old profile, but with one change. The new profile should have the PASSWORD_LIFE_TIME set to UNLIMITED.

Below is a script that can help you create the new profile. Just replace USERNAME_GOES_HERE with whatever username you wish to have no password expiration. Optionally, you can change the name of the profile that you’re creating. In the code snippet below, I called it NO_PASSWORD_EXPIRE, but you can change the script so that it’s more meaningful to you.

declare
  CONST_USERNAME constant varchar2(128) := 'USERNAME_GOES_HERE';
  CONST_NEW_PROFILE_NAME constant varchar2(128) := 'NO_PASSWORD_EXPIRE';
  
  v_existing_profile varchar2(128);
  v_sql varchar2(32767);
begin
  select du.profile into v_existing_profile
  from dba_users du
  where du.username = CONST_USERNAME;
  
  v_sql := ' create profile ' || CONST_NEW_PROFILE_NAME || ' limit ';
  
  for v_row in (select dp.resource_name, dp.limit
                from dba_profiles dp
                where dp.profile = v_existing_profile
                  and dp.resource_name <> 'PASSWORD_LIFE_TIME')
  loop
    v_sql := v_sql || v_row.resource_name || ' ' || v_row.limit || ' ';
  end loop;

  v_sql := v_sql || 'PASSWORD_LIFE_TIME UNLIMITED';
                
  declare
    profile_already_exists exception;
    pragma exception_init(profile_already_exists, -2379);
  begin
    execute immediate v_sql;
  exception when profile_already_exists then 
    null;
  end;
  
  
  execute immediate 'alter user ' || CONST_USERNAME || ' profile ' || CONST_NEW_PROFILE_NAME;
end;
/

Free Oracle SQL Tuning Guide

Check out my FREE guide! 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets

Источник