OraFAQ Forum: SQL & PL/SQL » How To Update Encrypted Password for all user id's using Update Query

Home » SQL & PL/SQL » SQL & PL/SQL » How To Update Encrypted Password for all user id's using Update Query (Oracle 11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How To Update Encrypted Password for all user id's using Update Query [message #652351]

Tue, 07 June 2016 12:30

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

Report message to a moderator

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

John Watson
Messages: 8929
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.

Report message to a moderator

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

ramraju20000
Messages: 8
Registered: September 2012

Junior Member

Can you please provide sample query for that using the above function ...password is "p@ssword123"

Report message to a moderator

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

John Watson
Messages: 8929
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.

Report message to a moderator

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

ramraju20000
Messages: 8
Registered: September 2012

Junior Member

Hi John .....it helps me a lot if you provide a sample query

Report message to a moderator

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

Michel Cadot
Messages: 68641
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.

Report message to a moderator

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

ramraju20000
Messages: 8
Registered: September 2012

Junior Member

got it ...Thanks for the reply Smile

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68641
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

Report message to a moderator

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

EdStevens
Messages: 1376
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'

Report message to a moderator

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

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.

Report message to a moderator

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

Solomon Yakobson
Messages: 3273
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

EdStevens
Messages: 1376
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

Solomon Yakobson
Messages: 3273
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 22:00:02 CDT 2024