This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jan 18, 2013 3:17 PM by user11919409 RSS

Needs to create a user in 100 databases

user11919409 Newbie
Currently Being Moderated
I have to create a user in 100 databases. Manually logging into 100 databases & creating a user is very difficult for me..

So is there any way to do easily ?
  • 1. Re: Needs to create a user in 100 databases
    sb92075 Guru
    Currently Being Moderated
    user11919409 wrote:
    I have to create a user in 100 databases. Manually logging into 100 databases & creating a user is very difficult for me..

    So is there any way to do easily ?
    quantify easily.

    The procedure can be automated, but realize that this has MAJOR security risks.


    Do all the DBs reside on the same OS name?
  • 2. Re: Needs to create a user in 100 databases
    marksmithusa Journeyer
    Currently Being Moderated
    As sb said, it is possible to automate it, but will take you time to put together the script. The security implications are potentially very bad and, really, how often are you going to create a user for 100 databases? Is a one-time hassle worth a fairly long time spent scripting and creating a potential security exploit?

    If you really have to create a user for 100 databases often, you're probably part of a DBA team who have spent years administering those databases. If it's something that happens regularly, ask them how they've been doing it for so long. DBAs would not keep quiet if doing this part was a normal part of the day-to-day job, trust me...
  • 3. Re: Needs to create a user in 100 databases
    user11919409 Newbie
    Currently Being Moderated
    Atleast per month 2 to 3 requests coming to us for creating user or dropping some users in all 100 databases...

    or some times we need to changes in the 100 databases...

    Security is not a concern as of now .....

    i need some script or procedure to do that ..... i am not good in shell scripting or like pl/sql scripting ....

    So if you have any scripts please share with me ....
  • 4. Re: Needs to create a user in 100 databases
    sb92075 Guru
    Currently Being Moderated
    user11919409 wrote:

    i am not good in shell scripting or like pl/sql scripting ....
    shell scripting is OS dependent & you decided to not share your OS with us.

    what are you good at, besides asking others to do your job for free?
  • 5. Re: Needs to create a user in 100 databases
    EdStevens Guru
    Currently Being Moderated
    user11919409 wrote:
    Atleast per month 2 to 3 requests coming to us for creating user or dropping some users in all 100 databases...

    or some times we need to changes in the 100 databases...

    Security is not a concern as of now .....
    Then it WILL become a concern .. when it is too late.

    >
    i need some script or procedure to do that ..... i am not good in shell scripting or like pl/sql scripting ....

    So if you have any scripts please share with me ....
  • 6. Re: Needs to create a user in 100 databases
    Gamblesk Explorer
    Currently Being Moderated
    If you have a common username and password in every database you could fairly easily script it and have it ask for the username/password when you run the script then use those to connect to every database and run the create user command.

    That should avoid most of the security concerns and save you time/work.
  • 7. Re: Needs to create a user in 100 databases
    marksmithusa Journeyer
    Currently Being Moderated
    user11919409 wrote:
    Atleast per month 2 to 3 requests coming to us for creating user or dropping some users in all 100 databases...

    or some times we need to changes in the 100 databases...

    Security is not a concern as of now .....

    i need some script or procedure to do that ..... i am not good in shell scripting or like pl/sql scripting ....

    So if you have any scripts please share with me ....
    As I said, if you have this type of requests this often, then your more experienced colleagues will have already developed a process to do this quickly and (you better hope for all your jobs' sake) with security in mind. There's no way a DBA would do this manually if it was such a regular part of their job - they have far better things to do than spend half a day doing this at a time.

    You need to ask your colleagues for their process - why would you trust someone you don't know on a forum to give you something that's effective and safe over your colleagues who know the databases and the business environment that you work in? I know I wouldn't, especially if I didn't understand what they were giving me.

    Your comment of 'security is not a concern as of now' scares me - security is ALWAYS a concern. Are you saying that not one of these 100 databases is Production?
  • 8. Re: Needs to create a user in 100 databases
    Rob_J Journeyer
    Currently Being Moderated
    How about use the Enterprise Manager? All passwords should be stored in there and you just create one job and submit it to all the targets (DBs) you want to run it on.
  • 9. Re: Needs to create a user in 100 databases
    rp0428 Guru
    Currently Being Moderated
    >
    If you have a common username and password in every database you could fairly easily script it and have it ask for the username/password when you run the script then use those to connect to every database and run the create user command.

    That should avoid most of the security concerns and save you time/work.
    >
    ABSOLUTELY WRONG!

    Far from avoiding most of the security concerns having a common username and password in every database would be a MAJOR security concern that should be addressed before even worrying about anything else.

    That is TERRIBLE advice if you are at all suggesting that OP make the scripting job easier by adopting common users and passwords.
  • 10. Re: Needs to create a user in 100 databases
    marksmithusa Journeyer
    Currently Being Moderated
    rp0428 wrote:
    >
    If you have a common username and password in every database you could fairly easily script it and have it ask for the username/password when you run the script then use those to connect to every database and run the create user command.

    That should avoid most of the security concerns and save you time/work.
    >
    ABSOLUTELY WRONG!

    Far from avoiding most of the security concerns having a common username and password in every database would be a MAJOR security concern that should be addressed before even worrying about anything else.

    That is TERRIBLE advice if you are at all suggesting that OP make the scripting job easier by adopting common users and passwords.
    +100

    Especially considering this user will have at least the 'ALTER USER' privilege (and what are the chances that the OP would just use a system user to do this anyway? Ugh)
  • 11. Re: Needs to create a user in 100 databases
    user11919409 Newbie
    Currently Being Moderated
    Thanks Rob_J     ... Using Grid control seems to be a good idea....but we are not using Grid control ....




    I am looking for some script as follows,

    A shell script that executes a command in all the databases with the input of only dbname & sql

    1.     Run.sh
    2.     Execute.sql
    3.     Dblist.txt

    Run.sh script should have the input like dblist.txt
    Dblist.txt should have the list of 100 database names.
    Execute.sql should have the sql that you need to execute in all 100 databases. Suppose if you have any other idea please let me know …


    ( Or )

    Is there any oracle tools or third party tools to do that ?
  • 12. Re: Needs to create a user in 100 databases
    vlethakula Expert
    Currently Being Moderated
    You can give try like (assumption u have common system password across all databases)
    1)Take one database , in that ORACLE_HOME keep all tns entries of your environment.
    $ORALCLE_HOME/network/admin/tnsnames.ora------------->Should contain tns entries of all databases

    2)List all the databases in one file say dblist.lst
    cat dblist.lst
    test1
    test2

    3)create sql file which contains following command; create_user.sql

    cat create_user.sql

    set echo off
    set heading off
    create user <username> identified by <password> default tablespace <xyz>;
    grant create session to <username>;
    other privs

    4) keep environmental variables of ORACLE_HOME,PATH in file say env_ORACLE

    5)shell script should like

    for i in `cat dblist.sql`
    do
    . /<complete path where env file present>/env_ORACLE
    sqlplus -s system/<password>@$i @create_user.sql
    done
  • 13. Re: Needs to create a user in 100 databases
    EdStevens Guru
    Currently Being Moderated
    user11919409 wrote:
    Thanks Rob_J     ... Using Grid control seems to be a good idea....but we are not using Grid control ....




    I am looking for some script as follows,

    A shell script that executes a command in all the databases with the input of only dbname & sql

    1.     Run.sh
    2.     Execute.sql
    3.     Dblist.txt

    Run.sh script should have the input like dblist.txt
    Dblist.txt should have the list of 100 database names.
    Execute.sql should have the sql that you need to execute in all 100 databases. Suppose if you have any other idea please let me know …


    ( Or )

    Is there any oracle tools or third party tools to do that ?
    great. Then if I can get that password, I have access to all 100 databases. That (using the same username/password on multiple databases) would have been a terminating offense at my last job. I sure hope you are not a service bureau handling the database for my bank.

    If, as you have indicated, this is a multiple-occurance requirement, you've got far bigger issues than 'how do it script this'.

    Edited by: EdStevens on Jan 18, 2013 12:18 PM
  • 14. Re: Needs to create a user in 100 databases
    Gamblesk Explorer
    Currently Being Moderated
    My mistake wasn't thinking of it from that perspective. Was just thinking it would be easy to not have a username/password in the filesystem somewhere and did not think that fully through.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points