RESTRICT NON DBA USER TO ACCESS DEVELOPMENT TOOLS LIKE SQLPLUS , TOAD,...

Create or Replace TRIGGER TG_OTHER_TOOL
  v_prog sys.v_$session.program%TYPE;
  v_module sys.v_$session.program%TYPE ;
lnUsercode number ;
lcUserdetails Varchar2(100)  ;
lnTotalrecords number ;
Begin
  Select program,module into v_prog,v_module From sys.v_$session
      Where  audsid = USERENV('SESSIONID')
    and  audsid != 0  -- Don't Check SYS Connections
    and  rownum = 1;  -- Parallel processes will have the same AUDSID's
  Select Count(*) into lnTotalrecords From Dba_Users Dba_Users
  Where Dba_Users.Username=User and Dba_Users.Default_tablespace='USR' ;
  If lnTotalrecords!=0 then
      IF UPPER(V_MODULE) NOT IN ('LD.EXE','LDSCHEDULER.EXE','LDSCHEDULER.VSHOST.EXE','DBMS_SCHEDULER','DLLHOST.EXE')
     Then
        Raise_application_error(-20000,'On Database Development tools are Restricted');
     end if;
  end if ;
  lnUsercode:=-1;
  If ((upper(v_prog)='LD.EXE' and upper(v_module)='LD.EXE') or
      (upper(v_prog)='LDSCHEDULER.EXE' and upper(v_module)='LDSCHEDULER.EXE') or
      (upper(v_prog)='LDSCHEDULER.VSHOST.EXE' and upper(v_module)='LDSCHEDULER.VSHOST.EXE') or
      (upper(v_prog)='ORACLE.EXE (J000)' and upper(v_module)='DBMS_SCHEDULER') or
      (upper(v_prog)='DBMS_SCHEDULER' and upper(v_module)='DBMS_SCHEDULER') or
      (upper(v_prog)='DLLHOST.EXE' and upper(v_module)='DLLHOST.EXE')) then
     lcUserdetails:=sys_context('userenv','ip_address')  ;
     Insert into tbltempoperationstatistics (nOpercode,cOperationname) Values (lnUsercode,lcUserdetails) ;
  else
     lcUserdetails:='USER LOGGED ON' ;
     Insert into tbltempoperationstatistics (nOpercode,cOperationname) Values (lnUsercode,lcUserdetails) ;
  end if ;  
  Commit ;
  Exception
      When NO_DATA_FOUND then
           NULL ;
End;
/

 

if Non DBA user is trying to login into sqlplusw / PlSQL developer or some other tool then it shows error because of splogininformation,tglogin.

if i rename sqlplusw.exe to abc.exe then non DBA user can login into sqlplusw.exe

i think you check v$session.program in SP for that.

you have to check v$session.module.

select program,module from v$session;

abc.exe   SQL*Plus

module shows currently executing module

program shows operating system program name

 

The MODULE column of V$SESSION only gets populated with the DBMS_APPICATION_INFO package. 

SQL*Plus automatically uses this package and registers the application with the database. 

This is why you can see it in the MODULE column of V$SESSION. Unfortunately, this won't happen until near the end of the SQL*Plus initialization. 

You can not set_module before logging. For obvious reason - you are not logged

http://docs.oracle.com/cd/A58617_01/server.804/a53717/ape.htm

PRODUCT_USER_PROFILE is owned by SYSTEM not SYS.

PRODUCT_USER_PROFILE is only work for SQLPLUSW not for other tool

One alternative is to make use of PRODUCT_USER_PROFILE table to restrict the nondba users from firing any command after logging in.

e.g to disallow nondba users from selecting any data from SQL * plus prompt, log in as system and 

insert into product_user_profile (product,userid,attribute,char_value)values ('SQL*Plus','%','SELECT','DISABLED'); 

insert into product_user_profile (product,userid,attribute,char_value)values ('SQL*Plus','%','UPDATE','DISABLED'); 

insert into product_user_profile (product,userid,attribute,char_value)values ('SQL*Plus','%','DELETE','DISABLED'); 

insert into product_user_profile (product,userid,attribute,char_value)values ('SQL*Plus','%','INSERT','DISABLED'); 

commit;