1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Script to retrieve all users and their passwords in oracle 12c

Discussion in 'SQL PL/SQL' started by sunil, Mar 7, 2016.

  1. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Zargon,
    Here, I am doing, DB cloning from Prod to Test Environment. Before starting this process, I would like to take backup of all the users and their passwords in Test Environment. So, Could you please help me in finding all the users and their passwords for storage in the Oracle 12c Environment.


    Thanks,
    Sunil
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You will need more than simply usernames and passwords to preserve the current user information in the test environment, unless all you are planning on doing is an 'alter user ... identified by ...;' statement. And if that's the case what will happen when you preserve usernames and passwords for TEST users not in PROD but fail to retrieve the privileges those users have been granted? Such an 'alter user' statement will fail to find the username provided and you'll lose those accounts.

    Your best bet is to use expdp to export the database, metadata only, to capture all of the required information to re-create those accounts. I can't guarantee that any script you write, no matter how thoroughly thought out, will actually work as expected when the time comes to re-create the users. An example expdp command is shown below:

    expdp directory=data_pump_dir dumpfile=users.dmp logfile=users.log full=y content=metadata_only

    Your choice of directory depends on what is configured in your 12c database; modify the command accordingly to work in your environment.

    You mention 'the Oracle 12c Environment' but what does that mean with respect to YOUR installation? Is 12c setup as a non-container database? If so then your job is easy and nothing differs from such an export run against 11.2.0.x and all users and grants will be exported. If this IS a container database then you have a different set of tasks ahead of you, as you'll need to connect to each of the individual pluggable databases to get the user data you desire.

    Replicating user accounts isn't an easy task, and it certainly isn't a job for a script. Using the proper tool for the job is the first step toward success.
     
    sunil likes this.
  3. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Zargon,
    I forgot to mention in the description, As I am using only non-container database.
    thanks for your information. Now, I got the clear picture and will update you once it is done.


    Thanks,
    Sunil
     
  4. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Zargon,
    I have a question that, we have all the passwords in Encrypt format, Is it possible to Decrypt the password? If yes, Can you please tell me the process?

    Thanks,
    Sunil
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, it isn't. Oracle uses a proprietary hashing algorithm to obscure the passwords. You should be able to do this:

    create user ... identified by values '<hashed password>';

    which should create the user with the existing password even though you don't know it.
     
  6. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Thanks for the info. But what actually, am looking is, In my environment, we didn't store the passwords of all the users. So, We are trying to get back all the existing passwords in decrypt format for all the users to store in the one place.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That's not a good idea, actually, since anyone could possibly hack that location and obtain user accounts and passwords to your system. This is why Oracle stopped storing clear text passwords in the database. Also storing clear text passwords violates most, if not all, security audits.
     
    sunil likes this.
  8. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Thanks for the information Zargon.