--1) init---------
sqlplus "/ as sysdba"
EXEC SYS.XS_ADMIN_UTIL.GRANT_SYSTEM_PRIVILEGE('ADMINISTER_SESSION', 'SCOTT', XS_ADMIN_UTIL.PTYPE_DB);

--2)create app user
connect scott/

DECLARE 
st_date TIMESTAMP WITH TIME ZONE;
BEGIN
  st_date := systimestamp;
  SYS.XS_PRINCIPAL.CREATE_USER(name=>'appuser1',schema=>'scott',start_date=>st_date);
END;
/
--3) set password app user
BEGIN
  SYS.XS_PRINCIPAL.SET_PASSWORD('APPUSER1','2Hrd2Guess',XS_PRINCIPAL.XS_SHA512);
END;
/

--4) check auth
--BEGIN
--  SYS.XS_PRINCIPAL.SET_VERIFIER('APPUSER1','6DFF060084ECE67F',XS_PRINCIPAL.XS_SHA512);
--END;
--/

--5) create roles
BEGIN
  SYS.XS_PRINCIPAL.CREATE_ROLE('hrmgr');
END;
/
DECLARE 
  st_date TIMESTAMP WITH TIME ZONE;
BEGIN
  st_date := SYSTIMESTAMP;
  SYS.XS_PRINCIPAL.CREATE_ROLE(name=>'hrrep',
                           enabled=>true,
                           start_date=>st_date);
END;
/
--6) grant role to app user
DECLARE 
  rg_list XS$ROLE_GRANT_LIST; 
BEGIN
  rg_list := XS$ROLE_GRANT_LIST(XS$ROLE_GRANT_TYPE('HRREP'),
                                XS$ROLE_GRANT_TYPE('HRMGR'));
 
  SYS.XS_PRINCIPAL.GRANT_ROLES('APPUSER1', rg_list);
END;
/

--7) create application session 
declare
 sessionid raw(16);
 l_usr     sys.v_$session.username%type;
 l_sid     sys.v_$session.sid%type;

begin
  SYS.DBMS_XS_SESSIONS.CREATE_SESSION('APPUSER1', sessionid);
--  select username, sid into l_usr,l_sid from sys.v_$session v where v.sid = (select sid from sys.v_$mystat where rownum=1);
--  dbms_output.put_line(l_usr||';'||l_sid);   

  -- присоединить сессию приложения к текущей БД сессиии
  SYS.DBMS_XS_SESSIONS.ATTACH_SESSION(sessionid);
--  select username, sid into l_usr,l_sid from sys.v_$session v where v.sid = (select sid from sys.v_$mystat where rownum=1);
--  dbms_output.put_line(l_usr||';'||l_sid);   

  SYS.DBMS_XS_SESSIONS.DETACH_SESSION;
--  select username, sid into l_usr,l_sid from sys.v_$session v where v.sid = (select sid from sys.v_$mystat where rownum=1);
--  dbms_output.put_line(l_usr||';'||l_sid);   

  SYS.DBMS_XS_SESSIONS.DESTROY_SESSION(sessionid);
end;
/

SCOTT;134
XS$NULL;134
SCOTT;134