Storing Passwords In The Database

-  Using the DBMS_OBFUSCATION_TOOLKIT package

Security Table

First we must build a table to hold the security information:

CREATE TABLE app_users (
  id        NUMBER(10)    NOT NULL,
  username  VARCHAR2(30)  NOT NULL,
  password  VARCHAR2(16)  NOT NULL
)
/

ALTER TABLE app_users ADD (
  CONSTRAINT app_users_pk PRIMARY KEY (id)
)
/

ALTER TABLE app_users ADD (
  CONSTRAINT app_users_uk UNIQUE (username)
)
/

CREATE SEQUENCE app_users_seq
/

Security Package

Next we create the package that contains the specification of the security code:

CREATE OR REPLACE PACKAGE app_user_security AS

  FUNCTION get_hash (p_username  IN  VARCHAR2,
                     p_password  IN  VARCHAR2)
    RETURN VARCHAR2;
    
  PROCEDURE add_user (p_username  IN  VARCHAR2,
                      p_password  IN  VARCHAR2);

  PROCEDURE change_password (p_username      IN  VARCHAR2,
                             p_old_password  IN  VARCHAR2,
                             p_new_password  IN  VARCHAR2);

  PROCEDURE valid_user (p_username  IN  VARCHAR2,
                        p_password  IN  VARCHAR2);

  FUNCTION valid_user (p_username  IN  VARCHAR2,
                       p_password  IN  VARCHAR2)
    RETURN BOOLEAN;

END;
/

We then create the package body to define the actual operations:

CREATE OR REPLACE PACKAGE BODY app_user_security AS

  FUNCTION get_hash (p_username  IN  VARCHAR2,
                     p_password  IN  VARCHAR2)
    RETURN VARCHAR2 AS
  BEGIN
    RETURN DBMS_OBFUSCATION_TOOLKIT.MD5(
      input_string => UPPER(p_username) || '/' || UPPER(p_password));
  END;

  PROCEDURE add_user (p_username  IN  VARCHAR2,
                      p_password  IN  VARCHAR2) AS
  BEGIN
    INSERT INTO app_users (
      id,
      username,
      password
    )
    VALUES (
      app_users_seq.NEXTVAL,
      UPPER(p_username),
      get_hash(p_username, p_password)
    );
    
    COMMIT;
  END;
   
  PROCEDURE change_password (p_username      IN  VARCHAR2,
                             p_old_password  IN  VARCHAR2,
                             p_new_password  IN  VARCHAR2) AS
    v_rowid  ROWID;
  BEGIN
    SELECT rowid
    INTO   v_rowid
    FROM   app_users
    WHERE  username = UPPER(p_username)
    AND    password = get_hash(p_username, p_old_password)
    FOR UPDATE;
    
    UPDATE app_users
    SET    password = get_hash(p_username, p_new_password)
    WHERE  rowid    = v_rowid;
    
    COMMIT;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
  END;

  PROCEDURE valid_user (p_username  IN  VARCHAR2,
                        p_password  IN  VARCHAR2) AS
    v_dummy  VARCHAR2(1);
  BEGIN
    SELECT '1'
    INTO   v_dummy
    FROM   app_users
    WHERE  username = UPPER(p_username)
    AND    password = get_hash(p_username, p_password);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
  END;
  
  FUNCTION valid_user (p_username  IN  VARCHAR2,
                       p_password  IN  VARCHAR2) 
    RETURN BOOLEAN AS
  BEGIN
    valid_user(p_username, p_password);
    RETURN TRUE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN FALSE;
  END;
  
END;
/

The overloads of VALID_USER allow the security check to be performed in a different manner.

The GET_HASH function is used to hash the combination of the username and password. It always returns a VARCHAR2(16) regardless of the length of the input parameters. This level of compression means that the hash value may not be unique, hence the unique constraint on the USERNAME column.

The DBMS_UTILITY.GET_HASH_VALUE function could be used to replace the DBMS_OBFUSCATION_TOOLKIT.MD5 function, but the hashing algorithm of the former is not garaunteed to stay constant between database versions.

Testing

First we create a new user:

SQL> exec app_user_security.add_user('tim','hall');

PL/SQL procedure successfully completed.

SQL> select * from app_users;

        ID USERNAME                       PASSWORD
---------- ------------------------------ ----------------
         1 TIM                            [w�44Z�꿿8fE��

Next we check the VALID_USER procedure:

SQL> EXEC app_user_security.valid_user('tim','hall');

PL/SQL procedure successfully completed.

SQL> EXEC app_user_security.valid_user('tim','abcd');
BEGIN app_user_security.valid_user('tim','hall1'); END;

*
ERROR at line 1:
ORA-20000: Invalid username/password.
ORA-06512: at "W2K1.APP_USER_SECURITY", line 37
ORA-06512: at line 1

Next we check the VALID_USER function:

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    IF app_user_security.valid_user('tim','hall') TH
  3      DBMS_OUTPUT.PUT_LINE('TRUE');
  4    ELSE
  5      DBMS_OUTPUT.PUT_LINE('FALSE');
  6    END IF;
  7  END;
  8  /
TRUE

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    IF app_user_security.valid_user('tim','abcd') T
  3      DBMS_OUTPUT.PUT_LINE('TRUE');
  4    ELSE
  5      DBMS_OUTPUT.PUT_LINE('FALSE');
  6    END IF;
  7  END;
  8  /
FALSE

PL/SQL procedure successfully completed.

SQL>

Finally we check the CHANGE_PASSWORD procedure:

SQL> exec app_user_security.change_password('tim','hall','hall1');

PL/SQL procedure successfully completed.

SQL> exec app_user_security.change_password('tim','abcd','abcd1');
BEGIN app_user_security.change_password('tim','abcd','abcd1'); END;

*
ERROR at line 1:
ORA-20000: Invalid username/password.
ORA-06512: at "W2K1.APP_USER_SECURITY", line 47
ORA-06512: at line 1

SQL>

Know SQL Rank through SQL query.

Problem Statement:

                     We have a table 'Student' as shown below. And we wanted to find the rank of the students in the class.

Student_Name             Total_Marks

John                                           980

Peter                                          990

Vijay                                          990

Raj                                              960

Patrick                                       980

Arnold                                       950

William                                      955

gilbert                                        955

Solution:

The first thing that would strike is, If we sort the data  in descending order, we would get the result. But from a practical point of view Ranking is not just about ordering but also resolve the conflict of those students whose rank is the same because both of them got the same marks. And what about the next student's rank whose total marks comes next to these conflicting students. To answer this we write a smart query for finding Rank. So lets see that,

Select S1.Student_Name, S1.Total_Marks,Count(S2.Total_Marks) as Rank 

From Student S1, Student S2

where  S1.Total_marks <= S2.Total_Marks

or (S1.Total_Marks = S2.Total_Marks and S1.Student_Name = S2.Student_Name)

Group by S1.Student_Name, S1.Total_Marks

Order by S1.Total_Marks DESC , S1.Name

Result:
 

Student_Name             Total_Marks       Rank

Peter                                             990                        1

Vijay                                             990                        1

John                                              980                        3

Patrick                                          980                        3

Raj                                                 960                        5

gilbert                                           955                        6

William                                         955                        6

Arnold                                          950                        8

Sounds Good right.

- 

KVSK