Password Verification in Oracle

Back to: Oracle DBA Tutorials

Password Verification in Oracle with Examples

In this article, I am going to discuss Password Verification in Oracle with Examples. Please read our previous article where we discussed the Creation of Profiles in a Pluggable Database with Examples.

Password Verification in Oracle

Until now we have learned how to create a common profile and how to create a local profile. Now, we will be learning about password verification. In the profile, we used password verification. Let us go ahead and open the below location in the virtual machine. We are opening this below path.

In the below path, we can see there are multiple files present.

In these files, we have to search for a file named utlpwdmg.sql. this file manages the password verification. In the oracle documentation, this file is mentioned as creating a verification script for profiles. But actually, this doesn’t do the creation.

Let’s go ahead and open the file.

This script only alters the default profile and makes changes to the script with the values that we have provided. In the end, we can see PASSWORD_VERIFY_FUNCITON is taken as ora12c_verify_fucntion. This is the task the script does. This just alters the default function. Let’s go ahead and search for another file called catpvf.sql

This file is used to create the inbuilt profile functions. Let’s go ahead and open the file.

From the above image, we can see this is creating a function called ora_complexity_check. We need not run this script because these are default functions in the oracle database. Let us try to do a practice with password verification.

Step 1: Connect to Pluggable Database.

Open Linux virtual machine and start the listener and start the database. Once the database is started, open the SQL Developer, and connect to the database as “sqlplus / as sysdba”. Let’s go ahead and check the database name and the user.

Step 2: Check the Verify Functions

We are going to run a query to check the functions with the VERIFY name in it. We are going to check the VERIFY functions in the database. Previously we have seen there are multiple verify functions.

• ORA12C_VERIFY_FUNCTION
• ORA12C_STRONG_VERIFY_FUNCTION
• VERIFY_FUNCTION_11G

All the above functions have a word verify in common. So, we are using the word verification to get the details of the functions.

Query: select * from dba_objects where object_name like ‘%VERIFY%’ and object_type=’FUNCTION’;

We can see there are multiple functions. These functions are present in the container database and the status is valid.

Step 3: Check the Functions in Container Database.

In order to check these functions in the container database we have to move to the container database. Let’s go ahead and move to the container database.

Command: alter session set container=jayapdb;

Now, we are in the container database. Let’s go ahead and check the functions by running the same query.

Query: select * from dba_objects where object_name like ‘%VERIFY%’ and object_type=’FUNCTION’;

We have all the same functions as in the container database. These functions are valid, so no need to run any scripts.

Step 4: Create a Profile with Oracle Defined Functions.

We are going to create a profile with the function ORA12C_VERIFY_FUNCTION. We will be using this function for the parameter. The function ORA12C_VERIFY_FUNCTION has the below requirements:

• The password contains no fewer than 8 characters and includes at least one numeric and one alphabetic character
• The password is not the same as the username or the username is reversed.
• The password is not the same as the database name.
• The password does not contain the word oracle(oracle123).
• The password differs from the previous password by at least 8 characters.
• The password contains at least 1 special character.
• The password doesn’t contain the double quotation character (“). However, it can be surrounded by double- quotation marks.

Statement:

CREATE PROFILE TEST_PROFILE LIMIT PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION;

So, the profile is created. Let’s check the details of the profile.

Query: select * from dba_profiles where profile=’TEST_PROFILE’;

We can see the values of all the parameters are default. There is one parameter that is mentioned as ORA12_VERIFY_FUNCTION. This is the function that we have added to the profile.

Note: When there is a DEFAULT option then the profile will read from the default profile.

Step 5: Test the Profile

We are going to create a user and assign this profile TEST_PROFILE to that user while creating. If the password met the requirements of the ORA12C_VERIFY_FUNCTION then the user is created or else SQL Developer will throw an error. Let’s go ahead and check with a sample example. We are going to create a user with the username test20.

Statement: create user test10 identified by welcome profile TEST_PROFILE;

So, we got an error that shows password verification failed. We are assigning a profile to the user and this profile has password verification, and that verification failed because the password ‘welcome’ is less than 8 characters. Let us try another password welcome1234.

Statement: create user test10 identified by welcome1234 profile TEST_PROFILE;

This error indicates that there should be at least a special character. Now, let’s try to add a special character to the password. We will be using the password welcome#1234.

Statement: create user test10 identified by welcome#1234 profile TEST_PROFILE;

We can see the user is created. Let’s grant privileges to the user.

Statement: grant create session to test10;

Step 6: Test the user

We are going to test the user by logging in as the test10 user in the virtual machine. Open the terminal and connect to the user test10.

Command: sqlplus test10/welcome#1234@jayapdb

Let’s try to change the password for the user test10.

Statement: alter user test10 identified by welcome#5678;

The error has the number ORA-28221 error occurs when a user tries to reset his own password without specifying the REPLACE keyword provided the user does not have ALTER USER privileges and the user is having a profile with a password verify function. So, there should be a word replacement in the statement.

Command: alter user test10 identified by welcome#5678 replace welcome#1234;

So, when a user doesn’t have to alter system user privileges then the user needs to do the above replace command in order to reset the password on his own. So, this is the information about user security and admin security in the oracle database.

In the next article, I am going to discuss Password Verification in Oracle with Examples. Here, in this article, I try to explain Password Verification in Oracle with Examples. I hope you enjoy this Password Verification in Oracle article.