How do I change my Oracle user password ?

c:\>
C:\>sqlplus testuser/Password@//ractest-scan.ractest.local:1521/service

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 31 11:44:13 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options


SQL> password

Changing password for TESTUSER
Old password:
New password:
Retype new password:
Password changed

SQL>

ORA-15260: permission denied on ASM disk group

PROBLEM

Try to set ASM diskgroup attribute, then get the following errors:

$ sqlplus / as sysdba
...
..
.
SQL> alter diskgroup OCR_VOTE SET ATTRIBUTE 'compatible.asm' ='11.2.0.2';
alter diskgroup OCR_VOTE SET ATTRIBUTE 'compatible.asm' ='11.2.0.2'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group

Subscribe to get access

Read more of this content when you subscribe today.

ORA-28002 : the password will expire within 3 days

User password can be restored back to its original one by using password hash value.

The password is unknown, and it is encrypted in application configurations. User doesn’t want to change it at the moment.

$ oerr ora 28002
28002, 00000, "the password will expire within %s days"
// *Cause: The user's account is about to expire and the password
// needs to be changed
// *Action: change the password or contact the DBA
//

SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,EXPIRY_DATE 
       from dba_users 
      where username='TESTUSER';

USERNAME         PASSWORD             ACCOUNT_STATUS    EXPIRY_DATE
---------------- -------------------- ----------------  ---------
TESTUSER                              EXPIRED(GRACE)    18-MAR-17

Get hash password value from USER$.

SQL> select password from user$ where name='TESTUSER';

PASSWORD
--------------------
512795B1F6AC27B8

Reset the password with the original one:

SQL> alter user testuser identified by values '512795B1F6AC27B8';

User altered.
SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE 
       from dba_users 
      where USERNAME='TESTUSER';

USERNAME       ACCOUNT_STATUS    EXPIRY_DATE
-------------- ---------------   -----------
TESTUSER       OPEN              14-MAY-17

Oracle Proxy User Configurations

Oracle Proxy User allows to access a schema via a different username/password combination, which is done by using the GRANT CONNECT THROUGH clause on the destination user.

A proxy user is a user who is able to connect as another user without password required. In this example, user “TESTUSER” needs connect as user “APPDBA” without knowing the password of user “APPDBA”.

  • Create destination user with appropriate role(s).
SQL> create user appdba identified by Password;
User created.

SQL> grant connect,resource,dba to appdba;
Grant succeeded.
  • Create the proxy user account.
SQL> create user testuser identified by PasswordProxy;
User created.
  • Alter the destinatiom user ( APPDBA) to connect through the proxy user (TESTUSER).
SQL> alter user appdba grant connect through testuser;
User altered.
  • Test the connection for proxy user.
SQL> select count(*) from dba_users;

 COUNT(*)
----------
 31


SQL> connect testuser[appdba]/PasswordProxy
Connected.
SQL> show user
USER is "APPDBA"


SQL> select * from proxy_users;

PROXY      CLIENT       AUT FLAGS
---------- ------------ --- -----------------------------------
TESTUSER   APPDBA       NO  PROXY MAY ACTIVATE ALL CLIENT ROLES


SQL> select sys_context('userenv','session_user') session_user,
            sys_context('userenv','session_schema') session_schema,
            sys_context('userenv','current_schema') current_schema,
            sys_context('userenv','proxy_user') proxy_user
    from dual;

SESSION_USER    SESSION_SCHEMA   CURRENT_SCHEMA PROXY_USER
--------------- --------------- --------------- ---------------
APPDBA          APPDBA          APPDBA          TESTUSER


SQL>select USERNAME,ACTION_NAME,COMMENT_TEXT,PROXY_SESSIONID 
      from dba_audit_trail 
      where PROXY_SESSIONID is not null

USERNAME ACTION_NAM COMMENT_TEXT               PROXY_SESSIONID
-------- ---------- -------------------------- ---------------
APPDBA   LOGON      Authenticated by: PROXY;    222208723
                    EXTERNAL NAME: oracle      


SQL> select USERNAME,ACTION_NAME,COMMENT_TEXT ,PROXY_SESSIONID 
     from dba_audit_trail 
     where SESSIONID=222208723;

USERNAME  ACTION_NAME               COMMENT_TEXT     PROXY_SESSIONID
--------  ------------------------  ---------------- ---------------
TESTUSER  PROXY AUTHENTICATION ONLY Authenticated by: 
                                    DATABASE

  • If you don’t want the proxy user (testuser ) has all the privilege of user APPDBA, alter the proxy user by giving the certain roles ( CONNECT, RESOURCE ). In this case, DBA_USERS is not accessible to user TESTUSER.
SQL> alter user appdba grant connect through testuser with role connect, resource;

User altered.

SQL> connect testuser[appdba]/PasswordProxy
Connected.

SQL> select count(*) from dba_users;
select count(*) from dba_users
 *
ERROR at line 1:
ORA-00942: table or view does not exist
  • The proxy authentication can be revoked using the following command.
ALTER USER appdba REVOKE CONNECT THROUGH testuser;

Change ASM Instance SYS ASMSNMP User Password

Change ASM instance SYS ASMSNMP user password by using ASM command “passwd”. If ASM password file is stored and shared in ASM diskgroup, then SQL “alter user” can still be used to change ASM user password.

Here is the way to change ASM instance SYS/ASMSNMP user password in 12c.

$ asmcmd
ASMCMD> pwget --asm
+OCR_VOTE/orapwASM

ASMCMD> cd +OCR_VOTE
ASMCMD> ls -l orapwASM
Type     Redund Striped Time           Sys Name
PASSWORD UNPROT COARSE  AUG 08 17:00:00 N  orapwASM => +OCR_VOTE/ASM/PASSWORD/pwdasm.256.919359249

ASMCMD> cd +OCR_VOTE/ASM/PASSWORD
ASMCMD> ls -l
Type     Redund Striped Time            Sys Name
PASSWORD UNPROT COARSE  AUG 08 17:00:00 Y   pwdasm.256.919359249
ASMCMD>

To see users in password file:

ASMCMD> lspwusr
Username sysdba sysoper sysasm
 SYS     TRUE   TRUE    TRUE
 ASMSNMP TRUE   FALSE   FALSE

ASMCMD> lspwusr --suppressheader
 SYS     TRUE TRUE  TRUE
 ASMSNMP TRUE FALSE FALSE
ASMCMD>

Change user password:

ASMCMD> passwd
usage: passwd <user>
help: help passwd
ASMCMD> help passwd
passwd
 Changes the password of a user.

Synopsis
 passwd <user>

Description
 The option for the passwd command is described below.

user - Name of the user.

An error is raised if the user does not exist in the Oracle ASM
 password file. The user is first prompted for the current password,
 then the new password. The command requires the SYSASM privilege to run

Examples
 This example changes the password of the oracle2 user.

ASMCMD [+] > passwd oracle2
 Enter old password (optional):
 Enter new password: ******


ASMCMD> passwd sys
Enter old password (optional):
Enter new password: *********
ASMCMD>

We can still use “alter user ” to change sys and asmsnmp user password :

SQL> alter user sys identified by "XXXXXXXX";
alter user sys identified by "XXXXXXXX"
 *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> connect / as sysasm
Connected.
SQL> alter user sys identified by "XXXXXXXX";

User altered.