First, create a view that lets the users check their own sessions:
connect / as sysdba
create or replace view my_sessions as
select username,sid,serial#,osuser,status,program,machine
from v$session
where username=SYS_CONTEXT('userenv','CURRENT_SCHEMA');
Create a public synonym, and grant select to public:
create public synonym my_sessions for my_sessions;
grant select on my_sessions to public;
To get the procedure to compile in the SYSTEM schema, it is required to grant the "alter system" privilege directly to the user - having the same privilege through a role won't do.
grant alter system to system;
grant select on v_$session to system;
Log in as system, and create the procedure:
CREATE OR REPLACE PROCEDURE KILL_SESSION(PN_SID NUMBER,PN_SERIAL NUMBER) AUTHID DEFINER
AS
LV_USER VARCHAR2(30);
EXE_USER VARCHAR2(30);
BEGIN
SELECT S.USERNAME INTO LV_USER
FROM V$SESSION S, USER_USERS U
WHERE S.SID = PN_SID
AND S.SERIAL# = PN_SERIAL
AND S.USERNAME = S.USERNAME;
SELECT USERNAME INTO EXE_USER
FROM V$SESSION
WHERE AUDSID=SYS_CONTEXT('userenv','SESSIONID');
IF EXE_USER = LV_USER THEN
EXECUTE IMMEDIATE 'alter system kill session '''||PN_SID||','||PN_SERIAL||'''';
dbms_output.put_line('Session ' || PN_SID || ',' || PN_SERIAL || ' killed.');
ELSE
RAISE_APPLICATION_ERROR(-20000,'Only your own sessions may be killed.');
END IF;
END;
/
Create a synonym for the procedure and give everyone execute privileges on it:
CREATE PUBLIC SYNONYM KILL_SESSION FOR KILL_SESSION;
GRANT EXECUTE ON KILL_SESSION TO PUBLIC;
Now the users have a way to check their own sessions, and to terminate them if they chose to do so:
connect scott
select * from my_session;
USERNAME SID SERIAL# OSUSER STATUS PROGRAM MACHINE
-------------------- ---------- ---------- -------------------- -------------------- ------------------------------------------------ ----------------------------------------------------------------
SCOTT 1152 9510 oracle INACTIVE sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com
SCOTT 1726 31531 oracle ACTIVE sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com
Kill a session:
scott@mydb>SQL>exec kill_session(1152,9510);
Afterwards, the view will change to show session 1152 to be in status KILLED:
USERNAME SID SERIAL# OSUSER STATUS PROGRAM MACHINE
-------------------- ---------- ---------- -------------------- -------------------- ------------------------------------------------ ----------------------------------------------------------------
SCOTT 1152 9510 oracle KILLED sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com
SCOTT 1726 31531 oracle ACTIVE sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com
No comments:
Post a Comment