Look in:

Web oracle-core-dba.blogspot.com

Monday, January 21, 2008

Limit user sessions in Oracle

How can I limit the number of times a single user can connect to the database at any one time ?

We can do this by limiting the number of sessions they are allowed to have.
Easy as always to do in Oracle. First we allow resource limits by setting the resource_limit to true, then we simply create a profile and assign that to the user. The profile can set the limit of how many session a user is allowed to have.

A little demonstration:

D:\>sqlplus sys/sys as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Jan 21 10:46:28 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter resource_limit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE

SQL> alter system set RESOURCE_LIMIT=true scope=both;

System altered.

SQL> create profile sesslimit limit sessions_per_user 1;

Profile created.

SQL> create user sree identified by sree123
default tablespace sbdata profile sesslimit;

User created.

SQL> grant create session to sree;

Grant succeeded.

If you want to add this profile to existing user then:

SQL> alter user sree profile sesslimit;

SQL> connect sree/sree123
Connected.

-- I'll start second session in another terminal.

SQL> connect sree/sree123
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

==========

SQL> SELECT DISTINCT resource_name, limit
FROM dba_profiles
ORDER BY resource_name;

SQL> drop profile sesslimit cascade;

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production With the Partitioning, OLAP and Data Mining options

No comments: