How to unlock an Oracle DB account without changing its password

Last updated on November 29, 2022

Sometimes you need to unlock an Oracle DB account without resetting its password. The “DBMS_metadata” utility package can be used to retrieve the password hash in order to unlock the DB account. We are going to see how I used it in this tutorial.

Unlock DB account
Unlock DB account

I was starting up the Oracle BI servers this morning when I get a “Failed to start one or more Servers (return 3)” error message:

Failed to start one or more Servers (return 3)
Failed to start one or more Servers (return 3)

Bummer :-/ Everything was working perfectly before and I had not modified the server configuration recently. I was scratching my head and decided to check for any “ORA” error messages in the “AdminServer.out” file:

grep "ORA" AdminServer.out
grep “ORA” AdminServer.out

The origin of the problem was clearly identified by the “java.sql.SQLException: ORA-28001: the password has expired” lines: some DB accounts password had expired. I remembered that I had modified the “system” DB account password after receiving an “ORA-28002: the password will expire within 7 days” warning message a few weeks ago. Surely the same thing was occurring now to the Oracle DB accounts used by the Oracle BI Server installation. You can see how I created an Oracle 19c DB user profile to turn off password expiration in this page.

I used sqlplus to connect to the Oracle DB instance as a sysdba. I selected the PDB used by the Oracle BI server and I queried for any “BI” (the prefix I chose during the installation) user with an expired password:

SELECT username, account_status, profile FROM dba_users where username like 'BI_%' AND (account_status like '%EXPIRED%' or account_status like '%LOCKED%') ORDER BY username;
SELECT username, account_status, profile FROM dba_users where username like ‘BI_%’ AND (account_status like ‘%EXPIRED%’ or account_status like ‘%LOCKED%’) ORDER BY username;

As you can see in the previous screenshot, seven “BI” DB users accounts had an expired password. This is commonly solved by unlocking the DB accounts with new passwords. Unfortunately these accounts had been created during the Oracle BI Server initial installation and configuration and I was not sure about how to update the Oracle BI Server configuration with the new passwords. An other solution could be to unlock these accounts by reusing their current passwords. But it would have required to know the passwords.

Fortunately there was an other solution: I could use “DBMS_METADATA.GET_DDL()” to retrieve the database accounts definitions including the password hashes. I could later use these hashes to alter the accounts without knowing their passwords. I wanted to store the information into a text file in order to be able to use it. I changed the following settings to avoid the hashes to be stored into multiple lines and I spooled the sqlplus statements output into the BI_USERS.txt file.

SPOOL BI_USERS.txt
SPOOL BI_USERS.txt

I executed the “select DBMS_METADATA.GET_DDL(‘USER’, ‘username’) from dual;” statement for the seven locked “BI” database accounts. The string after the “IDENTIFIED BY VALUES” correspond to the password hash:

select DBMS_METADATA.GET_DDL('USER', 'BI_PLATFORM') from dual;"
select DBMS_METADATA.GET_DDL(‘USER’, ‘BI_PLATFORM’) from dual;”

I opened the BI_USERS.txt file that had been generated with an editor and I copied the “CREATE USER … IDENTIFIED BY VALUES” lines with the password hashes into a new file I named “ALTER_BI_USERS.sql“.

CREATE USER "BI_PLATFORM" IDENTIFIED BY VALUES "password_hash_value"
CREATE USER “BI_PLATFORM” IDENTIFIED BY VALUES “password_hash_value”

I replaced the “CREATE USER” statements by “ALTER USER” statements in this file and I saved it as ALTER_BI_USERS.sql.

ALTER USER statements
ALTER USER statements

I executed the file as SYSDBA to unlock the seven BI users by reusing the current password hash.

@ALTER_BI_USERS.sql
@ALTER_BI_USERS.sql

I checked that the accounts were effectively unlocked:

SELECT username, account_status, profile from dba_users;
SELECT username, account_status, profile from dba_users;

I decided to associate these accounts with the NOPWDEXPIRATION profile I had created in a previous tutorial in order to avoid the accounts passwords to expire in the future. I created a small ALTER_BI_USERS_PROFILE.sql file with “ALTER USER … PROFILE C##NOPWDEXPIRATION;” statements to assign this profile to the “BI” accounts:

ALTER USER ... PROFILE C##NOPWDEXPIRATION;
ALTER USER … PROFILE C##NOPWDEXPIRATION;

I executed the file as SYSDBA:

@ALTER_BI_USERS_PROFILE.sql
@ALTER_BI_USERS_PROFILE.sql

I restarted the Oracle BI servers and after a few minutes I had the confirmation that they had been started successfully this time:

Finished starting servers
Finished starting servers

I opened a Web Browser and I connected to Oracle BI Analytics to perform a final check:

Oracle BI Analytics
Oracle BI Analytics

Everything was working fine once again, great!

Conclusion: do not forget to disable the password expiration policy for the BI DB accounts created during the installation and configuration of an Oracle BI Server!

Disclaimer

The information contained in this Website is for general information purposes only. We try to keep this information up to date and correct but we make no warranties of any kind about the completeness, accuracy, reliability with respect to the website, the information or the graphics contained on the website for any purpose. Any reliance you place on such information is strictly at your own risk. In no event will we be liable for any loss or damage whatsoever from the use of this Website.

The post How to unlock an Oracle DB account without changing its password appeared first on MyBIJourney

All trademarks are properties of their respective owners.
FacebookTwitterPinterestWhatsAppMail