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; |
No comments:
Post a Comment