CREATE OR REPLACE TRIGGER SYSTEM.smz_trigger_on_logon
  after logon
  on database
declare
  connect_information   smz_log_connection%rowtype;
  registered            number(1);
  fio_osuser            w_user_info.fio%type;
  fio_orauser           w_user_info.fio%type;
  note_info             smz_log_forbidden.note%type;

  procedure SaveForbiddenLog
  as
    pragma autonomous_transaction;
  begin
    insert into SMZ_Log_Forbidden
      (USERNAME, TIME_LOGIN, TERMINAL, OSUSER, PROGRAM, PROG_SHORT, AUDSID, SPID, NOTE)
      values (connect_information.USERNAME, connect_information.TIME_LOGIN, connect_information.TERMINAL,
              connect_information.OSUSER, connect_information.PROGRAM, connect_information.PROG_SHORT,
              connect_information.AUDSID, connect_information.SPID, note_info);
    commit;
  end;
begin
  -- take connect information
  select user, sysdate, terminal, osuser, program, null, audsid, spid
    into connect_information
    from smz_your_session;

  -- take short name program
  connect_information.prog_short :=
    substr(connect_information.program,
           instr(connect_information.program, '\', -1) + 1);
  connect_information.prog_short :=
    substr(connect_information.prog_short,
           instr(connect_information.prog_short, '/', -1) + 1);
  connect_information.prog_short :=
    replace(lower(connect_information.prog_short), '(tns v1-v3)');
  connect_information.prog_short :=
    rtrim(replace(connect_information.prog_short, '.xxx.yyyyy.com'));

  if instr(connect_information.prog_short, '(') > 0
  then
    connect_information.prog_short :=
      substr(connect_information.prog_short,
             1,
             instr(connect_information.prog_short, ' (') - 1);
  end if;

  -- check developer if development database
  if upper(sys_context('userenv', 'db_name')) like 'D%'
     and connect_information.osuser is not null
     and connect_information.username not in
            ('ADMIN_ACCESS', 'AC', 'AC_USER', 'TDOM', 'TDOM_LS', 'SMZ_CHECK_PWD')
  then
    begin
      select null
        into registered
        from smz_developers
       where net_name = connect_information.osuser;
    exception
      when no_data_found
      then
        begin
          note_info := 'not developer login';
          SaveForbiddenLog;
          raise_application_error(
            -20101,
            'Соединение запрещено, так как вы не являетесь разработчиком программ для этой базы данных.'
            || chr(13) || chr(10) ||
            'The connect is forbidden if you not developer of programms for this database.');
        end;
    end;
  end if;

  if connect_information.username like 'O__SMZ%'
  then
    -- check connection from test environment if production database
    if upper(connect_information.terminal) like 'EURRUS-DTS%'
       and upper(sys_context('userenv', 'db_name')) like 'P%'
    then
      note_info := 'connect from test env';
      SaveForbiddenLog;
      raise_application_error(
        -20102,
        'Работа в промышленной базе данных из тестовой среды запрещена.'
        || chr(13) || chr(10) ||
        'Work in a production database from the test environment is forbidden.');
    end if;

    -- check registration of application for application user (production and testing databases)
    if upper(sys_context('userenv', 'db_name')) not like 'D%'
    then
      registered := 1;

      if connect_information.prog_short in
             ('changepwd.exe', 'sqlldr.exe', 'undefined', 'oracle.exe')
      then
        null;
      elsif connect_information.prog_short like 'oracle%@%'
      then
        if     upper(sys_context('userenv', 'db_name')) like 'P%'
           and connect_information.prog_short not like 'oracle%@%p'
           and connect_information.program not like 'oracle%(J%'
        then
          registered := 0;
        elsif upper(sys_context('userenv', 'db_name')) like 'Q%'
              and connect_information.prog_short like 'oracled%@%'
        then
          registered := 0;
        end if;
      else
        select count( * )
          into registered
          from (select null
                  from w_pril
                 where lower(app_name) = connect_information.prog_short
                union all
                select null
                  from w_path_file
                 where lower(file_name) = connect_information.prog_short);
      end if;

      if registered = 0
      then
        note_info := 'forbidden application';
        SaveForbiddenLog;
        raise_application_error(
          -20103,
          'Работа в данной программе запрещена для пользователей приложений.'
          || chr(13) || chr(10) ||
          'Work in the given program is forbidden for application users.');
      end if;
    end if;

    -- check compliance OS_username and Oracle_username if production/test database
    if upper(sys_context('userenv', 'db_name')) not like 'D%'
       and connect_information.prog_short not like 'oracle%'
    then
      begin
        select 1
          into registered
          from w_user
         where oracle_name = connect_information.username
               and net_name = connect_information.osuser;
      exception
        when no_data_found
        then
          begin
            begin
              fio_osuser := ' ';

              select fio
                into fio_osuser
                from w_user_info
               where net_name = connect_information.osuser and rownum < 2;
            exception
              when no_data_found
              then
                null;
            end;

            select fio
              into fio_orauser
              from w_user_info
             where oracle_name = connect_information.username;

            note_info := 'OS-ORA non-compliance login';
            SaveForbiddenLog;
            raise_application_error(
              -20104,
              chr(13) || chr(10) || chr(13) || chr(10) || fio_osuser || '.'
              || chr(13) || chr(10) ||
              'Вами совершена попытка доступа в приложение под именем пользователя '
              || fio_orauser || '.' || chr(13) || chr(10) ||
              'Данное действие является нарушением политики использования учетных записей.'
              || chr(13) || chr(10) ||
              'Пожалуйста, используйте свою учётную запись для входа в приложение.'
              || chr(13) || chr(10) ||
              'Если у вас возникли какие-либо вопросы, обращайтесь в службу поддержки пользователей.'
              || chr(13) || chr(10) || '.');
          end;
      end;
    end if;
  end if;

  -- save log
  insert into SMZ_Log_Connection
      values connect_information;

  -- set context
  set_zarpl_role;

end;
/