USER Profiles

Profiles in Oracle
Profiles are a means to limit resources a user can use.
Before profiles can be assigned, they must be created with create profile. Then, they can be assigned to users with alter user ... profile.
For Example:

CREATE PROFILE
SQL> CREATE PROFILE developer LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 0
PASSWORD_REUSE_MAX 0
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME 2
CPU_PER_CALL 3000
PRIVATE_SGA 500K
LOGICAL_READS_PER_CALL 1000;

ALTER Profile:
SQL>ALTER PROFILE developer LIMIT FAILED_LOGIN_ATTEMPTS 3;

Drop Profile without user:
SQL> drp profile developer;

Drop Profile with user:
SQL> drp profile developer CASCADE;

Assigna profile to User:
SQL> alter user rama profile developer ;


Limitable resources

RESOURCE_LIMIT=TRUE is required for resource limiting portions of the profile. Password limiting functionality is not affected by this parameter.

SQL>SELECT name, valueFROM gv$parameterWHERE name = 'resource_limit';
SQL> ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH;

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)
....(composite_limit)
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)
-Password grace time (password_grace_time)
-Verify function for passwords(password_verify_function)


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

History of passwords
In order to track password related profile limits, Oracle stores the history of passwords for a user in user_history$.
Profiles already created and their settings can be queried through dba_profiles