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;

Script to Get Oracle Database Size

Save the following code as SQL file and run it via SQL developer or SQLPlus as SYSDBA to get the database size (dont forget the / in the end).

SET SERVEROUTPUT ON

Declare

  ddf Number:= 0;

  dtf Number:= 0;

  log_bytes Number:= 0;

  total Number:= 0;

BEGIN

  select sum(bytes)/power(1024,3) into ddf from dba_data_files;

  select sum(bytes)/power(1024,3) into dtf from dba_temp_files;

  select sum(bytes)/power(1024,3) into log_bytes from v$log;

  total:= round(ddf+dtf+log_bytes, 3);

  dbms_output.put_line('TOTAL DB Size is: '||total||'GB ');

END;
/

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. 

SQL Query to Find out Oracle Database Version

To find out your Oracle Database version any of these 2 queries:


SELECT * FROM V$VERSION;


which yields the following result:



BANNER                                                                           
-----------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production              
PL/SQL Release 11.2.0.2.0 - Production                                           
CORE 11.2.0.2.0 Production                                                         
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production                          
NLSRTL Version 11.2.0.2.0 - Production               





You can also run: 


SELECT * FROM PRODUCT_COMPONENT_VERSION;

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".

Hashing values in Oracle Database

ORA_HASH( ) function allows you hash a value. This is one way hash meaning you cannot decrypt it back to its original value.

Example,




SELECT ORA_HASH(column_name) FROM table_name;




If you want to hash a particular string (not column value) then do it this way: 




SELECT ora_hash('some text') FROM DUAL;


This will hash the text 'some text' for you. The value you will get is: 2387593664 which is the hash of 'some text'.

Connecting to Oracle Database in SQL plus

In SQLPlus, log into your database and then run the command with the following syntax:


sql> connect username/password@ipaddress:port/sid

So in if you had a XE database over local network at 10.0.0.143, find out the port number and SID (usually XE for Express Edition). Then you would connect as:

  sql> connect sys/secretpassword@10.0.0.143:1551/XE

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