Oracle RDBMS - How to control connection count or manage db connection in oracle database using DB user profile

 

Lab Objective: How to control connection count or manage db connection in oracle database using DB user profile

reference url: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-PROFILE.html

Steps:

1. Examine the existing profile to which your candidate user is assiged and its limit

set lines 1200 pages 40000 colsep , time on timing on trim on trims on

col username for a20

col profile for a30

col INITIAL_RSRC_CONSUMER_GROUP for a30

col last_login for a40

col profile for a40

col LIMIT for a40

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

select username

,account_Status

,created

,last_login

,profile

,INITIAL_RSRC_CONSUMER_GROUP

from dba_users

where oracle_maintained='N'

order by username;

select *

from dba_profiles

where profile='DEFAULT'

order by resource_name;

snippet from reference:

-- Ideally you would see the IDLE_TIME parameter set to UNLIMITED, when you update this value to some numeral, then the session staying in idle state is expected to be terminated after the set number.

-- The client will get to know that the session is terminated only when they try run the command again

Notice here there is one more limit INACTIVE_ACCOUNT_TIME which looks for the user last login and locks it as per the setting.

Output:

12:30:10 SQL> select username ,account_Status ,created ,last_login ,profile ,INITIAL_RSRC_CONSUMER_GROUP from dba_users where oracle_maintained='N' order by username;12:30:14   2  12:30:14   3  12:30:14   4  12:30:14   5  12:30:14   6  12:30:14   7  12:30:14   8  12:30:14   9 USERNAME            ,ACCOUNT_STATUS                  ,CREATED             ,LAST_LOGIN                              ,PROFILE                                 ,INITIAL_RSRC_CONSUMER_GROUP --------------------,--------------------------------,--------------------,----------------------------------------,----------------------------------------,------------------------------ C##GGADMIN          ,OPEN                            ,13/JUN/2023 21:50:44,13-JUN-23 10.14.52.000000000 PM +01:00  ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP INVENTORYADM        ,OPEN                            ,19/APR/2023 20:49:24,20-APR-24 11.02.05.000000000 AM +01:00  ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP ORDS_METADATA       ,OPEN                            ,22/APR/2023 20:18:05,                                        ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP ORDS_PUBLIC_USER    ,OPEN                            ,22/APR/2023 20:18:03,02-MAY-23 11.02.38.000000000 PM +01:00  ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP SCH_HELLOWORLD      ,OPEN                            ,02/MAY/2023 22:16:26,                                        ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP Elapsed: 00:00:00.01 12:30:15 SQL> select * from dba_profiles where profile='DEFAULT' order by resource_name;12:31:03   2  12:31:03   3  12:31:03   4 PROFILE                                 ,RESOURCE_NAME                   ,RESOURCE,LIMIT                                   ,COM,INH,IMP ----------------------------------------,--------------------------------,--------,----------------------------------------,---,---,--- DEFAULT                                 ,COMPOSITE_LIMIT                 ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,CONNECT_TIME                    ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,CPU_PER_CALL                    ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,CPU_PER_SESSION                 ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,FAILED_LOGIN_ATTEMPTS           ,PASSWORD,10                                      ,NO ,NO ,NO DEFAULT                                 ,IDLE_TIME                       ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,INACTIVE_ACCOUNT_TIME           ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,LOGICAL_READS_PER_CALL          ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,LOGICAL_READS_PER_SESSION       ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,PASSWORD_GRACE_TIME             ,PASSWORD,7                                       ,NO ,NO ,NO DEFAULT                                 ,PASSWORD_LIFE_TIME              ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,PASSWORD_LOCK_TIME              ,PASSWORD,1                                       ,NO ,NO ,NO DEFAULT                                 ,PASSWORD_REUSE_MAX              ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,PASSWORD_REUSE_TIME             ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,PASSWORD_VERIFY_FUNCTION        ,PASSWORD,NULL                                    ,NO ,NO ,NO DEFAULT                                 ,PRIVATE_SGA                     ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,SESSIONS_PER_USER               ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO 17 rows selected. Elapsed: 00:00:00.01 12:31:04 SQL>

