Question:
Is it possible to use this command [alter system kill session] without having DBA privileges? What is the privilege that needs to be granted to the ordinary users in order to execute the ALTER SYSTEM KILL SESSION statement?
Answer:
It is possible to grant "ALTER SYSTEM"
Please exercise extreme caution when granting the privilege to users, especially on a production machine, as it could cause a lot of problems. I would not grant such a privilege to any user on a production machine.
To do this, simply create a packaged procedure which kills sessions, and grant execute privileges on that.
Again, I would not grant alter system to just any user. This method is the way to go, but make sure the procedure restricts which sessions can be killed.
Russ Cannon adds:
There must be a check to prevent users from killing privileged DBA sessions:
create or replace procedure kill_session
(pn_sid number
,pn_serial number)
as
lv_user varchar2(30);
begin
select username into lv_user from v$session where sid = pn_sid and serial# = pn_serial;
if lv_user is not null and lv_user not in ('SYS','SYSTEM') then
execute immediate 'alter system kill session '''||pn_sid||','||pn_serial||'''';
else
raise_application_error(-20000,'Attempt to kill protected system session has been blocked.');
end if;
end;
/
Note: The check for LV_USER IS NOT NULL is not strictly necessary because KILL SESSION will not kill non-user sessions, but I include it so as to raise my own error for those as well.
You should add any DBA accounts that you have to the list of privileged users ('SYS','SYSTEM','MYDBA',etc.).
For this code to work, you will have to create a direct select grant on sys.v_$session to the owner of this procedure as follows:
grant select on v_$session to <owner>
Where <owner> is the schema that owns the above procedure. This has to be a direct grant and not through a role.
This does what you want and prevents users from clobbering DBA sessions. It also obviates the need for granting the dangerous 'alter system' privilege to non-DBAs.
Laurent Schneider adds:
“On the one hand, "ALTER SYSTEM" privilege is MUCH SAFER than granting "DBA". The privileged user could not read/drop tables/tablespaces/database. So, if you are granting this to a "Junior DBA", it is, in my opinion, acceptable.
On the other hand, if you grant this to an applicative user, it means the security design of your database is bad. I would, in this case, oppose myself to such permissive access to the database. If it is needed to kill other sessions, find out why other sessions must be killed! If the sessions are just "hanging" when middleware died, then watch out for "SQLNET.EXPIRE_TIME" in sqlnet.ora.
If you want to prevent user from accessing the databases, maybe there is a way to "lock" the tables, or to make your transaction read-only.
Also teach the users to make appropriate selections (like always selecting where txn_date < trunc(sysdate) in some reporting dbs).”
Related Links:
http://www.dba-oracle.com/t_ault_25_dbms_sql_alter.htm
http://www.dba-oracle.com/tips_oracle_orakill.htm
