asked on

insufficient privileges

I try to kill a session with "alter system kill session" command, but I am unable to kill due to lack of privileges

I check out dba_sys_privs table to see if the user I have logged into has the alter system privilege; and i found that
abc does have "alter system" privilege as shown below.

So why am I not able to kill the query using alter system kill session command?

SQL> select * from dba_sys_privs where PRIVILEGE like 'ALTER SESSION';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
abc        ALTER SESSION NO
def             ALTER SESSION NO
ghi        ALTER SESSION NO
jkl        ALTER SESSION NO
mno        ALTER SESSION NO
pqr             ALTER SESSION NO
stu        ALTER SESSION NO
vwx             ALTER SESSION NO
xyz               ALTER SESSION NO

SQL> show user
USER is "abc"

SQL> alter system kill session '35,4911' immediate;
alter system kill session '35,4911' immediate
*
ERROR at line 1:
ORA-01031: insufficient privileges

adrian_ang

You are looking at ALTER SESSION, the required one is ALTER SYSTEM

sonicefu

If you want to access V$SESSION, connect as SYS user and grant select privilege on v$session to the user:
GRANT SELECT ON v_$session TO userName;

-------------------------------------------------------

To kill sessions, a user needs to have ALTER SYSTEM privilege

ALTER SYSTEM KILL SESSION 'sid,serial#';

sonicefu

sorry for above comment

connect as DBA user and grant
GRANT alter system TO userName;

gram77

ASKER

Only the following users have ALTER SYSTEM privilege, and I am not among them.

SQL> select * from dba_sys_privs where PRIVILEGE like 'ALTER SYSTEM';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA ALTER SYSTEM YES
DBA_UTILITY ALTER SYSTEM NO
SYS ALTER SYSTEM NO

However, I can't even kill the running query using KILL -9 <spid>, as I don't have the admin rights in unix.

SELECT b.SID "Oracle Sid",
b.serial# "Oracle Serial",
a.spid "Unix PID",
c.sql_text "SQL Text"
FROM v$process a, v$session b, v$sql c
WHERE a.addr = b.paddr
AND b.sql_address = c.address
AND b.sql_hash_value = c.hash_value;

What is the way out for me to kill the errand query apart from asking the DBA to provide me the ALTER SYSTEM
privilege or assign me administrator role in unix?

ASKER CERTIFIED SOLUTION

sonicefu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION