Dynamically Creating User Schema with User Supplied Password using Batch Programming

Introduction

In this tip, we will see how we can setup the whole production level schema using Batch processing. You will be surprised to see how easy and simple the implementation is.

Background

During our onsite PADSS assessment, our PA QSA suddenly requested to freshly create our schema using his own supplied passwords (our application uses more than 10 Oracle Schemas). Previously, we had created user schemas using hardcoded passwords.

This critical gap could hit our assessment hard and we had to have a solution that could work in hours.

Using the Code

We immediately thought of batch processing as an immediate remedy and it worked. Below is the sample batch file that we used. The whole idea was to take password as user input and forward it as parameter to core files which will take that parameter and process the DDL.

REM FOR SYS 

@ECHO OFF

SET /P sysPwd=Please enter SYS Password: 
IF "%sysPwd%"=="" GOTO Error

REM FOR TEST_USER

SET /P testUserPwd=Please enter TEST_USER Password: 
IF "%testUserPwd%"=="" GOTO Error

sqlplus SYS/"%sysPwd%"@Omni AS SYSDBA @C:\Scripts\01-TestUserScript.sql %testUserPwd%   

01-TestUserScript.sql constant can be like this:

CREATE USER CORE_BANKING_INTERFACE
    IDENTIFIED BY &1
    PROFILE USER_PROFILE
    ACCOUNT UNLOCK; 

Here we provided password as &1 parameter which is a different batch file.

This quick approach gave immediate relief to us leading to no critical gap in this area.
History

• 6/28/2013: Initial post