ORACLE DBA TIPS & TRICKS

ORACLE DBA TIPS & TRICKS:

1.Block User with IP address from production server.

its a based on trigger.

SQL> CREATE OR REPLACE TRIGGER BLOCK_USER
AFTER LOGON
ON DATABASE
declare
adr1 varchar2(100);
usr1 varchar2(30);
begin

select trim(sys_context('userenv','ip_address')) into adr1 from dual;

if adr1=' 10.0.0.1' /*** any IP Adress ***/
then
RAISE_APPLICATION_ERROR(-20000, 'You are not Authoried to Access Database');

end if;

end;
/


Then try to connect with restricted IP address.


If need to disable this trigger the run >>>>>

SQL>ALTER TRIGGER BLOCK_USER DISABLE;



2. Block third party /Development tools from your production database .

Like "TOAD,SQL EDITOR,pl/sql developers,Excel Etc.


Step> CREATE OR REPLACE TRIGGER BLOCK_TOOLS_FROM_PROD
AFTER LOGON ON DATABASE
DECLARE
v_prog v$session.program%TYPE;

BEGIN

SELECT program INTO v_prog
FROM 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

--EXCEPTION WHEN NO_DATA_FOUND THEN

IF (v_prog) LIKE 'toad.exe'-- OR --UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad --or put the perameters " name /PLSQLDEV.exe/TOAD.exe/"which you are need to restricted

THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;
/


If you Need disable this trigger the>>

SQL>ALTER TRIGGER BLOCK_TOOLS_FROM_PROD DISABLE;