-
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)
)
RETURN BOOLEAN
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.
CREATE/ALTER PROFILE profile_name LIMIT
PASSWORD_VERIFY_FUNCTION routine_name
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/ 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);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()''*+,-/:;<=>?_';
--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.
isdigit:=FALSE;
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
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
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
<>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;
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
<>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
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);
ELSE
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;
END LOOP;
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 ;
RETURN(TRUE);
END;
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.
Thanks,
dorothy
-
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.
-
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.
