Basics of Password Authentication in Oracle 12c
Password protection is the most common way to protect data in computer systems, including Oracle 12c. This truth applies to bank ATMs, websites, and of course your Oracle database. Password protection helps establish identity. Passing this verification is the first step in showing you’re a trusted member of the club.
Nowadays when you create the database:
Default accounts are locked.
SYS and SYSTEM passwords are chosen and set during database creation.
Password security is enhanced by forcing complex passwords.
With these measures, the database is fairly secure as soon as it’s created.
How to enforce password security with profiles in Oracle 12c
A password profile is a mechanism in the database that forces a user to follow guidelines when creating or changing passwords. The guidelines help provide stronger security in the system by not allowing weak passwords.
The following are bad ideas for creating passwords, and neither you nor your users should do any of these things. Otherwise, you’re opening the door to uninvited guests. So don’t consider
Making the password the same as the username
Making password your password
Reusing the same password when the system asks you to change
Having to remember complex passwords is sometimes inconvenient, but accept it as part of your responsibility. Otherwise, you may at some point have to take the blame for someone guessing your password.
Password profiles prevent each of the problems in the preceding list. Password profiles are a database administrator (DBA) tool, and they let you do the following:
Limit the number of times a password can be reused. If you want to give your users a break, let them reuse the password twice — but that’s it.
Limit the amount of time before a password can be reused. Maybe you let them reuse the password, but they have to wait 90 days to do so.
Limit failed login attempts. If this number is met, you can lock the account until a security administrator unlocks it or for a certain period of time.
Assign a password lock time. If someone (or something) reaches the limit you set for the failed login attempts setting, you can force a waiting period before the user can try again. This setting can help against brute force attacks, where a machine bombards your database with a password cracker.
Give passwords a time limit (or in Oracles terminology, a limited life time). When this life time is met, the system asks the user to change his password.
Have a password grace time. When the life time is reached, the user is prompted with You have X number of days to change your password.
Check password complexity. A verification function
Makes sure the password and username are different.
Makes sure the new password differs from the previous by three characters.
Ensures the password is made up of alphabetical, numeric, and special characters.
You can create your own password verify function and attach it to a profile. A password verify function is a program written in PL/SQL (Procedural Language/Structured Query Language) that examines passwords when they’re chosen and accepts or rejects them based on criteria.
If you have special password requirements, you can write your own password verify function and assign it to your password profile by using the PASSWORD_VERIFY_FUNCTION attribute of the profile.
Oracle supplies a standard password verify function with the database. By default, it ensures the following:
The password is not the same as the username (forward and backward).
The password is more than seven characters.
The password is not the same as the server name.
The password is not a common poor choice, such as welcome1, password, database, abcdefg.
To use Oracle’s provided password verify function, follow these steps:
Log in to the database using SQL*Plus as SYS.
Run the following:
This step creates the default password verify function and assigns it to the DEFAULT profile. If you’re comfortable with PL/SQL, you can even take Oracle’s example file and modify it to fit your needs.
How to create a password profile in Oracle 12c
To create a password profile, follow these steps:
Log in to the database via SQL*Plus as SYSTEM.
Create the profile and limit the failed login attempts, password lock time, and password life time:
<CREATE PROFILE report_writer LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1/96 PASSWORD_LIFE_TIME 90;>
In this example, failed login attempts are limited to three, password lock time is limited to 15 minutes, and password life time is limited to 90 days.
You see this:
The password lock time in the preceding code is 1/96. In Oracle time, that is 15 minutes. The whole number 1 is 1 day, and 1/24 is one hour. Divide 1/24 by 4 and you get 1/96 (or 15 minutes).
Assign the report writer user profile to a user:
<ALTER USER hr PROFILE report_writer;>
This example assigns the new profile to the HR user. You see this in return:
The DEFAULT profile in Oracle 12c
What if you don’t give your users a profile? In that case, all users have the DEFAULT profile.
By default in Oracle 12c, the DEFAULT profile limits the following:
FAILED_LOGIN_ATTEMPT = 10
PASSWORD_GRACE_TIME 7 (DAYS)
PASSWORD_LIFE_TIME 180 (DAYS)
PASSWORD_LOCK_TIME 1 (DAY)
PASSWORD_VERIFY_FUNCTION NULL (no complexity enforced)
You can edit your profile or the DEFAULT profile. For example, to change the failed login attempts setting to 3 on the DEFAULT profile, type the following:
<ALTER PROFILE default LIMIT FAILED_LOGIN_ATTEMPTS 3;>
You see this: