The PROFILE specifies the name of the profile to be created. Use profiles to limit the database resources available to a user for a single call or a single session.
Oracle database enforces resource limits in the following ways:
When specified with a resource parameter, unlimited indicates that a user assigned this profile can use an unlimited amount of this resource. When specified with a password parameter, unlimited indicates that no limit has been set for the parameter.
Specify default if you want to omit a limit for this resource in this profile. A user assigned this profile is subject to the limit for this resource specified in the default profile. The default profile initially defines unlimited resources. You can change those limits with the alter profile statement.
Any user who is not explicitly assigned a profile is subject to the limits defined in the default profile. Also, if the profile that is explicitly assigned to a user omits limits for some resources or specifies default for some limits, then the user is subject to the limits on those resources defined by the default profile.
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.
Use the following clauses to set password parameters. Parameters that set length of time are interpreted in a number of days. For testing purposes, you can specify the minutes (n/1440) or even seconds (n/86400).
Failed_login_attempts: Specify the number of failed attempts, to log in to the user account before the account is locked.
Password_life_time: Specify the number of days the same password can be used for authentication. If you also set a value for password_grace_time, the password expires if it is not changed within the grace period, and further connections are rejected. If you do not set a value for password_grace_time, its default of unlimited will cause the database to issue a warning but let the user continue to connect indefinitely.
Password_reuse_time and password_reuse_max: These two parameters must be set in conjunction with each other. Password_reuse_time specifies the number of days before which a password cannot be reused. Password_reuse_max specifies the number of password changes required before the current password can be reused. For these parameter to have any effect, you must specify an integer for both of them.
Password_lock_time: Specify the number of days. Then an account will be locked after the specified number of consecutive failed login attempts.
Password_grace_time: Specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.
Password_verify_function: The password_verify_function clause lets a pl/sql password complexity verification script be passed as an argument to the create profile statement. Oracle database provides a default script, but you can create your own routine or use third-party software instead.
If you specify expr for any of the password parameters, the expression can be of any form except scalar subquery expression.
Creating a profile: example:
Use the CREATE PROFILE statement to create a profile, which is a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits.
The following statement creates the profile new_profile:
Sql>create profile new_profile limit password_reuse_max 10 password_reuse_time 30;
The following statement creates the profile app_user:
Sql>create profile app_user limit
If you assign the app_user profile to a user, the user is subject to the following limits in subsequent sessions:
Since the app_user profile omits a limit for idle_time and for password limits, the user is subject to the limits on these resources specified in the default profile.
The following statement creates the app_user2 profile with password limits values set:
Sql>create profile app_user2 limit failed_login_attempts 5 password_life_time 60 password_reuse_time 60 password_reuse_max 5 Password_verify_function verify_function password_lock_time 1/24 password_grace_time 10;
Use the ALTER PROFILE statement to add, modify, or remove a resource limit or a password management parameter in a profile.
Changes made to a profile with an ALTER PROFILE statement affect users, only in their subsequent sessions, not in their current sessions.
The following statement is used for altering the existing profiles.
Sql>alter profile limit failed_login_attempts 3;
Profiles can only be assigned to system users if the profile has first been created. Each system user is assigned with only one profile at a time. When a profile is assigned to a system user who already has a profile, the new profile replaces the old one – the current session, if one taking place, is not affected, but subsequent sessions are affected. Also, you cannot assign a profile to a role or another profile.
Create user uwclass
identified by “n0way!”
default tablespace uwdata
temporary tablespace temp
quota 0 on system
quota 0 on sysaux
quota unlimited on uwdata
quota 10m on indx_sml
profile app_user2 ;
Drop profile without users:
Drop profile app_user2; Drop profile with users:
Drop profile app_user2 cascade;
Get Updates on Tech posts, Interview & Certification questions and training schedules