Problem solve
Get help with specific problems with your technologies, process and projects.
Restricting users from connecting through SQL*Plus/Toad
I want to restrict selected users from connection through SQL*Plus and Toad but they can connect using the Oracle Developer application.
I want to restrict selected users from connection through SQL*Plus and Toad but they can connect using the Oracle Developer application. I have written a trigger on database startup and access history from SYS.V#Session. It doesn't work well. Can you help?
I was able to restrict users from using Toad by using a table, database trigger, a procedure and a job. The process works as follows:
- I created a table that looks like this:
SQL> desc LOGONAUDITTABLE Name Null? Type ----------------------------------------- -------- --------------------- EVENT VARCHAR2(10) TIMESTAMP DATE SCHEMA VARCHAR2(30) OSUSERID VARCHAR2(30) MACHINENAME VARCHAR2(64) SID NUMBER SERIAL# NUMBER PROGRAM VARCHAR2(100)
- I created a database trigger. This trigger inserted sys.v$session info into my table. My criterion for inserting data: 1) the user cannot be "R11" 2) the program equaled "TOAD.exe" or 3) the module equaled "T.O.A.D."
CREATE OR REPLACE TRIGGER logonauditing AFTER LOGON ON database DECLARE machinename VARCHAR2(64); osuserid VARCHAR2(30); sid NUMBER; serial# NUMBER; program VARCHAR2(100); module varchar2(100); CURSOR c1 IS SELECT osuser, machine , sid , serial# , program, module FROM v$session WHERE audsid = USERENV( 'sessionid' ); BEGIN OPEN c1; FETCH c1 INTO osuserid,machinename,sid,serial#,program, module ; if upper(program) = 'TOAD.EXE' or upper(module) = 'T.O.A.D.' then if user != 'R11' then INSERT INTO LOGONAUDITTABLE VALUES ( 'LOGON', SYSDATE,USER,osuserid, machinename,sid,serial#,program); end if; end if; CLOSE c1; END; / - I created a procedure that reads the LOGONAUDITTABLE and kills the sessions of all the users listed in the table. Then it deletes records from the table. This procedure looks like this:
CREATE OR REPLACE PROCEDURE KILLSESSION AS sid NUMBER; serial# NUMBER; timestamp DATE; CURSOR c1 IS SELECT sid , serial#, timestamp FROM LOGONAUDITTABLE; --WHERE INSTR(UPPER(program),UPPER('plus33w')) > 0; BEGIN FOR i1 IN c1 LOOP execute IMMEDIATE 'alter system kill session ' ||''''||i1.sid||','||i1.serial#||''''; DELETE LOGONAUDITTABLE WHERE sid = i1.sid AND serial#=i1.serial# AND timestamp = i1.timestamp; END LOOP; COMMIT; END; / - Next I re-created a job that runs every 30 seconds. It executes the procedure KILLSESSION.
