CODEWITHSUNDEEP
sqloracle-databasesql-insert
Graham J
Graham J

Reputation: 497

SQLPlus INSERT INTO "(USER)"."USERS" (USER_NAME, USER_PASS) < "how to hash password"

Good morning,

I'm certain this is a duplicate question but struggling to word this properly since I am not an SQL person, I've just been tasked with this.

I'm using ORACLE SQLPlus, in SQLDeveloper I have "Other Users" > SuperAdmin with a table below called USERS.

To add a new user to this database I'm using:

INSERT INTO "SUPERADMIN"."USERS" (USER_NAME, USER_PASS) VALUES ('SUPERTEST', 'NewPassword01')

It adds my user, but how do I hash out the plain text password?

Again, my sincerest apologies if this is a duplicate.

Upvotes: 1

Views: 868

Answers (1)

MT0
MT0

Reputation: 168416

Assuming your USER_PASS column has a RAW data type then use the STANDARD_HASH function:

INSERT INTO SUPERADMIN.USERS (
  USER_NAME,
  USER_PASS
) VALUES (
  'SUPERTEST',
  STANDARD_HASH( 'NewPassword01', 'SHA1' )
);

(Valid hash algorithms for the second argument are: SHA1, SHA256, SHA386, SHA512 or MD5.)

db<>fiddle here

Update

I've had a look at the settings, the USER_PASS is Simple VARCHAR2.

You should really work out how your current system is hashing the string and encoding it because if you don't do exactly the same method then things will go wrong.

You could use UTL_RAW.CAST_TO_VARCHAR2 if you want to convert the RAW value to a string without any encoding:

INSERT INTO USERS (
  USER_NAME,
  USER_PASS
) VALUES (
  'SUPERTEST',
  UTL_RAW.CAST_TO_VARCHAR2(
    STANDARD_HASH( 'NewPassword01', 'SHA1' )
  )
);

You could use RAWTOHEX to convert the RAW output if you want to encode it as a hexadecimal string:

INSERT INTO USERS (
  USER_NAME,
  USER_PASS
) VALUES (
  'SUPERTEST',
  RAWTOHEX( STANDARD_HASH( 'NewPassword01', 'SHA1' ) )
);

You could also use UTL_ENCODE.BASE64_ENCODE if you wanted the value in a Base64 encoding:

INSERT INTO USERS (
  USER_NAME,
  USER_PASS
) VALUES (
  'SUPERTEST',
  UTL_RAW.CAST_TO_VARCHAR2(
    UTL_ENCODE.BASE64_ENCODE(
      STANDARD_HASH( 'NewPassword01', 'SHA1' )
    )
  )
);

As you can see they all take the same input but they give very different values:

SELECT * FROM superadmin.users;

Outputs:

USER_NAME | USER_PASS                               
:-------- | :---------------------------------------
SUPERTEST | %U)?Q0?????lR? xm/                     
SUPERTEST | 255529AE5130DAB082A29CAC6C52BC20786D2F01
SUPERTEST | JVUprlEw2rCCopysbFK8IHhtLwE=

db<>fiddle here

Upvotes: 2

Related Questions