Creating a password profile

You can use a profile to implement your password policy.

Getting ready

To complete this recipe, you'll need an existing user who has create profile privilege (such as an OS-authenticated user who has database administrators (dba) role, for example, ops$zoran). Also, you'll need an unlocked user account named scott.

Make sure that the resource_limit parameter is set to true.

How to do it...

1. Connect to the database as a user who has create profile privilege:

          sqlplus /
   
   

2. Create a password profile:

          create profile userprofile limit
          failed_login_attempts 4
          password_lock_time 2
          password_life_time 180;
   

3. Alter the user to use a newly created password profile:

          alter user scott profile userprofile;
   
   

4. Alter the default password profile:

          alter profile default limit
   failed_login_attempts 4;
   
   

How it works...

In step 1, you used OS authentication to connect to the database.

In step 2, you created a password profile with the name userprofile that has the following restrictions:

• The system allows four login attempts before locking a user account (failed_login_attempts)

• After locking a user account, it will remain locked for two days (password_lock_time)

• A password for the user can remain unchanged for 180 days - after which the password will expire, and the user will have to change the password for his next login (password_life_time)

In step 3, we assigned a newly created password profile to the user scott. If we don't assign a password profile to the user, that user uses the default password profile.

In step 4, we altered the default password profile with the failed_login_attempts restriction.

There's more...

You can create different password profiles for different users in the database. There are a lot of restrictions that can be applied to a password profile.

In Oracle Database 12c, there are three password verify functions, out of which, two are new and improved:

• verify_function_11G (carried over)

• ora12c_verify_function (new)

• ora12c_strong_verify_function (new)

If password complexity checking is not enabled, and you want to use it, you should run the utlpwdmg.sql script provided by Oracle. It's located in $ORACLE_HOME/rdbms/admin. The ora12c_verify_function function is the default function that the utlpwdmg.sql script uses. If you want, you can customize password verify functions.

Note

Password complexity checking, even when enabled, doesn't apply to sys user.

If you want to choose which verify function will be used in the default profile, you can achieve that by using the following statement:

alter profile default limit password_verify_function ora12c_strong_verify_function;

In subsequent recipes, it is assumed that default values are set for the default profile and the password verify function is not used.

See also

• Creating password-authenticated users

• Locking a user account

• Creating and using OS-authenticated users