Profile using Oracle

Dr.Girija Narasimhan

These days everyone using transaction through interenet. Online transactions are not only convenient and it makes layman to save his/her time and easy to do any transaction. For every online transaction we need login, login id / user name  and password is compulsory.  There are various techniques used for securing transaction. 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 database object. 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 Disk storage space, I/O bandwidth to run queries, CPU power, connect time and so on.

Creating profile

Profiles only take effect when resource limits are “turned on” for the database as a whole. Specify the RESOURCE_LIMIT initialization parameter to TRUE (the default is FALSE). Use the ALTER SYSTEM statement to turn on resource limits

Use the CREATE PROFILE statement to create a user profile as “customer_profile”. The “customer_profile” is the name of the profile. Here four limits are assigned to customer_profile that is password_Life_time, password_Grace_time, Failed_login_attempts and password_Lock_time.

Password_Life_time and Password_grace_time

In the above created customer_profile it is given that password_life_time is 10 that means the user can use the password only 10 days or otherwise the password validity is only 10 days. How users know how many days are still remaining for changing password i.e reason password_grace_time limit is defined. Password_grace-time here it is given as 3 means 3 days. Three days before it will show message to user only three times are remaining to change new password like alert message that is 7th day of password life time it will start showing the status of the password. For example, the user created 27-6-2016 and profile is assigned same days or whenever user creation time. Then user password will expire 07-7-2016. Then user get alert message from 04-07-2016 onwards. Here Raghav is a database user created and assigned customer_profile. The DBA_USERS data dictionary view display when user password is created and expiry of the password. In this example both profile and user created same date. The user password is “raghav123”-theidentified by keyword followed by is password. Using Alter User statement assign profile customer_profileis to user Raghav.  There is two method to assign profile to user eithercreating the user i.e create user statement itself you can assign the profile to user or  using alter user statement also it is possible to assign profile to user.

Failed_login_attempts and Password_Lock_time

Suppose user “Raghav” try to connect his session using incorrect password four attempts, then account status become “lock”. The Failed_login_attempts 4 means it will allow user to try four attempts incorrect password. Password_Lock _time 2 means it will lock the account two days, thrid day it will automatically activate the user account as “open” status. If password_lock_time is not mentioned in the profile, by default it will lock the user account only one day.

Figure 4-Try First attempt with incorrect password

Figure 7-Try fourth attempt with incorrect password now account status is “Locked”

Password_lock_time can be automatically released once lock time is elapsed.  Otherwise manually unlock the user account as possible.  Suppose if the user lock his/her account manually then he/she should open/ unlock account manually.

On-line Application implementation

I have taken my state bank of india online account screen shot as example, that is a reason I hide the account number and branch. Whenver login the bank account there is messagae box “my Recent online activities” are appearing and showing failed login attempts details, when password changed, when is your last session etc.,   This sample screen shot is a proof  how profile concepts are implemented in the online banking security application.

The youtube link for the profile video is available in given below link: “


[1] Patricia Huey (2014) Oracle Database, Security Guide 11g Release 2 (11.2), E36292-05