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. 

Creating a Database Link in Oracle

CREATE DATABASE LINK "TESTLINK"
   CONNECT TO "schema_name" IDENTIFIED BY VALUES 'schema_password'
   USING '(DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 101.102.103.104)(PORT = 1521))
       )
       (CONNECT_DATA =
         (SID = XE)
       )
     )';
 


1) Replace the schema_name, schema_password, Host and port with your setup.

2) Login to your schema, and run the query above.

Test it by querying "Select * from dual@TESTLINK".

Resolving SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

If you set up Oracle Database XE 11G in Linux and you encounter the following error when starting up SQL Plus:

$ sqlplus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

To resolve it try the following. It's assumed Oracle XE 11G is installed at:

/u01/app/oracle/product/11.2.0/xe/

If you have a different set up, adjust your directory path accordingly.

1) Find out the path to sqlplus
    bash-3.2$ which sqlplus
   /u01/app/oracle/product/11.2.0/xe/bin/sqlplus

2) Set your Oracle_HOME (upto xe directory)

   bash-3.2$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/

3) Set the path to the directory where sqlplus is located to your PATH variable:

    bash-3.2$ export PATH=/u01/app/oracle/product/11.2.0/xe/bin:$PATH

 
You should now be able to use sqlplus. 

4) Now you need to edit you bash_profile so all these takes effect everytime you log in.

bash-3.2$ cd ~/
bash-3.2$ vim .bash_profile

5) In the .bash_profile you will need to add those export lines so every time you log in the same settings take effect.

bash-3.2$ vim .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
ORACLE_SID=XE
export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
unset USERNAME


6) Run the following:
bash-3.2$ . .bash_profile
 
After following these 6 steps you should be able to connect to sqlplus any time.

OBIEE 11G: Unable to login to OBIEE 11G Analytics due to error: WSM-06162

After installation of OBIEE 11G, I had trouble logging into Analytics using WebLogic credentials. Thankfully the friendly folks at OTN forum helped me out.

Once you have OBIEE 11G installed, you should be able to open up

http://host-port/analytics

and access Analytics with your WebLogic credentials. In case you can't, check your WebLogic console (running on command prompt) and see if you get WSM-06162 error which looks like:


WSM-06162 The policy referenced by URI "oracle/wss_username_token_service_policy" could not be retrieved as connection to Policy Manager cannot be established at "t3://AI-PC:7001" due to invalid configuration or inactive state

Please note, its assumed that your WebLogic credential is working on http://host:port/console and http://host:port/em.

1) Login to Oracle Enterprise Manager OEM by going to: http://host:port/em

2) Login with your WebLogic credential.

3) Expand the Internal Applications and check the status of wsm-pm.

4) If its a red down arrow, right-click on wsm-pm.

5)  Select Control >> Start Up to start it up.

6) wsm-pm should start up. Just note, it may require database access. Ensure your database is running. In my case my OracleXEClrAgent wasn't running.

7) Try restarting BI Services.

8) You should be able to login in now.

SOURCE:
https://forums.oracle.com/forums/thread.jspa?threadID=2327340&tstart=0

 http://docs.oracle.com/cd/E21764_01/core.1111/e10113/chapter_wsm_messages.htm#sthref26556

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

Useful Oracle SQL commands

This blog entry will be updated as I come across more common useful SQL queries.

View current schema:

select sys_context('userenv','current_schema') from dual;




Change Schema: alter session set current_schema=name_of_your_schema;



Grant access to tablespace to user (before a user created is allowed to create a table). grant all on all_tables to sys;



List of users who have privilege to create database linkSELECT * FROM dba_sys_privs WHERE privilege=’CREATE DATABASE LINK’
and admin_option=’YES’;


Grant permission to create database link privilegeGRANT CREATE DATABASE LINK TO sys WITH ADMIN OPTIONS



Unlock a schema or user accountALTER USER schema_name account unlock; 

Importing Database from a DMP file

imp schema_name/schema_password IGNORE=Y FILE=EXP_SCHEMA_NAME_SCHEMA.DMP LOG=IMPORT_LOG_FILE.log

Get a count of all objects in your schema

Pre-requisite: Must have access to schema you are querying on.

select owner,
count(object_type) "count", object_type
from ALL_OBJECTS
where owner = 'schema_name'
group by owner, object_type;


----

Replace schema_name with the name of your schema (in upper case).