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 18.104.22.168.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 22.214.171.124.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’.