Database

Created May 23, 2024 by Mumtaj Yasmin

Unit Objective

This unit will cover the introduction to profile. creating and altering profile, checking resource limit of the profile using data dictionary view. It also describes various resource limits such as failed_login-time, life_time, lock_time, idle_time, connection_time, password_reuse_max, password_reuse_time and Session_per_user etc.,

2.1 User Profile

Password is a common security of all the information system. Keeping passwords always safe and secret is difficult. Online world passwords are vulnerable to theft and misuse. Oracle is having password Management policy called User profile. A profile is one of the database object. Using CREATE PROFILE statement user can create their own profile. Any profile can be assigned to user. Each user has only one profile. Create user time if profile is not assigned by default it will assign “DEFAULT” profile. It is possible to configure database using profile connect time and idle time of the user and Disk storage space. A profile has collection of parameters like Life time of the password, locking time of the password, same password to be used after how many days by same user. Profile can be applied whenever RESOURCE_LIMIT initialization parameter value is TRUE, by default it is FALSE.

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;

The DBA_PROFILES Data Dictionary View lists all profiles in the database and associated settings for each limit in each profile.

Figure 2-1 Describe Profiles

“DEFAULT” profile is assigned to user by default, suppose if no profile is assigned to user. For example user “Arvind” still no profile is not assigned so user Arvind has DEFAULT profile.

Figure 2-2 Display DEFAULT Profile

Profile resources are useful for setting system resource to Profile with defined limited resources like password_expiry, password_lifetime, password_lock_time, password_reuse_max, password_reuse_time etc., In this unit we study detail of each resource limit applied in the profile.

2.2 Create Profile

fig 2-3.jpgFigure 2-3 Create profile

Figure 2-3 Create profile

Using CREATE PROFILE statement, create profile and describe resource limits of the profile. Suppose the user is already exist then using ALTER USER statement assign profile to user. Otherwise using CREATE USER statement mention profile parameter to new user - in this case first create profile and then assign to new user.

Idle_time is useful for disconnecting user session suppose user mentioned idle_time is expired. For example Salesrep profile "idel_time is 10" minutes. The idel_time assigned values are in minutes.

The connect_time parameter also like idle_time the specified minutes of the user session in the profile exceeds it will disconnect a current session of the user.

Each resource limit has three types of values such as <value | UNLIMITED | DEFAULT>.

For example “connect_time 600” here value 600 minutes is assigned.

Suppose Unlimited connection time, then it is specified as “connect_time UNLIMITED.”

The DEFAULT value omit a limit for this resource in the profile. For example “connect_time DEFAULT”

Session_per_user limits the user can do number of concurrent sessions at a time.

2.3 Alter Profile

Using ALTER PROFILE statement it is possible to modify resource limit values or add resource limit or remove resource limit.

fig2-4.jpgFigure 2-4 Alter profile

Figure 2-4 Alter profile

2.4 Display Resource Limits of the Profile

Using select statement display the DBA_PROFILES data dictionary view to check the resource limits value of the profile.

Figure 2-5 Display DBA_PROFILE limites

In the Limit column the specified value of the resource limit is available.

2.5 Password_Life_Time

If password_life_time resource limit is specified in the profile, based on the value of the resource limit the assigned user password will automatically expire. Suppose it is specified as 3 means 3 days. By default it will consider as days. Password life time starts which date user password is assigned to user using CREATE USER OR ALTER USER statement. Yesterday user ARVIND is created. i.e 14-APR-17 . Even though the profile salesrep is assigned to user Arvind 15-APR-17, assign password life time value in the profile it will count from yesterday not today based password created time. That is a reason next three days i.e 17-APR-17 it will expire automatically. If value is not mentioned in the password_life_time by default setting it will take 10 days.

Figure 2-6 Password Life Time

2.5.1 Account Status is Expired

Once password life time of the user expired if user not changed the password using ALTER USER then it will give ORA-28001 Error message. Then user should type new password for login session

Figure 2-19 password expired status

Figure 2-20 Password expired status

2.5.2 Change Password Expiry date

it is possible change expiry date of the password using ALTER USER statement and modify the password of the user Every "alter user..identified by <password>" statement, it will start the new life time count.

Figure 2-7 Alter user to change password life time

Figure 2-8 without changing password it will affect expiry date

Even though don't modify password just use the ALTER USER statement as shown in figure 2-8. it will start life time that particular date, For example the user sinduja is created 11-APR-2017 and 15-APR-17 it is profile is assigned. In the alter user statement not even changed password of the user sinduja, it will consider life time is from 15-APR-17, so expiry is 18-APR-17. So it is considering ALTER USER OR CREATE USER statement only.

2.6 Password_Grace_Time

The purpose of Password grace time to alert the user number of days remains for password expiry, In the grace time if the user change the password using ALTER USER statement, then password life time is renewed. If the user is not ALTER then every user login warning message to be issued to the user but login is allowed for specific user. So password grace time and password life time are inter related.

Figure 2-9 Password grace time

Once profile is changed, then assign the profile to user ALTER USER ARVIND PROFILE SALESREP. Suppose if grace period is starts whenever login the warning message will display

Figure 2-17 Grace time warning message

2.7 Account Status Message

There are various account status messages will intimates user based on resource limits values.

Figure 2-10 Various Account status

2.8 FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME

Figure 2-11 Failed login attempts and Lock time

The user ARVIND password is "Arvind789", first try with wrong password as "Arvind123" it will gives warning. Check the account status of the user is "open", As mentioned two attempts are allowed. so one more attempt is remaining.

Figure 2-12 Wrong password First attempt

Again try with wrong password as Arvind000, it will give warning message. As mentioned two times in the failed login attempt in profile it will allow the user to try two times incorrect password after that user account will lock automatically. Now user account status is LOCKED. Lock time value is 2, after two days i will automatically user account to be unlocked and account status become open.

Figure 2-13- Second attempt & Locked status

Suppose the user immediately want to restart or login account, then ALTER USER manually unlock the account. Then account status become active as “OPEN”

Figure 2-15 Account unlock

2.8.1 Account Status Lock

There is two way to lock the user account, one method is using Failed_login_attempts and another method is manually unlocking.

Fig 2-15. Account Lock

2.9 Modify profile parameter Default Value settings

By default all the parameter mentioned in the profiles are day value. Suppose to change value from day to minutes then value to be mentioned as 20/1440. i.e 1 hour= 60 minutes. So, one day 24 hours = 24 hours* 60 minutes = 1440 minutes/per day. Suppose value to be mention as hours then 2/24, it means 2 hours.

Figure 2-18 Parameter values in minutes

2.10 Password_Reuse_Max

If user current password life time is expired or changing before the life time, then user new password is control by two profile parameter such as Password_Reuse_Max and Password_Reuse_Time. The Password_Reuse_max parameter value controls current password to be reused based on value assigned to this parameter. By default value is UNLIMITED

PASSWORD_REUSE_MAX 3;

For example, the password reuse max value is 3 means, the current or recently expired password to be reused after three times changing the new password to the user. Suppose user Arvind password is ARVIND789. Using ALTER USER statement change the password as

ALTER USER ARVIND IDENTIFIED BY ARVIND0102

ALTER USER ARVIND IDENTIFIED BY ARVIND456

ALTER USER ARVIND IDENTIFIED BY ARVIND765

Three incidence assign different passwords to the user and then reuse the password ARVIND789 to user Arvind again.

2.11 Password_Reuse_Time

The password_reuse_time attribute value controls the user current password to be reused after specified interval time. By default value is UNLIMITED. For example

PASSWORD_RESUE_TIME 5

means after five days the current password to be reused and same password to be assigned to user.

ALTER USER ARVIND IDENTIFIED BY ARVIND789;

ALTER USER ARVIND IDENTIFIED BY ARVIND1009;

After five days, the user can reuse the password as ARVIND789 by the user Arvind.

2.11.1 Password_Reuse_time vs. Password_Reuse_max values

Password_reuse_max and reuse_time values

2.12 Password Expire

