| 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 <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 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 <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 |
The number of times a password must be changed before it can be reused
password_reuse_max <value | UNLIMITED | DEFAULT> |
| ALTER PROFILE developer LIMIT password_reuse_max 0; |
| PASSWORD_REUSE_TIME |
The number of days between reuses of a password
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 |
Verify 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 |
| 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 <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 Users |
DROP PROFILE <profile_name> CASCADE |
| DROP PROFILE developer CASCADE; |
