/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 3 : PROFILES AND PASSWORD SECURITY
Compatible with:
Oracle 12c / 19c / 21c / 23ai
Topics Covered
---------------
1 What is a Profile
2 Password security policies
3 Resource limits
4 Creating profiles
5 Assigning profiles
6 Password lifetime rules
7 Account locking policies
8 Viewing profile settings
9 Default profile
10 Password reuse policies
11 Idle session limits
12 Dropping profiles
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is a Profile in Oracle Database?
*******************************************************************************************/
-- A PROFILE is a set of resource limits and password policies
-- assigned to a database user.
-- Profiles control two categories:
-- 1 PASSWORD PARAMETERS
-- 2 RESOURCE LIMIT PARAMETERS
-- Every Oracle user is associated with a profile.
-- If no profile is assigned explicitly,
-- Oracle assigns the DEFAULT profile.
/*******************************************************************************************
QUESTION 2
What are Password Parameters in Profiles?
*******************************************************************************************/
-- Password parameters enforce security policies.
-- Examples include:
-- FAILED_LOGIN_ATTEMPTS
-- PASSWORD_LIFE_TIME
-- PASSWORD_REUSE_TIME
-- PASSWORD_REUSE_MAX
-- PASSWORD_LOCK_TIME
-- PASSWORD_GRACE_TIME
/*******************************************************************************************
QUESTION 3
What are Resource Parameters in Profiles?
*******************************************************************************************/
-- Resource parameters control system resource usage.
-- Examples include:
-- SESSIONS_PER_USER
-- CPU_PER_SESSION
-- CPU_PER_CALL
-- CONNECT_TIME
-- IDLE_TIME
-- LOGICAL_READS_PER_SESSION
/*******************************************************************************************
QUESTION 4
How do we create a secure profile for enterprise users?
*******************************************************************************************/
CREATE PROFILE enterprise_secure_profile
LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 30
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
SESSIONS_PER_USER 2
IDLE_TIME 30;
/*******************************************************************************************
QUESTION 5
What does FAILED_LOGIN_ATTEMPTS control?
*******************************************************************************************/
-- FAILED_LOGIN_ATTEMPTS specifies the number of incorrect login attempts
-- allowed before the account gets locked.
-- Example value:
FAILED_LOGIN_ATTEMPTS 3
-- After 3 failed attempts,
-- Oracle locks the account.
/*******************************************************************************************
QUESTION 6
What does PASSWORD_LIFE_TIME control?
*******************************************************************************************/
-- PASSWORD_LIFE_TIME defines how long a password remains valid.
-- Example:
PASSWORD_LIFE_TIME 30
-- Password expires after 30 days.
/*******************************************************************************************
QUESTION 7
What happens when password expires?
*******************************************************************************************/
-- Oracle forces the user to change password at next login.
-- Error example:
-- ORA-28001: password has expired
/*******************************************************************************************
QUESTION 8
What is PASSWORD_LOCK_TIME?
*******************************************************************************************/
-- PASSWORD_LOCK_TIME defines how long the account remains locked.
-- Example:
PASSWORD_LOCK_TIME 1
-- Means account is locked for 1 day.
/*******************************************************************************************
QUESTION 9
What is PASSWORD_GRACE_TIME?
*******************************************************************************************/
-- PASSWORD_GRACE_TIME allows login for a limited period after password expiry.
-- Example:
PASSWORD_GRACE_TIME 7
-- User can still log in for 7 days
-- but must change password.
/*******************************************************************************************
QUESTION 10
How do we assign a profile to a user?
*******************************************************************************************/
ALTER USER training_user
PROFILE enterprise_secure_profile;
/*******************************************************************************************
QUESTION 11
How do we check profile assigned to a user?
*******************************************************************************************/
SELECT username, profile
FROM dba_users
WHERE username = 'TRAINING_USER';
/*******************************************************************************************
QUESTION 12
How do we see profile parameters?
*******************************************************************************************/
SELECT *
FROM dba_profiles
WHERE profile = 'ENTERPRISE_SECURE_PROFILE';
/*******************************************************************************************
QUESTION 13
What is the DEFAULT profile?
*******************************************************************************************/
-- DEFAULT profile is automatically assigned
-- to all new users unless specified.
SELECT *
FROM dba_profiles
WHERE profile = 'DEFAULT';
/*******************************************************************************************
QUESTION 14
How do we modify an existing profile?
*******************************************************************************************/
ALTER PROFILE enterprise_secure_profile
LIMIT
FAILED_LOGIN_ATTEMPTS 5;
/*******************************************************************************************
QUESTION 15
What is PASSWORD_REUSE_TIME?
*******************************************************************************************/
-- PASSWORD_REUSE_TIME specifies number of days
-- before the same password can be reused.
-- Example
ALTER PROFILE enterprise_secure_profile
LIMIT PASSWORD_REUSE_TIME 365;
/*******************************************************************************************
QUESTION 16
What is PASSWORD_REUSE_MAX?
*******************************************************************************************/
-- PASSWORD_REUSE_MAX specifies number of password changes
-- before the old password can be reused.
ALTER PROFILE enterprise_secure_profile
LIMIT PASSWORD_REUSE_MAX 5;
/*******************************************************************************************
QUESTION 17
How do we enforce password complexity rules?
*******************************************************************************************/
-- Oracle uses password verify functions.
-- Example function:
VERIFY_FUNCTION_11G
/*******************************************************************************************
QUESTION 18
Assign password verify function to profile
*******************************************************************************************/
ALTER PROFILE enterprise_secure_profile
LIMIT PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;
/*******************************************************************************************
QUESTION 19
What does IDLE_TIME control?
*******************************************************************************************/
-- IDLE_TIME specifies number of minutes
-- a session can remain inactive.
ALTER PROFILE enterprise_secure_profile
LIMIT IDLE_TIME 30;
/*******************************************************************************************
QUESTION 20
What happens when IDLE_TIME is exceeded?
*******************************************************************************************/
-- Oracle automatically disconnects session.
-- Error example:
-- ORA-02396: exceeded maximum idle time
/*******************************************************************************************
QUESTION 21
What is CONNECT_TIME?
*******************************************************************************************/
-- CONNECT_TIME defines maximum duration of a session.
ALTER PROFILE enterprise_secure_profile
LIMIT CONNECT_TIME 120;
/*******************************************************************************************
QUESTION 22
How do we enforce concurrent session limits?
*******************************************************************************************/
ALTER PROFILE enterprise_secure_profile
LIMIT SESSIONS_PER_USER 2;
/*******************************************************************************************
QUESTION 23
How do we view resource usage limits?
*******************************************************************************************/
SELECT *
FROM dba_profiles
WHERE resource_type = 'KERNEL';
/*******************************************************************************************
QUESTION 24
How do we drop a profile?
*******************************************************************************************/
DROP PROFILE enterprise_secure_profile;
/*******************************************************************************************
QUESTION 25
Why might dropping a profile fail?
*******************************************************************************************/
-- Because users are assigned to it.
-- Oracle error:
-- ORA-02382: profile has users assigned
/*******************************************************************************************
QUESTION 26
How do we force drop a profile?
*******************************************************************************************/
DROP PROFILE enterprise_secure_profile CASCADE;
/*******************************************************************************************
QUESTION 27
What happens when profile is dropped with CASCADE?
*******************************************************************************************/
-- All users assigned to the profile
-- automatically switch to DEFAULT profile.
/*******************************************************************************************
QUESTION 28
How do enterprise systems design profiles?
*******************************************************************************************/
-- Typical enterprise profiles:
-- APPLICATION_USERS
-- REPORTING_USERS
-- DBA_USERS
-- BATCH_PROCESS_USERS
/*******************************************************************************************
QUESTION 29
Example enterprise application profile
*******************************************************************************************/
CREATE PROFILE app_user_profile
LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 10
PASSWORD_LOCK_TIME 1
IDLE_TIME 20
SESSIONS_PER_USER 3;
/*******************************************************************************************
QUESTION 30
Assign profile to application user
*******************************************************************************************/
ALTER USER training_user
PROFILE app_user_profile;
/*******************************************************************************************
END OF PART 3
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied warranties, including but not limited to warranties of accuracy, completeness, reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees that they do so at their own risk. The author shall not be held liable for any direct, indirect, incidental, consequential, special, or exemplary damages, including but not limited to loss of data, loss of profits, system failures, security breaches, or any other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and configurations in a controlled, non-production (test) environment prior to deploying them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author from any claims, damages, liabilities, or expenses arising from their use.
*******************************************************************************************/