|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
First, I check if resource_limit is set to true:
Nope, it's set to false. So, let's change it. I use an spfile, so I can specify the
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;
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.
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
The third test examines what happens if test_user repeatedly gives a wrong password.
$ 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.