Oracle provides the ability to set expirations, limit the reuse, and define the complexity of passwords. In addition, accounts can be locked if the password is entered incorrectly too many times. In this section of the lab we are going to create a custom profile that will then be applied to the SCOTT user.

To begin, log into your instance as the SYSTEM user.

Write SQL script that will create a new profile named SCOTT_PROFILE that will do the following:

Limit the number of failed login attempts to 3 in a row.

Limit the overall connection time to 10 hours (we will give him a little leeway incase he has to work overtime).

Allow a session to be idle no more than 1 hour.

Change the password every 60 days.

Allow the user 3 days to change the password after it expires.

Not allow a previous password be reused before there have been three password changes.

Execute your pfile script and verify that the profile has been created by running a query against the DBA_PROFILES view in the data dictionary. Limit your output to ONLY the SCOTT_PROFILE parameters.

While still logged into your instance as the SYSTEM user write and execute the SQL command that will assign the SCOTT_PROFILE profile to the SCOTT user.

Now log into SCOTT (password is TIGER). Remember that you must supply the database instance name when logging in from the SQL> prompt just as you do when using the login window, i.e. CONN SCOTT/TIGER@instance_name.

There are several things that we can test related to the logging in and changing a password so here we go.

You should now be successfully connected to the SCOTT user. Write the connect command again using an incorrect password. NOTE: you should get a warning message stating that you are no longer connected to Oracle. That is fine, just keep trying to log in.

Repeat the above process until you get the ORA-28000: the account is locked error which will indicate that the profile is working here.

At this point we need to get the account unlocked so you will need to login to your instance as the SYSTEM user and unlock the SCOTT account BUT DO NOT LOG BACK INTO THE SCOTT USER YET.

Now we can test the password reuse parameter. To do this we must EXPIRE the current password. Write and execute the SQL command to expire the password for the SCOTT user.

Now log back into the SCOTT user. You should receive a message stating that the password has expired (ORA-28001: the password has expired) and then prompting you to change the password.

Try to reuse the TIGER password. You should receive the following - ORA-28007: the password cannot be reused.

Now log into the SCOTT user again and this time change the password to LION to complete this step of the lab.