SET DEFINE ON VERIFY OFF ECHO OFF TERMOUT ON PAUSE OFF

/*

Creating function verify_user.

Parameters to supply during build:
   plsql_owner = name of oracle user who will own function
   plsql_owner_passwd = password of above user
   default_dbstring = default USING clause of db link for local db.
      For example: o817 (do not supply single-quotes)

Requires Oracle8i (8.1.6 or later)

*/

DISCONNECT
CONNECT &&plsql_owner/&&plsql_owner_passwd

CREATE OR REPLACE FUNCTION verify_user (username_in IN VARCHAR2,
   password_in IN VARCHAR2, dbstring IN VARCHAR2 DEFAULT '&&default_dbstring')
   RETURN BOOLEAN
   AUTHID CURRENT_USER

   /* verify_user: given a username, password, and connect string,
   || this function returns TRUE if a connection to the database
   || succeeds.
   ||
   || Creates and destroys a database link named 'tmp$' || username_in
   ||
   || Requires Net8; raises -20001 exception if Net8 is not up.
   */

AS
   dummy dual.dummy%TYPE;
   linkname VARCHAR2(34) := SUBSTR('tmp$' || username_in, 1, 34);
   retval BOOLEAN := TRUE;

   no_listener EXCEPTION;
   PRAGMA EXCEPTION_INIT(no_listener, -12541);

   PROCEDURE cleanup IS
   BEGIN
      EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || linkname;
   EXCEPTION
      WHEN OTHERS THEN NULL;
   END;

BEGIN
   cleanup;
   BEGIN
      EXECUTE IMMEDIATE 'CREATE DATABASE LINK ' || linkname || ' CONNECT TO '
         || username_in || ' IDENTIFIED BY ' || password_in
         || ' USING ''' || dbstring || '''';
      EXECUTE IMMEDIATE 'SELECT dummy FROM dual@' || linkname;
   EXCEPTION
      WHEN no_listener
      THEN
         RAISE_APPLICATION_ERROR(-20001, 
            'Cannot verify user/pass combination because ' ||
            'this function requires Net8, which is apparently not running.');
      WHEN OTHERS
      THEN
         retval := FALSE;
   END;
   cleanup;
   RETURN retval;
END;
/

SHOW ERRORS


GRANT EXECUTE ON verify_user TO SCOTT;

DISCONNECT
CONNECT SCOTT/TIGER
SET SERVEROUTPUT ON SIZE 1000000

PROMPT Testing...
BEGIN
   IF &&plsql_owner..verify_user('garbage','garbage')
   THEN
     DBMS_OUTPUT.PUT_LINE('unexpectedly returned TRUE with bad userid/password, default db string');
   ELSE
     DBMS_OUTPUT.PUT_LINE('returned FALSE as expected with bad userid/password, default db string');
   END IF;

   IF &&plsql_owner..verify_user('&&plsql_owner','&&plsql_owner_passwd','garbage')
   THEN
     DBMS_OUTPUT.PUT_LINE('unexpectedly returned TRUE as expected with bad db id, default db string');
   ELSE
     DBMS_OUTPUT.PUT_LINE('returned FALSE as expected with bad db id, default db string');
   END IF;

   IF &&plsql_owner..verify_user('scott','tiger')
   THEN
     DBMS_OUTPUT.PUT_LINE('returned TRUE as expected with good userid, password, default db string');
   ELSE
     DBMS_OUTPUT.PUT_LINE('returned FALSE even though had good userid, password, default db string');
   END IF;

   IF &&plsql_owner..verify_user('&&plsql_owner','&&plsql_owner_passwd')
   THEN
     DBMS_OUTPUT.PUT_LINE('returned TRUE as expected with good userid, password, default db string');
   ELSE
     DBMS_OUTPUT.PUT_LINE('returned FALSE even though had good userid, password, default db string');
   END IF;


   /* real garbage test */
   IF &&plsql_owner..verify_user('1238;79asdf&* @ |''  ^(*&^.....+++```~@',';;;  **')
   THEN
     DBMS_OUTPUT.PUT_LINE('unexpectedly returned TRUE with garbage userid and password');
   ELSE
     DBMS_OUTPUT.PUT_LINE('returned FALSE as expected with garbage userid and password');
   END IF;
END;
/