OraFAQ Forum: Security » How to setup Password rotation

Home » RDBMS Server » Security » How to setup Password rotation (Oracle 11g)

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

How to setup Password rotation [message #669521]

Fri, 27 April 2018 00:38

MeghaB
Messages: 22
Registered: April 2018

Junior Member

Hi,

Please let me know is there any method to do password rotation without any downtime. If so, please let me the steps for doing that.

Thanks in advance.

Report message to a moderator

Re: How to setup Password rotation [message #669526 is a reply to message #669521]

Fri, 27 April 2018 03:34

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

What do you mean by "password rotation"? If you want to change a password, just do it. There is no downtime.

Report message to a moderator

Re: How to setup Password rotation [message #669557 is a reply to message #669526]

Sun, 29 April 2018 22:28

MeghaB
Messages: 22
Registered: April 2018

Junior Member

Hi John,

We have more than 200 schemas and we need to change the password for each schema without breaking the application. As of now we are keeping some downtime to change the password to avoid any discrepancies. Since its a production environment i want to know is there any method in Oracle 11g where we can change the password without keeping any downtime.

Thanks,
Megha

Report message to a moderator

Re: How to setup Password rotation [message #669558 is a reply to message #669557]

Sun, 29 April 2018 23:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Application downtime is NOT required to change any password.
Existing session continue unimpeded & new sessions needs to use new password(s).

Who "designed" & approved an application with 200+ different schemas?
What was their plan for password maintenance?

Report message to a moderator

Re: How to setup Password rotation [message #669561 is a reply to message #669558]

Mon, 30 April 2018 01:06

MeghaB
Messages: 22
Registered: April 2018

Junior Member

Each schema is maintained for each customer..

I meant if someone is performing some action with old password, in middle of that if we change the password it will show the wrong credentials or else time out will happen.
So to avoid all these we are keeping downtime for password change.

In postgres there is something like Credential Rotation. In that 3 roles will be there. One role is assigned all the database privileges needed for the application.The other two roles inherit the first role's privileges. It will allow only one of the roles access to login to the database at a time .

I want to know is there any approach similar to this is there in oracle.

Report message to a moderator

Re: How to setup Password rotation [message #669562 is a reply to message #669561]

Mon, 30 April 2018 01:12

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Quote:

I meant if someone is performing some action with old password, in middle of that if we change the password it will show the wrong credentials or else time out will happen.

There is only one "action" that requires a password: logging in to create a session. If you change the password, you have to tell the user that it has been changed. That is all. Nothing to do with any roles.

This sounds like a non-existent problem. Are you projecting a problem that some other database has on to an Oracle database?

Report message to a moderator

Re: How to setup Password rotation [message #669564 is a reply to message #669562]

Mon, 30 April 2018 02:22

MeghaB
Messages: 22
Registered: April 2018

Junior Member

From the java/.net code there might have already connection happened with the old password, but in middle without keeping any downtime we are changing the password. After the password change if they are running any queries it will fail. So to avoid this issue we are keeping downtime and changing password. After that we will inform the customers about the password change.

But we dont want to keep any downtime. This problem is not related to any other database.

As i am not aware of this i just want to know is there any method for doing this.

Report message to a moderator

Re: How to setup Password rotation [message #669565 is a reply to message #669564]

Mon, 30 April 2018 02:36

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Quote:

From the java/.net code there might have already connection happened with the old password, but in middle without keeping any downtime we are changing the password. After the password change if they are running any queries it will fail.

The only way this can be a problem is if your application is disconnecting and reconnecting in the middle of the process. That is an application design issue, and I do not see how any Oracle supplied database facility can correct it. Can you not fix the code so that it uses persistent sessions, or perhaps a connection pool, correctly?

Report message to a moderator

Re: How to setup Password rotation [message #669566 is a reply to message #669565]

Mon, 30 April 2018 04:20

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

Quote:

After the password change, if they are running any queries it will fail.

This is certainly not true! As explained, users that are logged in and querying the database will be unaffected.
They just need to use the new password when they want to log in again.

Report message to a moderator

Re: How to setup Password rotation [message #669571 is a reply to message #669565]

Mon, 30 April 2018 06:54

MeghaB
Messages: 22
Registered: April 2018

Junior Member

Actually we dont have access to code. We are managing the database side. We are using GoldenGate for replication as well. Usually we used to keep downtime for password change. John you mean to say downtime is not required for password change?

Report message to a moderator

Re: How to setup Password rotation [message #669573 is a reply to message #669571]

Mon, 30 April 2018 07:08

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

Unless your app is doing something highly unusual (like opening a new DB session anytime it does anything) no downtime is required.
As the others have said more than once the only time the password gets checked for a given oracle session is at logon.

Report message to a moderator

Re: How to setup Password rotation [message #669577 is a reply to message #669521]

Mon, 30 April 2018 08:34

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

As others keep trying to tell you, there is no down time from changing a password. Password is only checked when a new connection is requested. If your app continually disconnects and reconnects, that is an application design flaw.

You could write a simple sql script to change all the passwords. It would probably take only a second or two to execute. The more important question is, how do you inform all of the client processes of the changes?

Report message to a moderator

Re: How to setup Password rotation [message #669591 is a reply to message #669577]

Tue, 01 May 2018 22:16

MeghaB
Messages: 22
Registered: April 2018

Junior Member

The problem is that if we change the password for the user which has active connections to the database, future connections to the database will fail, until the application are updated with the new credentials.

So to overcome this is there any method to have two users which we can rotate credentials between. So we dont want to keep any downtime

Report message to a moderator

Re: How to setup Password rotation [message #669595 is a reply to message #669577]

Wed, 02 May 2018 04:12

MeghaB
Messages: 22
Registered: April 2018

Junior Member

Is there any way to create 2 users and do a password rotation between the users?

Report message to a moderator

Re: How to setup Password rotation [message #669596 is a reply to message #669595]

Wed, 02 May 2018 04:18

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Quote:

The problem is that if we change the password for the user which has active connections to the database, future connections to the database will fail, until the application are updated with the new credentials.

So tell your users that you have changed their passwords. You could even be proactive, and tell them that you ARE GOING to change their passwords. Or even tell them to change the passwords for themselves. You can configure a timed password expiry that will force them to, you know.

Quote:

Is there any way to create 2 users and do a password rotation between the users?

I don't understand this.

Are you sure that you are not inventing a problem that really doesn't exist?

Report message to a moderator

Re: How to setup Password rotation [message #669597 is a reply to message #669595]

Wed, 02 May 2018 04:22

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

How would rotating passwords between two users solve the problem?

And why is the problem a problem?
If you're going to change everyone's password why don't you just make them log in again?

Report message to a moderator

Re: How to setup Password rotation [message #669598 is a reply to message #669596]

Wed, 02 May 2018 04:40

MeghaB
Messages: 22
Registered: April 2018

Junior Member

John,

We will not give any access to customers to change the password from UI. we are doing it from the database.

I am not sure that is it possible in oracle to do password rotation. So if its not possible please let me know.

We will create 2 users User A and User B with database privilege needed for application. And will allow only one user(UserA) to login at a time. But at the time of password change will allow both users to login and will change the password of User B and redeploy the application and will make sure none of the connection is not using old password. After this again will allow only User B to login to the application with the new password. This is what i meant by password rotation between users.

Again i am not sure something like this is there or not. So if its not there i am really sorry.

Report message to a moderator

Re: How to setup Password rotation [message #669599 is a reply to message #669597]

Wed, 02 May 2018 04:45

MeghaB
Messages: 22
Registered: April 2018

Junior Member

We are keeping downtime for password change in order to avoid inconvenience for customers. We will run the query for changing password for set of schemes and will redeploy as of now. If we didn't keep downtime in middle of password change if some one is trying to login with old password it will fail. so we want to avoid that.

Report message to a moderator

Re: How to setup Password rotation [message #669600 is a reply to message #669597]

Wed, 02 May 2018 04:46

MeghaB
Messages: 22
Registered: April 2018

Junior Member

[quote title=cookiemonster wrote on Wed, 02 May 2018 04:22]How would rotating passwords between two users solve the problem?

two users for same schema with same permission

Report message to a moderator

Re: How to setup Password rotation [message #669601 is a reply to message #669600]

Wed, 02 May 2018 05:01

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

Still don't see how that would solve anything

Report message to a moderator

Re: How to setup Password rotation [message #669602 is a reply to message #669601]

Wed, 02 May 2018 05:06

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

We need a fair amount of clarification here.
You originally said you need to change the passwords of 200 schemas, now you are talking about just 2.
How do the 2 relate to the 200?
How do all the users actually login? Do they all have their own oracle account on the DB? Or are they all logging into the same account?
If the same account, how do you control that?

Report message to a moderator

Re: How to setup Password rotation [message #669603 is a reply to message #669602]

Wed, 02 May 2018 05:37

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

Also - can you please explain what you mean by password rotation.

The standard definition of password rotation is simply to change passwords regularly.
You appear to be using the term differently.

EDIT: missing word

[Updated on: Wed, 02 May 2018 05:37]

Report message to a moderator

Re: How to setup Password rotation [message #669693 is a reply to message #669603]

Tue, 08 May 2018 04:20

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

As I read it he wants to create two identical users with different credentials and let the application roll between them between password changes.

HOWEVER I'm unclear on how that fixes the purported "problem" because you'll still need to tell the app which to use.

@OP. You have an application issue, not a database one. Stop letting them make this your problem.*

*I say that, it's the story of my life...

Report message to a moderator

Re: How to setup Password rotation [message #669696 is a reply to message #669693]

Tue, 08 May 2018 07:55

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

Roachcoach wrote on Tue, 08 May 2018 10:20

As I read it he wants to create two identical users with different credentials and let the application roll between them between password changes.

I assumed that too.

Roachcoach wrote on Tue, 08 May 2018 10:20

HOWEVER I'm unclear on how that fixes the purported "problem" because you'll still need to tell the app which to use.

Indeed. I can't see where it gets the OP either, hence the questions.

Report message to a moderator

Re: How to setup Password rotation [message #669715 is a reply to message #669696]

Wed, 09 May 2018 04:10

MeghaB
Messages: 22
Registered: April 2018

Junior Member

To make much more clear. We have one database and 200 schemas. Each schema is related to each user.. Here i said one example only.. i dont want the exact implementation in the same way, since i have more schemas and its not possible to create one more user for each schema. But what i have mentioned is like once we change the password for the second user we will redeploy the app which will take the password of second user.

I want to know is there any way to change the password without keeping any downtime(without affecting customers.. ).

If nothing like that will close this. Since i am also not sure is this will work or not.

Thank you for all the replies..

Report message to a moderator

Re: How to setup Password rotation [message #669716 is a reply to message #669715]

Wed, 09 May 2018 04:42

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

As far as oracle is concerned there is a simple way to change the password with zero downtime:

You just change the password.

That's it.

So if changing the password in your application causes problems then that is due to how your application handles connecting to the DB.
If you want help avoiding issues with password change then you need to explain to us how the application handles connecting to the DB and why that's a problem for you.
With that information we should be able to suggest work arounds.

Report message to a moderator

Re: How to setup Password rotation [message #669741 is a reply to message #669716]

Thu, 10 May 2018 00:23

MeghaB
Messages: 22
Registered: April 2018

Junior Member

T=1
app1, app2, app3 use User1/password1
T=2
User2/password2 changed to new value
T=3
app1, app2,app3, are transitioned to User2/password2
T=4
User1 password changed to password2

Next change User2/password2 are transitioned to User1/password3

During transition secondary user is the user which handles db connections.

This is the scenario.

If we are changing the password, for already login users connection will not interrupt, but someone tries to login with the old password the app will throw error for wrong credentials. So how we can mange this

Report message to a moderator

Re: How to setup Password rotation [message #669742 is a reply to message #669741]

Thu, 10 May 2018 03:18

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

It's still not at all clear how users log on.
But either:
1) User supplies password for DB account - in which case they need to be informed of the new password.
2) Application supplies password for DB account - in which case password the app holds needs updating when the password is changed.

There's no magic here and I'm still none the wiser as to why this is such a problem for you.

[Updated on: Thu, 10 May 2018 03:19]

Report message to a moderator

Re: How to setup Password rotation [message #669743 is a reply to message #669742]

Thu, 10 May 2018 04:00

MeghaB
Messages: 22
Registered: April 2018

Junior Member

whenever db password in the application changes the app needs to redeploy to make the new password in effect which needs the downtime. I am talking about that downtime. Is there anyway to avoid this, without redeploying and taking the new password.

Report message to a moderator

Re: How to setup Password rotation [message #669744 is a reply to message #669743]

Thu, 10 May 2018 04:14

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

MeghaB wrote on Thu, 10 May 2018 10:00

whenever db password in the application changes the app needs to redeploy to make the new password in effect which needs the downtime

WHY?

Existing sessions will not be affected.

Report message to a moderator

Re: How to setup Password rotation [message #669745 is a reply to message #669743]

Thu, 10 May 2018 04:16

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you change the password then you need to supply the new one.
If the app needs to be redeployed in order to do that then it needs to be redeployed.
And how is changing over the user the app uses going to help? Won't you need to redeploy to do that too? In which case why bother (is it because you've got multiple apps and don't want to change them all in one go?)

The problem here is nothing to do with oracle. It is everything to do with the app.
You need to look into ways to change the password the app uses without having to redeploy it.

Report message to a moderator

Re: How to setup Password rotation [message #669746 is a reply to message #669744]

Thu, 10 May 2018 04:18

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

Roachcoach wrote on Thu, 10 May 2018 10:14

MeghaB wrote on Thu, 10 May 2018 10:00
whenever db password in the application changes the app needs to redeploy to make the new password in effect which needs the downtime
WHY?

Existing sessions will not be affected.

Seems they need to redeploy the app to have it pick up the new password, so no new sessions can start till that's done.

Report message to a moderator

Re: How to setup Password rotation [message #669747 is a reply to message #669745]

Thu, 10 May 2018 05:09

MeghaB
Messages: 22
Registered: April 2018

Junior Member

yes we need to redeploy that, Which will take enough downtime. Whether we can keep some other user(with same password) in oracle where we can switch to that user while changing the password of the active user.

Report message to a moderator

Re: How to setup Password rotation [message #669748 is a reply to message #669744]

Thu, 10 May 2018 05:16

MeghaB
Messages: 22
Registered: April 2018

Junior Member

Roachcoach wrote on Thu, 10 May 2018 04:14

MeghaB wrote on Thu, 10 May 2018 10:00
whenever db password in the application changes the app needs to redeploy to make the new password in effect which needs the downtime
WHY?

Existing sessions will not be affected.

New session will be affected, when it tries to connect to db with old password.

Report message to a moderator

Re: How to setup Password rotation [message #669749 is a reply to message #669747]

Thu, 10 May 2018 05:36

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

MeghaB wrote on Thu, 10 May 2018 11:09

yes we need to redeploy that, Which will take enough downtime. Whether we can keep some other user(with same password) in oracle where we can switch to that user while changing the password of the active user.

You can have some other user to connect to sure. And if you've got the privs and synonyms set right it'll work the same as the existing user (or you could use alter session set current_schema).
But how do you get the app to connect to a different user without redeploying it?

Report message to a moderator

Re: How to setup Password rotation [message #669750 is a reply to message #669749]

Thu, 10 May 2018 06:40

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

cookiemonster wrote on Thu, 10 May 2018 05:36

MeghaB wrote on Thu, 10 May 2018 11:09
yes we need to redeploy that, Which will take enough downtime. Whether we can keep some other user(with same password) in oracle where we can switch to that user while changing the password of the active user.

You can have some other user to connect to sure. And if you've got the privs and synonyms set right it'll work the same as the existing user (or you could use alter session set current_schema).
But how do you get the app to connect to a different user without redeploying it?

Indeed.
It seems the problem of telling the app to connect as a different user is no different than the problem of telling the app to connect with a different password. We await clarification from the OP.

Report message to a moderator

Re: How to setup Password rotation [message #669753 is a reply to message #669746]

Thu, 10 May 2018 07:51

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

cookiemonster wrote on Thu, 10 May 2018 10:18

Roachcoach wrote on Thu, 10 May 2018 10:14
MeghaB wrote on Thu, 10 May 2018 10:00
whenever db password in the application changes the app needs to redeploy to make the new password in effect which needs the downtime
WHY?

Existing sessions will not be affected.

Seems they need to redeploy the app to have it pick up the new password, so no new sessions can start till that's done.

Still not seeing a problem with a properly designed app tier tbh. Cycle one machine at a time, we do it all the time in here, albeit not for that reason.

Report message to a moderator