ORACLE DBA PROFILES MANAGEMENT

PROFILES
Profile: Profile is set of passwords & resources limit called profile. We should set the
parameter in init.ora file: resource_limit=true.
To enable the profiles.
SQL> alter system set resource_limit=true;
CONNECT_TIME: Allowable connect time per session in mints.
SQL> alter profile developer limit connect_time 600;
CPU_PER_SESSION: Maximum CPU time per session (1000ths of a second).
SQL> alter profile developer limit cpu_per_session unlimited;
IDLE_TIME: Allowed idle time before user is disconnected (100 minutes).
SQL> alter profile developer limit idle_time 20;
LOGICAL_READS_PER_CALL: Maximum number of database blocks reads per call.
SQL> alter profile developer limit logical_reads_per_call 1000;
SESSIONS_PER_USER: Number of concurrent multiple sessions allowed per user.
SQL> alter profile developer limit sessions_per_user 1;

FAILED_LOGIN_ATTEMPTS: The number of failed attempts to log in to the user account
before the account is locked.
SQL> alter profile developer limit failed_login_attempts 3;
PASSWORD_LIFE_TIME: The number of days the same password can be used for
authentication.
SQL> alter profile developer limit password_life_time 60;
PASSWORD_GRACE_TIME: 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.
SQL> alter profile developer limit password_grace_time 10;
PASSWORD_LOCK_TIME: The number of days an account will be locked after the
specified number of consecutive failed login attempts defined by
FAILED_LOGIN_ATTEMPTS.
SQL> alter profile developer limit password_lock_time 30;
PASSWORD_REUSE_MAX: The number of times a password must be changed before it
can be reused.
SQL> alter profile developer limit password_reuse_max 0;
PASSWORD_REUSE_TIME: The number of days between reuses of a password.
SQL> alter profile developer limit password_reuse_time 0;
PASSWORD_VERIFY_FUNCTION: Verify passwords for length, content, and
complexity.
SQL> alter profile chanti limit
password_verify_function verify_function;
Note: Before give this password_verify_function run this Sample script for creating a
password verify function.

{ORACLE_HOME}/rdbms/admin/utlpwdmg.sql
Note: The function requires the old and new passwords so password changes cannot be
done with ALTER USER. Password changes should be performed with the SQL*Plus
PASSWORD command or through a stored procedure that requires the correct inputs.
To create profile like this
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;
Syntax: create user <user_name> identified by <password> profile <profile_name>;
SQL> create user developer identified by developer
default tablespace ramki_ts
temporary tablespace temp
quota 10m on system
quota 10m on sysaux
profile developer;
To Assign Profile after User Creation
Syntax: alter user <user_name> profile <profile_name>;
SQL> alter user krishna profile developer;
SQL> alter user olduser quota unlimited on users;
SQL> alter user olduser quota 10000m on users;

Drop Profile without Users
Syntax: drop profile <profile_name>
SQL> drop profile developer;
Drop Profile with Users
Syntax: drop profile <profile_name> cascade
SQL> drop profile developer cascade;
VIEWS OF PROFILES
DBA_PROFILES
USER_PASSWORD_LIMITS
UER_RESOURCE_LIMITS
V$SESSION
V$SESSTAT
$STATNAME