Profiles helps DBA to control usage of database resources by user sessions.
Database profiles provide a means for administrators to control and restrict usage of various database and operating system resources on a per user session level and also as a policy to manage passwords. The below are various resources that can be controlled with profiles.
This controls total resource cost for a session. It is expressed as service units. Service units are calculated as weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION and PRIVATE_SGA. The formula that Oracle uses to arrive at COMPOSITE_LIMIT can be specified using ALTER RESOURCE COST statement. If a user session exceeds the COMPOSITE_LIMIT, the user session is terminated with error.
How to alter various resource weights with ALTER RESOURCE COST statement.
The weight is derived as CPU_PER_SESSION * 100 + CONNECT_TIME * 1
CPU_PER_SESSION and CONNECT_TIME are values set in profile for the user.
Since LOGICAL_READS_PER_SESSION & PRIVATE_SGA are not included in ALTER statement, these two parameters are not used in arriving the total resource cost.
Database multiplies the weights with each resource CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION & PRIVATE_SGA; The product is expressed in unit called service units To arrive at the composite limit, database sums up all the product. The sum is expressed in unit called service unit.
Consider the below statement.
The new COMPOSITE_LIMIT will be :
LOGICAL_READS_PER_SESSION * 10 + CONNECT_TIME * 0 + CPU_PER_SESSION * 100
The weight for CPU_PER_SESSION is taken from previous weight, new weight for CONNECT_TIME from current setting and the new weight for LOGICAL_READS_PER_SESSION.
This is the total elapsed time limit for a user session. If the value for this parameter is 20, then the user session expires after twentyth minute. It is expressed in minutes.
This is the time limit for each operation parse, execute and fetch. It is expressed in hundredth of a second.
This is the CPU time limit set for a session which is expressed in hundredth of a second.
This limit locks the account if the user attemts to enter incorrect password for the value set to this parameter. That is, if the FAILED_LOGIN_ATTEMPTS is set to 3, then user can enter incorrect password two times. If the password provided for the third time is incorrect, the account is locked, hence further logins denied until the account is unlocked. If the password provided for the third time is correct, the user logs into the database. Default is 10 attempts.
This limit expires the user session if the session is idle for the value set to this parameter. For example, if IDLE_TIME is set to 10, the user session can be idle for 10 minutes (600 seconds) after which the used has to re-login. Session that run long queries that cross this time limit are not expired. The unit is minutes.
This parameter sets the limit for number of data blocks that can be read by a SQL. Default is UNLIMITED.
This parameter sets the limit for number of data blocks that can be read by all SQLs for a session. This value includes both disk blocks and memory blocks. Default is UNLIMITED.
This parameter is used to issue warnings to user about password expiry. If PASSWORD_LIFE_TIME is set to 10 days, and PASSWORD_GRACE_TIME is 3, the warning "ORA-28002: the password will expire within 3 days" appear after 7 days for the remaining 3 days. If the password is not changed, the user account locks preventing further connection to database. Default grace time is 7 days.
Denotes the duration in days the same password can be used. The user is required to change the password with the days specified by this parameter or the account is locked. Default is 180 days.
Specifies the number of days the account remain locked when an incorrect password is provided more than FAILED_LOGIN_ATTEMPTS. For example, if FAILED_LOGIN_ATTEMPTS is 3 and PASSWORD_LOCK_TIME is 1, then a user tried to connect by incorrect password more than 3 times, the account is locked for 1 day, after which the account is unlocked automatically. Default is 10 days. LOCK_DATE column of DBA_USERS is of interest for further understanding of PASSWORD_LOCK_TIME.
These two parameters decides when a password that was used earlier can be used now. If a user wants to use a password that he used earlier, then he must change passwords value set in PASSWORD_REUSE_MAX and should also be more than days specified in PASSWORD_REUSE_TIME. These two parameters are used in conjunction. For example, if PASSWORD_REUSE_TIME is set to 90 days and PASSWORD_REUSE_MAX is set to 3 times, then, to use a password that a user is using now, he must change the password 3 times within 90 days. If either of the parameters is set to UNLIMITED, the user can never reuse the password. For example, if the password is "abc123", the user can never use "abc123" as his future password. If he value of either parameter is DEFAULT, then the value of that parameter set in DEFAULT profile will be used. If the value for the parameter is UNLIMITED, then the user can never reuse the password. If the value is UNLIMITED for both the parameters, then both the parameters are ignored by database.
Eables to specify what PL/SQL function should be used to verify the complexity of the password. Complexity enforces how the password needs to be constructed, the inclusion of special characters, digits and password lengths. The default password function is provided by $ORACLE_HOME/rdbms/admin/utlpwdmg.sql. A value of NULL does not use any function to determine the complexity. To alter the password complexity verify function: alter profile <profile_name> limit password_verify_function <routine_name>; To disable password complexity verify function: alter profile <profile_name> limit password_verify_function null; The password complexity function can be a custom made function. In such case, the following prototype must be followed : CREATE OR REPLACE FUNCTION <verify_function_name> (username varchar2, password varchar2, old_password varchar2) RETURN boolean
Specifies the amount of space a user can take in shared pool.
Specifies the number of concurrent sessions a particular user can connect to database. For example, if SESSION_PER_USER is set to 3, a user cannot more than 3 different connections.
How to create profile
create profile <profile name> limit <resource parameter 1> <value 1> <resource parameter 2> <value 2> <resource parameter n> <value n> ... <password parameter 1> <value 1> <password parameter 2> <value 2> <password parameter n> <value n>;
create profile sample_profile limit sessions_per_user 5 private_sga 500K;
How to assign profiles to an existing user
How to assign profiles to new user
How to check the profiles of users