Scripts
Grant a "standard" db user the possibility to kill database sessions
User Rating: / 1
- Details
-
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: 7204
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
#####################################################################################################################