Restrict Users to access other application exe

create or replace procedure sp_ltmlogin AS
  MODULE SYS.V_$SESSION.MODULE%TYPE ;
L_USER NUMBER ;
BEGIN
 SELECT MODULE INTO MODULE FROM SYS.V_$SESSION WHERE  AUDSID = USERENV('SESSIONID') AND  AUDSID != 0 AND  ROWNUM = 1;
 SELECT COUNT(*) INTO L_USER FROM DBA_USERS DBA_USERS WHERE DBA_USERS.USERNAME=USER AND DBA_USERS.PROFILE='LTM';
  IF L_USER=0 THEN
     IF UPPER(MODULE) ='LDLIVETRADEMONITOR.EXE'
     THEN
        RAISE_APPLICATION_ERROR(-20000,'you are not authorized to access LTM software');
     END IF;
  END IF ;
  IF L_USER > 0 THEN
     IF UPPER(MODULE) ='LD.EXE'
     THEN
        RAISE_APPLICATION_ERROR(-20000,'you are not authorized to access LD software');
     END IF;
  END IF ;

  EXCEPTION
      WHEN NO_DATA_FOUND THEN
           NULL ;
END;
/

create or replace

trigger tg_ltmlogin

      after logon on Database

Begin

    sp_ltmlogin() ;

End;

/

To restrict user to login only LTM user, you have to create a LTM profile and move that users to LTM profile

Followings are the steps to implement the same

1     Connect to sysdba user

    Create profile for LTM users and move them to LTM profile

CREATE PROFILE LTM

  LIMIT PASSWORD_REUSE_MAX DEFAULT

        PASSWORD_REUSE_TIME DEFAULT;

ALTER USER LTM PROFILE LTM;

      

Drop User Trigger

create or replace trigger tg_dropuser before drop on database
when (ora_dict_obj_type ='USER') 
 declare
 l_name varchar2(30);
 begin
 l_name := ORA_DICT_OBJ_NAME;
execute immediate 'delete from ldbo.tbloperatormenurights where coperator = ''' ||ora_dict_obj_name || '''';
 end;
/

create or replace trigger trUserDrop after drop on database 

when (ora_dict_obj_type ='USER') 

begin 

dbms_output.put_line('user dropped '|| ora_dict_obj_name); 

end; 

/

if you have the privilege granted like Grant Create User, Drop, etc., this is good example:

Use a database BEFORE DROP trigger. Make sure to create this as some other user (then the 

user doing the drop, else they can drop the trigger!)

 grant create session, create user, drop user to a 

identified by a;

Grant succeeded.

 grant create session to b identified by b;

Grant succeeded.

 create table app_users ( username varchar2(30) );

Table created.

 insert into app_users values ( 'B' );

1 row created.

 create or replace trigger drop_user_trigger

 before drop on database

 when ( user = 'A' )

 declare

 l_cnt number;

 l_name varchar2(30);

 begin

 if ( ora_dict_obj_type = 'USER' )

 then

 l_name := ORA_DICT_OBJ_NAME;

 select count(*) into l_cnt

 from dual

 where exists ( select null

 from app_users

 where username = l_name );

 if ( l_cnt <> 1 )

then

 raise_application_error( -20001, 'You cannot drop that user' );

end if;

 end if;

 end;

/

Trigger created.

 @connect a/a

 drop user scott;

drop user scott

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: You cannot drop that user

ORA-06512: at line 15

drop user b;

User dropped.

Clear Table records after startup database

If user does not logout properly / kill task / services restarted then record of that user will present in tblrklogindetails table.

We can create trigger that will delete all records if database service will be restarted

create or replace trigger tg_clear_logindetails

    after startup

     on database

  declare

     j   integer;

    pragma autonomous_transaction;

  begin

    dbms_job.submit (j,  'begin execute immediate ''truncate table ldbo.tblrklogindetails''; end;');

   commit;

 end tg_clear_logindetails;

 /

If there is no records in the table then following Error message will popup.

Please change this message to some user friendly message

“Session is Disconnected from a server. Please Login again”

Or something else

In web application , client makes connection with server using IIS manager.

1)      User login into rk application and fire report

2)      Oracle service is restart in between. Oracle Not connected Error has started because connection is break between client and server.

3)      Error is coming until we have not logout and login again into application.

4)      Iisreset help to reconnect client server without logout application.

Resolution

When database is shutdown, application should automatically shutdown when showing oracle not connected .

Tblrklogindetails records should be clear.