Authentication is a very important process, whose purpose is to determine whether someone or something is, in fact, who or what it claims to be.
In this chapter, you'll learn basic stuff about some of the different authentication methods supported by Oracle Database 12c. Also, a brief overview about creating and using database roles will be given.
There are three new administrative privileges introduced in Oracle Database 12c (sysbackup, syskm, and sysdg). Their purpose is to enable better separation of duties and they are designed in such a way to also enable implementation of the least privilege principle. Although it may seem that implementation of this principle in systems is easy or straightforward, usually it's quite tricky.
Note
For all recipes in this chapter, you will use non-CDB 12c. We assume that the database is up and running and each user has at least the
create session
privilege.
In this set of recipes, you will learn to perform, mostly basic, user administration tasks.
Creating a password profile
You can use a profile to implement your password policy.
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;
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_limi
t parameter is set to true
.
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.