Dbms Quotes

CUSTOM_HASH Function create or replace function custom_hash (p_username in varchar2, p_password in varchar2) return varchar2 is l_password varchar2(4000); l_salt varchar2(4000) := 'XV1MH24EC1IHDCQHSS6XQ6QTJSANT3'; begin -- This function should be wrapped, as the hash algorithm is exposed here.  You can change the value of l_salt or the --method of which to call the DBMS_OBFUSCATOIN toolkit, but you must reset all of your passwords if you choose to do --this. l_password := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5 (input_string => p_password || substr(l_salt,10,13) || p_username || substr(l_salt, 4,10))); return l_password; end;   CUSTOM_AUTH Function create or replace function custom_auth (p_username in VARCHAR2, p_password in VARCHAR2) return BOOLEAN is l_password varchar2(4000); l_stored_password varchar2(4000); l_expires_on date; l_count number; begin -- First, check to see if the user is in the user table select count(*) into l_count from demo_users where user_name = p_username; if l_count > 0 then -- Fetch the stored hashed password & expire date select password, expires_on into l_stored_password, l_expires_on from demo_users where user_name = p_username; -- Next, check whether the user's account is expired. If it isn’t, execute the next statement, else return FALSE if l_expires_on > sysdate or l_expires_on is null then -- If the account is not expired, apply the custom hash function to the password l_password := custom_hash(p_username, p_password); -- Finally, compare them to see if they are the same and return either TRUE or FALSE if l_password = l_stored_password then return true; else return false; end if; else return false; end if; else -- The username provided is not in the DEMO_USERS table return false; end if; end;

The overall objective of logical design is to achieve a design that’s (a) hardware independent, for obvious reasons; (b) operating system and DBMS independent, again for obvious reasons; and finally, and perhaps a little controversially, (c) application independent (in other words, we’re concerned primarily with what the data is, rather than with how it’s going to be used). Application independence in this sense is desirable for the very good reason that it’s normally—perhaps always—the case that not all uses to which the data will be put are known at design time; thus, we want a design that’ll be robust, in the sense that it won’t be invalidated by the advent of application requirements that weren’t foreseen at the time of the original design.