Pages

Monday, September 1, 2014

Users and Profiles in Oracle

=================================
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.

- 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




1 comment: