Now suppose user HR wants to kill the first session. For this I created two session with the user HR with sqlplus utility Session 1 I execute a small test to demonstrate the procedure usage so that there is no doubt and developers can use this procedure flawlessly. They will not be able to kill any other user session and will get error message if he tries to execute that. Now all user has the access to this Procedure and They can kill there own session only. SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') If you want to deploy the code where in any user can use the procedure the kill its own session, we can execute below steps CREATE OR REPLACE PROCEDURE sys.kill_session(p_sid NUMBER, p_serial NUMBER) ORA-20002: Unauthorized Attempt to Kill a Non User Session has been Blocked. ORA-20001: Session has Expired or Invalid sid/serial Arguments PassedīEGIN apps.kill_session(7522,58115) END He will not be able to kill any session not owned and will get the error message He can identify the session to be killed and then execute the command set serveroutput on
User can get the session information using the below query select sid,serial#, Username ,module from v$session where username='SCOTT' RAISE_APPLICATION_ERROR(-20002,'Unauthorized Attempt to Kill a Non User Session has been Blocked.') Ĭreate or replace procedure kill_session( p_sid in number, p_serial# in number)
RAISE_APPLICATION_ERROR(-20001,'Session has Expired or Invalid sid/serial Arguments Passed') IF v_user IN ('SCOTT') THEN -the list can be extendedĮXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || ''''
Suppose we want to give kill session access to the user SCOTT to kill its own session CREATE OR REPLACE PROCEDURE sys.kill_session(p_sid NUMBER, p_serial NUMBER) This way developer will be more independent and DBA will also get time to work on other priorities This bottleneck can be achieved by creating a procedure and then giving execute on that procedure to the user. We can give alter system privilege but it will other grants also which Oracle DBA don’t want. We dont have any privilege like alter system kill session which can be given to the user. They want the oracle kill own session privilege. Often developers ask how we can kill our own session as they dont want to wait for the DBA to respond and kill.
0 kommentar(er)
