DROP VIEW USER_ROLES_VIEW;

DROP TABLE SEC_USER_ROLE_MAPS CASCADE CONSTRAINTS;
DROP TABLE SEC_USERS CASCADE CONSTRAINTS;
DROP TABLE SEC_ROLES CASCADE CONSTRAINTS;
DROP TABLE SEC_REALMS CASCADE CONSTRAINTS;

DROP SEQUENCE "SEC_USERS_SEQ"; 
DROP SEQUENCE "SEC_ROLES_SEQ";
DROP SEQUENCE "SEC_REALMS_SEQ";

DROP PACKAGE "DBPROCLM";

CREATE SEQUENCE "SEC_USERS_SEQ" INCREMENT BY 1 START WITH  200 MAXVALUE 1.0E28 MINVALUE 200 NOCYCLE CACHE 20 NOORDER;
CREATE SEQUENCE "SEC_ROLES_SEQ" INCREMENT BY 1 START WITH  300 MAXVALUE 1.0E28 MINVALUE 300 NOCYCLE CACHE 20 NOORDER;
CREATE SEQUENCE "SEC_REALMS_SEQ" INCREMENT BY 1 START WITH 400 MAXVALUE 1.0E28 MINVALUE 400 NOCYCLE CACHE 20 NOORDER;

CREATE TABLE "SEC_USERS" (
"USERID" NUMBER(10) 	 NOT NULL, 
"USERNAME" VARCHAR2(100) NOT NULL, 
"PASSWORD" VARCHAR2(100) NOT NULL, 
"VALID_ACCOUNT" NUMBER(1) DEFAULT 1 NOT NULL, 
 CONSTRAINT "SEC_USER_PK" PRIMARY KEY("USERID"),CONSTRAINT "SEC_USER_UNNIQUE_USERNAME" UNIQUE("USERNAME"), 
 CONSTRAINT "VALID_ACCOUNT_IN_1_0" CHECK(VALID_ACCOUNT IN (0,1)));
  
 CREATE TABLE "SEC_ROLES" (
 "ROLEID" NUMBER(10) NOT NULL, 
 "ROLENAME" VARCHAR2(100) NOT NULL,CONSTRAINT "ROLES_PK" PRIMARY KEY("ROLEID"), 
  CONSTRAINT "ROLENAME_MUST_BE_UNIQUE" UNIQUE("ROLENAME"));
 
 CREATE TABLE "SEC_REALMS" (
 "REALMID" NUMBER(10) NOT NULL,
 "REALM_NAME" VARCHAR2(100) NOT NULL, 
 PRIMARY KEY("REALMID"));  
 
 CREATE TABLE "SEC_USER_ROLE_MAPS" (
 "USERS_USERID"   NUMBER(10) NOT NULL, 
 "ROLES_ROLEID"   NUMBER(10) NOT NULL, 
 "REALMS_REALMID" NUMBER(10) NOT NULL, 
 "VALID_GRANT" NUMBER(1) DEFAULT 1 NOT NULL, 
  CONSTRAINT "SEC_USERS_USERID_FK" FOREIGN KEY("USERS_USERID")
  REFERENCES "SEC_USERS"("USERID"), 
  CONSTRAINT "SEC_REALMS_REALMID_FK" FOREIGN KEY("REALMS_REALMID")
  REFERENCES "SEC_REALMS"("REALMID"), 
  CONSTRAINT "SEC_ROLES_ROLEID_FK" FOREIGN KEY("ROLES_ROLEID")
  REFERENCES "SEC_ROLES"("ROLEID"), 
  CONSTRAINT "VALID_GRANT_IN_1_0" CHECK(VALID_GRANT IN(1,0)));
    
  CREATE OR REPLACE VIEW "USER_ROLES_VIEW" AS 
  SELECT SEC_ROLES.ROLENAME, SEC_USER_ROLE_MAPS.ROLES_ROLEID AS "ROLEID",  
  SEC_USER_ROLE_MAPS.USERS_USERID AS "USERID", SEC_REALMS.REALM_NAME AS "REALM" 
  FROM SEC_ROLES, SEC_USER_ROLE_MAPS,SEC_USERS,SEC_REALMS  
  WHERE SEC_USER_ROLE_MAPS.ROLES_ROLEID = SEC_ROLES.ROLEID 
  AND SEC_USER_ROLE_MAPS.VALID_GRANT = 1 
  AND SEC_USERS.USERID = SEC_USER_ROLE_MAPS.USERS_USERID 
  AND SEC_USERS.VALID_ACCOUNT = 1 
  AND SEC_USER_ROLE_MAPS.REALMS_REALMID = SEC_REALMS.REALMID WITH READ ONLY;
  
  INSERT INTO SEC_USERS VALUES (100,'Darth Vader','Q26Z1cz//5LWbZC8ZI6BopNeXW0=',1);
  INSERT INTO SEC_USERS VALUES (101,'Luke Skywalker','IXdWhyugZJn40y5lRKI7KPn2KnU=',1);
  INSERT INTO SEC_USERS VALUES (102,'Han Solo','wkwFK+Xu31ali2OKx+1YUYl3/Ko=',1);
  
  INSERT INTO SEC_ROLES VALUES (200,'Administrator');
  INSERT INTO SEC_ROLES VALUES (201,'User');
  INSERT INTO SEC_ROLES VALUES (203,'Manager');
  INSERT INTO SEC_ROLES VALUES (204,'Content Owner');
  
  INSERT INTO SEC_REALMS VALUES (300,'Online Trainings');
  INSERT INTO SEC_REALMS VALUES (301,'Soccer 2006');
  
  INSERT INTO SEC_USER_ROLE_MAPS VALUES (100,200,300,1);
  INSERT INTO SEC_USER_ROLE_MAPS VALUES (100,201,300,1);
  INSERT INTO SEC_USER_ROLE_MAPS VALUES (100,204,300,1);
  INSERT INTO SEC_USER_ROLE_MAPS VALUES (101,201,300,1);
  INSERT INTO SEC_USER_ROLE_MAPS VALUES (102,201,300,1);
  INSERT INTO SEC_USER_ROLE_MAPS VALUES (102,203,300,1);
  INSERT INTO SEC_USER_ROLE_MAPS VALUES (102,204,300,1);
  
  INSERT INTO SEC_USER_ROLE_MAPS VALUES (100,200,301,1);
  INSERT INTO SEC_USER_ROLE_MAPS VALUES (101,201,301,1);
  INSERT INTO SEC_USER_ROLE_MAPS VALUES (102,200,301,1);

