Simple usage of a user profile to limit concurrent logins

We had an issue in a development database where a group of developers were not using a connection manager, but trying to manage their own connections. This did not end well.

To limit our exposure and the impact on other application users, we implemented a simple profile for the problematic user. A simple test case, where the names and values are changed, is shown below.

SQL> create profile foo limit sessions_per_user 5;

Profile created.

SQL> alter user foo_user profile foo;

User altered.

SQL> alter system set resource_limit = true;

System altered.

SQL> 

We then use the python code below to test.

#!/home/oracle/local/bin/python
import cx_Oracle, time, sys, thread
from threading import Thread

#------------------------------------------------------------------

class getTxns(Thread):
   def __init__ (self):
      Thread.__init__(self)

   def run(self):
     con = cx_Oracle.connect("foo_user","foo_user")
     print "got connection"
     time.sleep(10)

#------------------------------------------------------------------

threadList = []

for _ in range(7):
  current = getTxns()
  threadList.append(current)
  current.start()


for thisThread in threadList:
  thisThread.join()
print "Finished"

The output of a test where we try to open seven connections is shown below…

emgrid01:oracle:emprod1:/home/oracle>./foo_user.py
got connection
got connection
got connection
got connection
got connection
Exception in thread Thread-2:
Traceback (most recent call last):
  File "/home/oracle/local/lib/python2.6/threading.py", line 525, in __bootstrap_inner
    self.run()
  File "./foo_user.py", line 12, in run
    con = cx_Oracle.connect("foo_user","foo_user")
DatabaseError: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit


Exception in thread Thread-7:
Traceback (most recent call last):
  File "/home/oracle/local/lib/python2.6/threading.py", line 525, in __bootstrap_inner
    self.run()
  File "./foo_user.py", line 12, in run
    con = cx_Oracle.connect("foo_user","foo_user")
DatabaseError: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit