| 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; |