commit;

 CREATE OR REPLACE  PACKAGE "DBPROCLM" IS
    TYPE principal_ref IS REF CURSOR; 
    function get_user_authentication(p_username in varchar2, p_password in varchar2, p_realm varchar2) return principal_ref;
  END;
 
 /
 
CREATE OR REPLACE  PACKAGE BODY "DBPROCLM" IS
  
  FUNCTION get_user_authentication (p_username in varchar2, p_password in varchar2, p_realm varchar2) 
  RETURN principal_ref
  AS
    var_username varchar2(100);
    var_userid   number(10);
    var_password varchar2(100);
    role_cursor principal_ref;
    FAILED_AUTHENTICATION exception;
  BEGIN
    
    select userid, username, password into var_userid, var_username, var_password from sec_users where username = p_username;

    if (var_password = p_password) then
      begin
         if (p_realm is null) then 
          open role_cursor for
          select rolename from user_roles_view where userid = var_userid;
        else 
          open role_cursor for
          select rolename from user_roles_view where userid = var_userid and realm=p_realm;
        end if; -- p_realm check
      end;
      -- if password doesn't match, raise Excpetion for LM to 
      -- abort the authentication process
      else raise FAILED_AUTHENTICATION;
    end if;
  RETURN role_cursor;
  END get_user_authentication;
END;
/