Oracle 10g & PowerShell - How to create hashed password

Question

I have a PowerShell script that will create a new Oracle user. The code is correct, and the user is created as expected. My only problem is replicating Oracle's password hashing.

I'd like to have the ability to hash the password in PowerShell using the same algorithm as Oracle 10g and then insert the hashed password into Oracle using this code. IDENTIFIED BY VALUES 'NEED TO INSERT HASHED PASSWORD'.

Here's an example of a current password in the database: IDENTIFIED BY VALUES '033348433385A2DE'

I tried a few things in PowerShell but couldn't get it to work. According to my understanding, Oracle uses the username & password combination to generate the hashed value, among other things. Any advice would be greatly appreciated.

This is the code I'm currently using. It establishes an Oracle connection and then inserts the required information.

$login_newname = 'SomeName'
    
$query_user =  CREATE USER $login_newname
      IDENTIFIED BY VALUES 'NEED TO INSERT HASHED PASSWORD'
      DEFAULT TABLESPACE USER_DATA
      TEMPORARY TABLESPACE TEMPORARY_DATA
      PROFILE DEFAULT
      ACCOUNT UNLOCK"

$connectionstring = "User Id = $username;Password = $password;Data Source = $datasource"
$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection(“$connectionString”)
$con.open()
$cmd=$con.CreateCommand()



$query_user_1 = "GRANT CONNECT TO $login_newname WITH ADMIN OPTION"
$query_user_2 = "GRANT RESOURCE TO $login_newname WITH ADMIN OPTION"
$query_user_3 = "GRANT ALL_ACCESS TO $login_newname WITH ADMIN OPTION"
$query_user_4 = "ALTER USER $login_newname DEFAULT ROLE CONNECT, RESOURCE"
$query_user_5 = "GRANT SELECT ANY TABLE TO $login_newname WITH ADMIN OPTION"
$query_user_6 = "GRANT UNLIMITED TABLESPACE TO $login_newname WITH ADMIN OPTION"


$cmd.CommandText=$query_user 
$cmd.ExecuteNonQuery() | Out-Null
$cmd.CommandText=$query_user_1
$cmd.ExecuteNonQuery() | Out-Null
$cmd.CommandText=$query_user_2
$cmd.ExecuteNonQuery() | Out-Null
$cmd.CommandText=$query_user_3
$cmd.ExecuteNonQuery() | Out-Null
$cmd.CommandText=$query_user_4
$cmd.ExecuteNonQuery() | Out-Null
$cmd.CommandText=$query_user_5
$cmd.ExecuteNonQuery() | Out-Null
$cmd.CommandText=$query_user_6
$cmd.ExecuteNonQuery() | Out-Null

Answer 1

I have managed to find a work-around for anyone that needs to replicate the same functionality.

I have defaulted this line like so IDENTIFIED BY VALUES 'password' .This will now create a user in Oracle with a password of 'password' but it won't apply the hashing.

I had to add an extra line:

$query_user_7 = "ALTER USER $login_newname IDENTIFIED BY password(This can be anything, your desired password)"

And then execute the query like so:

$cmd.CommandText=$query_user_7
$cmd.ExecuteNonQuery() | Out-Null

This will apply Oracle hashing to your new password and will allow you to login.

Please note, this works for Oracle 10g. I have not tried to do this on later Oracle versions.