2. Remember updating the existing profile is going to change the profile limit for all the other users who are assigned this profile, so it is better to create a new profile as a clone of existing and update the new profile with the setting needed. 

set long 20000

select dbms_metadata.get_ddl('PROFILE','DEFAULT') from dual;

modify alter statement with create statement...

   CREATE PROFILE "PROF_IDLECNTL"

    LIMIT

         COMPOSITE_LIMIT UNLIMITED

         SESSIONS_PER_USER UNLIMITED

         CPU_PER_SESSION UNLIMITED

         CPU_PER_CALL UNLIMITED

         LOGICAL_READS_PER_SESSION UNLIMITED

         LOGICAL_READS_PER_CALL UNLIMITED

         IDLE_TIME 2

         CONNECT_TIME UNLIMITED

         PRIVATE_SGA UNLIMITED

         FAILED_LOGIN_ATTEMPTS 10

         PASSWORD_LIFE_TIME UNLIMITED

         PASSWORD_REUSE_TIME UNLIMITED

         PASSWORD_REUSE_MAX UNLIMITED

         PASSWORD_VERIFY_FUNCTION NULL

         PASSWORD_LOCK_TIME 86400/86400

         PASSWORD_GRACE_TIME 604800/86400

         INACTIVE_ACCOUNT_TIME UNLIMITED;

select * from dba_profiles where profile='&profname' order by resource_name;

Output:

1* select dbms_metadata.get_ddl('PROFILE','DEFAULT') from dual 12:35:05 SQL> / DBMS_METADATA.GET_DDL('PROFILE','DEFAULT') --------------------------------------------------------------------------------    ALTER PROFILE "DEFAULT"     LIMIT          COMPOSITE_LIMIT UNLIMITED          SESSIONS_PER_USER UNLIMITED          CPU_PER_SESSION UNLIMITED          CPU_PER_CALL UNLIMITED          LOGICAL_READS_PER_SESSION UNLIMITED          LOGICAL_READS_PER_CALL UNLIMITED          IDLE_TIME UNLIMITED          CONNECT_TIME UNLIMITED          PRIVATE_SGA UNLIMITED          FAILED_LOGIN_ATTEMPTS 10          PASSWORD_LIFE_TIME UNLIMITED          PASSWORD_REUSE_TIME UNLIMITED          PASSWORD_REUSE_MAX UNLIMITED          PASSWORD_VERIFY_FUNCTION NULL          PASSWORD_LOCK_TIME 86400/86400          PASSWORD_GRACE_TIME 604800/86400          INACTIVE_ACCOUNT_TIME UNLIMITED Elapsed: 00:00:00.06 12:35:06 SQL> 12:35:06 SQL>    CREATE PROFILE "PROF_IDLECNTL" 12:36:31   2      LIMIT          COMPOSITE_LIMIT UNLIMITED          SESSIONS_PER_USER UNLIMITED          CPU_PER_SESSION UNLIMITED          CPU_PER_CALL UNLIMITED          LOGICAL_READS_PER_SESSION UNLIMITED          LOGICAL_READS_PER_CALL UNLIMITED 12:36:31   3  12:36:31   4  12:36:31   5  12:36:31   6  12:36:31   7  12:36:31   8  12:36:31   9           IDLE_TIME 2          CONNECT_TIME UNLIMITED          PRIVATE_SGA UNLIMITED          FAILED_LOGIN_ATTEMPTS 10          PASSWORD_LIFE_TIME UNLIMITED          PASSWORD_REUSE_TIME UNLIMITED          PASSWORD_REUSE_MAX UNLIMITED          PASSWORD_VERIFY_FUNCTION NULL          PASSWORD_LOCK_TIME 86400/86400          PASSWORD_GRACE_TIME 604800/86400          INACTIVE_ACCOUNT_TIME UNLIMITED;12:36:31  10  12:36:31  11  12:36:31  12  12:36:31  13  12:36:31  14  12:36:31  15  12:36:31  16  12:36:31  17  12:36:31  18  12:36:31  19 Profile created. Elapsed: 00:00:00.01 12:36:31 SQL> select * from dba_profiles where profile='&profname' order by resource_name; Enter value for profname: PROF_IDLECNTL old   1: select * from dba_profiles where profile='&profname' order by resource_name new   1: select * from dba_profiles where profile='PROF_IDLECNTL' order by resource_name PROFILE                                 ,RESOURCE_NAME                   ,RESOURCE,LIMIT                                   ,COM,INH,IMP ----------------------------------------,--------------------------------,--------,----------------------------------------,---,---,--- PROF_IDLECNTL                           ,COMPOSITE_LIMIT                 ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,CONNECT_TIME                    ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,CPU_PER_CALL                    ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,CPU_PER_SESSION                 ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,FAILED_LOGIN_ATTEMPTS           ,PASSWORD,10                                      ,NO ,NO ,NO PROF_IDLECNTL                           ,IDLE_TIME                       ,KERNEL  ,2                                       ,NO ,NO ,NO PROF_IDLECNTL                           ,INACTIVE_ACCOUNT_TIME           ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,LOGICAL_READS_PER_CALL          ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,LOGICAL_READS_PER_SESSION       ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,PASSWORD_GRACE_TIME             ,PASSWORD,7                                       ,NO ,NO ,NO PROF_IDLECNTL                           ,PASSWORD_LIFE_TIME              ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,PASSWORD_LOCK_TIME              ,PASSWORD,1                                       ,NO ,NO ,NO PROF_IDLECNTL                           ,PASSWORD_REUSE_MAX              ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,PASSWORD_REUSE_TIME             ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,PASSWORD_VERIFY_FUNCTION        ,PASSWORD,NULL                                    ,NO ,NO ,NO PROF_IDLECNTL                           ,PRIVATE_SGA                     ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,SESSIONS_PER_USER               ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO 17 rows selected.

3. Assign the profile to the candidate user

alter user INVENTORYADM profile &profname;

Output:

12:38:01 SQL> alter user INVENTORYADM profile &profname; Enter value for profname: PROF_IDLECNTL old   1: alter user INVENTORYADM profile &profname new   1: alter user INVENTORYADM profile PROF_IDLECNTL User altered. Elapsed: 00:00:00.00 12:38:11 SQL> 12:38:11 SQL> select username ,account_Status ,created ,last_login ,profile ,INITIAL_RSRC_CONSUMER_GROUP from dba_users where oracle_maintained='N' order by username; 12:38:58   2  12:38:58   3  12:38:58   4  12:38:58   5  12:38:58   6  12:38:58   7  12:38:58   8  12:38:58   9 USERNAME            ,ACCOUNT_STATUS                  ,CREATED             ,LAST_LOGIN                              ,PROFILE                                 ,INITIAL_RSRC_CONSUMER_GROUP --------------------,--------------------------------,--------------------,----------------------------------------,----------------------------------------,------------------------------ C##GGADMIN          ,OPEN                            ,13/JUN/2023 21:50:44,13-JUN-23 10.14.52.000000000 PM +01:00  ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP INVENTORYADM        ,OPEN                            ,19/APR/2023 20:49:24,20-APR-24 11.02.05.000000000 AM +01:00  ,PROF_IDLECNTL                           ,DEFAULT_CONSUMER_GROUP ORDS_METADATA       ,OPEN                            ,22/APR/2023 20:18:05,                                        ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP ORDS_PUBLIC_USER    ,OPEN                            ,22/APR/2023 20:18:03,02-MAY-23 11.02.38.000000000 PM +01:00  ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP SCH_HELLOWORLD      ,OPEN                            ,02/MAY/2023 22:16:26,                                        ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP Elapsed: 00:00:00.01 12:38:58 SQL>

4. Test if the user session gets terminated

-> login

> use dbeaver to connect to the user

-> run a test query

> open sql editor

SELECT object_name FROM user_objects;

-> check in v$session the session details

select sid,serial#,username,status,state,sql_id,program,logon_time from v$session where username='&usr';

-> leave the session for "2 mins" idle time

-> see if the session is terminated

a. check v$session

select sid,serial#,username,status,state,sql_id,program,logon_time from v$session where username='&usr';

b. try query from the program where user is connected for test and see the response

SELECT object_name FROM user_objects;

Output:

12:38:58 SQL> select sid,serial#,username,status,state,sql_id,program,logon_time from v$session where username='&usr'; Enter value for usr: INVENTORYADM old   1: select sid,serial#,username,status,state,sql_id,program,logon_time from v$session where username='&usr' new   1: select sid,serial#,username,status,state,sql_id,program,logon_time from v$session where username='INVENTORYADM'        SID,   SERIAL#,USERNAME            ,STATUS  ,STATE              ,SQL_ID       ,PROGRAM                                         ,LOGON_TIME ----------,----------,--------------------,--------,-------------------,-------------,------------------------------------------------,--------------------         63,     48434,INVENTORYADM        ,INACTIVE,WAITING            ,             ,DBeaver 23?0?1 ? Metadata                       ,20/APR/2024 12:39:34        112,     46550,INVENTORYADM        ,INACTIVE,WAITING            ,             ,DBeaver 23?0?1 ? Main                           ,20/APR/2024 12:39:33        127,     40145,INVENTORYADM        ,INACTIVE,WAITING            ,5rsm4y10jd4p2,DBeaver 23?0?1 ? SQLEditor ?Script?sql?         ,20/APR/2024 12:39:34 Elapsed: 00:00:00.00 12:40:27 SQL> select sql_text from v$sqltext where sql_id='5rsm4y10jd4p2' order by piece; SQL_TEXT ---------------------------------------------------------------- BEGIN DBMS_OUTPUT.GET_LINE(:1 , :2 ); END; Elapsed: 00:00:00.02 12:40:45 SQL> select sid,serial#,username,status,state,sql_id,program,logon_time,prev_Sql_id from v$session where username='&usr'; Enter value for usr: INVENTORYADM old   1: select sid,serial#,username,status,state,sql_id,program,logon_time,prev_Sql_id from v$session where username='&usr' new   1: select sid,serial#,username,status,state,sql_id,program,logon_time,prev_Sql_id from v$session where username='INVENTORYADM'        SID,   SERIAL#,USERNAME            ,STATUS  ,STATE              ,SQL_ID       ,PROGRAM                                         ,LOGON_TIME          ,PREV_SQL_ID ----------,----------,--------------------,--------,-------------------,-------------,------------------------------------------------,--------------------,-------------         63,     48434,INVENTORYADM        ,INACTIVE,WAITING            ,             ,DBeaver 23?0?1 ? Metadata                       ,20/APR/2024 12:39:34,73b7u0bsntwxp        112,     46550,INVENTORYADM        ,INACTIVE,WAITING            ,             ,DBeaver 23?0?1 ? Main                           ,20/APR/2024 12:39:33,5qus23w3vf7ry        127,     40145,INVENTORYADM        ,INACTIVE,WAITING            ,5rsm4y10jd4p2,DBeaver 23?0?1 ? SQLEditor ?Script?sql?         ,20/APR/2024 12:39:34,5rsm4y10jd4p2 Elapsed: 00:00:00.02 12:41:02 SQL> select sql_text from v$sqltext where sql_id='5qus23w3vf7ry' order by piece; SQL_TEXT ---------------------------------------------------------------- SELECT SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA' ) FROM DUAL Elapsed: 00:00:00.00 12:41:13 SQL> select sql_text from v$sqltext where sql_id='73b7u0bsntwxp' order by piece; SQL_TEXT ---------------------------------------------------------------- SELECT  O.*, t.TABLE_TYPE_OWNER,t.TABLE_TYPE,t.TABLESPACE_NAME,t .PARTITIONED,t.IOT_TYPE,t.IOT_NAME,t.TEMPORARY,t.SECONDARY,t.NES TED,t.NUM_ROWS FROM ALL_OBJECTS O , ALL_ALL_TABLES t WHERE t.OWN ER(+) = O.OWNER AND t.TABLE_NAME(+) = o.OBJECT_NAME AND O.OWNER= :1  AND O.OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW') AND O.OBJECT_NAME=:2 6 rows selected. Elapsed: 00:00:00.00 12:41:20 SQL> select sid,serial#,username,status,state,sql_id,program,logon_time,prev_Sql_id from v$session where username='INVENTORYADM';        SID,   SERIAL#,USERNAME            ,STATUS  ,STATE              ,SQL_ID       ,PROGRAM                                         ,LOGON_TIME          ,PREV_SQL_ID ----------,----------,--------------------,--------,-------------------,-------------,------------------------------------------------,--------------------,-------------         63,     48434,INVENTORYADM        ,INACTIVE,WAITING            ,             ,DBeaver 23?0?1 ? Metadata                       ,20/APR/2024 12:39:34,73b7u0bsntwxp        112,     46550,INVENTORYADM        ,INACTIVE,WAITING            ,             ,DBeaver 23?0?1 ? Main                           ,20/APR/2024 12:39:33,5qus23w3vf7ry        127,     40145,INVENTORYADM        ,INACTIVE,WAITING            ,5rsm4y10jd4p2,DBeaver 23?0?1 ? SQLEditor ?Script?sql?         ,20/APR/2024 12:39:34,5rsm4y10jd4p2 Elapsed: 00:00:00.01 12:42:15 SQL> / ---> idle for 2 mins ---> session terminated: 12:42:15 SQL> /        SID,   SERIAL#,USERNAME            ,STATUS  ,STATE              ,SQL_ID       ,PROGRAM                                         ,LOGON_TIME          ,PREV_SQL_ID ----------,----------,--------------------,--------,-------------------,-------------,------------------------------------------------,--------------------,-------------         63,     48434,INVENTORYADM        ,KILLED  ,WAITED KNOWN TIME  ,             ,DBeaver 23?0?1 ? Metadata                       ,20/APR/2024 12:39:34,73b7u0bsntwxp        112,     46550,INVENTORYADM        ,KILLED  ,WAITED KNOWN TIME  ,             ,DBeaver 23?0?1 ? Main                           ,20/APR/2024 12:39:33,5qus23w3vf7ry        127,     40145,INVENTORYADM        ,KILLED  ,WAITED KNOWN TIME  ,             ,DBeaver 23?0?1 ? SQLEditor ?Script?sql?         ,20/APR/2024 12:39:34,5rsm4y10jd4p2 Elapsed: 00:00:00.00 12:42:41 SQL> /

---> it stays in killed state

alert log:

2024-04-20 12:42:17.730000 +01:00

KILL SESSION for sid=(63, 48434):

  Reason = profile limit idle_time

  Mode = KILL SOFT -/-/NO_REPLAY

  Requestor = PMON (orapid = 2, ospid = 11375, inst = 1)

  Owner = Process: USER (orapid = 52, ospid = 18254)

  Result = ORA-0

KILL SESSION for sid=(112, 46550):

  Reason = profile limit idle_time

  Mode = KILL SOFT -/-/NO_REPLAY

  Requestor = PMON (orapid = 2, ospid = 11375, inst = 1)

  Owner = Process: USER (orapid = 51, ospid = 18252)

  Result = ORA-0

KILL SESSION for sid=(127, 40145):

  Reason = profile limit idle_time

  Mode = KILL SOFT -/-/NO_REPLAY

  Requestor = PMON (orapid = 2, ospid = 11375, inst = 1)

  Owner = Process: USER (orapid = 53, ospid = 18256)

  Result = ORA-0

Dbeaver screenshot post session termination:

YouTube Video: