Oracle Profiles
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose  
Dependencies
CDB_PROFILES PROFILE$ PROFNAME$
DBA_PROFILES    
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
conn sys@pdbdev

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 99;
PASSWORD_REUSE_TIME
Days between password reuses
password_reuse_time <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT password_reuse_time 9999;
 
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 9999
PASSWORD_REUSE_MAX 1
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;

Related Topics
Consumer Groups
Fine Grained Access Control
Product User Profiles
Roles
Security
Users

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