Scripts

Grant a "standard" db user the possibility to kill database sessions

User Rating:  / 1
PoorBest 
Parent Category: Articles
Created on Wednesday, 05 November 2014 13:36
Last Updated on Wednesday, 05 November 2014 14:18
Published on Wednesday, 05 November 2014 13:36
Hits: 7181

How to grant a standard database user the possibility to kill user sessions 

 

How to grant a standard database user the possibility to kill user sessions

 

1. Without the need to grant him the alter system privilege

2. Without the risk it is used to kill background sessions

 

create or replace procedure I_AM_DBA.KILL_USER_SESSION ( nsid number, nserial number )
authid definer
/* Guy Lambregts 2014-09-29 */
/* This procedure can be used to grant a database user the possibility to kill user sessions */
/* without the need to grant the alter system privilege to the user */
/* STEP 1 as sysdba "create user i_am_dba identified by secret account lock" */
/* STEP 2 as sysdba "grant alter system to i_am_dba" + "grant select on sys.v_$session to i_am_dba" */
/* STEP 3 as sysdba compile this procedure under schema i_am_dba */
/* STEP 4 as sysdba "grant execute on i_am_dba.kill_user_session to a_user" */
/* STEP 5 as sysdba "grant select on v_$session to a_user" */
/* usage : connected as a_user : SQL > exec i_am_dba.kill_user_session(34,17); */
as
statement varchar2(100);
begin
select 'alter system kill session '''||sid||','||serial#||''' immediate' into statement from sys.v_$session
where sid = nsid and serial# = nserial and username is not null and username != 'SYS' ;
execute immediate(statement);
exception
when no_data_found then
raise_application_error(-20001, 'Session does not exist or SYS session / background process');
when others then
raise_application_error(-20002,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end;
/

 

#####################################################################################################################

SQL> show user
USER is "SYS"
 
SQL> create user i_am_dba identified by secret account lock;
User created.
 
SQL> grant alter system to i_am_dba;
Grant succeeded.
 
SQL> grant select on sys.v_$session to i_am_dba;
Grant succeeded.
 
SQL> create or replace procedure I_AM_DBA.KILL_USER_SESSION ( nsid number, nserial number )
2 authid definer
3 /* Guy Lambregts 2014-09-29 */
4 /* This procedure can be used to grant a database user the possibility to kill user sessions */
5 /* without the need to grant the alter system privilege to the user */
6 /* STEP 1 as sysdba "create user i_am_dba identified by secret account lock" */
7 /* STEP 2 as sysdba "grant alter system to i_am_dba" + "grant select on sys.v_$session to i_am_dba" */
8 /* STEP 3 as sysdba compile this procedure under schema i_am_dba */
9 /* STEP 4 as sysdba "grant execute on i_am_dba.kill_user_session to a_user" */
10/* STEP 5 as sysdba "grant select on v_$session to a_user" */
11 /* usage : connected as a_user : SQL > exec i_am_dba.kill_user_session(34,17); */
12 as
13 statement varchar2(100);
14 begin
15 select 'alter system kill session '''||sid||','||serial#||''' immediate' into statement from sys.v_$session
16 where sid = nsid and serial# = nserial and username is not null and username != 'SYS' ;
17 execute immediate(statement);
18 exception
19 when no_data_found then
20 raise_application_error(-20001, 'Session does not exist or SYS session / background process');
21 when others then
22 raise_application_error(-20002,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
23 end;
24 /
 
Procedure created.
 
SQL> create user i_am_user identified by welcome;
User created.
 
SQL> grant create session to i_am_user;
Grant succeeded.
 
SQL> grant execute on i_am_dba.kill_user_session to i_am_user;
Grant succeeded.
 
SQL> grant select on v_$session to i_am_user;
Grant succeeded.
 
SQL> conn i_am_user/welcome
Connected.
 
SQL> select username,sid,serial# from v$session;
 
USERNAME                       SID        SERIAL#
------------------------------ ---------- ----------
                               1          1
                               3          5
                               17         1
                               33         1
                               49         1
                               50         1
                               65         1
                               66         1
                               67         1
I_AM_USER                      81         7
                               82         1
                               97         1
                               98         3
                               113        1
                               114        3
                               129        1
                               130        1
                               145        1
                               146        1
                               161        1
                               162        1
                               177        1
                               178        1
                               193        1
                               194        1
                               209        1
                               210        1
                               225        1
                               226        9
                               241        1
I_AM_ANOTHER_USER              242       11
 
31 rows selected.
 
SQL> exec i_am_dba.kill_user_session(242,11);
 
PL/SQL procedure successfully completed.
 
SQL> exec i_am_dba.kill_user_session(241,1);
BEGIN i_am_dba.kill_user_session(241,1); END;
*
ERROR at line 1:
ORA-20001: Session does not exist or SYS session / background process
ORA-06512: at "I_AM_DBA.KILL_USER_SESSION", line 20
ORA-06512: at line 1
 

#####################################################################################################################