Thursday, June 19, 2008

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;

No comments: