General
=================================
Profiles are used to managed advanced settings for Oracle users.
Flow of Events
1. Create a Profile
2. Edit profile properties
3. Assign profile to user.
=================================
View Current Settings
=================================
To see current user profile:
SELECT username, profile FROM DBA_USERS;
DESCRIBE DBA_PROFILES
Name Null? Type
------------- -------- -------------
PROFILE NOT NULL VARCHAR2(30)
RESOURCE_NAME NOT NULL VARCHAR2(32)
RESOURCE_TYPE VARCHAR2(8)
LIMIT VARCHAR2(40)
SELECT resource_name, resource_type, limit FROM DBA_PROFILES
WHERE profile = 'DEFAULT'
RESOURCE_NAME RESOURCE_TYPE LIMIT --------------------------- ------------- -------------- COMPOSITE_LIMIT KERNEL UNLIMITED CONNECT_TIME KERNEL UNLIMITED CPU_PER_CALL KERNEL UNLIMITED CPU_PER_SESSION KERNEL UNLIMITED IDLE_TIME KERNEL UNLIMITED LOGICAL_READS_PER_CALL KERNEL UNLIMITED LOGICAL_READS_PER_SESSION KERNEL UNLIMITED PRIVATE_SGA KERNEL UNLIMITED SESSIONS_PER_USER KERNEL UNLIMITED FAILED_LOGIN_ATTEMPTS PASSWORD 10 PASSWORD_GRACE_TIME PASSWORD 7 PASSWORD_LIFE_TIME PASSWORD UNLIMITED PASSWORD_LOCK_TIME PASSWORD 1 PASSWORD_REUSE_MAX PASSWORD UNLIMITED PASSWORD_REUSE_TIME PASSWORD UNLIMITED PASSWORD_VERIFY_FUNCTION PASSWORD NULL
=================================
Profile parameters
=================================
There are two types of profile parameters:
- KERNEL
- PASSWORD
=================================
KERNEL Parameters
=================================
- COMPOSITE_LIMIT
Total resource cost for a session, in service units.Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.
Total resource cost for a session, in service units.Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.
- CONNECT_TIME
Total elapsed time limit for a session, in minutes.
- CPU_PER_CALL
CPU time limit for a call (a parse, execute, or fetch) in hundredths of seconds.
- CPU_PER_SESSION
CPU time limit for a session in hundredth of seconds.
- IDLE_TIME
Permitted periods of continuous inactive time during a session, in minutes. Long-running queries and other operations are not subject to this limit.
- LOGICAL_READS_PER_CALL
Number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
- LOGICAL_READS_PER_SESSION
Number of data blocks read in a session, including reads from memory and disk.
- PRIVATE_SGA
Amount of private space a session can allocate in the shared pool of SGA in bytes.
- SESSIONS_PER_USER
Number of concurrent sessions per user.
=================================
Default Profile
=================================
If a value is set to DEFAULT, the actual value is taken from the DEFAULT profile.
The DEFAULT profile initially has all limits set to UNLIMITED.
=================================
Creating a Profile
=================================
Any user who is not explicitly assigned a profile is subject to the limits defined in the DEFAULT profile.
Also, if the profile that is explicitly assigned to a user omits limits for some resources or specifies DEFAULT for some limits, then the user is subject to the limits on those resources defined by the DEFAULT profile.
Example of creating a Profile
Step A. - Create a Profile
CREATE PROFILE my_profile
LIMIT PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED ;
Step B. - Assign the Profile to user.
ALTER USER my_user PROFILE my_profile;
See the current settings:
SELECT username, profile FROM DBA_USERS WHERE username='MY_USER';
SELECT * FROM DBA_PROFILES WHERE profile='MY_PROFILE';
Oracle Profile and Password Management
Password management in Oracle is done via Profile assigned to a user.
This is done with two parameters:
PASSWORD_REUSE_TIME
PASSWORD_REUSE_MAX
These parameters can have an Integer value or UNLIMITED
Option A.
If parameters are set to UNLIMITED, passwords can be reused freely.
Option B.
If one of these limits is set to UNLIMITED, and the other is set to an integer, the user cannot reuse passwords.
Option C.
If both limits are set to integers, then a password can be reused after the PASSWORD_REUSE_TIME number of days has expired and the password has been changed less than PASSWORD_REUSE_MAX number of times.
How to handle Oracle Error: "ORA-28007 The password cannot be reused" without changing password by example.
SQL> ALTER USER SOME_USER IDENTIFIED BY SOME_PASSWORD;
ALTER USER SOME_USER IDENTIFIED BY SOME_PASSWORD
*
ERROR at line 1:
ORA-28007: the password cannot be reused
SQL> SELECT * FROM DBA_PROFILES WHERE profile='DEFAULT' AND RESOURCE_TYPE like '%PASSWORD%';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------- -------------------------------- -------- ----------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3
DEFAULT PASSWORD_LIFE_TIME PASSWORD 60
DEFAULT PASSWORD_REUSE_TIME PASSWORD 15
DEFAULT PASSWORD_REUSE_MAX PASSWORD 3
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD 5
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME UNLIMITED;
Profile altered.
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX UNLIMITED;
Profile altered.
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 365;
Profile altered.
SQL> ALTER USER SOME_USER IDENTIFIED BY SOME_PASSWORD;
User altered.
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME 15;
Profile altered.
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX 3;
Profile altered.
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 60;
Profile altered.
SELECT USERNAME, ACCOUNT_STATUS, EXPIRY_DATE
FROM DBA_USERS
WHERE username = 'SOME_USER';
USERNAME ACCOUNT_STATUS EXPIRY_DA
------------------- -------------------------------- ---------
SOME_USER OPEN 14-AUG-15
Appendix
Short Reference: Orafaq
Please visit my blog יוסף אלברק
ReplyDelete