Oracle Database: unexpire schema or fix ORA-28001 without changing the password

In Oracle Database, to unexpire schemas, you will need to issue the command as SYS/SYSTEM:

ALTER USER <USERNAME> IDENTIFIED BY <PASSWORD>

For this to happen you will need to know the password of the user you are trying to "unexpire". There are situations however where we do not know the password of the schema we are trying to unexpire.


A work around is retrieving the hashed password of the schema and issue the ALTER USER statement in slightly different manner:

Using the SCOTT/tiger example below: 

STEP 1: Retrieve the hashed password of the expired schema

Connect as SYSDBA and run the query: 

SQL> select password from sys.user$ where name = 'SCOTT';

PASSWORD
------------------------------
F894844C34402B67

STEP 2: Run the ALTER USER command as below:

SQL> ALTER USER SCOTT IDENTIFIED BY VALUES 'F894844C34402B67';

If account is locked you may need to run the following as well:

ALTER USER SCOTT ACCOUNT UNLOCK;

Hashing passwords in Oracle Database using MD5

When building applications (especially with Oracle Apex) we often have the requirement for managing users with a decent authentication system set up. A security consideration here is hashing password.

You can use the following function to hash your password using MD5.

However if you recursively hash it several times, you can secure your application and prevent dictionary attacks.

Here is a very simple function I wrote for this purpose.



create or replace Function hashPass
   ( raw_pass IN varchar2 )
   RETURN varchar2
IS
    I NUMBER;
    RES VARCHAR2(500);
BEGIN
    res:=raw_pass; -- start by putting raw value into result

    FOR I IN 1..95 LOOP -- hash it 95 times to prevent dictionary attacks
        SELECT WWV_FLOW_ITEM.MD5(res) into res FROM DUAL;
    END LOOP;

    RETURN RES; -- return hash
END;​

Note: 

1. Hashing with MD5 once is never secure as there is possibility of dictionary attacks.
2. I used 95 times for my example. You may use any arbitrary number. 

OBIEE 11G: weblogic.security.SecurityInitializationException

Recently I installed OBIEE 11G in my laptop (simple install) with Windows 7. When I start BI Services from START, my weblogic throws this error before shutting down:  

weblogic.security.SecurityInitializationException: Authentication denied: Boot identity not valid; The user name and/or password from the boot identity file (boot.properties) is not valid. The boot identity may have been changed since the boot identity file was created. Please edit and update the boot identity file with the proper values of username and password. The first time the updated boot identity file is used to start the server, these new values are encrypted.  

First Weblogic log file is located at:
C:\BI11g\user_projects\domains\bifoundation_domain\servers\AdminServer\logs

During installation I configured to put all my BI stuff in BI11g folder in C: drive. Hence this is the root folder of my BI installation. Anyway, this log files tells the errors above in bold, in more readable format than digging through tonnes of lines in command prompt.

I Googled around and came across someone in OTN forum who had the same issues. Turns out there may be something wrong in some boot.properties file.

The boot.properties is located at:  
C:\BI11g\user_projects\domains\bifoundation_domain\servers\AdminServer\security

This file only has 3 lines the 2nd and 3rd being the weblogic username and password. I had a stray "/" in password which I removed.

Then I restarted the BI Services and it works now. If you go back to your boot.properties files again, you'll notice that your Weblogic username and password is now hashed.

SOURCE:
https://forums.oracle.com/forums/thread.jspa?threadID=2241882

Lost APEX ADMIN password?

You need to locate and run the file C:\oraclexe\app\oracle\product\11.2.0\server\apex\apxchpwd.sql

If you are running your database in Linux, find the apxchpwd.sql file by

$ locate apxchpwd.sql

Then go to that directory and run SQLPlus.

Follow these instructions to recover your ADMIN password. Note this is not for reseting your workspace ADMIN account password. Its for resetting the password for ADMIN account for your entire APEX installation.

1. Open command prompt and cd to C:\oraclexe\app\oracle\product\11.2.0\server\apex
2. Run sqlplus from command prompt.
3. Run the command alter session set current_schema='apex_040000';

Run the command @apxchpwd.sql and enter your password.

You should now be able to log into http://localhost:8080/apex/apex_admin. APEX will ask you change  your password again after logging in.