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?
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
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.
Agamehdi has this snippet on preventing external connections to
CONNECT / AS SYSDBA;
CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
SELECT program INTO v_prog
audsid = USERENV('SESSIONID')
audsid != 0
-- Don't Check SYS Connections
ROWNUM = 1;
-- Parallel processes will have the same
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
UPPER(v_prog) LIKE '%BUSOBJ%' OR
-- Business Objects
UPPER(v_prog) LIKE '%EXCEL%'
-- MS-Excel plug-in
tools are not allowed here.');
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.