Skip to main content

Creating and Assigning User Profile in Oracle



Profiles in Oracle


Profiles are used to limit resources a user can use. Then, they can be assigned to users with alter user ... profile command.

Limitable resources

The following limits can be specified:

Kernel limits:


Maximum concurrent sessions for a user: (sessions_per_user)
CPU time limit per session: (cpu_per_session)
CPU time limit per call: (cpu_per_call)
Call being parse, execute and fetch


Maximum connect time: (connect_time)
The session will be dropped by oracle after specified time.
Maximum idle time (idle_time)


The session will be dropped by oracle after specified time of doing nothing. Long running processes are not idle!
Maximum blocks read per session: (logical_reads_per_session)
Maximum blocks read per call: (logical_reads_per_call)
Maximum amount of SGA: (private_sga)


In order to enforce kernel limits, resource_limit must be set to true.


Password limits:




Maximum failed login attempts: (failed_login_attempts)
Maximum time a password is valid: (password_life_time)
Minimum of different passwords before password can be reused: (password_reuse_max)
Minimum of days before a password can be reused: (password_reuse_time)
Number of days an account is locked after failing to login: (password_lock_time)
Verify function for passwords: (password_verify_function)



Resource Parameters

• SESSIONS_PER_USER
Specify the number of concurrent sessions to which you want to limit the user.
• CPU_PER_SESSION
Specify the CPU time limit for a session, expressed in hundredth of seconds.
• CPU_PER_CALL
Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.
• CONNECT_TIME
Specify the total elapsed time limit for a session, expressed in minutes.
• IDLE_TIME
Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
• LOGICAL_READS_PER_SESSION
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
• LOGICAL_READS_PER_CALL
Specify the permitted the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
• PRIVATE_SGA
Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.
• COMPOSITE_LIMIT
Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.


If a session exceeds one of these limits, Oracle will terminate the session. If there is a logoff trigger, it won't be executed.


Creating and assigning a user profile to ORACLE DB users:
  • Check the resource limits of DEFAULT profile.
SQL> SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';

RESOURCE_NAME                    RESOURCE_TYPE LIMIT
-------------------------------- ------------- ---------
COMPOSITE_LIMIT                  KERNEL        UNLIMITED
SESSIONS_PER_USER                KERNEL        UNLIMITED
CPU_PER_SESSION                  KERNEL        UNLIMITED
CPU_PER_CALL                     KERNEL        UNLIMITED
LOGICAL_READS_PER_SESSION        KERNEL        UNLIMITED
LOGICAL_READS_PER_CALL           KERNEL        UNLIMITED
IDLE_TIME                        KERNEL        UNLIMITED
CONNECT_TIME                     KERNEL        UNLIMITED
PRIVATE_SGA                      KERNEL        UNLIMITED
FAILED_LOGIN_ATTEMPTS            PASSWORD      10
PASSWORD_LIFE_TIME               PASSWORD      UNLIMITED
PASSWORD_REUSE_TIME              PASSWORD      UNLIMITED
PASSWORD_REUSE_MAX               PASSWORD      UNLIMITED
PASSWORD_VERIFY_FUNCTION         PASSWORD      NULL
PASSWORD_LOCK_TIME               PASSWORD      UNLIMITED
PASSWORD_GRACE_TIME              PASSWORD      UNLIMITED


All resource limits for DEFAULT profile is set to UNLIMITED, but only for FAILED_LOGIN_ATTEPTS attribute, it’s set to some value (10). Due to this the user account keeps getting locked(timed).When we check in the Oracle Documentations, it’s stated that FAILED_LOGIN_ATTEPTS attribute for DEFAULT profile has been changed from 10.2.0.1 from UNLIMITED to 10.

What we can do is, either we may need to change the resource limit for FAILED_LOGIN_ATTEPTS attribute in DEFAULT profile, or create a new profile for that user with FAILED_LOGIN_ATTEPTS attribute value set to UNLIMITED. But for security reasons, we will not tamper the DEFAULT profile, which is not recommended too. Then let’s go for creating a new profile and assign that profile to the user.

Create a profile.

SQL> CREATE PROFILE TESTUSER_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 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;

Profile created.

Assign the newly created profile to the user as default profile.

SQL> ALTER USER TESTUSER PROFILE TESTUSER_default;
User altered.



Labels:

Comments

Post a Comment

Popular posts from this blog

ORACLE FLASH RECOVERY AREA USAGE QUERY

FINDING ORACLE FLASH RECOVERY AREA USAGE SELECT NAME,        (SPACE_LIMIT / 1024 / 1024 / 1024) SPACE_LIMIT_GB,          ((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE) / 1024 / 1024 / 1024) AS SPACE_AVAILABLE_GB,        ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL   FROM V$RECOVERY_FILE_DEST;
Post a Comment
Read more

Shared Pool Tuning: Cursor Tuning (Tuning Open_Cursors, Session_Cached_Cursors, Cursor_Space_For_Time)

Shared Pool Tuning: Cursor Tuning The three most important parameter for shared pool tuning are OPEN_CURSORS , SESSION_CACHED_CURSORS and CURSOR_SPACE_FOR_TIME. But most of the time we see that these two parameters SESSION_CACHED_CURSORS and CURSOR_SPACE_FOR_TIME are ignored or unused . OPEN CURSORS Open cursors take up space in the shared pool, in the library cache. OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. V$open_cursor shows cached cursors, not currently open cursors, by session. If you’re wondering how many cursors a session has open, don’t look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.  To monitor open cursors, query v$sesstat where name= ’opened cursors current’ . This will give the number of currently opened cursors, by session:
Post a Comment
Read more

How to delete/remove Management Agent from Oracle Enterprise Manager 12C

  1. Before you deinstall a Management Agent, do the following:     a. Stop the Agent using command from Management Agent home:                 cd /u01/oemcc_latest/core/12.1.0.2.0/bin/                 $ emctl stop agent     b. Wait for the Management Agent to go to the unreachable state in the Cloud Control console.     c. It is mandatory to delete the Management Agent and their monitored targets using any of the following methods: Remove the Agent target manually from the console: 1. Login to 12C Cloud Control 2. Navigate to Setup => Manage Cloud Control => Agents 3. Go to the Home page of the Agent that you want to remove 4. Expand the drop-down menu near the " Agent " 5. Expand the " Target Setup " option 6. Select " Remove Target "            In Cloud Control Release 12.1.0.2, the following dialog box will be displayed if the Agent is still monitoring targets. Click Continue. You can then remove all targets (usin
Post a Comment
Read more