Home » SQL & PL/SQL » SQL & PL/SQL » How To Update Encrypted Password for all user id's using Update Query (Oracle 11)
How To Update Encrypted Password for all user id's using Update Query [message #652351] Tue, 07 June 2016 12:30 Go to next message
ramraju20000
Messages: 8
Registered: September 2012
Junior Member
Hi All,

I have a table which contains the "Hash" Column as Encrypted Password for each userd id.

The Encrypted password using SHA-512 Algorithm, I want to update the Password for all userid's to a default password in one shot.


can you please suggest how can i achieve this using SQL Update query ...by storing the encrypted password in the Hash coulmn

(Example : if i want to update my default pasword as "p@ssword123" how can i encrypt this using sha 512 and store in that column)

Please suggest on this.

Thanks in Advance.
Ram

Re: How To Update Encrypted Password for all user id's using Update Query [message #652352 is a reply to message #652351] Tue, 07 June 2016 12:36 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
dbms_crypto.hash can generate sha2 512bit hashes. You'll need to grant access to the package from a sysdba account.
Re: How To Update Encrypted Password for all user id's using Update Query [message #652353 is a reply to message #652352] Tue, 07 June 2016 12:39 Go to previous messageGo to next message
ramraju20000
Messages: 8
Registered: September 2012
Junior Member
Can you please provide sample query for that using the above function ...password is "p@ssword123"
Re: How To Update Encrypted Password for all user id's using Update Query [message #652354 is a reply to message #652353] Tue, 07 June 2016 12:41 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
Can you please provide consulting fees? Smile

Or, failing that, show some evidence that you have at least looked up the function in the docs.

I notice that you have NEVER provided any feedback on your previous topics. That does not make me inclined to assist further. Goodbye.

Re: How To Update Encrypted Password for all user id's using Update Query [message #652355 is a reply to message #652353] Tue, 07 June 2016 12:42 Go to previous messageGo to next message
ramraju20000
Messages: 8
Registered: September 2012
Junior Member
Hi John .....it helps me a lot if you provide a sample query
Re: How To Update Encrypted Password for all user id's using Update Query [message #652356 is a reply to message #652355] Tue, 07 June 2016 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sure, can you send all the work you have to do and we will do it as soon as possible for free.

Re: How To Update Encrypted Password for all user id's using Update Query [message #652357 is a reply to message #652356] Tue, 07 June 2016 13:04 Go to previous messageGo to next message
ramraju20000
Messages: 8
Registered: September 2012
Junior Member
got it ...Thanks for the reply Smile
Re: How To Update Encrypted Password for all user id's using Update Query [message #652365 is a reply to message #652357] Tue, 07 June 2016 14:32 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Simply google the string

dbms_crypto.hash

There are MANY examples on the web. Google is your friend
Re: How To Update Encrypted Password for all user id's using Update Query [message #652369 is a reply to message #652365] Tue, 07 June 2016 15:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You mean like this message he received in one of his previous topics: Smile

LKBrwn_DBA wrote on Tue, 24 June 2014 19:31
Your Google broken?
Here, you can use mine: Google

Re: How To Update Encrypted Password for all user id's using Update Query [message #652414 is a reply to message #652353] Wed, 08 June 2016 07:39 Go to previous messageGo to next message
EdStevens
Messages: 1333
Registered: September 2013
Senior Member
ramraju20000 wrote on Tue, 07 June 2016 12:39
Can you please provide sample query for that using the above function ...password is "p@ssword123"



Really?

Have you seen what happens when you 'sqlplus myuser/p@ssword123@mydb'
Re: How To Update Encrypted Password for all user id's using Update Query [message #652415 is a reply to message #652414] Wed, 08 June 2016 08:01 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Ed,
I suspect that this is a hashed password for an application, you can't do an update to change the password for an oracle user, only an alter user command.
Re: How To Update Encrypted Password for all user id's using Update Query [message #652418 is a reply to message #652414] Wed, 08 June 2016 09:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3067
Registered: January 2010
Location: Connecticut, USA
Senior Member
EdStevens wrote on Wed, 08 June 2016 08:39

Really?

Have you seen what happens when you 'sqlplus myuser/p@ssword123@mydb'


Not an issue, just an inconvenience of using quoted names:

SQL> grant create session to u1 identified by p@ssword123;
grant create session to u1 identified by p@ssword123
                                          *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> grant create session to u1 identified by "p@ssword123";

Grant succeeded.

SQL> connect u1/p@ssword123@pdb1sol12
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Warning: You are no longer connected to ORACLE.
SQL> connect u1/"p@ssword123"@pdb1sol12
Connected.
SQL> 

SY.

[Updated on: Wed, 08 June 2016 09:15]

Report message to a moderator

Re: How To Update Encrypted Password for all user id's using Update Query [message #652427 is a reply to message #652418] Wed, 08 June 2016 11:45 Go to previous messageGo to next message
EdStevens
Messages: 1333
Registered: September 2013
Senior Member
Solomon Yakobson wrote on Wed, 08 June 2016 09:14

Not an issue, just an inconvenience of using quoted names:


Sy -

While I knew about the quoting trick, I guess we differ on when an "inconvenience" rises to becomes a "problem".
Which means we also differ on when a "technique" becomes a "trick".
Personally, I consider any use of double-quotes in Oracle to be stepping out on to thin ice.

Smile

[Updated on: Wed, 08 June 2016 11:46]

Report message to a moderator

Re: How To Update Encrypted Password for all user id's using Update Query [message #652433 is a reply to message #652427] Wed, 08 June 2016 18:30 Go to previous message
Solomon Yakobson
Messages: 3067
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, I don't see how it can be a problem. In fact, it is completely transparent to pretty much every tool except SQL*Plus. Toad, SQL Navigator, SQL Developer do not require quoting at all. And for old school like me Smile quoting doesn't bother at all (or maybe it's because I drive stick and refuse to drive automatic Smile ).

SY.
Previous Topic: Read/write files from remote server using utl_file in oracle
Next Topic: Rebuild table contain partition
Goto Forum:
  


Current Time: Sun Aug 01 17:31:36 CDT 2021