Restricting TOAD,PLSQL,EXCEL,SQL access from ORACLE App Schema who has DBA Priv

Scenario:

We have application schema with dba priv to be audited and restricted from toad like programs.

But if you enable audit to application schema it will effect performance of the application in day as well as in EOD process.

So you want to do audit other activity of normal user through and Restrict direct access to the schema from toad like programs.

1.Cloning dba role to new dba role,Since log on trigger will not work for dba users.
2.Grant new dba role to application schema.
3.Revoke standard dba role from application schema.
3.Creating log on trigger and role to block toad like program access through direct login.
4.Create Normal user and assign log on trigger.
5.Enable audit to normal schema.

++ Follow below link to duplicate dba role.
<< http://oracletechdba.blogspot.com/2014/06/create-duplicate-role-as-like-dba-role.html >>
grant DBA_FCUBS_ROLE to << applicaton schema >>;
revoke dba from << applicaton schema >>;

++ Now find the list of program connecting to the database
select distinct PROGRAM from dba_hist_active_sess_history;

++ List what are the programs you want to restrict. Eg:PLSQL,EXCEL,TOAD and SQLPLUS so on..

++ Now create role and trigger to block programs which we desired before.

CREATE ROLE FCUBS_TOAD_BLOCK_ROLE NOT IDENTIFIED;
BEGIN
  SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
   'SQLPLUS_BLOCK_ROLE', 'DEFAULT_CONSUMER_GROUP', false);
  SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

GRANT FCUBS_TOAD_BLOCK_ROLE TO DBA; -- All the DBA users will not be restricted.

++ Trigger to blocking toad like program for non FCUBS_TOAD_BLOCK_ROLE role users

CREATE OR REPLACE TRIGGER FCUBS_BLOCK_TOAD_TRIGGER
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
  l_count pls_integer;
  
     my_forced_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(MY_FORCED_EXCEPTION, -20101);
BEGIN
  SELECT program
    INTO v_prog
    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
     IF  UPPER(v_prog) LIKE '%SQL%' OR        -- SQL like programs!
      UPPER(v_prog) LIKE '%TOAD%' OR       -- Toad
      UPPER(v_prog) LIKE '%BUSOBJ%' OR     -- Business Objects
      UPPER(v_prog) LIKE '%EXCEL%'         -- MS-Excel plug-in
    THEN
    SELECT COUNT (*)
      INTO l_count
      FROM dba_role_privs
     WHERE     granted_role = 'FCUBS_TOAD_BLOCK_ROLE'
           AND grantee = SYS_CONTEXT ('userenv', 'session_user');  
          
     IF l_count=0 THEN
       RAISE my_forced_exception;
     END IF;
  END IF;
EXCEPTION
  WHEN my_forced_exception THEN
    RAISE_APPLICATION_ERROR(-20101, 'Action not allowed. Please contact your ADMIN to help you!');
  WHEN OTHERS THEN
    null;
END;
/

++ Now create read only role or read write role for application schema as like to grant to nornal user who wants to access the application from the toad like program.

 << http://oracletechdba.blogspot.com/2014/06/creating-read-only-user-access-for-non.html >> ==> read only role
 << http://oracletechdba.blogspot.com/2014/06/creating-read-write-user-access-for-non.html >> ==> read write access

++ Grant create user for each normal users who what directly access the database from toad.
create user test identified by test;
grant connect,resource to test;
grant FCUBS_TOAD_BLOCK_ROLE TO test; ==> assign block role
grant RWFCUBS_TABLE_ROLE to test; ==> application access to that user

++ Now normal user can login through toad and set current_schma to access application tables but you can audit those user.

++enable audit to user.
AUDIT ALL BY test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY test BY ACCESS;


Ref:http://damir-vadas.blogspot.com/2010/01/how-to-prevent-sqlplus-acces-to.html