Create profile and assigned to user in Oracle
1. Create a new profile
Parameter set according to your need.
CREATE PROFILE app_user LIMIT
2. Assigned the profile to user.
SQL> ALTER USER TEST profile app_user;
3. Verify the profile of user.
select profile from dba_users where username = 'TEST'
Profile parameter meanings:
COMPOSITE_LIMIT: Specify the total resource cost for a session, expressed in service units. The total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.
SESSIONS_PER_USER: Number of concurrent sessions for the user.
CPU_PER_SESSION: CPU time limit for a session(hundredth of seconds)
CPU_PER_CALL: CPU time limit for a call (a parse, execute, or fetch)(hundredths of seconds)
CONNECT_TIME: Total elapsed time limit for a session(minutes)
IDLE_TIME: Permitted periods of continuous inactive time during a session(minutes).
LOGICAL_READS_PER_SESSION: Permitted number of data blocks read in a session, including blocks read from memory and disk.
LOGICAL_READS_PER_CALL: Permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
PRIVATE_SGA: Amount of private space a session can allocate in the shared pool of the system global area (SGA).
FAILED_LOGIN_ATTEMPTS : Attempts made for login exceed this limit will lock the account.
PASSWORD_LIFE_TIME : Number of days password can be used for authentication. It also depend upon PASSWORD_GRACE_TIME is set then password will work until grace period defined after it will not allow to connect. It will give warning only if PASSWORD_GRACE_TIME is unlimited value.
PASSWORD_GRACE_TIME : Warning is issue after number of days value of PASSWORD_LIFE_TIME exceed upto value of PASSWORD_GRACE_TIME defined after that password is expired.
PASSWORD_REUSE_TIME defined in days
PASSWORD_REUSE_MAX defined in days
Both use PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 15, then the user can reuse the password after 30 days if the password has already been changed 15 times.
PASSWORD_LOCK_TIME : Number of days an account will be locked after the specified number of consecutive failed login attempts.
PASSWORD_VERIFY_FUNCTION : PL/SQL password complexity verification script be passed as an argument to the CREATE PROFILE statement.