FAQ
Does anybody have a dynamic sql script that pulls user definitions (including current passwords) into a text file? I need to move users from a 10g database over to an 11g database. They want to keep their current passwords.

Search Discussions

  • TJ Kiernan at Mar 4, 2011 at 5:30 pm
    Since password are hashed, this will take some fancy footwork. I
    haven't tried this, but the approach seems like a decent candidate
    (unless the hashing algos change between versions).



    Thanks,

    T. J.



    The information contained in this message is privileged and confidential
    information intended only for the use of the individual or entity
    identified above. If the receiver of this message is not the intended
    recipient, you are hereby notified that any dissemination, distribution,
    use or copying of this message is strictly prohibited. If you have
    received this message in error, please immediately notify the sender by
    replying to his/her e-mail address noted above and delete the original
    message, including any attachments. Thank you.



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of P D
    Sent: Friday, March 04, 2011 11:08 AM
    To: oracle-l@freelists.org
    Subject: create user with existing password



    Does anybody have a dynamic sql script that pulls user definitions
    (including current passwords) into a text file? I need to move users
    from a 10g database over to an 11g database. They want to keep their
    current passwords.
  • Goulet, Richard at Mar 4, 2011 at 6:30 pm
    I've moved a lot of users from one database to another over the years.
    The easiest way to keep the existing password is to find it in sys.user$
    and then use the identified by values clause. Worlds from 8i to 11G R2.


    Dick Goulet
    Senior Oracle DBA/NA Team Leader



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of TJ Kiernan
    Sent: Friday, March 04, 2011 12:31 PM
    To: pdba1966_at_hotmail.com; oracle-l@freelists.org
    Cc: TJ Kiernan
    Subject: RE: create user with existing password

    Since password are hashed, this will take some fancy footwork. I
    haven't tried this, but the approach seems like a decent candidate
    (unless the hashing algos change between versions).



    Thanks,

    T. J.



    The information contained in this message is privileged and confidential
    information intended only for the use of the individual or entity
    identified above. If the receiver of this message is not the intended
    recipient, you are hereby notified that any dissemination, distribution,
    use or copying of this message is strictly prohibited. If you have
    received this message in error, please immediately notify the sender by
    replying to his/her e-mail address noted above and delete the original
    message, including any attachments. Thank you.



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of P D
    Sent: Friday, March 04, 2011 11:08 AM
    To: oracle-l@freelists.org
    Subject: create user with existing password



    Does anybody have a dynamic sql script that pulls user definitions
    (including current passwords) into a text file? I need to move users
    from a 10g database over to an 11g database. They want to keep their
    current passwords.
  • Joel.Patterson_at_crowley.com at Mar 4, 2011 at 6:47 pm
    I am sure this works. I have/had a script to 'become_u'. which essentially saves off the encrypted password using a values clause sql statement as shown below, then changes the password to something simple, logs in, and then executes the saved statement and deletes the saved statement.

    It started to generate alert log errors, I believe ora-0600 in 11g databases. An analyst at metalink said it was never supported or documented.... (surprise to me since I used it since oracle 8). So... they started talking about proxy users and such... (just more complication), but the bottom line is, you might expect to see some errors show up...

    Joel Patterson
    Database Administrator
    904 727-2546

    From: oracle-l-bounce_at_freelists.org On Behalf Of TJ Kiernan
    Sent: Friday, March 04, 2011 12:31 PM
    To: pdba1966_at_hotmail.com; oracle-l@freelists.org
    Cc: TJ Kiernan
    Subject: RE: create user with existing password

    Since password are hashed, this will take some fancy footwork. I haven't tried this, but the approach seems like a decent candidate (unless the hashing algos change between versions).

    Thanks,
    T. J.

    The information contained in this message is privileged and confidential information intended only for the use of the individual or entity identified above. If the receiver of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, use or copying of this message is strictly prohibited. If you have received this message in error, please immediately notify the sender by replying to his/her e-mail address noted above and delete the original message, including any attachments. Thank you.

    From: oracle-l-bounce_at_freelists.org On Behalf Of P D
    Sent: Friday, March 04, 2011 11:08 AM
    To: oracle-l@freelists.org
    Subject: create user with existing password

    Does anybody have a dynamic sql script that pulls user definitions (including current passwords) into a text file? I need to move users from a 10g database over to an 11g database. They want to keep their current passwords.
  • Japplewhite_at_austinisd.org at Mar 4, 2011 at 5:31 pm
    I've used this SQL in a script that runs against the old DB and gets all
    Users but the built-in ones like Sys, System, etc. (User_ID >
    ). It makes sure to set their Temp tablespace correctly, in
    case it wasn't - some of our Apps create Users with Temp tablespace =
    System. I do System and Role privs in different scripts, as well as
    tablespace Quotas. Then I take those files to the new DB and run them in
    order.

    Select 'Create User ' || UserName || || Chr(10) ||
    'Identified by Values ''' || Password || '''' || Chr(10) ||
    'Default Tablespace ' || Default_Tablespace || Chr(10) ||
    'Temporary Tablespace Temp;'
    From DBA_Users
    Where User_ID > 33
    Order By UserName

    /

    Hope this helps.

    Jack C. Applewhite - Database Administrator
    Austin I.S.D. - MIS Department
    512.414.9250 (wk) / 512.935.5929 (pager)

    From: P D
    To:
    Date: 03/04/2011 11:11 AM

    Subject: create user with existing password
    Sent by: oracle-l-bounce_at_freelists.org

    Does anybody have a dynamic sql script that pulls user definitions
    (including current passwords) into a text file? I need to move users
    from a 10g database over to an 11g database. They want to keep their
    current passwords.
  • Rodd Holman at Mar 4, 2011 at 7:08 pm
    This works,
    I'm currently using this to move users from a 10g to 11g database. The
    kicker is to remember that 11g, by default, now uses case sensitivity in
    the passwords. So, if they entered their password in all lower case in
    10g, that's still what they need to do.
    On 03/04/2011 11:31 AM, japplewhite_at_austinisd.org wrote:
    I've used this SQL in a script that runs against the old DB and gets
    all Users but the built-in ones like Sys, System, etc. (User_ID >
    ). It makes sure to set their Temp tablespace correctly,
    in case it wasn't - some of our Apps create Users with Temp tablespace
    = System. I do System and Role privs in different scripts, as well as
    tablespace Quotas. Then I take those files to the new DB and run them
    in order.

    Select 'Create User ' || UserName || || Chr(10) ||
    'Identified by Values ''' || Password || '''' || Chr(10) ||
    'Default Tablespace ' || Default_Tablespace || Chr(10) ||
    'Temporary Tablespace Temp;'
    From DBA_Users
    Where User_ID > 33
    Order By UserName
    /

    Hope this helps.

    Jack C. Applewhite - Database Administrator

    From: P D
    Subject: create user with existing password
    --
    http://www.freelists.org/webpage/oracle-l
  • D'Hooge Freek at Mar 4, 2011 at 7:38 pm
    Also, keep in mind that in 11g (not sure it was since 11gR1 or only in 11gR2) passwords expire by default after 180 days... .
    So best to either create a new profile (which I prefer) and put the users in this profile or alter the default profile.

    regards

    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge_at_uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer

    --
    From: oracle-l-bounce_at_freelists.org On Behalf Of Rodd Holman
    Sent: vrijdag 4 maart 2011 20:08
    To: oracle-l@freelists.org
    Subject: Re: create user with existing password

    This works,
    I'm currently using this to move users from a 10g to 11g database.  The kicker is to remember that 11g, by default, now uses case sensitivity in the passwords.  So, if they entered their password in all lower case in 10g, that's still what they need to do.

    On 03/04/2011 11:31 AM, japplewhite_at_austinisd.org wrote:
    I've used this SQL in a script that runs against the old DB and gets all Users but the built-in ones like Sys, System, etc. (User_ID > ).  It makes sure to set their Temp tablespace correctly, in case it wasn't - some of our Apps create Users with Temp tablespace = System.  I do System and Role privs in different scripts, as well as tablespace Quotas.  Then I take those files to the new DB and run them in order.

    Select 'Create User '            || UserName ||           || Chr(10) ||
    'Identified by Values ''' || Password || ''''      || Chr(10) ||
    'Default Tablespace '     || Default_Tablespace    || Chr(10) ||
    'Temporary Tablespace Temp;'
    From   DBA_Users
    Where  User_ID       > 33
    Order By UserName
    /

    Hope this helps.

    Jack C. Applewhite - Database Administrator

    From:        P D
    Subject:        create user with existing password
    --
    http://www.freelists.org/webpage/oracle-l
  • Goulet, Richard at Mar 4, 2011 at 7:40 pm
    That started in 11g R1 which also introduced case sensitive passwords by default.

    Dick Goulet
    Senior Oracle DBA/NA Team Leader

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of D'Hooge Freek
    Sent: Friday, March 04, 2011 2:39 PM
    To: rodd.holman_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: create user with existing password

    Also, keep in mind that in 11g (not sure it was since 11gR1 or only in 11gR2) passwords expire by default after 180 days... .
    So best to either create a new profile (which I prefer) and put the users in this profile or alter the default profile.

    regards

    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge_at_uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer
    --
    From: oracle-l-bounce_at_freelists.org On Behalf Of Rodd Holman
    Sent: vrijdag 4 maart 2011 20:08
    To: oracle-l@freelists.org
    Subject: Re: create user with existing password

    This works,
    I'm currently using this to move users from a 10g to 11g database.  The kicker is to remember that 11g, by default, now uses case sensitivity in the passwords.  So, if they entered their password in all lower case in 10g, that's still what they need to do.

    On 03/04/2011 11:31 AM, japplewhite_at_austinisd.org wrote:
    I've used this SQL in a script that runs against the old DB and gets all Users but the built-in ones like Sys, System, etc. (User_ID > ).  It makes sure to set their Temp tablespace correctly, in case it wasn't - some of our Apps create Users with Temp tablespace = System.  I do System and Role privs in different scripts, as well as tablespace Quotas.  Then I take those files to the new DB and run them in order.

    Select 'Create User '            || UserName ||           || Chr(10) ||
    'Identified by Values ''' || Password || ''''      || Chr(10) ||
    'Default Tablespace '     || Default_Tablespace    || Chr(10) ||
    'Temporary Tablespace Temp;'

    From   DBA_Users
    Where  User_ID       > 33
    Order By UserName
    /

    Hope this helps.

    Jack C. Applewhite - Database Administrator

    From:        P D
    Subject:        create user with existing password
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Powell, Mark at Mar 4, 2011 at 8:58 pm
    I will add that the dbms_metadata package can also be used to generate the create user statements and has the potential advantage if you work with an environment where some of the users are OS authenticated or are Globally authenticated that it will generate valid create user statements where the posted SQL appears to be designed for users with passwords only.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Goulet, Richard
    Sent: Friday, March 04, 2011 2:41 PM
    To: Freek.DHooge_at_uptime.be; rodd.holman_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: create user with existing password

    That started in 11g R1 which also introduced case sensitive passwords by default.

    Dick Goulet
    Senior Oracle DBA/NA Team Leader

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of D'Hooge Freek
    Sent: Friday, March 04, 2011 2:39 PM
    To: rodd.holman_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: create user with existing password

    Also, keep in mind that in 11g (not sure it was since 11gR1 or only in 11gR2) passwords expire by default after 180 days... .
    So best to either create a new profile (which I prefer) and put the users in this profile or alter the default profile.

    regards

    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge_at_uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer
    --
    From: oracle-l-bounce_at_freelists.org On Behalf Of Rodd Holman
    Sent: vrijdag 4 maart 2011 20:08
    To: oracle-l@freelists.org
    Subject: Re: create user with existing password

    This works,
    I'm currently using this to move users from a 10g to 11g database.  The kicker is to remember that 11g, by default, now uses case sensitivity in the passwords.  So, if they entered their password in all lower case in 10g, that's still what they need to do.

    On 03/04/2011 11:31 AM, japplewhite_at_austinisd.org wrote:
    I've used this SQL in a script that runs against the old DB and gets all Users but the built-in ones like Sys, System, etc. (User_ID > ).  It makes sure to set their Temp tablespace correctly, in case it wasn't - some of our Apps create Users with Temp tablespace = System.  I do System and Role privs in different scripts, as well as tablespace Quotas.  Then I take those files to the new DB and run them in order.

    Select 'Create User '            || UserName ||           || Chr(10) ||
    'Identified by Values ''' || Password || ''''      || Chr(10) ||
    'Default Tablespace '     || Default_Tablespace    || Chr(10) ||
    'Temporary Tablespace Temp;'

    From   DBA_Users
    Where  User_ID       > 33
    Order By UserName
    /

    Hope this helps.

    Jack C. Applewhite - Database Administrator

    From:        P D
    Subject:        create user with existing password
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • John Hallas at Mar 7, 2011 at 3:00 pm
    I have never been that keen on altering the default profile. No particular justification to support my view but I like to leave sys/system and dbsnmp alone.

    www.jhdba.wordpress.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of D'Hooge Freek
    Sent: 04 March 2011 19:39
    To: rodd.holman_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: create user with existing password

    Also, keep in mind that in 11g (not sure it was since 11gR1 or only in 11gR2) passwords expire by default after 180 days... .
    So best to either create a new profile (which I prefer) and put the users in this profile or alter the default profile.

    regards

    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge_at_uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer
    --
    From: oracle-l-bounce_at_freelists.org On Behalf Of Rodd Holman
    Sent: vrijdag 4 maart 2011 20:08
    To: oracle-l@freelists.org
    Subject: Re: create user with existing password

    This works,
    I'm currently using this to move users from a 10g to 11g database.  The kicker is to remember that 11g, by default, now uses case sensitivity in the passwords.  So, if they entered their password in all lower case in 10g, that's still what they need to do.

    On 03/04/2011 11:31 AM, japplewhite_at_austinisd.org wrote:
    I've used this SQL in a script that runs against the old DB and gets all Users but the built-in ones like Sys, System, etc. (User_ID > ).  It makes sure to set their Temp tablespace correctly, in case it wasn't - some of our Apps create Users with Temp tablespace = System.  I do System and Role privs in different scripts, as well as tablespace Quotas.  Then I take those files to the new DB and run them in order.

    Select 'Create User '            || UserName ||           || Chr(10) ||
    'Identified by Values ''' || Password || ''''      || Chr(10) ||
    'Default Tablespace '     || Default_Tablespace    || Chr(10) ||
    'Temporary Tablespace Temp;'

    From   DBA_Users
    Where  User_ID       > 33
    Order By UserName
    /

    Hope this helps.

    Jack C. Applewhite - Database Administrator

    From:        P D
    Subject:        create user with existing password
    --
    http://www.freelists.org/webpage/oracle-l

    Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

    If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
    If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

    This email does not constitute a contract in writing for the purposes of the Law of Property (Miscellaneous Provisions) Act 1989.

    Our Standard Terms and Conditions of Purchase, as may be amended from time to time, apply to any contract that we enter into. The current version of our Standard Terms and Conditions of Purchase is available at: http://www.morrisons.co.uk/gscop

    Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
    and it is the responsibility of recipients to carry out their own virus checks.

    --
    http://www.freelists.org/webpage/oracle-l
  • Yechiel Adar at Mar 7, 2011 at 11:14 am
    Data pump with include = users????
    On 04/03/2011 19:08, P D wrote:
    Does anybody have a dynamic sql script that pulls user definitions
    (including current passwords) into a text file? I need to move
    users from a 10g database over to an 11g database. They want to keep
    their current passwords.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 4, '11 at 5:08p
activeMar 7, '11 at 3:00p
posts11
users10
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase