Oracle Profiles

General Dependencies 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 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 ALTER PROFILE developer LIMIT composite_limit 5000000; CONNECT_TIME Allowable connect time per session in minutes connect_time ALTER PROFILE developer LIMIT connect_time 600; CPU_PER_CALL Maximum CPU time per call (100ths of a second) cpu_per_call ALTER PROFILE developer LIMIT cpu_per_call 3000; CPU_PER_SESSION Maximum CPU time per session (100ths of a second) cpu_per_session ALTER PROFILE developer LIMIT cpu_per_session UNLIMITED; IDLE_TIME Allowed idle time before user is disconnected (minutes) idle_time ALTER PROFILE developer LIMIT idle_time 20; LOGICAL_READS_PER_CALL Maximum number of database blocks read per call logical_reads_per_call 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 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 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 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 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 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 password_gracetime 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 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 ALTER PROFILE developer LIMIT password_lock_time 30; PASSWORD_REUSE_MAX The number of times a password can be reused password_reuse_max ALTER PROFILE developer LIMIT password_reuse_max 0; PASSWORD_REUSE_TIME The number of days between reuses of a password password_reuse_time 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 Verify passwords for length, content, and complexity password_verify_function 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 List things that can be limited in a profile SELECT DISTINCT resource_name, limit FROM dba_profiles ORDER BY resource_name; Create profile CREATE PROFILE LIMIT ....; 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 LIMIT ; ALTER PROFILE developer LIMIT FAILED_LOGIN_ATTEMPTS 3; Assign Profile Assign During User Creation CREATE USER IDENTIFIED BY PROFILE ; 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 PROFILE ; ALTER USER uwclass PROFILE developer; Drop Profile Drop Profile without Users DROP PROFILE DROP PROFILE developer; Drop Profile with Users DROP PROFILE CASCADE DROP PROFILE developer CASCADE;