Oracle Profiles
Version 11.2.0.3
 
General Information
Dependencies
DBA_PROFILES PROFILE$ PROFNAME$
System Privileges alter profile
create profile
drop profile
RESOURCE_LIMIT=TRUE is required for resource limiting portions of the profile. Password limiting functionality is not affected by this parameter. resource_limit = TRUE
set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name = 'resource_limit';

ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH;

SELECT name, value
FROM gv$parameter
WHERE name = 'resource_limit';
 
Kernel Resources
COMPOSITE_LIMIT Maximum weighted sum of: CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. If this limit is exceeded, Oracle aborts the session and returns an error.

composite_limit <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT composite_limit 5000000;
CONNECT_TIME Allowable connect time per session in minutes

connect_time <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT connect_time 600;
CPU_PER_CALL Maximum CPU time per call (100ths of a second)

cpu_per_call <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT cpu_per_call 3000;
CPU_PER_SESSION Maximum CPU time per session (100ths of a second)

cpu_per_session <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT cpu_per_session UNLIMITED;
IDLE_TIME Allowed idle time before user is disconnected (minutes)

idle_time <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT idle_time 20;
LOGICAL_READS_PER_CALL Maximum number of database blocks read per call

logical_reads_per_call <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT logical_reads_per_call 1000;
LOGICAL_READS_PER_SESSION Maximum number of database blocks read per session

logical_reads_per_session <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT logical_reads_per_session UNLIMITED;
PRIVATE_SGA Maximum integer bytes of private space in the SGA (useful for systems using multi-threaded server MTS)

private_sga <value | UNLIMITED | DEFAULT>

Only valid with TP-monitor
ALTER PROFILE developer LIMIT private_sga 15K;
SESSIONS_PER_USER Number of concurrent multiple sessions allowed per user

sessions_per_user <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT sessions_per_user 1;
 
Password Resources
FAILED_LOGIN_ATTEMPTS
The number of failed attempts to log in to the user account before the account is locked
failed_login_attempts <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT failed_login_attempts 3;

-- to count failed log in attempts:
SELECT name, lcount
FROM user$
WHERE lcount <> 0;
PASSWORD_GRACE_TIME
The number of days during which a login is alowed but a  warning is issued
password_gracetime <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT password_grace_time 10;
PASSWORD_LIFE_TIME
The number of days the same password can be used for authentication
password_life_time <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT password_life_time 60;
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

password_lock_time <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT password_lock_time 30;
PASSWORD_REUSE_MAX
Times a password can be reused
password_reuse_max <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT password_reuse_max 0;
PASSWORD_REUSE_TIME
Days between password reuses
password_reuse_time <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT password_reuse_time 0;
 
Password Verification
Sample script for creating a password verify function {ORACLE_HOME}/rdbms/admin/utlpwdmg.sql
PASSWORD_VERIFY_FUNCTION
Verifies a passwords for length, content, and complexity
password_verify_function <function_name | NULL | DEFAULT>
ALTER PROFILE developer LIMIT
password_verify_function uw_pwd_verification;
Changing passwords with a password verify function The function requires the old and new passwords so password changes can not 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.
 
Create Profiles
View existing profiles SELECT profile, resource_name, limit
FROM dba_profiles
ORDER BY profile, resource_name;
Create profile CREATE PROFILE <profile_name> LIMIT
<profile_item_name> <value>
<profile_item_name> <value>
....;
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
Alter profile syntax ALTER PROFILE <profile_name> LIMIT <profile_item_name> <value>;
ALTER PROFILE developer LIMIT FAILED_LOGIN_ATTEMPTS 3;
 
Assign Profile
Assign During User Creation CREATE USER <user_name>
IDENTIFIED BY <password>
PROFILE <profile_name>;
CREATE USER uwclass
IDENTIFIED BY "N0Way!"
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA 0 ON SYSAUX
QUOTA UNLIMITED ON uwdata
QUOTA 10M ON indx_sml
PROFILE developer;
Assign Profile After User Creation ALTER USER <user_name>
PROFILE <profile_name>;
ALTER USER uwclass PROFILE developer;
 
Drop Profile
Drop Profile without Users DROP PROFILE <profile_name>
DROP PROFILE developer;
Drop Profile with associated Users DROP PROFILE <profile_name> CASCADE
DROP PROFILE developer CASCADE;
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved