We help IT Professionals succeed at work.

restricting multiple sessions in oracle 10g

venu3108
venu3108 asked
on
hi

we have been migrated to 10g forms and want to know how to setup multiple sessions restrictions since the user is opening n number of sessions .


Rgds
Venu
Comment
Watch Question

Top Expert 2011

Commented:
- you can set using the following command. n is number of session that you want to restrict per user:

alter profile default limit SESSIONS_PER_USER n;



- refer to the doc:
http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_2007.htm

Author

Commented:
Hi

i am not asking at the database level since users are logging with userid separately at application level not at database .


eg: a user KKK is having access to application . he is logging with kkk once.
again he is logging in the new browser with same id.

i want to restrict application level session limit.

rgds
Top Expert 2011

Commented:
- i'm not a form developer, however i can think of a few ways:

1- create a session column in your user table. update session=1 when user login. always check for this column value in the login page, if 1 means user cannot login again. reset that column = 0 on exit / user logout from the form.

2- grant the oracle user to query to v$session. you can make use of OSUSER, MACHINE/TERMINAL data in v$session to restrict only 1OS user or 1terminal is allowed to login.

3- there are some suggestion that you can look into the following thread such as DBMS_APPLICATION_INFO.SET_CLIENT_INFO, set_context and etc
https://forums.oracle.com/forums/thread.jspa?threadID=1037566&start=0&tstart=0
Geert GOracle dba
BRONZE EXPERT
Top Expert 2009

Commented:
why would the multiple sessions be a problem ?
why would you want to restrict it ?

if the database can handle it without any problems, then it shouldn't be restricted
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
You might also be able to handle this with an 'AFTER LOGON' trigger to check v$session and take appropriate action.

Examples are out there on this.

Here is one that limits access based on program:
http://dba-oracle.com/t_trigger_restrict_end_user_access.htm

BE CAREFUL:  With this type of a trigger, you can easily keep everyone from connecting if you don't get it right.