In our environment we have a production server which handles all reads/writes and we have an active DG which we use to offload backups as well as read only reporting on. We have already established a mechanism to handle password resets when an end user needs to reset thier password which will reset it on the primary and carry over to the secondary/reporting instance.

My question here is how if possible can we completely restrict end business users from connecting to the Primary production DB. Currently we use a method of jobs which query v$session and kill connections coming from TOAD/SQLDeveloper, which runs every 1 min. This effectively kills any long running connections from business users trying to query the production copy, however allows them to run on the secondary/Reporting instance.

Is there a method or way of disabling the login/user on the production copy and still allow it to connect to the secondary/reporting instance. In MS SQL we accomplish this by creating a server level login and db user on the primary and then after it is log shipped to the secondary we remove the server level login so it cannot authenticate to the Database, however the DB user is still intact and on the secondary.

CREATE OR REPLACE TRIGGER SECURE_ACCESS
after logon on database
declare
  l_program v$session.program%type;
  l_osuser  v$session.osuser%type;
  l_machine v$session.machine%type;
  l_user    varchar2(30) := user;

  l_instance v$instance.instance_name%type := sys_context('USERENV','INSTANCE_NAME');

begin
  select osuser, machine, program
  into   l_osuser, l_machine, l_program
  from   v$session
  where  sid = sys_context('USERENV','SID');

  if ( l_osuser like 'blah%' or lower(l_program) like '%blah%' or lower(machine) like '%primary%' or lower(l_instance) like '%blah%' ) 
  then

    select count(*)
    into   l_is_dba
    from   dba_role_privs
    where  grantee = l_user
    and    granted_role = 'DBA';

    if l_is_dba = 0 then
      raise_application_error(-20999,'You are not meant to be logging in here');
    end if;
  end if;

end;
/