unexpire oracle account

12:15:37 SQL> select username,user_id,account_status,password,PROFILE from dba_users;

USERNAME                          USER_ID ACCOUNT_STATUS                   PASSWORD
------------------------------ ---------- -------------------------------- ------------------------------
PROFILE
------------------------------
OPS$SAPSERVICEZPX                      37 OPEN                             EXTERNAL
DEFAULT

OPS$ORAZPX                             39 OPEN                             EXTERNAL
DEFAULT

OPS$ZPXADM                             33 OPEN                             EXTERNAL
DEFAULT

SYS                                     0 OPEN
DEFAULT

SYSTEM                                  5 OPEN
DEFAULT

SAPSR3DB                               38 EXPIRED
SAPUPROF

SAPSR3                                 34 EXPIRED(GRACE)
SAPUPROF

OUTLN                                   9 LOCKED
DEFAULT

APPQOSSYS                              31 EXPIRED & LOCKED
DEFAULT

DIP                                    14 EXPIRED & LOCKED
DEFAULT

DBSNMP                                 30 EXPIRED & LOCKED
DEFAULT

ORACLE_OCM                             21 EXPIRED & LOCKED


Need to unexpire account for user SAPSR3DB VERY URGENTLY PRODUCTION SYSTEM AFFECTED oracle 11g
LVL 2
profjohan11Asked:
Who is Participating?
 
OP_ZaharinCommented:
hi Johan,
- when you connect to sqlplus using an expired user, it will prompt for a new password. that is exactly what happen to you. so the ALTER USER statement and the sqlplus prompt for password is the same. (if i can remember) you can simply type PASSWORD in sqlplus to change your current connected user password.

- the following Oracle doc  explain this:
"If you log on or connect as a user whose account has expired, you are prompted to change your password before you can connect."

http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch3.htm
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
reset the password for them which are showing as EXPIRED ....

reset the password and unlock the account which are showing as EXPIRED & LOCKED
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
UNLOCK COMMAND WILL DO THAT
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Christoffer SwanströmPartnerCommented:
Use the following to unlock and reset passwords
alter user username account unlock;

alter user username identified by new_password;

Open in new window

0
 
OP_ZaharinCommented:
- as SAPSR3DB  is marked as expired, you just need to set a new password for it:
ALTER USER SAPSR3DB IDENTIFIED BY <newpassword>;
0
 
profjohan11Author Commented:
hi,

This is what worked for me:

assword set to original password fpr user sapsr3db

SQL> conn sapsr3db
Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for sapsr3db
New password:
Retype new password:
Password changed
Connected.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


My question is as follows:

What is the difference between my solution and using ALTER USER SAPSR3DB IDENTIFIED BY <newpassword>;  ?

I do know that this is a SAP instance running on oracle so if you only do this and set new password you may have inconsistencies in sap due toe sapuser table not being updated.

ALTER USER SAPSR3DB IDENTIFIED BY <newpassword>;  ?


Another sap suggessted solution was to use brconnect -u sapsr3db/\password -f chpass  and this did not work either.

I will award points shortly to all who answers and thanks for your replies.

Johan
0
 
Christoffer SwanströmPartnerCommented:
You say you set the password to the original one. Where did you do that?
0
 
Wasim Akram ShaikCommented:
hi johan, as mentioned in above comments the password got expired for the mentioned user

if you feel that your user password should never get expired then you have to do following things

CREATE PROFILE <profile_name> LIMIT
       PASSWORD_LIFE_TIME UNLIMITED;

ALTER USER <user_name>PROFILE <profile_name>;

Open in new window


for more information about the profiles and their expiration dates and setting up rules on database security, you can check out the oracle standard documentation at the below link

http://docs.oracle.com/cd/B28359_01/server.111/b28337/tdpsg_user_accounts.htm#BABBICGB

check out these variables

PASSWORD_LIFE_TIME  180
Sets the number of days the user can use his or her current password.



PASSWORD_LOCK_TIME  1
Sets the number of days an account will be locked after the specified number of consecutive failed login attempts.

PASSWORD_REUSE_MAX   UNLIMITED
Sets the number of password changes required before the current password can be reused.

PASSWORD_REUSE_TIME UNLIMITED
Sets the number of days before which a password cannot be reused.

0
 
Jayesh AcharyaTechnichal ConsultantCommented:
-- get you r account uloncked and set password to Sap1234!

alter user SAPSR3DB account unlock;

alter user SAPSR3DB identified by Sap1234!;


0
 
Jayesh AcharyaTechnichal ConsultantCommented:
--unlock and reset all passwords you indentified ...


declare
cursor c1 is
select username,user_id,account_status,password,PROFILE from dba_users
where account_status like '%LOCKED%'
    or account_status like '%EXPIRED%';
   
begin
  for x in c1 loop
    alter user x.USERNAME account unlock;
   
    alter user x.USERNAME identified by Sap1234!;
   
  end loop;
 
end;
.
/

-- this will get you all the users unlocked and passwords reset to Sap1234!
0
 
profjohan11Author Commented:
H guys,

How do I determine the password_grace_period before account locks.

i.e. username sapsr3 and profile name sapuprof

0
 
Christoffer SwanströmPartnerCommented:

SELECT * FROM dba_profiles WHERE resource_name = 'PASSWORD_GRACE_TIME'

Open in new window

0
 
Christoffer SwanströmPartnerCommented:
To be more accurate:
SELECT * FROM dba_profiles WHERE resource_name = 'PASSWORD_GRACE_TIME' AND profile = 'SAPUPROF'

Open in new window

0
 
Wasim Akram ShaikCommented:
this query will give you those details..

you need to query dba_users to get the profile_name of an user an that should be substituted in below query to get the desired result

select * from dba_profiles where profile='<PROFILE_NAME>' and name='PASSWORD_GRACE_TIME';

0
 
Christoffer SwanströmPartnerCommented:
Sorry, something wrong with the code formatting there, here once again:

SELECT * FROM dba_profiles WHERE resource_name = 'PASSWORD_GRACE_TIME' AND profile = 'SAPUPROF';
0
 
profjohan11Author Commented:
the sapuprof profile is assigned to several users , so how can I DETERMINE when the specific users password grace expires?

SQL> SELECT * FROM dba_profiles WHERE resource_name = 'PASSWORD_GRACE_TIME' AND profile = 'SAPUPROF';

PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
SAPUPROF                       PASSWORD_GRACE_TIME              PASSWORD
DEFAULT
0
 
Christoffer SwanströmPartnerCommented:
Check expiry_date from dba_users and then add the password_grace_time from dba_profiles.
0
 
Christoffer SwanströmPartnerCommented:
Here's a query to do that, just add the proper user name:

SELECT
  usr.username
  ,usr.expiry_date
  ,prf.limit
  ,usr.expiry_date + prf.limit AS grace_time_limit
FROM
  dba_users usr
INNER JOIN
  dba_profiles prf
ON
  prf.profile = usr.profile
AND
  prf.resource_name = 'PASSWORD_GRACE_TIME'
WHERE
  usr.username = <ADD USER NAME>
0
 
Christoffer SwanströmPartnerCommented:
By the way, I think your original question about "unexpiring" accounts has been answered. For any follow-up questions it would be fair to open a new question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.