Profile in Oracle

In oracle you can set limits on the amount of various system resources available to each user. By doing so, you can prevent the uncontrolled consumption of valuable system resources such as CPU time.

Resource limits and password management of a user are assigned by the profile. Every user (even SYS) is assigned to a profile: by default, to the profile called DEFAULT. Based on the profile settings it is determined how resource and/or password management will be enforced to a user.

To use profiles, first categorize the related types of users in a database. Just as roles are used to manage the privileges of related users, profiles are used to manage the resource limits of related users.

How I can see in which profile a user is assigned?
-------------------------------------------------------
SQL> select profile from dba_users where username='ARJU';
PROFILE
------------------------------
DEFAULT

To see the default profile settings issue the query,

select * from dba_profiles where profile ='DEFAULT';

From the output one row is ,
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ---------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

So we see here FAILED_LOGIN_ATTEMPTS in DEFAULT profile is set to 10 which means after failed 10 consecutive connect requests with an incorrect password, the account will be locked.

To change the settings use
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 100;

Resource_type in DBA_PROFILES table can have two values- KERNEL or PASSWORD.

The kernel limitations are to do with how much processing capacity a session is allowed to consume.
The password resource type limits the settings that are relevant to security

There are other resources like PASSWORD_LIFE_TIME,PASSWORD_REUSE_TIME etc.

Related Documents:
-------------------
User Resource Limit in Oracle