04:15:23 system3>col sys_context('userenv','session_user') for a50;
04:15:30 system3>col sys_context('userenv','sid') for a50;
04:15:30 system3>select sys_context('userenv','session_user') ,sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SESSION_USER') SYS_CONTEXT('USERENV','SID')
-------------------------------------------------- --------------------------------------------------
SYSTEM 2525
04:15:30 system3>
04:15:32 system3>select sid,serial#,username,type,program,machine from v$session where machine like '%srcbdc%';
SID SERIAL# USERNAME TYPE PROGRAM MACHINE
---------- ---------- ------------------------------ ---------- ------------------------------------------------ --------
1070 469 JF_ISU USER sqlplus@srcbdc (TNS V1-V3) srcbdc
2525 511 SYSTEM USER sqlplus@srcbdc (TNS V1-V3) srcbdc
3012 751 JF_ISU USER sqlplus@srcbdc (TNS V1-V3) srcbdc
安全限制测试：
u JF_ISU不能kill其它用户的会话；(JF_ISU无法kill system用户的）
04:14:46 sql1>exec 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_ISU
ORA-06512: at "SYS.P_KILL_SESSION", line 12
ORA-06512: at "SYS.P_KILL_USER_SESSION", line 10
ORA-06512: at line 1
u JF_ISU不能kill当前session；
04:16:29 sql1>exec 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 12
ORA-06512: at line 1
u 未获得存储过程执行权限的用户不能调用该存储过程。
04:15:41 system3>exec 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 declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
基本测试：
JF_ISU能kill除当前session之外的自己的所有会话（且该用户不具备alter system权限）；
04:16:38 sql1>exec sys.p_kill_user_session(1070);
PL/SQL procedure successfully completed.
04:17:16 sql1>
04:17:18 sql1>select * from session_privs; （实际上只需要有create session并获得exec on procedure p_kill_user_session即可完成）
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
SELECT ANY TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
SELECT ANY SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SELECT ANY DICTIONARY
DEBUG CONNECT SESSION
DEBUG ANY PROCEDURE
18 rows selected.
04:17:44 sql1>
04:15:01 sql2>select 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 user