DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2000
    I have created a table that has a password column e.g.
    Create table emp(
    username char(8) NOT NULL,
    user_id char(6) NOT NULL,
    password char (6) NOT NULL

    This table will be used as a log on screen(to grant access to users through a web browser).
    I have been asked to encrypt the password column.I know that there is a package in oracle 8i(8.1.6) release 2 but I can't use it because I am running oracle 8i (8.1.5).

    So I read the below documentation but I don't know if this documentation can help me do what I want to do or not:

    Password Verification Routine Formatting Guidelines
    DBAs can enhance the existing password verification complexity routine or create their own password verification routines using PL/SQL or third-party tools.

    The DBA-authored PL/SQL call must adhere to the following format:

    routine_name (
    userid_parameter IN VARCHAR(30),
    password_parameter IN VARCHAR (30),
    old_password_parameter IN VARCHAR (30)

    After a new routine is created, it must be assigned as the password verification routine using the user's profile or the system default profile.


    The password verify routine must be owned by SYS.

    Password Verification Routine: Sample Script
    The following sample script sets default password resource limits and provides minimum checking of password complexity. You can use this sample script as a model when developing your own complexity checks for a new password.

    This script sets the default password resource parameters, and must be run to enable the password features. However, you can change the default resource parameters if necessary.

    The default password complexity function performs the following minimum complexity checks:

    The password satisfies minimum length requirements.

    The password is not the username. You can modify this function based on your requirements.

    This function must be created in SYS schema, and you must connect sys/<password> as sysdba before running the script.

    CREATE OR REPLACE FUNCTION verify_function
    (username varchar2,
    password varchar2,
    old_password varchar2)
    RETURN boolean IS
    n boolean;
    m integer;
    differ integer;
    isdigit boolean;
    ischar boolean;
    ispunct boolean;
    digitarray varchar2(20);
    punctarray varchar2(25);
    chararray varchar2(52);

    digitarray:= '0123456789';
    chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

    --Check if the password is same as the username
    IF password = username THEN
    raise_application_error(-20001, 'Password same as user');
    END IF;

    --Check for the minimum length of the password
    IF length(password) < 4 THEN
    raise_application_error(-20002, 'Password length less than 4');
    END IF;

    --Check if the password is too simple. A dictionary of words may be
    --maintained and a check may be made so as not to allow the words
    --that are too simple for the password.
    IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle',
    'computer', 'abcd') THEN raise_application_error(-20002, 'Password too simple');
    END IF;

    --Check if the password contains at least one letter, one digit and one
    --punctuation mark.
    --1. Check for the digit
    --You may delete 1. and replace with 2. or 3.
    m := length(password);
    FOR i IN 1..10 LOOP
    FOR j IN 1..m LOOP
    IF substr(password,j,1) = substr(digitarray,i,1) THEN
    GOTO findchar;
    END IF;
    IF isdigit = FALSE THEN
    raise_application_error(-20003, 'Password should contain at least one
    digit, one character and one punctuation');
    END IF;
    --2. Check for the character
    FOR i IN 1..length(chararray) LOOP
    FOR j IN 1..m LOOP
    IF substr(password,j,1) = substr(chararray,i,1) THEN
    GOTO findpunct;
    END IF;
    IF ischar = FALSE THEN
    raise_application_error(-20003, 'Password should contain at least one digit, one
    character and one punctuation');
    END IF;
    --3. Check for the punctuation
    FOR i IN 1..length(punctarray) LOOP
    FOR j IN 1..m LOOP
    IF substr(password,j,1) = substr(punctarray,i,1) THEN
    GOTO endsearch;
    END IF;
    IF ispunct = FALSE THEN raise_application_error(-20003, 'Password should contain at least
    one \ digit, one character and one punctuation');
    END IF;


    --Check if the password differs from the previous password by at least 3 letters
    IF old_password = '' THEN
    raise_application_error(-20004, 'Old password is null');
    END IF;
    --Everything is fine; return TRUE ;
    differ := length(old_password) - length(password);

    IF abs(differ) < 3 THEN
    IF length(password) < length(old_password) THEN
    m := length(password);
    m:= length(old_password);
    END IF;
    differ := abs(differ);
    FOR i IN 1..m LOOP
    IF substr(password,i,1) != substr(old_password,i,1) THEN
    differ := differ + 1;
    END IF;
    IF differ < 3 THEN
    raise_application_error(-20004, 'Password should differ by at
    least 3 characters');
    END IF;
    END IF;
    --Everything is fine; return TRUE ;

    Can any body please tell me if this documentation can help and if so how?
    I will appreciate it if you could give me specific instructions.


  2. #2
    Join Date
    Jun 2000
    I think that documentation was regarding the password verify function for database users.

    What language is the web application you're using? Some have built in libraries where you could use md5 hashes of the passwords, that way nobody can read what is in the database.

    Eg, when users register, the functions compute a one way encrypted value which is for all intents and purposes unique, this is what's stored in the database. When they log in, a function calculates the same hash on the password they inputted, compares it to the encrypted string in the database, and if they match the login is successful. No plain text needs to be in the database, if they lose the password you have to generate a new one since the encryption was one way.

    However this logic would have to be at the application level not the database level.

  3. #3
    Join Date
    Sep 2000
    The thing that you mentioned (creating a function to be used in profiles) is for creating Profiles.Pls have a look once more.

    The one that you need is completly diffrent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.