Posted in faq's-common doubts

what are oracle profiles? – complete reference for the new oracle dba

   February 25, 2011




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

oracle profiles introduction

create profile syntax

alter profile syntax

 

 

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 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.
Then, they can be assigned to users with alter user … profile.

Limitable resources

The following limits can be specified:

Kernel limits

  • ·         Maximum concurrent sessions for a user
    (sessions_per_user)
  • ·         CPU time limit per session
    (cpu_per_session)
  • ·         CPU time limit per call
    (cpu_per_call)
    Call being parse, execute and fetch
  • ·         Maximum connect time
    (connect_time)
    The session will be dropped by oracle after specified time.
  • ·         Maximum idle time
    (idle_time)
    The session will be dropped by oracle after specified time of doing nothing. Long running processes are not idle!
  • ·         Maximum blocks read per session
    (logical_reads_per_session)
  • ·         Maximum blocks read per call
    (logical_reads_per_call)
  • ·         Maximum amount of SGA
    (private_sga)
  • ·         ….
    (composite_limit)

In order to enforce kernel limits, resource_limit must be set to true.

Password limits

  • ·         Maximum failed login attempts
    (failed_login_attempts)
  • ·         Maximum time a password is valid
    (password_life_time)
  • ·         Minimum of different passwords before password can be reused
    (password_reuse_max)
  • ·         Minimum of days before a password can be reused
    (password_reuse_time)
  • ·         Number of days an account is locked after failing to login
    (password_lock_time)
  • ·         ???
    (password_grace_time)
  • ·         Verify function for passwords
    (password_verify_function)

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.

 

Author: admin