ora 28001 the password has expired что делать
Oracle ORA-28001: the password has expired
Перестало работать приложение, использующее СУБД Oracle XE, в логе сообщение об истечении срока действия пароля: ORA-28001: the password has expired. При чем, было неизвестно под каким пользователем коннектится приложение. Но ясно, что искать надо просроченные пароли.
Приконнектиться к базе как супер админ:
Посмотреть пользователей с истекшими паролями:
Стало ясно, что искомый пользователь ORAUSER. Теперь надо его разлочить и сменить\переназначить пароль:
Проверить статус и дополнительно посмотреть дату истечения пароля:
Видно, что учетка работает, но 3 января срок действия пароля снова истечет. Чтобы этого не происходило надо отключить данную возможность. Для этого надо узнать к какому профилю относится пользователь и отключить истечение времени действия пароля для этого профиля:
Убедиться что все сработало. Поле EXPIRY_DATE должно быть пустым:
Похожие записи
Комментарии к посту “ Oracle ORA-28001: the password has expired ”
Хорошо, что так примитивно рассказано. Ораклисты скажут, что нужно вспоминать матчасть. А у меня ситуация такая — робот сломался во время торгов на бирже, нужно было за минимальное время решить проблему. Здесь пошагово получилось за 2 минуты…
SQL> connect SYSTEM/34PassworD46@127.0.0.1:1521/xe;
ERROR:
ORA-28001: the password has expired
—Истек срок действия пароля
—Далее просит сменить пароль, меняю пароль сложный
Changing password for SYSTEM
New password: —Ввожу мега сложный пароль.
Retype new password: —Подтверждаю пароль.
ERROR:
ORA-01017: invalid username/password; logon denied
Password unchanged
SQL>
—Вываливается ошибка, пароль не изменен! в чем затык не понимаю.
Пардоныч хлопцы, вопрос решил у меня видимо, что то с ПК видимо, Выполнить cmd > sqlplus /nolog > и далее все что описывал (короче не работает командная строка).
Помогла утилита Oracle RUN SQL Command Line, через нее все тоже самое проделал и все сменилось.
Автору спасибо, как не хватает статей написанных в таком стиле… без лишних отсылок типа «поищите в гугле». Просто, конкретно и понятно.
Полдня лазания по форумам ораклистов не дали никакого понимания. Тут за 2 минуты все понял.
Однако, если статус пользователя EXPIRED, т.е. пароль истек уже, то смена пароля ему почему-то не помогает
Через SQL Developer удалось смениь пароль. Непредсказуемо
How to Resolve ORA-28001: the password has expired
ORA-28001
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
Solutions to ORA-28001
There’re two methods to solve ORA-28001, the first one is to change password as they want. The other is to set the original password for users.
Method 1: 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;
I know what you’re looking for, just keep reading the post.
Method 2: 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, no syntax can achieve that, we have to set the original password for our user.
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;
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’;
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.
How do I turn off Oracle password expiration?
I’m using Oracle for development. The password for a bootstrap account that I always use to rebuild my database has expired.
How do I turn off password expiration for this user (and all other users) permanently?
I’m using Oracle 11g, which has passwords expire by default.
6 Answers 6
To alter the password expiry policy for a certain user profile in Oracle first check which profile the user is using:
Then you can change the limit to never expire using:
If you want to previously check the limit you may use:
For development you can disable password policy if no other profile was set (i.e. disable password expiration in default one):
Then, reset password and unlock user account. It should never expire again:
As the other answers state, changing the user’s profile (e.g. the ‘DEFAULT’ profile) appropriately will lead to passwords, that once set, will never expire.
However, as one commenter points out, passwords set under the profile’s old values may already be expired, and (if after the profile’s specified grace period) the account locked.
The solution for expired passwords with locked accounts (as provided in an answering comment) is to use one version of the ALTER USER command:
However the unlock command only works for accounts where the account is actually locked, but not for those accounts that are in the grace period, i.e. where the password is expired but the account is not yet locked. For these accounts the password must be reset with another version of the ALTER USER command:
Below is a little SQL*Plus script that a privileged user (e.g. user ‘SYS’) can use to reset a user’s password to the current existing hashed value stored in the database.
EDIT: Older versions of Oracle store the password or password-hash in the pword column, newer versions of Oracle store the password-hash in the spare4 column. Script below changed to collect the pword and spare4 columns, but to use the spare4 column to reset the user’s account; modify as needed.
ORA-28001: The password has expired
I’m facing the problem about my database in Oracle, When I opened my database, it appear a message with: «ORA-28001: The password has expired»
I have updated my account with somes:
I check and see that, my account: ‘system’ has OPEN, but I open it on Oracle SQL Developer, it still have alert:
I have refered very much links but it still same the problem, how to fix this?
12 Answers 12
I have fixed the problem, just need to check:
Check «PASSWORD_LIFE_TIME» by
Sql > select * from dba_profiles;
Set to Never expire
Sql> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Then reset the password of locked user.
Try to connect with the users in SQL Plus, whose password has expired. it will prompt for the new password. Enter the new password and confirm password.
1.open command prompt 2.type sqlplus 3.It will ask Enter Password, you can give old password, it will show password has expired ORA-28001 4.It will ask new password and retype password 5.It will change with new password 6.Go to the sql database and try to connect with new password, it will connect.
I had same problem even after changing the password, it wasn’t getting reflected in SQLDEVELOPER.
Atlast following solved my problem :
sqlplus sys/root as sysdba 
SELECT username, account_status, expiry_date FROM dba_users;
ALTER USER dummy_user IDENTIFIED BY dummy_password;
ALTER USER dummy_user ACCOUNT UNLOCK;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
SELECT username, account_status, expiry_date FROM dba_users;
SELECT username, account_status, expiry_date FROM dba_users;
ALTER USER dummy_user IDENTIFIED BY dummy_password;
ALTER USER dummy_user ACCOUNT UNLOCK;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;