During my assignment with one of the Indian telecom client, the requirement was to – Existing DB2 password reset happens in BI, wherein login Ids and mail Ids are stored in table audit.user_details. After 45 days the password is reset with Unix scripts and the new password is sent to the mail ID corresponding to the login of the user. However at times this mail is not being delivered. Also if a user wants to reset a password before its expiry, it was not possible through the BI scripts. A separate IT ticket needed to be raised for password reset which is a tedious process.

The new functionality to be implemented requires this DB2 password reset to be done through SMART tool. When a user raises a request for a password reset via sms, SMART tool to validate user and reset his password. The new password should be sent to the user via sms. If the users account is locked, the account should be unlocked first and then the password should be reset and sent to user.

The existing all oracle/Sql Server the user password is getting reset via the SMART RESET application. This application accepts request via Mobile and do the necessary validation and then responded with the new password. This application uses the App Server to do the process and connecting to the requested database via jdbc connection for which the password change request initiated by the user, and will do the password change and shared back the new password back to the user via SMS.

In case of DB2 UDB, security consists of two parts, one is Authentication and Authorization.  We are having the default security plugin for the DB2 Authorization which is configured at the time of db2 installation/set up. i, e, User has to get the access privilege for the Aix Operating System and then have to get the CONNECT privilege for accessing the database as authorization to access the database.

The existing production DB2 UDB database, user and group authentication is managed in a facility external to DB2 UDB, i.e., operating system.

Below the DB2 Security model diagram,

After further analysis and working different options, I found a solution, i.e., by deploying Java API along with the external stored procedure. Through the DB2 Database using Java API, we can reset the password of any user in Operating System. The idea is to change the Operating system Password from the Database without connecting the Operating System via the JDBC call.

Component Model

The core components of this DB2 Password reset method is,

1. External Stored Procedure
2. Java API
3. Shell Script

Java API will be the interface of DB2 Database with Operating system, and the API will provide advantage of changing the password without connecting to the operating system. This is easier to use, remotely connect to the database and reset the password without interacting with the OS environment. This whole process will take only seconds.

Below the architecture of the component,

External Stored procedure:

The stored procedure can be defined either in SQL or in high level programming language. If the definition is in high level language then it is known as external procedure. Here we are pointing the Java class file. This java class file method is calling the password change script and setting the password.

Below the sample store procedure,

CREATE PROCEDURE change_pwd(IN param1 VARCHAR(1000), IN param2
VARCHAR(1000), OUT status VARCHAR(1000))
LANGUAGE java
PARAMETER STYLE java
FENCED THREADSAFE
EXTERNAL NAME 'PSWD.CHPS(java.lang.String, java.lang.String, java.lang.String[])';

Where param1 is the user id and param2 is the password.

 Java API:

This is a java class file which is calling the password change shell script to execute and set the password in the OS side.

Please refer the below sample code, 

Once the java class file is ready without any error, compile and generate the class file. To recognize the class file by the db2 stored procedure, move the class file under sqllib/function directory. The required privileges to be given to the file.

Shell Script:

Use chpasswd command and write a shell script (change_pass.sh) to set the password for the user. Also provide required access privileges to the change_pass.sh file and the ownership to the root only.

sudo chown root:root /home/bismart/test/change_pass.sh
sudo chmod 700 /home/<username>/change_pass.sh

 
Please check, whether all db scripts are internally running using the fenced user id. Set up sudo to allow change_pass.sh script to execute without requiring a password using the fenced user id. Below line to be added in sudoer’s file,

<db2 fence user id>  ALL=(ALL) NOPASSWD: /home/username/change_pass.sh

The above framework can be reused for different purposes like changing password without connecting to the OS, unlock user id, reset password and also can be used to generate the encryption and decryption of the table column.