KathysFriend
asked on
Oracle 11g password expire
Hi,
In 11g when I set a user's password to expire. No matter what tool they use - (Toad, sqlplusw or cmd - sqlplus) they a prompted to enter in their new password and then get an error message ORA-01017: invalid username/password; logon denied.
Anyone else experience this?
In 11g when I set a user's password to expire. No matter what tool they use - (Toad, sqlplusw or cmd - sqlplus) they a prompted to enter in their new password and then get an error message ORA-01017: invalid username/password; logon denied.
Anyone else experience this?
To investigate the password stuff use:
This simple script will check if a user password is expiring in the next 120 days.
set pagesize 500
set linesize 200
set trimspool on
column EXPIRE DATE format a20
select username as USER NAME, expiry_date as EXPIRE DATE, account_status
from dba_users
where expiry_date < sysdate+120
and account_status IN ( OPEN, EXPIRED(GRACE) )
order by account_status, expiry_date, username
/
Another scripts:
INVESTIGATE AND CHANGE PROFILE ENTRIES
connect sys/pasw@some_instance as sysdba
select profile from dba_users where username='SYSTEM';
select LIMIT, RESOURCE_NAME
from dba_profiles
where RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PA SSWORD_LIF E_TIME','P ASSWORD_RE USE_MAX',' PASSWORD_R EUSE_TIME' )
and PROFILE=(select profile from dba_users where username='SYSTEM');
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
This simple script will check if a user password is expiring in the next 120 days.
set pagesize 500
set linesize 200
set trimspool on
column EXPIRE DATE format a20
select username as USER NAME, expiry_date as EXPIRE DATE, account_status
from dba_users
where expiry_date < sysdate+120
and account_status IN ( OPEN, EXPIRED(GRACE) )
order by account_status, expiry_date, username
/
Another scripts:
INVESTIGATE AND CHANGE PROFILE ENTRIES
connect sys/pasw@some_instance as sysdba
select profile from dba_users where username='SYSTEM';
select LIMIT, RESOURCE_NAME
from dba_profiles
where RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PA
and PROFILE=(select profile from dba_users where username='SYSTEM');
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ASKER
In testing this out with a new user - I am using the same password as the one I use for my own id - so the password complexity is not the issue.
If you have an 11g database - can you test it out - create a user and set their password to expire - then try to login as that user.
If you have an 11g database - can you test it out - create a user and set their password to expire - then try to login as that user.
Please run this and see what the account status is:
set line 120
select username, account_status, expiry_date, lock_date from dba_users
Works fine on my 11g.
Try this also. Turn on auditing for create session (11g has it on by default I think). If not, run: audit create session
Try this also. Turn on auditing for create session (11g has it on by default I think). If not, run: audit create session
select * from dba_audit_session where username = 'whoever';
ASKER
I ran the scripts that you gave me and it showed the account status as expired.
USERNAME ACCOUNT_STATUS EXPIRY_DA LOCK_DATE
-------------------------- ---- -------------------------- ------ --------- ---------
PIERCEY EXPIRED 25-MAR-09
1 row selected.
I tried to sign in twice in Toad and here is my activity in a cmd window
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\>C:\oracle\product\10.2 .0\db_1\BI N\sqlplus piercey@test
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 26 11:23:53 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-28001: the password has expired
Changing password for piercey
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied
C:\>C:\oracle\product\10.2 .0\db_1\BI N\sqlplus piercey@test
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 26 11:24:59 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-28001: the password has expired
Changing password for piercey
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied
Password unchanged
Enter user-name:
Then I tried it again from Toad
USERNAMERETURNCODE EXTENDED_TIMESTAMP
PIERCEY 28001 2009-03-25 3:00:35.359000 PM -03:00
PIERCEY 1017 2009-03-25 3:00:46.218000 PM -03:00
PIERCEY 28001 2009-03-26 11:23:57.406000 AM -03:00
PIERCEY 28001 2009-03-26 11:25:11.531000 AM -03:00
PIERCEY 28001 2009-03-26 11:42:50.031000 AM -03:00
PIERCEY 28001 2009-03-26 11:54:59.109000 AM -03:00
PIERCEY 1017 2009-03-26 11:55:08.062000 AM -03:00
It doesn't seem to log every event.
USERNAME ACCOUNT_STATUS EXPIRY_DA LOCK_DATE
--------------------------
PIERCEY EXPIRED 25-MAR-09
1 row selected.
I tried to sign in twice in Toad and here is my activity in a cmd window
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\>C:\oracle\product\10.2
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 26 11:23:53 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-28001: the password has expired
Changing password for piercey
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied
C:\>C:\oracle\product\10.2
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 26 11:24:59 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-28001: the password has expired
Changing password for piercey
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied
Password unchanged
Enter user-name:
Then I tried it again from Toad
USERNAMERETURNCODE EXTENDED_TIMESTAMP
PIERCEY 28001 2009-03-25 3:00:35.359000 PM -03:00
PIERCEY 1017 2009-03-25 3:00:46.218000 PM -03:00
PIERCEY 28001 2009-03-26 11:23:57.406000 AM -03:00
PIERCEY 28001 2009-03-26 11:25:11.531000 AM -03:00
PIERCEY 28001 2009-03-26 11:42:50.031000 AM -03:00
PIERCEY 28001 2009-03-26 11:54:59.109000 AM -03:00
PIERCEY 1017 2009-03-26 11:55:08.062000 AM -03:00
It doesn't seem to log every event.
ASKER
I figured it out...it only works with version 11 client software.
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Mar 27 09:13:29 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: piercey
Enter password:
ERROR:
ORA-28001: the password has expired
Changing password for piercey
New password:
Retype new password:
Password changed
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Mar 27 09:13:29 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: piercey
Enter password:
ERROR:
ORA-28001: the password has expired
Changing password for piercey
New password:
Retype new password:
Password changed
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Ah, thanks for the info. I will note that.
It is possible to setup the 11g database with backwards compatible security settings, possibly you remember when you installed, 11g has a new choice in DBCA.
That may be another workaround for future databases, but its just a guess, and won't help you now, though I'm sure there is a way to alter the settings on the fly, but I would have to research.
It is possible to setup the 11g database with backwards compatible security settings, possibly you remember when you installed, 11g has a new choice in DBCA.
That may be another workaround for future databases, but its just a guess, and won't help you now, though I'm sure there is a way to alter the settings on the fly, but I would have to research.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have him try a more complex password, or change the policy.