CREATE TABLE customer (
  id        NUMBER(10)    NOT NULL,
  username  VARCHAR2(30)  NOT NULL,
  password  VARCHAR2(40)  NOT NULL
);

ALTER TABLE customer add (
  CONSTRAINT cutomer_pk PRIMARY KEY (id)
);

ALTER TABLE customer add (
  CONSTRAINT customer_uk UNIQUE (username)
);

CREATE SEQUENCE customer_seq;


 CREATE OR REPLACE FUNCTION get_hash (p_username  IN  VARCHAR2,
                     p_password  IN  VARCHAR2)
    RETURN VARCHAR2 AS
    l_salt VARCHAR2(30) := 'customerSalt';
  BEGIN


    RETURN DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(UPPER(p_username) || l_salt || UPPER(p_password)),DBMS_CRYPTO.HASH_SH1);
  END;



  CREATE OR REPLACE PROCEDURE add_customer (p_username  IN  VARCHAR2,
                      p_password  IN  VARCHAR2) AS
  BEGIN
    INSERT INTO customer (
      id,
      username,
      password
    )
    VALUES (
      customer_seq.NEXTVAL,
      UPPER(p_username),
      get_hash(p_username, p_password)
    );

    COMMIT;
  END;

BEGIN
add_customer('admin','1234');
END;
-------------------------
 CREATE OR REPLACE PROCEDURE get_login (p_username  IN  VARCHAR2,
                        p_password  IN  VARCHAR2) AS
    login  VARCHAR2(1);

     BEGIN
    SELECT'1'
    INTO   login
    FROM   customer
    WHERE  username = UPPER(p_username)
    AND    password = get_hash(p_username, p_password);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
  END;
 -------------------------------
CREATE OR REPLACE FUNCTION get_login_p (p_username  IN  VARCHAR2,
                       p_password  IN  VARCHAR2)
    RETURN BOOLEAN AS
  BEGIN
    get_login(p_username, p_password);
    RETURN TRUE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN FALSE;
  END;
-----------------------------------------------
--------------------
BEGIN
  IF get_login_p('admin','1234') THEN
    DBMS_OUTPUT.PUT_LINE('1');
  ELSE
    DBMS_OUTPUT.PUT_LINE('0');
  END IF;
END;

BEGIN
  IF get_login_p('admin','shooooo') THEN
    DBMS_OUTPUT.PUT_LINE('1');
  ELSE
    DBMS_OUTPUT.PUT_LINE('0');
  END IF;
END;
-------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE change_password (p_username      IN  VARCHAR2,
                             p_old_password  IN  VARCHAR2,
                             p_new_password  IN  VARCHAR2) AS
    v_rowid  ROWID;
  BEGIN
    SELECT ROWID
    INTO   v_rowid
    FROM   customer
    WHERE  username = UPPER(p_username)
    AND    password = get_hash(p_username, p_old_password)
    FOR UPDATE;

    UPDATE customer
    SET    password = get_hash(p_username, p_new_password)
    WHERE  ROWID    = v_rowid;

    COMMIT;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
  END;


EXEC change_password('admin','1234','123');



EXEC change_password('admin','kennedy','1234');

------------------------------------------------------------------
CREATE OR REPLACE PACKAGE customer_security AS

  FUNCTION get_hash (p_username  IN  VARCHAR2,
                     p_password  IN  VARCHAR2)
    RETURN VARCHAR2;

  PROCEDURE add_customer (p_username  IN  VARCHAR2,
                      p_password  IN  VARCHAR2);

  PROCEDURE change_password (p_username      IN  VARCHAR2,
                             p_old_password  IN  VARCHAR2,
                             p_new_password  IN  VARCHAR2);

  PROCEDURE get_login (p_username  IN  VARCHAR2,
                        p_password  IN  VARCHAR2);

  FUNCTION get_login_p (p_username  IN  VARCHAR2,
                       p_password  IN  VARCHAR2)
    RETURN BOOLEAN;

END;
----------------------------



CREATE OR REPLACE PACKAGE BODY customer_sequrity AS

  FUNCTION get_hash (p_username  IN  VARCHAR2,
                     p_password  IN  VARCHAR2)
    RETURN VARCHAR2 AS
    l_salt VARCHAR2(30) := 'customerSalt';
  BEGIN


    RETURN DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(UPPER(p_username) || l_salt || UPPER(p_password)),DBMS_CRYPTO.HASH_SH1);
  END;

PROCEDURE add_customer (p_username  IN  VARCHAR2,
                      p_password  IN  VARCHAR2) AS
  BEGIN
    INSERT INTO customer (
      id,
      username,
      password
    )
    VALUES (
      customer_seq.NEXTVAL,
      UPPER(p_username),
      get_hash(p_username, p_password)
    );

    COMMIT;
  END;


  PROCEDURE get_login (p_username  IN  VARCHAR2,
                        p_password  IN  VARCHAR2) AS
    login  VARCHAR2(1);

     BEGIN
    SELECT'1'
    INTO   login
    FROM   customer
    WHERE  username = UPPER(p_username)
    AND    password = get_hash(p_username, p_password);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
  END;

  FUNCTION get_login_p (p_username  IN  VARCHAR2,
                       p_password  IN  VARCHAR2)
    RETURN BOOLEAN AS
  BEGIN
    get_login(p_username, p_password);
    RETURN TRUE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN FALSE;
  END;


  PROCEDURE change_password (p_username      IN  VARCHAR2,
                             p_old_password  IN  VARCHAR2,
                             p_new_password  IN  VARCHAR2) AS
    v_rowid  ROWID;
  BEGIN
    SELECT ROWID
    INTO   v_rowid
    FROM   customer
    WHERE  username = UPPER(p_username)
    AND    password = get_hash(p_username, p_old_password)
    FOR UPDATE;

    UPDATE customer
    SET    password = get_hash(p_username, p_new_password)
    WHERE  ROWID    = v_rowid;

    COMMIT;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
  END;
END;

 ----------------------------------