Re: ARH-How syncronize database passwords
Date: Wed, 14 Jan 2009 06:33:24 -0800 (PST)
Message-ID: <2f8fa0d9-acec-4b7c-9a0a-f9c85ff72d74_at_v13g2000yqm.googlegroups.com>
On Jan 13, 2:34 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jan 13, 4:28 am, "ahanj..._at_gmail.com" <ahanj..._at_gmail.com> wrote:
>
> > I have around 40 odd oracle 10g databases that use the same username/
> > password through the application. Now I need to synchronize these
> > passwords anytime someone changes it on 1 database and this has to
> > happen in near/real time for my requirement to be met.
> > How can I do this completely or partially through Oracle?
>
> You may be able to use the alter user ... identified by values sql. I
> don't think this is documented, but you can grep for it in an old-
> style exp full=y rows=n to see the usage and value, or see the value
> in dba_users. Naturally, this is an extremely bad idea from a
> security viewpoint. I believe 11g doesn't allow this. You might
> break things if something requires a password file (which I would hope
> wouldn't be the case, but there are all sorts of weirdo apps out
> there, especially those that require the password to be hardcoded).
>
> The value is the same across databases, that's how exp/imp does it.
>
> jg
> --
> _at_home.com is bogus.
> Hope I die before I get... wait a minute...http://www3.signonsandiego.com/stories/2009/jan/13/1n13social001926-h...
I see two problems using exp/imp. The first is the time delay. In fact the time dely in propogation of the password change is a potential problem no matter which method is finally chosen if the application is constantly making new connections to one or more databases. In fact where is the application picking up the pasword to be used. Definite timing issue.
The second potential problem with exp/imp is that if the application usernamne is also an object owner then you are going to be exporting and the potentially importing objects that you do not want between databases. I am not familiar enought with the newer expdp/impdp options to know if I can just export the user definition and no other objects.
An alternate that would allow you to export the user password without knowing what it is might be to use the dbms_metadata package to generate the create user and then format the password into an alter user statement. The alter user statement could be applied using the method that follows or following other approaches to propogating commands.
I think the best approach is to not allow the password to be changed except during scheduled maintenance periods when a coordinated task can be ran. A simple script that connects to all 40 database instances and issues the alter user identified by command would then work plus the application password source file would also need updating.
IMHO -- Mark D Powell -- Received on Wed Jan 14 2009 - 08:33:24 CST