3 Replies Latest reply: Feb 27, 2013 4:51 PM by rp0428 RSS

    Sharing passwords between multiple instances

    729625
      I have an applicaiton made up of 8 order databases identical based on thier structure. Most users but not all are created in each instance, but the passwords for each account is independent of one another. Is there a tool available to keep these all in sync that I should look into?

      Ideally, I don't want users to have to remember 8 separate passwords. So when they change it on one, it should change it on all IF the account exists on other sites.

      This has got to be possible (at least in my head), i know that i can export passwords from one site or another, so i was thinking that there has to be a way to code this, let alone some already provided component from Oracle.

      Thanks!

      (running an 11g Database on a Sun OS)
        • 1. Re: Sharing passwords between multiple instances
          ji li
          Well, separating instances from databases, I'm assuming you are referring to databases, and not instances.
          An instance only lives in memory, and the database is physically on disks (or similar media).

          As to your question, Oracle Enterprise Single Sign-on, but that might be a little too robust.
          I believe there is another option that is part of the Advanced Security Option (or something like that).
          I have not used it but remember learning about it some time back as part of my OCP learning.
          • 2. Re: Sharing passwords between multiple instances
            sb92075
            http://www.oracle.com/webapps/dialogue/ns/dlgwelcome.jsp?p_ext=Y&p_dlg_id=12063392&src=7665797&Act=46&sckw=WWMK12065371MPP004.GCM.8100.100

            or

            LDAP
            • 3. Re: Sharing passwords between multiple instances
              rp0428
              >
              This has got to be possible (at least in my head), i know that i can export passwords from one site or another, so i was thinking that there has to be a way to code this, let alone some already provided component from Oracle.
              >
              If you have to 'roll your own' you can set up auditing to trap the password change. See the SQL Language doc
              http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4007.htm
              >
              sql_statement_shortcut

              Specify a shortcut to audit the use of specific SQL statements. Table 13-1 and Table 13-2 list the shortcuts and the SQL statements they audit.

              Note:

              Do not confuse SQL statement shortcuts with system privileges. For example:
              •An AUDIT USER statement specifies the USER shortcut for auditing of all CREATE USER, ALTER USER, and DROP USER SQL statements. Auditing in this case includes an operation in which a user changes his or her own password with an ALTER USER statement.

              •An AUDIT ALTER USER statement specifies the ALTER USER system privilege for auditing of all operations that make use of that system privilege. Auditing in this case does not include an operation in which a user changes his or her own password, because that operation does not require the ALTER USER system privilege.
              >
              Then this can trigger a proc that changes the password on the other systems by capturing the info from USER$ and issuing ALTER USER . . . IDENTIFIED BY VALUES.
              http://laurentschneider.com/wordpress/2008/03/alter-user-identified-by-values-in-11g.html

              NOTE: you should use a master site to issue these changes from and will need to prevent circular references. That is, if you have auditing enabled on all systems and don't use a master site then every password change will trigger changes on the other systems, which will trigger changes on the other systems, etc.

              You audit procedure will need to detect if the change comes from your master site to keep the loop from happening.