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.
Dig Deeper on Using Oracle PL-SQL