Oracle User Profile and Resource Management

Sometimes we don't notice that there is something called 'User Profile' used to manage user resources and other important things. I would like to say in simple words - 'User profile' is some system parameters specific to a user.

The good use of profile could be password management for the user.

What is the profile assigned to a user?

When we create a database user, the default profile is assigned automatically.

SQL> SELECT x.username, x.profile
2 FROM dba_users x
3 WHERE username='SCOTT'
4 ORDER BY x.username;

USERNAME PROFILE
------------- -----------------------------------------------
SCOTT DEFAULT

For example, how many time(s) a database user/schema can attempt with incorrect login/password before the account got locked.

What are there in default profile?

SQL> SELECT p.resource_name, p.limit
2 FROM dba_users u, dba_profiles p
3 WHERE u.profile=p.profile
4 and u.username = 'SCOTT'
5 ORDER BY p.resource_name;


RESOURCE_NAME LIMIT
--------------------- ---------------------------
COMPOSITE_LIMIT UNLIMITED
CONNECT_TIME UNLIMITED
CPU_PER_CALL UNLIMITED
CPU_PER_SESSION UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PRIVATE_SGA UNLIMITED
SESSIONS_PER_USER UNLIMITED


How to create a profile?

SQL> CREATE PROFILE TEMP_10_DAYS_USER LIMIT PASSWORD_LIFE_TIME 10 SESSIONS_PER_USER 5;

If this new profile is assigned to 'SCOTT' user, the account will be valid for 10 days and the user could open 5 concurrent sessions.

SQL> ALTER USER SCOTT PROFILE TEMP_10_DAYS_USER;
The detailed Oracle document could be found here -

How to change a profile?

SQL> ALTER PROFILE TEMP_10_DAYS_USER LIMIT PASSWORD_LIFE_TIME 10 SESSIONS_PER_USER 10;

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6010.htm