Amit E. wrote:If you want suggestions for the best solution it would be better if you stated the business problem to be solved, rather than describing some pre-conceived (and often ill-conceived) technical solution.
Actually i dont want to kill all the sessions coming from these nodes. The procedure will kill only those sessions connecting to database with usernames specified in APPSUSERS table. This table contains some of the DB schemas which should not be used from particular nodes.
Amit E. wrote:As DK2010 said, this would be better done via triggers. Then you don't have to have a job start every few seconds, and they never get in at all. The trigger will have to identify what machine they are coming from (hostname? IP address?) . Rather than keeping up with all of the hosnames or IP address to block, I'd just code the list of acceptable addresses, and block anything else. And I wouldn't hard-code that in the procedure, but put the list of acceptable hosts in a lookup table - with update access (and possibly even read access, though that may be hard to do given the proliferation of SELECT ANY TABLE privileges) to that table limited to a short list of DBAs.
Here is the problem to be solved -
Our Application team members know the db application schema passwords to connect to the database. The connections to the database can only be made using certain machines (called jumphosts where tools like sqlplus, toad, sqldeveloper are installed) and application servers. The application team members can not logon to the application servers because they do not have OS logon credentials on these application servers.
By right the app team members should use their own DB IDs (which have limited access) to connect to the database from jumphosts but if someone logs in using application schema accounts we want to terminate this session.
We have scheduled the pl/sql block given in my first update in database scheduler to run every 10 seconds so if someones connects to database from jumphost using Application schema accounts his/her session gets killed. so within 10 secods its highly unlikely to perform the actions which they should not. I hope that I have answered your question "why would you want to allow a certain user to connect from machine A but not from machine B?"
If at all there is any need to logon using Application database schema account we disable this job during that time.
I know that easiest solution for this is to change application schema account but lets keep that aside as there is different story for not changing the passwords.