I tested this on an Oracle 11.1 database.
Oracle
profiles control how certain database resources are allocated to a user
session. They also define some security rules. When you create a user,
it is assigned a profile and, if you do not specify it explicitly, the DEFAULT profile will be used:
SQL> grant create session to andrew
2 identified by reid
3 /
Grant succeeded.
SQL> select profile from dba_users
2 where username = 'ANDREW'
3 /
PROFILE
------------------------------
DEFAULT
SQL>
You can look at the limits defined in a profile by querying DBA_PROFILES:
SQL> select profile, resource_name, limit
2 from dba_profiles
3 where profile = 'DEFAULT'
4 and resource_name = 'SESSIONS_PER_USER'
5 /
PROFILE RESOURCE_NAME LIMIT
---------- -------------------- ----------
DEFAULT SESSIONS_PER_USER UNLIMITED
SQL>
The SESSIONS_PER_USER resource is set to UNLIMITED so a user with the DEFAULT
profile can have as many simultaneous sessions as he wishes. What can
you do if you do not like this? You can create a new profile with a
different SESSIONS_PER_USER limit and assign it to the user. I will demonstrate this in a future post. Alternatively you can alter the DEFAULT profile, as shown below:
SQL> alter profile default
2 limit sessions_per_user 3
3 /
Profile altered.
SQL> select profile, resource_name, limit
2 from dba_profiles
3 where profile = 'DEFAULT'
4 and resource_name = 'SESSIONS_PER_USER'
5 /
PROFILE RESOURCE_NAME LIMIT
---------- -------------------- ----------
DEFAULT SESSIONS_PER_USER 3
SQL>
You also need to set RESOURCE_LIMIT to TRUE otherwise Oracle does not check limits in a user's profile at all:
SQL> alter system set resource_limit = true
2 /
System altered.
SQL>
The
screen print below shows how this works. As usual, click on the image
to enlarge it and bring it into focus if necessary. If that doesn't work, use your browser's zoom function. Three simultaneous
connections have been made to the database. A fourth connection is then
attempted but fails with an ORA-02391:
No comments:
Post a Comment