Archive

Archive for December, 2014

PASSWORD column NULL in DBA_USERS from 11g onwards

December 16, 2014 Leave a comment

I got a request from support team to reset the DBA user password as they were getting some error due to password expiration.
However support person was not aware of the password.

SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE
  2  from dba_users
  3  where USERNAME = 'PEOPLE';

USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------------------------------------------------------
EXPIRY_DATE
---------------
PEOPLE
EXPIRED
03-NOV-14

Oracle 11g brought several security enhancements, as it is well known by the 11g users. On previous Oracle versions it was possible to query the DBA_USERS PASSWORD column to get the hashed password string. It was useful when someone tried to temporarily reset the user’s password and restore it to its original value without actually knowing it.
The command:

ALTER USER IDENTIFIED BY VALUES ‘F28740221A2D9A70’;

it could take the hashed value from the DBA_USERS data dictionary view. However starting with Oracle 11g this column is null … so where are we supposed to take this hashed value from?.

SQL> SELECT USERNAME, PASSWORD
2 FROM DBA_USERS
3 WHERE USERNAME='PEOPLE';

USERNAME PASSWORD
--------------- ------------------------------
PEOPLE 

Starting with Oracle 11g, there is a view called SYS.USER$ which stores the PASSWORD, the way it was stored prior to 11g.

Oracle 11g only makes it a little bit more difficult to get the hashed password, but if you login with “SYS” account, you can still apply the conventional (prior to 11g method) to temporarily reset the password, and still have access to the hashed password.

SQL> SELECT NAME, PASSWORD
FROM SYS.USER$
WHERE NAME = 'PEOPLE'   2    3  ;

NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
PEOPLE
F28740221A2D9A70

And the command used to reset the password

SQL> alter user PEOPLE identified by values 'F28740221A2D9A70';

User altered.

SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE
  2  from dba_users
  3  where USERNAME = 'PEOPLE';

USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------------------------------------------------------
EXPIRY_DATE
---------------
PEOPLE
OPEN
14-JUN-15
Advertisements
Categories: Oracle Database Tags: ,

How to change “orcladmin” user password when it is expired

December 9, 2014 Leave a comment
  • Login to $ORACLE_HOME/bin (Infra Home)
    • Make sure vnc process is running and DISPLAY variable is configured to the VNC port
  • export DISPLAY=<server_name>:<port_no>
  • Invoke the script -oidamin
    • $ ./oidadmin
  • Now login to the GUI (onVNC) with the following credential
    • User = “cn=orcladmin”
    • Password = default it is the same as that of “ias_admin”
  • Once logged in, navigate as below
  • Entry Management
    • cn=OracleContext
      • dc=com
      • dc=abc <domain_name>
        • cn=Users
        • cn=<orcladmin>
  • Click on cn=<orcladmin>
  • Scroll to the bottom in “Properties” tab
  • Look for the attribute “userpassword”
  • Update the value with the password and then click on “Apply”
Categories: Oracle Portal Tags: