Rem
Rem
Rem dbmhtdb2.sql
Rem
Rem Copyright (c) 2005, Oracle.  All rights reserved.  
Rem
Rem    NAME
Rem      dbmhtdb2.sql - HTMLDB_SYSTEM test
Rem
Rem    DESCRIPTION
Rem    Call library from a package defined by FLOWS_020000 instead
Rem
Rem    NOTES
Rem
Rem TEST_SCRIPT
Rem   get dbmhtdb2.sql
Rem   sql dbmhtdb2.sql
Rem   appfile dbmhtdb2.lis regularTest.lis diffSchemaTest.lis
Rem   compare dbmhtdb2.lis
Rem END
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    lvbcheng    09/06/05   - Created
Rem    jkallman    01/23/2006 - Adjust flows_020100 references to flows_020200
Rem    jkallman    09/29/2006 - Adjust flows_020200 references to flows_030000
Rem    jkallman    08/02/2007 - Change FLOWS_030000 references to FLOWS_030100
Rem    jkallman    09/09/2008 - Change FLOWS_030100 references to APEX_030200
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

create or replace package SYS.WWV_FLOW_VAL authid current_user
as
  /*********************************/
  /* return values for verify_user */
  /*********************************/

  valid_user CONSTANT PLS_INTEGER := 0;
  invalid_user CONSTANT PLS_INTEGER := 1;
  null_input CONSTANT PLS_INTEGER := -1;

  /******************************/
  /* exceptions for verify_user */
  /******************************/

  invalid_caller exception; /* this exception is raised if the user
                               is not allowed to call this package
                             */
  internal_error exception; /* this exception is raised only in unusual
                               situations (i.e., out of memory, database
                               down) */
  account_locked exception; /* this exception is raised if the account
                               is locked */
  password_expired exception; /* this exception is raised if the password
                                 has expired */
  /***********************/
  /* exception constants */
  /***********************/

  invalid_caller_errcode     CONSTANT PLS_INTEGER:= -32058;
  internal_error_errcode     CONSTANT PLS_INTEGER:= -600;
  account_locked_errcode     CONSTANT PLS_INTEGER:= -28000;
  password_expired_errcode   CONSTANT PLS_INTEGER:= -28001;

  PRAGMA EXCEPTION_INIT(invalid_caller,     -32058);
  PRAGMA EXCEPTION_INIT(internal_error,     -600);
  PRAGMA EXCEPTION_INIT(account_locked,     -28000);
  PRAGMA EXCEPTION_INIT(password_expired,   -28001);

  function verify_user(username IN varchar2 character set any_cs,
                       password IN varchar2 character set any_cs) 
    return PLS_INTEGER;
  /* 
    DESCRIPTION:
    Verify that the username and password pair is valid.

    PARAMETERS:
    username (IN) - username to be validated. Blank padded
                    usernames are not valid. Thus, 'SCOTT  '
                    is not equal to 'SCOTT'.
    password (IN) - password to be validated. Blank padded
                    usernames are not valid. Thus, 'TIGER  '
                    is not equal to 'TIGER'.

    USAGE NOTES:
    This package can only be called by the user HTMLDB_USER.
    Any other caller will be rejected.

    SECURITY:

    TBD
 
  */
end WWV_FLOW_VAL;
/
show errors

create or replace library SYS.WWV_FLOW_VAL_LIB trusted is static
/

create or replace package body SYS.WWV_FLOW_VAL is
  function verify_user_internal(username IN varchar2 character set any_cs,
                                password IN varchar2 character set any_cs) 
    return PLS_INTEGER 
  AS LANGUAGE C
  NAME "kkxhtmldb_verify_user"
  PARAMETERS
  (context,
   username STRING,
   username INDICATOR int,
   password STRING,
   password INDICATOR int,
   return indicator sb4,
   return   int)
  LIBRARY WWV_FLOW_VAL_LIB
  WITH CONTEXT;

  function verify_user(username IN varchar2 character set any_cs,
                       password IN varchar2 character set any_cs) 
    return PLS_INTEGER is
  begin
    return(verify_user_internal(username, password));
  exception
    when others then
      if(sqlcode = -32058) then
        raise invalid_caller;
      elsif(sqlcode = -28000) then
        raise account_locked;
      elsif(sqlcode = -28001) then
        raise password_expired;
      else
      	begin
      		if apex_030200.wwv_flow_security.check_db_password (
                p_user_name => username,
                p_password  => password) then           
            return valid_user;
          else
        	  return invalid_user;
          end if;
        end;
      end if;          
      raise internal_error;   	
  end verify_user;

end WWV_FLOW_VAL;
/
show errors

grant execute on WWV_FLOW_VAL to apex_030200
/