4 Replies Latest reply on May 10, 2012 3:36 PM by Rob_J

    Not Showing the Encrypted passwords in DBA_USERS

    494959
      Hi Techies.,

      I have issue in OEM(Enterprise Manager is not able to connect to the database instance), To rectify that I have to update the encrypted password of sysman user in properties file.

      But when I issue the below command, getting blank in password;
      SQL> select username,lock_date,expiry_date,profile,password from dba_users where username='SYSMAN';
      
      USERNAME                       LOCK_DATE EXPIRY_DA PROFILE                        PASSWORD
      ------------------------------ --------- --------- ------------------------------ ------------------------------
      SYSMAN                         10-MAY-12 06-NOV-12 DEFAULT
      h2.account is getting locked after change the profile.

      I have changed the default profile also.what parameter need to change in Profile . i have modified the
       ALTER PROFILE default LIMIT password_lock_time 1000;
      SQL> select username,account_status,lock_date,expiry_date,profile,password from dba_users where username='SYSMAN';
      
      USERNAME                       ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA PROFILE                PASSWORD
      ------------------------------ -------------------------------- --------- --------- ------------------------------ ------------------------------
      SYSMAN                         LOCKED(TIMED)                    10-MAY-12 06-NOV-12 DEFAULT
        • 1. Re: Not Showing the Encrypted passwords in DBA_USERS
          The procedure to change the password in the .properties file is documented
          You need to provide the password in cleartext and Oracle will encrypt it.
          Your 'issue' with respect to dba_users also demonstrates you don't read documentation, in 11gR2 the password doesn't show anymore in dba_users.

          -------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Not Showing the Encrypted passwords in DBA_USERS
            kuljeet singh -
            But when I issue the below command, getting blank in password;
            from 11g dba_users is not showing any encrypted password value in password column.
            but you may get the detail from user$ view.
            SQL> select username,password from dba_users where username='SCOTT';
            
            USERNAME                       PASSWORD
            ------------------------------ ------------------------------
            SCOTT
            
            SQL> select name,password from user$ where name='SCOTT';
            
            NAME                           PASSWORD
            ------------------------------ ------------------------------
            SCOTT                          F894844C34402B67
            Edited by: Kuljeet Pal Singh on May 10, 2012 8:08 PM
            • 3. Re: Not Showing the Encrypted passwords in DBA_USERS
              Mark D Powell
              To add to what Kuljeet posted. First sys.user$ is an rdbms base table and by default only sysdba sessions can see it. Normally there is no valid reason for anyone to see the hashed password value. If you alter a user password, it changed. You can take it on faith.

              In those special cases where you do need to see it you can connect as a sysdba (i.e. user sys) or run dbms_metadata to generate the create user DDL and it contains the hash.

              HTH -- Mark D Powell --
              • 4. Re: Not Showing the Encrypted passwords in DBA_USERS
                Rob_J
                Hi Tippu,

                In 11g you should make sure that the case of the password is correct, as case sensitivity is enabled by default I believe. This can cause account locking issues.

                Also, after refreshed on my development environments I used to run a script to update user passwords with encrypted values, something like:
                ALTER USER ABC IDENTIFIED BY VALUES '<encrypted password>';
                In 11g this is no longer possible. I found this article about it online:

                http://askdba.org/weblog/2008/11/how-to-changerestore-user-password-in-11g/

                In short, you can still do it but you have to do it slightly differently, using the user$ view that the other members have commented on so far.

                Hope that helps,
                Rob
                [url http://www.ora00600.com]www.ora00600.com