René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

April 17, 2005: On profiles

A profile is a named collection of database resource limits. Each user can be assigned a previously created profile and Oracle will then prevent the user from exceeding one of the limits within that profile. There are two types of ressources: physical limits and password related limits. A list of these resources can be found here. In order to enforce kernel limits, resource_limit must be set to true.
In the following example, the three ressources sessions per user, idle time and failed login attempts will be limited for a user.
First, I check if resource_limit is set to true: 
SQL> show parameter resource_limit
resource_limit                       boolean     FALSE
Nope, it's set to false. So, let's change it. I use an spfile, so I can specify the scope=both option: 
SQL> alter system set resource_limit=true scope=both;
SQL> show parameter resource_limit
resource_limit                       boolean     TRUE
The profile can now be created: 
create profile example_profile limit
  sessions_per_user     1
  idle_time             1
  failed_login_attempts 3;
With this command, I have created a profile that can now be assigned to a user. The user can then only have one concurrent session, can only be idly connected for one minute and cannot more than three times try to logon with a wrong password without giving his correct password.
The assigned profile for a user can be queried with dba_users: 
SQL> select profile from dba_users where username = 'TEST_USER';
EXAMPLE_PROFILE
The limits for profiles can be found through dba_profiles: 
SQL> select resource_name, limit from dba_profiles where profile = 'EXAMPLE_PROFILE';
COMPOSITE_LIMIT                  DEFAULT
SESSIONS_PER_USER                1
CPU_PER_SESSION                  DEFAULT
CPU_PER_CALL                     DEFAULT
LOGICAL_READS_PER_SESSION        DEFAULT
LOGICAL_READS_PER_CALL           DEFAULT
IDLE_TIME                        1
CONNECT_TIME                     DEFAULT
PRIVATE_SGA                      DEFAULT
FAILED_LOGIN_ATTEMPTS            3
PASSWORD_LIFE_TIME               DEFAULT
PASSWORD_REUSE_TIME              DEFAULT
PASSWORD_REUSE_MAX               DEFAULT
PASSWORD_VERIFY_FUNCTION         DEFAULT
PASSWORD_LOCK_TIME               DEFAULT
PASSWORD_GRACE_TIME              DEFAULT
It's time to create a user and assign the profile to him. The create user SQL statement allows to do that in one go: 
create user          test_user
identified by        test_user
default tablespace   data
temporary tablespace temp
profile              example_profile;
Off course, test_user needs the create session privilege in order to connect to the database. So, I give that to him, too: 
SQL> grant create session to test_user;

First test

In the first test, I check what happens if test_user stays inactive while one minute: 
$ sqlplus test_user/test_user

SQL*Plus: Release 10.1.0.2.0 - Production on Sun Apr 17 14:30:36 2005

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Now, in another session (and as another user), I determine the status of TEST_USER's session: 
SQL> select status from v$session where username = 'TEST_USER';
INACTIVE
I see that the session is inactive after approx 7 seconds
I'd expect now this status to change after a minute. However, Oracle does not take action after exactly one minute (as that would eat too much system resources), so, the status is still INACTIVE. So, I wait a bit longer and execute the query again: 
RENE> select status from v$session where username = 'TEST_USER';
SNIPED
The session of TEST_USER has become sniped. TEST_USER doesn't get disconnected immediatly, however, in order for him to receive an error message explaining what happened to him when he executes his next statement: 
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again


ERROR:
ORA-01012: not logged on
Only now is he truly disconnected.

Second test

In the second test, I try to connect as test_user simultaneosly twice. To save space, I don't show the connect of the first session, instead, only the behaviour of the seond is shown: 
$ sqlplus test_user/test_user

SQL*Plus: Release 10.1.0.2.0 - Production on Sun Apr 17 14:31:49 2005

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

ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

Third test

The third test examines what happens if test_user repeatedly gives a wrong password. 
$ sqlplus -l test_user/wrong_password

SQL*Plus: Release 10.1.0.2.0 - Production on Sun Apr 17 15:37:12 2005

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

ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
Second attempt: 
$ sqlplus -l test_user/wrong_password

SQL*Plus: Release 10.1.0.2.0 - Production on Sun Apr 17 15:37:12 2005

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

ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
Third attempt: 
$ sqlplus -l test_user/another_wrong_password

SQL*Plus: Release 10.1.0.2.0 - Production on Sun Apr 17 15:38:36 2005

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

ERROR:
ORA-28000: the account is locked


SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
The account needs now be unlocked: 
SQL>alter user test_user account unlock;

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.