stopping connections for certain programs?

[mrchrispy]

pretty much says it in the subject actually

does anyone know of a simple way I can stop an oracle user from connecting to a database using a certain program? (where the program would be what is seen in v$session)

i.e. stop user 'bob' connecting to the database using 'SQLPLUS.EXE'

I'm thinking along the lines of a role or something but my mind has turned to cheese at the moment. any ideas welcome.

chris

[Sameer]

simple way I can stop an oracle user from connecting to a database using a certain program?

:-) What is the idea behind this...

I wonder what will be use of this.. but u can write a small cron job which will run every minute and check v$session

If v$session.program = 'SQLPLUSW.EXE' and v$session.username = 'bob' then
   kill the session
end if

I don't know anything else will be possible.. But quering v$session every minute is :-((((((

Sameer

[gopi]

Chris, you aint gonna like my creative way and I'd be curious to know how others are doing this, but:

1. write an AFTER LOGON ON trigger that determines user andtool from v$session.
2. If Bob/SQLPLUS, then put serial# and sid out to O/S file with UTL_DIR file
3. A UNIX script is in a continuous loop looking for the existence of the file mentioned in #2.
4. If the the file exists, invoke sqlplus and kill the session, then delete the file and return to the infinite loop in the UNIX script.

This sucks but it works for me. (I simply assumed that you are on UNIX box and that your version of Oracle has AFTER LOGON ON)

[mrchrispy]

I was hoping that there is a jazzy profile type command that would assist me in my quest to become an evil DBA :-) but no luck as of yet.

I'd rather hitting v$session on a regular basis but it might be my only way....

[mrchrispy]

I'm afraid to admit that I'm running on NT (i must have done something wrong in a former life)

[gopi]

Just be aware that an evil user will have up-to 1 minute to do damage and when that session gets killed she can just log on again and have another minute to perform truncates, drops, deletes,

my 2 cents

[jmodic]

Originally posted by gopi
Chris, you aint gonna like my creative way and I'd be curious to know how others are doing this, but:

1. write an AFTER LOGON ON trigger that determines user and tool from v$session.

So far so good. But I wonder, why after this point, there is a need for so much complication by using (UTL_FILE + cron job + kill session)? Why not simply raise an application error from within the same logon trigger and terminate the connection immediately?

[Sameer]

when that session gets killed she can just log on again and have another minute to perform truncates, drops, deletes,

true... can we run crob job every second :-))

[Sameer]

Why not simply raise an application error from within the same logon trigger and terminate the connection immediately?

I admit.. I still need to learn more... :-)

[gopi]

Jurij.. your point is well taken - thanks!

Jurij and Sameer...I'm not even using a cron. Just a script that runs forever in a loop, sleeping for one second, waking up, checking and killing.... NO CRON AT ALL!

nohup the_script.sh &

Even without the use of cron, I agree with Jurij that this is still complicated.