This does what you want and prevents users from clobbering DBA sessions. This has to be a direct grant and not through a role. Where is the schema that owns the above procedure. You should add any DBA accounts that you have to the list of privileged users ('SYS','SYSTEM','MYDBA',etc.).įor 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: 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. Raise_application_error(-20000,'Attempt to kill protected system session has been blocked.') If lv_user is not null and lv_user not in ('SYS','SYSTEM') thenĮxecute immediate 'alter system kill session '''||pn_sid||','||pn_serial||'''' Select username into lv_user from v$session where sid = pn_sid and serial# = pn_serial There must be a check to prevent users from killing privileged DBA sessions: This method is the way to go, but make sure the procedure restricts which sessions can be killed. To do this, simply create a packaged procedure which kills sessions, and grant execute privileges on that.Īgain, I would not grant alter system to just any user. I would not grant such a privilege to any user on a production machine. Please exercise extreme caution when granting the privilege to users, especially on a production machine, as it could cause a lot of problems. Is it possible to use this command 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?
0 kommentar(er)
