How to forcibly logout users 2

[Livia8]

Another day, another question!

While the month-end processes are being carried out, we normally ask users to refrain from logging into Oracle. We then monitor who accesses it by runnning this query:

select last_connect, usr.user_name, resp.responsibility_key, function_type, icx.*
from apps.icx_sessions icx
join apps.fnd_user usr on usr.user_id=icx.user_id
left join apps.fnd_responsibility resp on resp.responsibility_id=icx.responsibility_id
where last_connect>sysdate-nvl(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),30)/60/24
and disabled_flag != 'Y' and pseudo_flag = 'N'


The output usually shows, among the rest, the list of user who are logged in in real time and the responsibility they're in

E.g.:

USER_NAME --> RESPONSIBILITY_KEY
JSMITH --> PAYABLES ENTRY
JDOE --> CASH ENTRY

When then contact the individuals and ask them to log off the system.

Is there a way of forcibly log out JSMITH and JDOE out remotely?

Thanks.

 

[Andrzejek]

Not really an answer to your question, but...
"we normally ask users to refrain from logging into Oracle" - why?
Because "the month-end processes are being carried out", but still, why you want all users to be out?
What is in the 'month-end processes' that forces all your users out?

Just curious...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Livia8]

Hi Andy,

The month-end process consist in processing pay runs, carrying out journal entries, reconciling accounts, etc.

It's not too bad if users log in to run reports (but may use up resources that may be needed by Accounts and end up delaying them), but often people go in and continue to process invoices or transfer funds - this would have a direct impact on the work Accounts are doing. So, if we run that query and see that, for instance, JSMITH and MJONES have logged in and are in a Payables responsibility, we contact them directly and ask them to log out immediately. However, sometimes they cannot be reached, or they don't log off cleanly leaving "ghost" sessions and potentially creating locks.

Being able to kick them off the system remotely would be very helpful.

 

[Andrzejek]

This is just my opinion, but “Being able to kick them off the system remotely would be very” bad idea.
If your business process relays on processing records for any particular time span, like weekly, monthly, annually, etc. then your records should have time stamp (or some other date data) so you can process them accordingly.

Another approach could be to automatically copy your data base (or just subset of your tables) to a temporary location (where it would be ‘frozen in place’) for your “month-end process” and – after that is done - delete the temporary data.

We have a lot of processes that run at night (with Stored Procedures and Packages) when no users are connected to the data base. A job could be written that runs “month-end process” that would be scheduled at the last day (well, night…) of the month.

Or, you can set-up your application that at the specified day (First of the month? Last day of the month?) you prevent users “to process invoices or transfer funds” (etc.) until Accounts says: “OK, we are done with our month-end process.” You (or Accounts) flip a switch at the data base and all your users get back full functionality of your application(s).

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Livia8]

These are processes that need to be checked by humans, so automating them is not an option (e.g. there are some parts that need to be signed off before they can be completed). I don't work in Accounts myself, so I don't have the full details of all the steps that need to be taken or I'd give you more details. Why would logging users off remotely be a bad thing?

 

[Andrzejek]

> Why would logging users off remotely be a bad thing?
Would you - as a user of an application - be happy if in the middle of your work you would be kicked out? No message, no warning, just... puff! Gone.

What's wrong with my other propositions?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Livia8]

If someone had told me to stay out, I couldn't really complain if the bouncers got involved once I tried to get in.

9 times out of 10, when we do get hold of them, they tell us "Sorry I forgot", and 8 out of those 9 don't log out cleanly.

Also, being able to remotely log someone out would involve less work than creating additional processes that may or may not have repercussions on other parts of the system. If we let users know in advance that if they do log in they'll be kicked out, they may forget why it happened the first time it happens, but they won't the next one.

Thanks.

 

[Andrzejek]

I know you have your heart set on kicking people out, but… I am sure you did your Google research (I know I did) and you either didn’t find any way of doing it or you found others who wanted to do that with a whole bunch of people asking: “Why?” and giving other options. That’s what I found out in my research.
I know DBAs (Data Base Administrators) can do it, but they don’t like to do it, especially on the ‘regular’ basis, because it is not a ‘normal’ procedure.
And as you can see here on TT, nobody else jumped in with the solution, and here are a lot of knowledgeable people. It is because nobody else wants to kick people out.

