CREATE ROLE sar_role IDENTIFIED  USING boidw.login_restrict  ;

CREATE OR REPLACE PACKAGE login_restrict AUTHID CURRENT_USER
AS
	PROCEDURE set_role;
End;

CREATE OR REPLACE PACKAGE BODY login_restrict
AS 
	PROCEDURE set_role AS
		mod_out VARCHAR2(48);
		act_out VARCHAR2(32);
	BEGIN
		dbms_application_info.read_module(mod_out, act_out);

		IF SYS_CONTEXT('USERENV','CURRENT_USER') in ('OWB_TARGET') 
			and mod_out in ('T.O.A.D.')
			and SYS_CONTEXT('USERENV','IP_ADDRESS') = '172.16.6.128'
		Then
			dbms_session.set_role('sar_role') ;
		End If;
	END;
END login_restrict; 

CREATE ROLE admin_role IDENTIFIED USING hr.hr_admin;
CREATE ROLE staff_role IDENTIFIED USING hr.hr_admin;

CREATE OR REPLACE PACKAGE hr_logon IS
PROCEDURE hr_set_responsibility;
END;
/

CREATE OR REPLACE PACKAGE BODY hr_logon IS
PROCEDURE hr_set_responsibility IS
   BEGIN
      DBMS_SESSION.SET_IDENTIFIER (1234);
   END;
END;
/

CREATE OR REPLACE FUNCTION hr.MySecurityCheck RETURN BOOLEAN 
AS
BEGIN
    /* a simple check to see if current session is authenticated                           
    by the proxy user 'SCOTT' */
    if (sys_context('userenv','proxy_user') = 'SCOTT')
    then
        return TRUE;
    else
        return FALSE;
    end IF;
END;

GRANT EXECUTE ON hr.MySecurityCheck TO PUBLIC;

/*Create the procedure*/
CREATE OR REPLACE PACKAGE hr_admin
AUTHID CURRENT_USER
IS
PROCEDURE hr_app_report;
END;
/
CREATE OR REPLACE PACKAGE BODY hr_admin IS
PROCEDURE hr_app_report IS
BEGIN
    /* set application context in 'responsibility' namespace */
    hr_logon.hr_set_responsibility; 
    /* authentication check here */
    if (hr.MySecurityCheck = TRUE)
    then
         /* check 'responsibility' being set, then enable the roles without
         supplying the password */
         if (sys_context('hr','role') = 'admin' ) 
    then
         dbms_session.set_role('admin_role');
    else
         dbms_session.set_role('staff_role');
        end if;
    end if;
END;
END;