you are a new oracle dba and you would like to know about oracle profiles, syntax of the various profile commands and how to use them.
Below is the complete reference
General | ||||
Dependencies |
|
|||
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 ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH; SELECT name, value |
||||
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: |
||||
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 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; |
ALTER PROFILE profile LIMIT
{ resource_parameters | password_parameters }
[ resource_parameters | password_parameters
]… ;
alter profile DEFAULT limit failed_login_attempts 10
DBA_PROFILES
Display all profiles and their limits
Columns
___________________________
PROFILE
Profile name
RESOURCE_NAME
Resource name
RESOURCE_TYPE
--
LIMIT
Limit placed on this resource for this profile
Profiles in Oracle | ||
Profiles are a means to limit resources a user can use.
Before profiles can be assigned, they must be created with create profile. Limitable resources The following limits can be specified: Kernel limits
In order to enforce kernel limits, resource_limit must be set to true. Password limits
If a session exceeds one of these limits, Oracle will terminate the session. If there is a logoff trigger, it won’t be executed. History of passwords In order to track password related profile limits, Oracle stores the history of passwords for a user in user_history$. Quering created profiles Profiles already created and their settings can be queried through dba_profiles |
Sessions_per_user: We set that to 6, based on the way our users used that application. They each usually had two Oracle Forms sessions open and one Oracle Reports session. This allowed them to use one other work station as well if someone else was not using it, for some occasional long-running processes.
Connect_time: This value is in minutes. This application was used in an office that normally operated from 8:00am to 6:00pm, or 10 hours maximum. Setting connect time to 600 forced connections off after 10 hours if someone forgot to log out when they left in the evening, but did not interrupt the normal work day.
The other values were determined by a trial-and-error process to arrive at values that protected the system from poorly-written, ad-hoc queries, but allowed all standard screens and reports to work fine. We did have a different profile with much higher values for a couple of users who needed to run batch processes that involved much more I/O.
Basically these values need to be set to reflect your application, your database, and your users. Values from other systems may be somewhat helpful, but each Oracle database installation is unique, so some of your resource limits will likely be different than ours were.