0

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`**

Improve this question
11
  • 1
    At what point to do you want to encrypt and decrypt it, and why? It needs to be plain text in the identified by clause; Oracle will hash it for its internal storage. (As a one-way hash, not encrypted - you can't get the plain text back from the hashed value, by design.) Commented Jun 11, 2021 at 10:13
  • 1
    I'm not sure what you mean. I understand not storing it anywhere as plain text, but it has to be plain text when it's used. Do you mean that when the script prompts for the password, you want the user to enter the encrypted password, and have your script decrypt it to pass to the create user statement? Commented Jun 11, 2021 at 10:25
  • 2
    But during execution your script will know the plain text, and will encrypt and decrypt back to plain text... so what is the point, that doesn't add any security? Nothing is being stored. You could, I suppose, encrypt the password in the script and pass that encrypted value into an Oracle procedure that decrypts and creates the user, but both sides need the same mechanism and key, and seems a lot of work for little benefit - unless you're worried about someone snooping on the DB connection. Commented Jun 11, 2021 at 11:10
  • 2
    Which point are you protecting? Someone could snoop the shell session, or the user's keyboard... not trying to be facetious, but you need to figure out what risk you are combating and the lengths you want to go to. If there is a risk across the network between shell and Oracle then like I said you could pass it into Oracle encrypted; then decrypt in and create with plain text there (which will then be hashed internally, yes). But encrypting it only within the shell script doesn't really make sense. Commented Jun 11, 2021 at 11:30
  • 1
    if the intention is to keep from echoing the password as the user enters said password, try read -s PASSWORD; the value entered by the user will still be 'unencrypted' but it shouldn't be displayed on the user's terminal Commented Jun 11, 2021 at 12:24

2 Answers 2

Reset to default
2

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/

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

What if we need to create multiple users . if we give Username,password,tablespace name in a text file. Then once user created. password values will be replaced to ******* so that hardcoded passwords are not visible.
Check the link I included. It proposes a different way to handle multiple users. You're best off never storing plain text passwords in a file.
Thanks a lot. It works. If i store the output of the SQL query like Display the user names and tablespace names. can i store that in a variable shell?
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.