Profile in Oracle

Small article on Oracle Profile and how it can be used to limit database resources.

In database world, Oracle is one of the most popular and well known databases. Resource control of a particular user across the database is very important aspect to run a successful database. In this small article we’ll see what role ‘Oracle Profile’ plays to make user control easier in a reference with Resource.

The way, to control user privileges we can create a role and assign it to users, we can use the concept of ‘Oracle Profile’ to control resources that can be consumed by any particular user in the database.

1. So let’s Invoke SQL*PLUS and create an ‘Oracle Profile’.

SQL> CREATE PROFILE dem_profile LIMIT
SESSIONS_PER_USER 3
IDLE_TIME 5
CONNECT_TIME 30
CPU_PER_CALL 7500
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1;

Profile created.

The user assigned with this profile will have following limits.

• SESSIONS_PER_USER 3 : User can’t have more than 3 simultaneous sessions open at a time.
• IDLE_TIME 5 : User’s sessions will be terminated internally if it’s idle for more than 5 minutes.
• CONNECT_TIME 30 : User’s session will have an age of 30 minutes. Even if the user is continuously active, in 30 minutes his session will be terminated.
• CPU_PER_CALL 7500 : User’s SQL or PL/SQL statement can not consume more than 75 sesconds of CPU time.
• FAILED_LOGIN_ATTEMPTS 3 : User’s account will be locked after 3 failed login attempts.
• PASSWORD_LOCK_TIME 1 : User’s account will be locked for 1 day(s) if he reached defined FAILED_LOGIN_ATTEMPTS.

---

2. Let’s create a test user named ‘DEM_USER’.

SQL> CREATE USER DEM_USER
IDENTIFIED BY DEM_PASSWORD
ACCOUNT UNLOCK;

User created.

And grant basic privileges.

SQL> GRANT CONNECT, CREATE SESSION TO DEM_USER;

Grant succeeded.

---

3. Assign created profile to user.

SQL> ALTER USER DEM_USER PROFILE dem_profile;

User altered.

Note : To Oracle Profile take effect, RESOURCE_LIMIT initialization parameter should be ‘TRUE’.

---

4. Check if RESOURCE_LIMIT is true or not.

SQL> show parameter RESOURCE_LIMIT

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

It’s false by default, let’s make it true.

SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

System altered.

Let’s check again.

SQL> show parameter RESOURCE_LIMIT

NAME           TYPE     VALUE
-------------- -------- -------
resource_limit boolean  TRUE

---

5. Let’s now check, if the profile ‘DEM_PROFILE’ takes effect in limit resource of the user ‘DEM_USER’.

1. I have 3 sessions already open for the user ‘DEM_USER’. Let’s try opening 4th one.

[oracle@dbnode1 ~]$ sqlplus DEM_USER/DEM_PASSWORD

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 2 04:53:09 2018

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

ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

Oracle is not allowing the user to create another session as its exceeding SESSIONS_PER_USER parameter defined in the profile.

---

2. My session was idle for more than 5 minutes. Let’s see what happens when I fire a query.

SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again

Oracle has terminted the session internally because it exceeded idle time defined for IDLE_TIME in the profile.

---

3. I entered wrong password thrice. Let’s see what happens when I try to login 4th time.

[oracle@dbnode1 ~]$ sqlplus DEM_USER/DEM_WRONG_PASSWORD

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 2 05:11:22 2018

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

ERROR:
ORA-28000: the account is locked

Oops, the account is now locked as per the FAILED_LOGIN_ATTEMPTS parameter and will be locked till period defined in PASSWORD_LOCK_TIME parameter.

---

So these are some of the limitations which can be made on the user with the help of Oracle Profile. DBA_PROFILES data dictionary view can be queried to check Profiles and their limits.

SQL> SELECT * FROM DBA_PROFILES WHERE PROFILE = 'DEM_PROFILE' AND LIMIT != 'DEFAULT';

PROFILE     RESOURCE_NAME          RESOURCE LIMIT
----------- ---------------------  -------- -------
DEM_PROFILE SESSIONS_PER_USER      KERNEL   3
DEM_PROFILE CPU_PER_SESSION        KERNEL   7500
DEM_PROFILE IDLE_TIME              KERNEL   5
DEM_PROFILE CONNECT_TIME           KERNEL   30
DEM_PROFILE FAILED_LOGIN_ATTEMPTS  PASSWORD 3
DEM_PROFILE PASSWORD_LOCK_TIME     PASSWORD 1

6 rows selected.

So these are the resources which we’re limiting using ‘Oracle Profile’.

Peace 🙂