CODEWITHSUNDEEP
bashoracle-databaseshellencryptionpassword-encryption
Vidhi Chhabra
Vidhi Chhabra

Reputation: 1

How to encrypt and decrypt user password for oracle databases in bash script

Question: I want to create user for oracle databases. For that i have created shell script, it will ask to enter the username and password. then these variables will be stored in SQL query to get output. Please find the below commands:

**Here password is in plain text. Please tell how to encrypt and decrypt the password which is giving in input form. ? **

**echo -n "Enter user to be created"
read USERNAME

echo -n "Enter new password"
read PASSWORD

output=`sqlplus -s '/as sysdba' <<EOF
CREATE USER "$USERNAME" IDENTIFIED BY "$PASSWORD"
DEFAULT TABLESPACE "$DETAB"
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
exit;

EOF`**

Upvotes: 0

Views: 4700

Answers (2)

Dario
Dario

Reputation: 470

As suggested in the comments, here the revised script

echo -n "Enter user to be created"
read USERNAME

echo -n "Enter new password"
read PASSWORD

output=`sqlplus -s '/nolog' >/dev/null 2>/dev/null <<EOF
WHENEVER SQLERROR EXIT SQL.SQLCODE
connect / as sysdba
CREATE USER "$USERNAME" IDENTIFIED BY "$PASSWORD"
DEFAULT TABLESPACE "$DETAB"
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
exit; 
EOF`
success=`echo $? `
unset PASSWORD
echo $USERNAME
echo $success
echo $output
echo $PASSWORD

if $success returns 0 everything is ok. if not there is an error. But $output and $PASSWORD are empty at the end of the execution For debug purpose, do not redirect to /dev/null but you will see the password.

Passwords are stored encrypted inside Oracle as already said in the comments by other people helping you

Other suggestion I think that the better solution is to force the User to Change the Password at First/Next Login

e.g Create the user with :

  1. the option by a password expire
  2. a dummy password and password expire option
  3. expire the password using the command alter user $USERNAME password expire;.

By doing this Oracle will ask to the user to update his password at the first or next connection. The password issue will be managed by sqlplus directly (or by other tools).

You can add in your script conn $USENAME/<dummypwd> and sqlplus will prompt for the new password

Upvotes: 0

pmdba
pmdba

Reputation: 7043

Your use of shell variables in general is a problem, from a security perspective. The instant you store the password in a shell variable, it can be read by other users on the system. A better approach would be to prompt the user for input as part of the SQL script, rather than in the shell:

create_user.sql:

-- get username
accept username char prompt 'Enter user to be created > ';

-- get password and hide value from screen 
accept password char prompt 'Enter password > ' HIDE;

-- get default tablespace
accept detab char prompt 'Enter default tablespace > ';

set echo off;

CREATE USER &&username IDENTIFIED BY &&password
DEFAULT TABLESPACE &&detab
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

exit;

Then run the script like this:

sqlplus / as sysdba @create_user.sql

The script can now run without exposing the password value to the shell, or being echoed to the screen.

I've written previously on these types of issues. See here for more info: https://pmdba.wordpress.com/2020/01/13/how-to-hide-oracle-passwords-in-a-script/

Upvotes: 2

Related Questions