This content has been marked as final. Show 19 replies
jets wrote:grant alter session to username;
We need to grant 2 privs to user.
1) User XXX need to enable and disable restricted session on DB. Can we grant some priv's to him, to do this? Or this can do only DBA ?
2) User XXX need to kill all sessions, not only sessions in him schema.
Can we do this?
grant alter system to username;
SQL> conn sys/oracle as sysdba; Connected. SQL> grant alter session to test; Grant succeeded. SQL> grant alter system to test; Grant succeeded.
One way to do this is to create a definer rights stored procedure to handle this to avoid giving ALTER SYSTEM privilege which is quite powerful: you only need to grant EXECUTE privilege on stored procedure instead of ALTER SYSTEM privilege.
Please read following discussion for a possible example: Re: ALTER SYSTEM KILL SESSION privilege
jets wrote:yes, you are correct. it is not advisable to grant alter system privilege to normal user.
I think that:
grant alter session to username;
grant alter system to username;
It's a very bug privs for user, right?
Is it dangerous to give this privs to user? Can it do something with db with alter system priv ?
please see/refer the link refered by P. Forstmann
I created 2 procedures.
Now we can kill and enable\disable restricted sessions without DBA priv and sys password.
But i have 1 question about sessions...
Sometimes we have sessions from user TEST that we cannot delete...
I mean when we run alter sytem disconnect session 'sid,serial' immediate; It asked that session marked to kill.
And sometimes it lock some resources...
We resolve this issue using pid on OS system: I mean that we find PID on oracle database and login as oracle user to OS and kill -9 session that we need.
We created 2 procedures and lock password sys for users who will use this procedures...
We would like to lock oracle password too, but how they can kill KILLED sessions in OS ?
if you are using 10g or 11g you could try to create a DBMS_SCHEDULER job that will run a OS script to kill the operating system process.
Please have a look to Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch files Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch files
You can pass argument to job as documented in above mentioned link. Here is just an example that should be adapted:
begin dbms_scheduler.create_job('myjob', job_action=><your OS job>, number_of_arguments=>2, job_type=>'executable', enabled=>false); dbms_scheduler.set_job_argument_value('myjob',1,<session sid>); dbms_scheduler.set_job_argument_value('myjob',2,<session serial>); dbms_scheduler.enable('myjob'); end;
Found mistake 'sid' 'serial' need to be in '' ...
I need to know how to complete this task...
1) I create test.sh script, with something like this:
# Test script
echo Test message
echo Please enter PID:
echo kill -9 "$x"
2) How to create job that it will provide parameter x to script ? Or it unreal ?
We need to kill "KILLED" sessions on OS, if it will not killed like 'alter system disconect session 'sid,serial' immediate' ...
First you need to identify OS process identifier of dedicated server process for database session with:
Then you need to pass this as argument from database job to script shell.
select spid from v$process, v$session where paddr=addr and sid=<session sid>;
In database something like:
In script shell something like:
begin dbms_scheduler.create_job('myjob', job_action=><your OS job>, number_of_arguments=>1, job_type=>'executable', enabled=>false); dbms_scheduler.set_job_argument_value('myjob',1,<spid>); dbms_scheduler.enable('myjob'); end;
Edited by: P. Forstmann on 9 juin 2011 09:51
#!/bin/sh echo print "script shell parameter 1:$1" echo kill -9 $1