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

EXPDP/IMPDP Export/Import dumpfile to a Remote Server Using Network_Link.

EXPDP/IMPDP Export/Import dumpfile to a Remote Server Using Network_Link. Step 1:   First you have to create a TNS entry at destination database which will be used to connect to the remote target database. pumplink =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.1.171)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = Ultimus)     )   ) Step 2:   Connect to SQL plus: --Issue the following command to create db link on destination database: CREATE PUBLIC DATABASE LINK pumplink    connect to scott identified by tiger USING 'pumplink'; Step 3:   Issue the expdp command on the destination server using Network_link parameter: expdp scott/tiger directory= dumpdir logfile=impi_temp.log network_link= pumplink  schemas=scott dump...
5 comments
Read more

All Dictionary View Tables:

All Dictionary View Tables: TABLE_NAME COMMENTS ALL_ALL_TABLES Description of all object and relational tables accessible to the user ALL_APPLY Details about each apply process that dequeues from the queue visible to the current user ALL_APPLY_CONFLICT_COLUMNS Details about conflict resolution on tables visible to the current user ALL_APPLY_DML_HANDLERS Details about the dml handler on tables visible to the current user ALL_APPLY_ENQUEUE Details about the apply enqueue action for user accessible rules where the destination queue exists and is visible to the user ALL_APPLY_ERROR Error transactions that were generated after dequeuing from the queue visible to the current user ALL_APPLY_EXECUTE Details about the apply execute action for all rules visible to the user ALL_APPLY_KEY_COLUMNS Alternative key columns for a STREAMS table visible to the current user ALL_APPLY_PARAME...
1 comment
Read more

Solution of problem: Resultset Exceeds the Maximum Size (100 MB)

Solution of problem: Resultset Exceeds the Maximum Size (100 MB) I was running a select statement in PL/SQL Developer. it was a short query but the data volume that the query was fetching was huge. But when ever i Click the button Fetch Last Page or press 'ALT+End' button a message box comes after a while saying: Then I started looking for the exact reason of this sort of problem in Google. When I realized there was no direct solution in the web, I started looking the PL/SQL Developer Software menu and found the ultimate solution. The reason of this problem is there is a parameter of maximum result set size in PL/SQL Developer Software which is by default set to 100 MB. To change this parameter you have to go to the following location: 1. Goto Edit Menu and click ' PL/SQL Beautifier Options '. A new window will open. 2. Click SQL Window of " Window Types ". 3. Now Change the value of "Maximum Result Set Size( 0 is unlimited)"  ...
6 comments
Read more