网站建设公司-信科网络,网站备案需要些什么,网页制作平台播放视频,网站浏览器兼容性通用声明#xff1a;仅用于测试环境方便调试#xff0c;不可能应用于生产环境#xff1b;故请勿加入到程序源代码来实现自动杀进程。只需一个参数#xff0c;就能kill用户自己的会话#xff0c;请小心操作#xff0c;以免误kill进程。使用方法#xff1a;新开一个session后仅用于测试环境方便调试不可能应用于生产环境故请勿加入到程序源代码来实现自动杀进程。只需一个参数就能kill用户自己的会话请小心操作以免误kill进程。使用方法新开一个session后执行EXEC SYS.P_KILL_USER_SESSION(要杀的会话的sid)就能实现sys用户才能操作的alter system kill session(sid,serial#);例子04:14:46 sql1exec sys.p_kill_user_session(2525);目的一般用户在不具备执行alter system权限的前提下对于自己的所有session能达到alter system kill session功能。原理普通用户先根据(自己的)username和要kill的session的sid查找到这个session的(sid,serial#)再把这两个变量传到另一个存储过程P_KILL_SESSION,该存储过程中sys用户会亲自执行alter system kill session (sid,serial#);从而杀掉session。背景知识sid是唯一的不会重复假设登录了A用户意图要kill user A的会话结果输入了user B的sid则在查找(sid,serial#)时因为限制了usernameA and sid输入的sid则会返回0条记录。从而限制了只能kill当前操作用户的session。实施步骤1.sys用户先建立procedure存储过程P_KILL_SESSIONCREATE OR REPLACE PROCEDURE P_KILL_SESSION(P_USER IN VARCHAR2,P_SID IN VARCHAR2) ASV_SQL VARCHAR2(32767);BEGINSELECT ALTER SYSTEM KILL SESSION || SID || , || SERIAL# || INTO V_SQLFROM V$SESSIONWHERE USERNAME P_USERAND SID P_SID;EXECUTE IMMEDIATE V_SQL;EXCEPTIONWHEN NO_DATA_FOUND THENRAISE_APPLICATION_ERROR(-20001,SID || P_SID || DOES NOT EXISTS, OR THE SESSION USER IS NOT ||P_USER);END;存储过程P_KILL_USER_SESSIONCREATE OR REPLACE PROCEDURE P_KILL_USER_SESSION(P_SID IN NUMBER) AUTHID CURRENT_USER ASV_USERNAME VARCHAR2(30);V_SID NUMBER;BEGINSELECT SYS_CONTEXT(USERENV, SESSION_USER),SYS_CONTEXT(USERENV, SID)INTO V_USERNAME, V_SIDFROM DUAL;IF P_SID ! V_SID THENP_KILL_SESSION(V_USERNAME, P_SID);ELSERAISE_APPLICATION_ERROR(-20000, CAN NOT KILL CURRENT SESSION!);END IF;END;2.sys再grant执行存储过程的权限给用户GRANT EXECUTE ON P_KILL_USER_SESSION TO JF_ISU;3.获得授权的用户根据会话的sid就可以杀自己的任何session了exec sys.p_kill_user_session(sid)验证在服务器srcbdc建立3个会话2个JF_ISU用户sql1(3012751)sql2(1070,469)1个system用户(其它用户)会话104:14:22 192.168.210.65:1521/SRCBFINJF_ISU set sqlp sql104:14:30 sql1col sys_context(userenv,session_user) for a50;04:14:46 sql1col sys_context(userenv,sid) for a50;04:14:46 sql1select sys_context(userenv,session_user) ,sys_context(userenv,sid) from dual;SYS_CONTEXT(USERENV,SESSION_USER) SYS_CONTEXT(USERENV,SID)-------------------------------------------------- --------------------------------------------------JF_ISU 3012会话204:14:52 192.168.210.65:1521/SRCBFINJF_ISU set sqlp sql204:14:59 sql2col sys_context(userenv,session_user) for a50;04:15:01 sql2col sys_context(userenv,sid) for a50;04:15:01 sql2select sys_context(userenv,session_user) ,sys_context(userenv,sid) from dual;SYS_CONTEXT(USERENV,SESSION_USER) SYS_CONTEXT(USERENV,SID)-------------------------------------------------- --------------------------------------------------JF_ISU 1070会话304:15:05 192.168.210.65:1521/SRCBFINSYSTEM set sqlp system3;04:15:23 system3col sys_context(userenv,session_user) for a50;04:15:30 system3col sys_context(userenv,sid) for a50;04:15:30 system3select sys_context(userenv,session_user) ,sys_context(userenv,sid) from dual;SYS_CONTEXT(USERENV,SESSION_USER) SYS_CONTEXT(USERENV,SID)-------------------------------------------------- --------------------------------------------------SYSTEM 252504:15:30 system304:15:32 system3select sid,serial#,username,type,program,machine from v$session where machine like %srcbdc%;SID SERIAL# USERNAME TYPE PROGRAM MACHINE---------- ---------- ------------------------------ ---------- ------------------------------------------------ --------1070 469 JF_ISU USER sqlplussrcbdc (TNS V1-V3) srcbdc2525 511 SYSTEM USER sqlplussrcbdc (TNS V1-V3) srcbdc3012 751 JF_ISU USER sqlplussrcbdc (TNS V1-V3) srcbdc安全限制测试u JF_ISU不能kill其它用户的会话(JF_ISU无法kill system用户的)04:14:46 sql1exec sys.p_kill_user_session(2525);BEGIN sys.p_kill_user_session(2525); END;*ERROR at line 1:ORA-20001: SID? 2525 DOES NOT EXISTS, OR THE SESSION USER IS NOT JF_ISUORA-06512: at SYS.P_KILL_SESSION, line 12ORA-06512: at SYS.P_KILL_USER_SESSION, line 10ORA-06512: at line 1u JF_ISU不能kill当前session04:16:29 sql1exec sys.p_kill_user_session(3012);BEGIN sys.p_kill_user_session(3012); END;*ERROR at line 1:ORA-20000: CAN NOT KILL CURRENT SESSION!ORA-06512: at SYS.P_KILL_USER_SESSION, line 12ORA-06512: at line 1u 未获得存储过程执行权限的用户不能调用该存储过程。04:15:41 system3exec sys.p_kill_user_session(3012);BEGIN sys.p_kill_user_session(3012); END;*ERROR at line 1:ORA-06550: line 1, column 7:PLS-00201: identifier SYS.P_KILL_USER_SESSION must be declaredORA-06550: line 1, column 7:PL/SQL: Statement ignored基本测试JF_ISU能kill除当前session之外的自己的所有会话(且该用户不具备alter system权限)04:16:38 sql1exec sys.p_kill_user_session(1070);PL/SQL procedure successfully completed.04:17:16 sql104:17:18 sql1select * from session_privs; (实际上只需要有create session并获得exec on procedure p_kill_user_session即可完成)PRIVILEGE----------------------------------------CREATE SESSIONUNLIMITED TABLESPACECREATE TABLESELECT ANY TABLECREATE CLUSTERCREATE SYNONYMCREATE VIEWCREATE SEQUENCESELECT ANY SEQUENCECREATE DATABASE LINKCREATE PROCEDURECREATE TRIGGERCREATE TYPECREATE OPERATORCREATE INDEXTYPESELECT ANY DICTIONARYDEBUG CONNECT SESSIONDEBUG ANY PROCEDURE18 rows selected.04:17:44 sql104:15:01 sql2select sys_context(userenv,session_user) ,sys_context(userenv,sid) from dual;select sys_context(userenv,session_user) ,sys_context(userenv,sid) from dual*ERROR at line 1:ORA-00028: your session has been killed附录查看当前session信息要对视图v$session有select权限才能获得serial#,col username for A10;sql1select sid,serial#,username from v$session where sid(select sys_context(userenv,sid) from dual);SID SERIAL# USERNAME---------- ---------- ----------1457 3 JF_ISU04:43:18 sql1select * from user_role_privs;USERNAME GRANTED_ROLE ADM DEF OS_------------------------------ ------------------------------ --- --- ---JF_ISU CONNECT NO YES NOJF_ISU RESOURCE NO YES NO04:43:24 sql104:43:25 sql1select * from role_sys_privs;ROLE PRIVILEGE ADM------------------------------ ---------------------------------------- ---CONNECT CREATE SESSION NORESOURCE CREATE CLUSTER NORESOURCE CREATE SEQUENCE NORESOURCE CREATE TRIGGER NORESOURCE CREATE TABLE NORESOURCE CREATE PROCEDURE NORESOURCE CREATE TYPE NORESOURCE CREATE OPERATOR NORESOURCE CREATE INDEXTYPE NO9 rows selected.04:43:34 sql104:43:35 sql1select * from user_sys_privs;USERNAME PRIVILEGE ADM------------------------------ ---------------------------------------- ---JF_ISU SELECT ANY DICTIONARY NOJF_ISU CREATE SEQUENCE NOJF_ISU DEBUG CONNECT SESSION NOJF_ISU CREATE TYPE NOJF_ISU CREATE VIEW NOJF_ISU SELECT ANY TABLE NOJF_ISU CREATE DATABASE LINK NOJF_ISU CREATE TABLE NOJF_ISU UNLIMITED TABLESPACE NOJF_ISU CREATE TRIGGER NOJF_ISU CREATE SYNONYM NOJF_ISU DEBUG ANY PROCEDURE NOJF_ISU SELECT ANY SEQUENCE NO13 rows selected.04:43:43 sql1