they tell us "Sorry I forgot" – really bad approach to programming, IMO. It is NOT user’s job to remember to stay out. It is programmer’s job to create an application that will do what it needs to do without ‘bouncers’ around to keep users in check.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

As Andy points out, autoterminating sessions is going to lead to wasted work that has to be reaccomplished as well as potentially throwing your books off if commits have been executed part way through a process. On the other hand, you might try putting the database into restricted mode a couple of hours before the close starts. If you can afford to do this, it might keep people from logging in to do work right before close. The downside to this approach is that you effectively extend the time the database is unavailable due to close.

One way of kicking everybody out would be to do a shutdown immediate (or shutdown abort) of your database and then reopen it in restricted mode. Then come out of restricted mode when your close is finished. This would throw everybody out, then only let those with restricted session privileges (DBAs and those needed to perform the close) to get back in. But again, it's not a good idea.

Perhaps a script that would run an hour before close that could find all open sessions and send out an email to those who are connected? That would take away the "I forgot" excuse.

 

[Livia8]

Thank you both.

@Andy: I can't put the database in restricted mode because Accounts have to use the same "parts" of the application as the other users would (aside from the fact that I've no idea how to do it! I know very little about DBA so I'm trying to pick up bits and pieces wherever I can, I'm sure it shows). Kicking out individual users would also allow me to get rid of ghost sessions caused by people not logging out cleanly - at least, I'd hope so. The system automatically logs people out after 30 mins of inactivity, this would be a quicker way of doing it manually instead of waiting half an hour. I did research it, both in search engines and on the Oracle Support website, but I often find that I'm using the wrong keywords (again, I don't know enough about DBA to ask the right questions) and nothing suitable come up - e.g., I found entries that show me how to log users out, but in these cases "users" meant SYSTEM, SYSADMIN, etc., not JSMITH. I've used the"kill -9" entry, but not in this context, I don't know if it can be applied to front-end users.

@carp: RE: "Perhaps a script that would run an hour before close that could find all open sessions and send out an email to those who are connected? That would take away the "I forgot" excuse.": We already email them the day before and send a reminder the in the morning, but they still log in, as we discover when we run the script above.

Ideally, I'd like users to be logged out in the same way Windows kicks you out when it's installing updates ("You'll be logged out in 30 seconds, save your work" type of thing).

Thanks.

 

[Andrzejek]

My Windows never kicks [me] out when it's installing updates. Certainly doesn't give me "30 seconds, save your work". Imagine, you work for several hours, go to get a cup of coffee, and when you get back, all your work is gone.

There is an application where I work. Whoever designed and wrote it claims that users are using this app wrong. How is that possible? It is never users’ fault; it is always analyst / programmers’ fault. How and when app is used should not be up to the user, it is your application’s function to properly accommodate all necessary needs.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

You probably want to look up "alter system kill session" and go from there.

 

[Livia8]

Unfortunately, Windows does in our place... Ok, it gives us more than 30 seconds, I was exaggerating, but we don't even have the chance to snooze the notification. And no, it's not our side of the house that mangages that.

There is an application where I work. Whoever designed and wrote it claims that users are using this app wrong. How is that possible? It is never users’ fault; it is always analyst / programmers’ fault. How and when app is used should not be up to the user, it is your application’s function to properly accommodate all necessary needs.

I agree to a certain extent, but if you're using a knife to eat your soup and you cut yourself in the process, it's not the knife's fault, nor is it the fault of the knife's manufacturer. Of if you sit your laptop on top of a shag-pile carpet, it's not suprising if it overheats.


The way I see this is: if the fire alarm rings, you have to get out, you can't stop and collect your belongings or log out nicely. If you don't, the fire officers will make you.

 

[Andrzejek]

But it is not the fire you are talking about. Fire implies unforeseen emergency. At least that's the case at my end... If I have a fire and I need to kick people out of my app, I call my DBA and he will do it.
You are talking about your "month-end process", something that happens every month, at predictable day / time. Your application should be able to accommodate all and any business needs.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Livia8]

....like a fire drill?

How would your DBA do it?

 

[Livia8]

@carp - thanks, I'll look more into that - sorry, I've only just seen your post.

 

[Andrzejek]

>....like a fire drill?
Now you are grasping the straws...

DBAs have a lot of tools they can employ to do whatever they need to do. Like dropping (deleting) the data base. Do they use it on monthly basis? No.

But they can Log Out Selected or All Users

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

Nope - you save "DROP DATABASE" for when you get laid off!