Posted On August 5, 2015 by Shruthi S filed under Enterprise
Database security systems that depend on passwords require that passwords be kept secret at all times. Because passwords are vulnerable to theft and misuse, Oracle Database uses a password management policy. Database administrators and security officers control this policy through user profiles, enabling greater control of database security.A profile is a collection of parameters that sets limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits. You can use profiles to configure database settings such as sessions per user, logging and tracing features, and so on. Profiles can also control user passwords.Use the CREATE PROFILE statement to create a user profile. The profile is assigned to a user with the CREATE USER or ALTER USER statement.
CREATE PROFILE prof LIMIT
ALTER USER AHMED PROFILE prof;
Ahmed User assigned by profile Prof. Current Ahmed user password is Ahmed. The password_life_time 22 limits the user can use the current password only 22 days. i.e Ahmed can use his password ahmed only 22 days, account_status of the user is “open”.
The password_grace_time 7 days limits informs the user ahmed 7 days before the password expiry grace time status. I.e 11-oct-2014 onwards it informs the user ahmed to change his password.
Failed_login_attemps 4 -limits the user can how many times the user can use the wrong password. For example Ahmed can try wrong password four times. Suppose fifth time tries with wrong password, the user account will lock. The password_lock_time 2 -limits the user account lock two days, after two days i.e third day it will automatically unlock the user account.
You can create or modify the password-specific parameters individually by using the CREATE PROFILE or ALTER PROFILE statement.
ALTER PROFILE prof LIMIT
To find information about the current password settings in the profile, you can query the DBA_PROFILES data dictionary view.
Password-Specific Settings in the Default Profile
Automatically Locking a User Account after a Failed Login
Oracle Database can lock a user’s account after a specified number of consecutive failed log-in attempts. You can set the PASSWORD_LOCK_TIME user’s profile parameter to configure the account to unlock automatically after a specified time interval or to require database administrator intervention to be unlocked. The database administrator also can lock accounts manually, so that they must be unlocked explicitly by the database administrator.
You can specify the permissible number of failed login attempts by using the CREATE PROFILE statement. You can also specify the amount of time accounts remain locked.
Sets the maximum number of failed login attempts for the user JINAN to 2. i.e Jinan can use wrong password two times.The number of days account locked to 1 day. The account will unlock automatically after 1day .
If you do not specify a time interval for unlocking the account, then PASSWORD_LOCK_TIME assumes the value specified in a default profile. (The recommended value is 1 day.)
If you specify PASSWORD_LOCK_TIME as UNLIMITED, then you must explicitly unlock the account by using an ALTER USER statement.
ALTER PROFILE prof LIMIT
ALTER USER JINAN PROFILE prof;
SQL> conn jinan/j1
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn jinan/j11
ORA-01017: invalid username/password; logon denied
SQL> conn jinan/j13
ORA-28000: the account is locked
SQL> conn sys as sysdba
Enter password: ***
SQL> alter user jinan account unlock;
After a user successfully logs into an account, Oracle Database resets the unsuccessful login attempt count for the user, if it is non-zero, to zero. The security officer can also explicitly lock user accounts. When this occurs, the account cannot be unlocked automatically, and only the security officer should unlock the account.
The CREATE USER or ALTER USER statements explicitly lock or unlock user accounts. For example, the following statement locks the user account, Jinan:
ALTER USER JINAN ACCOUNT LOCK;
There is three account status of any account, whether it is open, in grace, or expired, lockby running the following query:
User Accounts Have Default Passwords
The user accounts that have default passwords. These are default accounts that are created when you create a database, such as the HR, OE, and SCOTT accounts. Using a default password that is commonly known can make your database vulnerable to attacks by intruders.To find both locked and unlocked accounts that use default passwords, log onto SQL*Plus using the SYSDBA privilege and then query the DBA_USERS_WITH_DEFPWD data dictionary view. Most of its default accounts are locked with the passwords expired.
Then change the passwords for any accounts that the DBA_USERS_WITH_DEFPWD view lists.
ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY tiger;
User Ability to Reuse Previous Passwords
You can ensure that users do not reuse their previous passwords for a specified amount of time or for a specified number of password changes. To do so, configure the rules for password reuse with CREATE PROFILE or ALTER PROFILE statements.
If you do not specify a parameter, then the user can reuse passwords at any time, which is not a good security practice. For example, suppose that the profile of JINAN has PASSWORD_REUSE_MAX set to 5 and PASSWORD_REUSE_TIME set to 10. Jinan cannot reuse a password until he or she has reset the password 5 timesi.e. she will change five different password and then only allow to reuse current password. The password_reuse_time is 10 means after 10 days only it is allow to reuse the current password.
If either parameter is specified as UNLIMITED, then the user can never reuse a password.i.e PASSWORD_REUSE_MAX or PASSWORD_REUSE_TIME any one set as UNLIMITED.
PASSWORD_REUSE_MAX or PASSWORD_REUSE_TIME UNLIMITED
ALTER PROFILE Prof LIMIT
If you set both parameters to UNLIMITED, then Oracle Database ignores both, and the user can reuse any password at any time.
Both PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME UNLIMITED
SQL> CONN JINAN/j13
SQL> alter user jinan identified by jin;
SQL> alter user jinan identified by j13;
The another contol factor by setting the PASSWORD_VERIFY_FUNCTION parameters.
CREATE OR REPLACE FUNCTION my_verification_function (username VARCHAR2, password VARCHAR2, old_password VARCHAR2)
SQL> ALTER PROFILE prof LIMIT PASSWORD_VERIFY_FUNCTION my_verification_function;
SQL> ALTER USER JINAN PROFILE PROF;
Password Change Life Cycle
Phase 1: After the user account is created or the password of an existing account is changed, the password lifetime period begins.
Phase 2: This phase represents the period of time after the password lifetime ends but before the user logs in again with the correct password. The correct credentials are needed for Oracle Database to update the account status.
Oracle Database does not have any background process to update the account status. All changes to the account status are driven by the Oracle Database server process on behalf of authenticated users.
Phase 3: When the user finally does log in, the grace period begins. Oracle Database then updates the DBA_USERS.EXPIRY_DATE column to a new value using the current time plus the value of thePASSWORD_GRACE_TIME setting from the account’s password profile. At this point, the user receives an ORA-28002 warning message about the password expiring in the near future (for example, ORA-28002 The password will expire within 7 daysif PASSWORD_GRACE_TIME is set to 7 days), but the user can still log in without changing the password.
Phase 4: After the grace period (Phase 3) ends, the ORA-28001: The password has Expired error appears, and the user is prompted to change the password after entering the current, correct password before the authentication can proceed.
A database administrator or a user who has the ALTER USER system privilege can explicitly expire a password by using the CREATE USER and ALTER USER statements. This setting forces the user to change the password before the user can log in to the database.There is no "password unexpire" clause for the CREATE USER statement, but an account can be "unexpired" by changing the password on the account.Techniques for Enforcing Security
3.10 Password change time
As a database administrator, you can find an account’s last password change time as follows:
3.11 Display Profile Information
Enabling or Disabling Password Case Sensitivity
When you create or modify user accounts, by default, passwords are case sensitive. To control the use of case sensitivity in passwords, set the SEC_CASE_SENSITIVE_LOGONinitialization parameter.Only users who have the ALTER SYSTEM privilege can set theSEC_CASE_SENSITIVE_LOGON parameter. Set it to TRUE to enable case sensitivity or FALSE to disable case sensitivity.
Procedure for Enabling Password Case Sensitivity
- If you are using a password file, ensure that it was created with the IGNORECASE parameter set to N. The IGNORECASE parameter overrides the SEC_CASE_SENSITIVE_LOGON parameter. By default, IGNORECASE is set to Y, which means that passwords are treated as case-insensitive.
- Enter the following ALTER SYSTEM statement:
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE
The entire tutorial is available in youtube link “https://www.youtube.com/watch?v=uH8ZgXHPxDo&list=PLV3NgZiVnUZmv29bMaAm7aD9GDLPy7hPP&index=2”
 Patricia Huey (2014) Oracle Database, Security Guide 11g Release 2 (11.2), E36292-05