Restrict development tools on Production

Environment: Oracle 11gR2 Database on Windows 2008R2
Purpose: Implementing business rules to restrict development tools on production. Developers may work on production through these development tools  on-demand.


1- create a table for the developers to work on production through development tools on

CREATE TABLE HOMELOG.HOME_DEV_TEAM
(
  DEV_ID              NUMBER,
  DEV_NAME            VARCHAR2(25 BYTE),
  DEV_TERMINAL        VARCHAR2(20 BYTE),
  CONNECT_PROD        CHAR(1 BYTE),
  REMARKS             VARCHAR2(100 BYTE),
  DEPLOYMENT_ALLOWED  CHAR(1 BYTE)              DEFAULT 'N',
  EMAIL               VARCHAR2(50 BYTE),
  "SCHEMA"            VARCHAR2(30 BYTE),
  DEV_TOOL_ALLOWED    CHAR(1 BYTE)
)

2- Insert a row for a developer in the table

Insert into HOME_DEV_TEAM
   (DEV_ID, DEV_NAME, DEV_TERMINAL, CONNECT_PROD, REMARKS,
    DEPLOYMENT_ALLOWED, EMAIL, "SCHEMA", DEV_TOOL_ALLOWED)
 Values
   (91, 'Inam Bukhari', 'INAM-PC', 'Y', NULL,
    'Y', NULL, NULL, 'N');

3- write the trigger to implement your business rule(s)

CREATE OR REPLACE TRIGGER SYS.TRG_ban_DEVTools
AFTER LOGON
ON DATABASE
DECLARE
   v_session_user  varchar2(64);
   v_module        varchar2(64);
   rec_home_dev_team homelog.home_dev_team%rowtype;
   rec_v$session v$session%rowtype;
   v_msg varchar2(1000);
   v_sid number;
BEGIN
    v_sid := sys_context('USERENV', 'SID');
    v_module := UPPER(SYS_CONTEXT('USERENV', 'MODULE'));
    v_session_user := UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER'));
  SELECT * into rec_v$session from v$session where sid=v_sid;
  SELECT * into rec_home_dev_team  from HOMELOG.HOME_DEV_TEAM WHERE schema =v_session_user;

   IF    (v_module LIKE '%SQL%'    or v_module LIKE '%TOAD%' OR v_module LIKE 'FRM%' OR v_module LIKE 'RWB%')    AND rec_home_dev_team.dev_tool_allowed = 'N'    THEN
   v_msg := chr(10)||'Dear '||rec_home_dev_team.dev_name||chr(10);
   v_msg := v_msg ||'Connection through development tool ('||rec_v$session.program||') is not allowed on production'||chr(10);
   v_msg := v_msg ||'Contact securityteam for approval ';
v_msg := v_msg ||'Attempted: '||rec_v$session.username||':'||rec_v$session.osuser||':'||
rec_v$session.terminal||':'||rec_v$session.program;
    RAISE_APPLICATION_ERROR(-20003,v_msg);
    
   END IF;
EXCEPTION      
WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20002,'Seems you are not HOME developer, Contact HOME DBA');
END;
4- Test it (update the DEV_TOOL_ALLOWED column in above table to 'Y' & 'N')
C:\Users\inam.HOME>sqlplus inam/inam@homedevnew

SQL*Plus: Release 11.2.0.3.0 Production on Sat May 4 13:08:24 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20003:
Dear Inam Bukhari
Connection through development tool (sqlplus.exe) is not allowed on production
Contact securityteam for approval
ORA-06512: at line 19


Enter user-name: