To block end-user from access the database outside their application in SQL*Plus, you can use the PRODUCT_USER_PROFILE feature. It can be configured to disallow update operations. This is accomplished with the PRODUCT_USER_PROFILE table. Issuing the following row into this table will disable any ad-hoc updates with SQL*Plus:
INSERT INTO PRODUCT_USER_PROFILE (product, user_id, attribute) VALUES ( ‘SQL*Plus’, ‘%’ ‘UPDATE’);
You may want to block SQL*Plus or SQL Developer, or block 3rd party tools such as TOAD. This recent OTN thread showed this logon trigger ode for blocking TOAD users;
CREATE OR REPLACE TRIGGER NOTOAD_VB AFTER LOGON ON DATABASE DECLARE SHOULD_EXECUTE INTEGER; BEGIN SELECT DECODE(SUBSTR(UPPER(PROGRAM),1,4),’TOAD’,1,’VB’,1,0)+DECODE(INSTR(PROGRAM,”,-1),0,0, DECODE(SUBSTR(UPPER(SUBSTR(PROGRAM,INSTR(PROGRAM,”,-1)+1)),1,4),’TOAD’,1,’VB’,1,0)) INTO SHOULD_EXECUTE FROM V$SESSION WHERE SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1); IF SHOULD_EXECUTE > 0 THEN raise_application_error(-20001,’Please no toad yet, try again later’); –EXECUTE IMMEDIATE ‘ALTER SESSION SET EVENTS ”10046 TRACE NAME CONTEXT FOREVER,LEVEL 12”’; END IF; END;
Many shops choose force their developers and end-user community to use specific tools to access Oracle. This is especially true if your users have Oracle ID’s and you want to close all back-doors, and force them to log-in using your own access method.
Some people use the grant execute access method, because it closes all back-doors. Using the grant execute model, the end users will only have database privileges when they are executing the stored procedure and will have no ability to access Oracle outside of their procedures.
