• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4362
  • Last Modified:

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

0
gram77
Asked:
gram77
3 Solutions
 
adrian_angCommented:
You are looking at ALTER SESSION, the required one is ALTER SYSTEM
0
 
sonicefuCommented:
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#';
0
 
sonicefuCommented:
sorry for above comment

connect as DBA user and grant
GRANT alter system TO userName;
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
gram77Author Commented:
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?
0
 
sonicefuCommented:
connect as any DBA user (sys, system, any other) and grant

GRANT alter system TO userName;

without having "alter system" you cannot kill a session
0
 
schwertnerCommented:
If you want to kill the session without DBA help you have
to stop the application (so you will run in the so called 'death connection').

The DBA should edit the SQLNET.ORA file and to add the entry

SQLNET.EXPIRE_TIME = 10

In 10 minutes the Listener automatically will kill the session.
Meanwhile you can establish a new session.

Another workaround is to ask the DBA to write a procedure to kill a particular session under SYS account and to grant you the privilige to run this procedure. In this case you do not need any rights except to execute that procedure.
0
 
anandmahajanCommented:
maybe you can crate Unix shell script and run your sql from that shell script.

If you want to kill session you can kill your unix session and sql will get killed as its child process.

But sometimes SQL doesnt get killed immediately in that case u might need to ask dba to kill it manually.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now