Using ALTER USER OR CREATE USER statement either Database Administrator(DBA) or the user whose has ALTER USER system privilege to be used explicitly.

ALTER USER ARVIND IDENTIFIED BY ARVIND789 PASSWORD EXPIRE;

The “PASSWORD EXPIRE” enforced to change the password before user log in to the database. There is no “PASSWORD UNEXPIRE” clause.

Figure 2-22 Password Expire

Figure 2-22, Even after ALTER USER change the password and login, first time it will force to change password of the user.

2.13 Password_Verify_Function

Password _verify_function parameter control the password validation in the profile. For example length of the password and user and password should be unique not same. By default NULL value specified in DEFAULT profile, so there is no password verification function performed by DEFAULT.

Figure2-23 Password verification

Figure 2-24 password verification using user

The utlpwdmg.sql Script is for DEFAULT Profile password resource limits.

C: \app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlpwdmg.sql (location where oracle software is running)

Figure 2-25 utlpwdmg.sql

For enabling password features this script to be run and default resource parameter to be changed like figure 2-23 and Figure 2-24 and ALTER PROFILE and assign profile to user using ALTER USER. Using this script, the user can modify the code based on requirement and implement in any profile can control password validation.

FIGURE 2-26 Script utlpwdmg.sql file

fig2-26.jpgFIGURE 2-26 Script utlpwdmg.sql file

2.14 Last Modified password Date/time

Recently or last attempt when user changed his/her password date and time can be display using SYS.USER$ log file.

Recently changed password time

2.15 Password Case Sensitive

Before login the user session check the user password case (upper case/lower case or mixed case) which is applied in CREATE USER or ALTER USER statement. Apply same password case in connect statement.

Whenever setting user password using CREATE OR ALTER USER statement by default passwords are case sensitive. For controlling case sensitive of the password use the ALTER SYSTEM privilege to set the SEC_CASE_SENSITIVE_LOGON initialization parameter is "TRUE" .

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE

If parameter value is False then it will disable the password case sensitivity.

password case sensitive

2.16 Drop Profile

Drop profile statement remove the profile. Suppose if the profile is assigned to some user, while removing it will give error. Avoiding error use CASCADE in the drop profile statement. If user profile is dropped it will assign automatically DEFAULT profile to user.

fig2-30_gUFEgQq.jpgdrop profile

drop profile

Unit Summary

End of this unit the reader get through knowledge about profile and password security. Several password aspects such as reusing password, incorrect password, validity of the password using life time and pasword lock time. Account status stages and their meaning.

Practical Exercise

1. Create Profile name as "HR_Manager_Prof", in this profile apply the following limits

- Password validity is 3 months

- Fifteen days before the password validity expiry the user get proper information.

- User is allowed to try three incorrect password attempts

- If incorrect password attempt exist then it will lock the user account two days

2. Create the user Induja and assign the profile the HR_Manager_Prof to her.Display the assigned profile to user Induja.

3. Modify the HR_Manager_Prof ,

- User is allow to work two concurrent session.

- User is allowed for only 60 minutes per session

- Change the user lock time is 6 hours

4. Display password created, expired date information

5. Which date and time the user recently changed the password?

6. Change the user password should not be case sensitive.

7. Prohibit the user Induja to access database?

8. Release access deny of the user Induja

9. Don’t allow if user password and username is identical

10. User never allow to reuse the same password

11. Remove HR_Manager_Prof profile from user Induja

12. Whenever the user Induja login her session first attempts, the user must type the new password.

Question

1. Explain "UNLIMITED" value apply in password_reuse_max and password_reuse_time

2. Compare Manual Lock and password_lock_time

3. Discuss various Account status of the password.

4. Differentiate connect time and idle time.

Video Tutorial Link

The below given youtube tutorial link explain unit 2 information.

Create Profile youtube Tutoiral

Lecturer Notes

References

1. http://www.dba-oracle.com/t_idle_time.htm

2. http://www.morganslibrary.org/reference/profiles.html

3. http://www.dba-oracle.com/t_password_security.htm

4. https://oracle-base.com/articles/11g/case-sensitive-passwords-11gr1

Return to top

Learning Goals