Re: How to restrict the user connections

  • From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 19 Mar 2004 09:57:59 -0400

> Have a great weekend.
The same for you
----- Original Message ----- 
From: "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, March 19, 2004 9:44 AM
Subject: RE: How to restrict the user connections


> Hey Juan,
>
> Yeah, I just saw that in the Admin guide and replied.  :-)
>
> Although I was originally joking about creating a logon trigger (and
> randomly deleting some user), it looks like that is the only way to go.
>
> I'll search metalink this afternoon and post anything that I find.  Right
> now I have to get back to some real work.
>
> Have a great weekend.
>
> -------------------------------
> Alan Davey
> Senior Programmer/Analyst
> Oracle 9i OCA
> w) 973-267-5990 x458
> w) 212-295-3458
>
>
>
> -----Original Message-----
> From: Juan Cachito Reyes Pacheco [mailto:jreyes@xxxxxxxxxxxxxxxx]
> Sent: 19 March 2004 08:30
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: How to restrict the user connections
>
>
> Alan did you see, the previous example?
>
> And any way you should read this
>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/create.h
> tm#999110
> Setting License Parameters
> Note:
> Oracle no longer offers licensing by the number of concurrent sessions.
> Therefore the LICENSE_MAX_SESSIONS and LICENSE_SESSIONS_WARNING
> initialization parameters have been deprecated and are no longer discussed
> in this book.
>
> The point is, and is my opinion, if he want to control de number of
> concurrent users, he will have to create a login trigger,
> the second chance is use  LICENSE_MAX_USERS, but what happens if he have
20
> licenses and 40 users, and only 20 working at the same time?
>
> The third chance is ask Oracle support (metalink and ask what they suggest
> for his specific situation).
> Alan, if you have metalink support, can you ask them?, I don't have.
> I think they will can give a better answer, because at last, it is a
problem
> of licnsing, and they
> must give a guide to measure it.
> :)
>
> Thanks.
>
> ----- Original Message ----- 
> From: "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Friday, March 19, 2004 8:44 AM
> Subject: RE: How to restrict the user connections
>
>
> > Hi Juan,
> >
> > By "autonomus function" do you mean 'pragma autonomous_transaction'?
> >
> > If so, then it works.
> >
> > First dos prompt:
> > connect as system
> > SQL> alter system set license_max_sessions=2;
> >
> > System altered.
> >
> > --leave sql/plus session active.
> >
> > second dos prompt:
> > connect as adavey
> >
> > --leave active.
> >
> > third dos prompt:
> > connect as scott/tiger
> > ERROR:
> > ORA-00019: maximum number of session licenses exceeded
> >
> > Back to second dos prompt:
> > SQL> ed
> > Wrote file afiedt.buf
> >
> >   1  create or replace procedure test as
> >   2  pragma autonomous_transaction;
> >   3  begin
> >   4    null;
> >   5* end;
> > SQL> /
> >
> > Procedure created.
> >
> > SQL> exec test;
> >
> > PL/SQL procedure successfully completed.
> >
> > I'm not familiar with developer, but from my experience with JDeveloper
> and
> > Toad, the various windows are all working via one user session.
> >
> > I believe we need more information from the original poster to see
exactly
> > what his requirements are.
> >
> >
> > -------------------------------
> > Alan Davey
> > Senior Programmer/Analyst
> > Oracle 9i OCA
> > w) 973-267-5990 x458
> > w) 212-295-3458
> >
> >
> >
> > -----Original Message-----
> > From: Juan Cachito Reyes Pacheco [mailto:jreyes@xxxxxxxxxxxxxxxx]
> > Sent: 18 March 2004 15:40
> > To: oracle-l@xxxxxxxxxxxxx
> > Subject: Re: How to restrict the user connections
> >
> >
> > Hi Alan, this is true if he runs a single session client,
> > This is not true if he for example is using developer , then every
window
> > can be a
> > distinct session.
> > Every user can have more than a session. For example what happen if he
> runs
> > an
> > autonomus function?
> > :)
> > ----- Original Message ----- 
> > From: "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
> > To: <oracle-l@xxxxxxxxxxxxx>
> > Sent: Thursday, March 18, 2004 2:44 PM
> > Subject: RE: How to restrict the user connections
> >
> >
> > > Hi Juan,
> > >
> > > license_max_sessions is the correct answer until Senthil answer's
Tim's
> > > question as to why it isn't good enough.
> > >
> > > You can go the trigger route, but unless there is a compelling reason
to
> > do
> > > so (only allow specific users), it requires more time spent
programming
> > and
> > > debugging (hmm, there are 20 current users and I now I can't log in as
> my
> > > dba account to perform/fix xxx).
> > >
> > > -----------------------------------------
> > > Alan Davey
> > > Senior Analyst/Project Leader
> > > Oracle 9i OCA; 3/4 OCP
> > > w) 973.267.5990 x458
> > > w) 212.295.3458
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: Juan Cachito Reyes Pacheco [mailto:jreyes@xxxxxxxxxxxxxxxx]
> > > Sent: Thursday, March 18, 2004 12:00 PM
> > > To: oracle-l@xxxxxxxxxxxxx
> > > Subject: Re: How to restrict the user connections
> > >
> > >
> > > Hi Alan,
> > >
> > > LICENSE_MAX_SESSIONS specifies the maximum number of concurrent user
> > > sessions allowed. When this limit is reached, only users with the
> > RESTRICTED
> > > SESSION privilege can connect to the database. Users who are not able
to
> > > connect receive a warning message indicating that the system has
reached
> > > maximum capacity.
> > >
> > > LICENSE_MAX_USERS specifies the maximum number of users you can create
> in
> > > the database. When you reach this limit, you cannot create more users.
> You
> > > can, however, increase the limit.
> > >
> > > I think none of both are a solution, because license max user limits
the
> > > number of user created, and license max sessions limits the maximum
> number
> > > of concurrent user sessions allowed.
> > >
> > > I think you 'll have to use a trigger.
> > >
> > > ----- Original Message ----- 
> > > From: "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
> > > To: <oracle-l@xxxxxxxxxxxxx>
> > > Sent: Thursday, March 18, 2004 11:29 AM
> > > Subject: RE: How to restrict the user connections
> > >
> > >
> > > > Hi,
> > > >
> > > > I replied to your email too quickly.
> > > >
> > > > You can also set license_max_sessions dynamically via alter system.
> So
> > if
> > > > you only need to set it for certain times, then that is another
> option.
> > > >
> > > > HTH,
> > > >
> > > > -----------------------------------------
> > > > Alan Davey
> > > > Senior Analyst/Project Leader
> > > > Oracle 9i OCA; 3/4 OCP
> > > > w) 973.267.5990 x458
> > > > w) 212.295.3458
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: Davey, Alan
> > > > Sent: Thursday, March 18, 2004 9:40 AM
> > > > To: 'oracle-l@xxxxxxxxxxxxx'
> > > > Subject: RE: How to restrict the user connections
> > > >
> > > >
> > > > Hi,
> > > >
> > > > Create a logon trigger that checks the number of sessions, and if
more
> > > than
> > > > 20 exists, it will randomly select one and kill it.  The idea being
> that
> > > > that user has probably been on long enough and needs to learn to
share
> > > > resources.
> > > >
> > > > Alternatively, you can set license_max_sessions in your init.ora
file.
> > > >
> > > > -----------------------------------------
> > > > Alan Davey
> > > > Senior Analyst/Project Leader
> > > > Oracle 9i OCA; 3/4 OCP
> > > > w) 973.267.5990 x458
> > > > w) 212.295.3458
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: Senthil Kumar [mailto:senthilkumard@xxxxxxxxxxxxxxx]
> > > > Sent: Thursday, March 18, 2004 9:28 AM
> > > > To: oracle-l@xxxxxxxxxxxxx
> > > > Subject: How to restrict the user connections
> > > >
> > > >
> > > > Hi List,
> > > >
> > > > How do I restrict the number of user connections. I want only 20
users
> > to
> > > > access the database on a particular time.
> > > >
> > > > How do I do this.
> > > >
> > > > TIA
> > > > Senthil.
> > > >
> > > >
> > > > ----------------------------------------------------------------
> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > > ----------------------------------------------------------------
> > > > To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> > > > put 'unsubscribe' in the subject line.
> > > > --
> > > > Archives are at //www.freelists.org/archives/oracle-l/
> > > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > > -----------------------------------------------------------------
> > > >
> > > >
> > > > "This information in this e-mail is intended solely for the
addressee
> > and
> > > > may contain information which is confidential or privileged.  Access
> to
> > > this
> > > > e-mail by anyone else is unauthorized.  If you are not the intended
> > > > recipient, or believe that you have received this communication in
> > error,
> > > > please do not print, copy, retransmit, disseminate, or otherwise use
> the
> > > > information. Also, please notify the sender that you have received
> this
> > > > e-mail in error, and delete the copy you received."
> > > >
> > > >
> > > > ----------------------------------------------------------------
> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > > ----------------------------------------------------------------
> > > > To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> > > > put 'unsubscribe' in the subject line.
> > > > --
> > > > Archives are at //www.freelists.org/archives/oracle-l/
> > > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > > -----------------------------------------------------------------
> > > >
> > > >
> > > > "This information in this e-mail is intended solely for the
addressee
> > and
> > > > may contain information which is confidential or privileged.  Access
> to
> > > this
> > > > e-mail by anyone else is unauthorized.  If you are not the intended
> > > > recipient, or believe that you have received this communication in
> > error,
> > > > please do not print, copy, retransmit, disseminate, or otherwise use
> the
> > > > information. Also, please notify the sender that you have received
> this
> > > > e-mail in error, and delete the copy you received."
> > > >
> > > >
> > > > ----------------------------------------------------------------
> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > > ----------------------------------------------------------------
> > > > To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> > > > put 'unsubscribe' in the subject line.
> > > > --
> > > > Archives are at //www.freelists.org/archives/oracle-l/
> > > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > > -----------------------------------------------------------------
> > >
> > >
> > > ----------------------------------------------------------------
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > ----------------------------------------------------------------
> > > To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> > > put 'unsubscribe' in the subject line.
> > > --
> > > Archives are at //www.freelists.org/archives/oracle-l/
> > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > -----------------------------------------------------------------
> > >
> > >
> > > "This information in this e-mail is intended solely for the addressee
> and
> > > may contain information which is confidential or privileged.  Access
to
> > this
> > > e-mail by anyone else is unauthorized.  If you are not the intended
> > > recipient, or believe that you have received this communication in
> error,
> > > please do not print, copy, retransmit, disseminate, or otherwise use
the
> > > information. Also, please notify the sender that you have received
this
> > > e-mail in error, and delete the copy you received."
> > >
> > >
> > > ----------------------------------------------------------------
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > ----------------------------------------------------------------
> > > To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> > > put 'unsubscribe' in the subject line.
> > > --
> > > Archives are at //www.freelists.org/archives/oracle-l/
> > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > -----------------------------------------------------------------
> > >
> >
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
> >
> > "This information in this e-mail is intended solely for the addressee
and
> > may contain information which is confidential or privileged.  Access to
> this
> > e-mail by anyone else is unauthorized.  If you are not the intended
> > recipient, or believe that you have received this communication in
error,
> > please do not print, copy, retransmit, disseminate, or otherwise use the
> > information. Also, please notify the sender that you have received this
> > e-mail in error, and delete the copy you received."
> >
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
> "This information in this e-mail is intended solely for the addressee and
> may contain information which is confidential or privileged.  Access to
this
> e-mail by anyone else is unauthorized.  If you are not the intended
> recipient, or believe that you have received this communication in error,
> please do not print, copy, retransmit, disseminate, or otherwise use the
> information. Also, please notify the sender that you have received this
> e-mail in error, and delete the copy you received."
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: