[Oracle] Managing Oracle Database user profiles

Hi all, in this tutorial we are going to explore a bit how Oracle profiles can be used.

Normally, each user or schema (the oracle name for user) has an associated profile, even the SYS account. Let’s take this one as example. Running the following query we get the associated profile:

select profile from dba_users where username = 'SYS';

Normally, SYS standard profile should be “default“. With the following query we are able to get all the attributes of the “default” profile:

select DBMS_METADATA.GET_DDL('PROFILE','DEFAULT') from dual;

RESOURCE_NAME                    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            UNLIMITED
PASSWORD_LIFE_TIME               UNLIMITED
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               UNLIMITED
PASSWORD_GRACE_TIME              UNLIMITED
INACTIVE_ACCOUNT_TIME            UNLIMITED

Let’s see the most useful options:

• SESSIONS_PER_USER: it specifies the maximun number of sessions the user can actually open. This is very useful in databases shared by multiple applications, in order to not overload them
• IDLE_TIME: specifies the maximum number of minutes the user sessions can be idle. After that, the session status becomes SNIPED and once the application tries to connect again, an error will be prompt and the connection will be closed. If you want to close the SNIPED sessions before the application tries to connect, just implement a JOB that kills all the session with the status value in v$session is SNIPED. Useful when application does not manage well the disconnection of a session
• CONNECT_TIME: specifies the maximum number of minutes the user sessions can be currently active and executing queries. This is useful when you want to forcibly stoo long running queries whose execution time is normally lower. Careful when using this parameter, it must be tuned in order to not disconnect legitimate sessions
• all PASSWORD***** attributes: as the name of these attributes suggests, they are used to enforce some basic security when choosing a password. They are very useful with external users, because end-users are the most weak point of an infrastructure
• INACTIVE_ACCOUNT_TIME: this is new in Oracle 12 and it let you specify a number of days after that the account is locked if it has not been accessed. It is useful in a database with a large number of non-application users (external) in order to purge old users not accessed any more. This is not normally used with application users

In order to change one of these attributes you can execute the following query:

alter profile DEFAULT limit IDLE_TIME 60;

This is actually force a session to become SNIPED if it has been inactive for one hour (60 minutes).

You can also create a new profile and add it to a new user. Just issue the following commands:

create profile MYPROFILE 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                        60
CONNECT_TIME                     UNLIMITED
PRIVATE_SGA                      UNLIMITED
FAILED_LOGIN_ATTEMPTS            UNLIMITED
PASSWORD_LIFE_TIME               UNLIMITED
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               UNLIMITED
PASSWORD_GRACE_TIME              UNLIMITED
INACTIVE_ACCOUNT_TIME            UNLIMITED
;

alter user MYUSER profile MYPROFILE;

This actually creates the profile MYPROFILE and assigns it to MYUSER.

Hope that it was clear, see you in the next tutorial!