Restricting Oracle access

Oracle Database Tips by Donald BurlesonOctober 8, 2015

Question: I have an APEX system and I've noticed some of my end-users using SQL*Plus to access the data. Because I have coded special rules in the application, I need to restrict my end users and block them from accessing Oracle, except via my application. How can I use a logion trigger to restrict access to end-users from tools like SQL*Plus, ODBC, Crystal Reports and so on?

Answer: It's always a best practice to do whatever you can to ensure that your end-users "play by the rules" and only access their Oracle data. This is especially true when you are using standard "grant" security, but it's not necessary when using the "grant execute" security, since end-users can only get the data when they execute your stored procedure or function.

See my general note here on blocking access to Oracle tools.

To block end-users from using external tools like ODBC or SQL*Plus, you can deploy a login trigger to force them to only use your application tool.

Oracle author Kamran Agayev Agamehdi has this snippet on preventing external connections to Oracle

CONNECT / AS SYSDBA;

CREATE OR REPLACE TRIGGER block_tools_from_prod

AFTER LOGON ON DATABASE

DECLARE

v_prog sys.v_$session.program%TYPE;

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 '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad

UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator

UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer

UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects

UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in

THEN

RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');

END IF;

END;

SHOW ERRORS

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

��