Operating System - HP-UX
1748089 Members
4922 Online
108758 Solutions
New Discussion юеВ

Creating 800 users in oracle

 
SOLVED
Go to solution
Adrian_72
Advisor

Creating 800 users in oracle

How I can to create 800 users for example:

create user cap101 identified by cap101
default tablespace siebeldata
temporary tablespace temp;
grant sse_role,create session to cap101;

and is the same for cap102 to cap900

you know any script for run fast this creation;

17 REPLIES 17
Steven E. Protter
Exalted Contributor
Solution

Re: Creating 800 users in oracle


while read -r username
do

sqlplus '/as sysdba' << EOF
create user cap101 identified by $username
default tablespace siebeldata
temporary tablespace temp;
grant sse_role,create session to $username;
EOF


done < userlist


This methodology might work.
all you need is a text list of users one per line in the file userlist.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Adrian_72
Advisor

Re: Creating 800 users in oracle

Steven my database is working in windows2000

how I can run your instructions?

Thank you
Jean-Luc Oudart
Honored Contributor

Re: Creating 800 users in oracle

Hi,
I would suggest user only one session
Generate the SQL script 1st :
#!/bin/sh

echo " " | awk '{
for(i=101;i < 901;i++) {
printf("create user cap%d identified by cap%d default tablespace siebeldata tem
porary tablespace temp;\n",i,i);
printf("grant sse_role,create session to cap%d;\n",i);
}
}'

then use outpout as SQL script
sqlplus / @
Regards,
Jean-Luc

fiat lux
Jean-Luc Oudart
Honored Contributor

Re: Creating 800 users in oracle

no access to Unix/shell/awk ?

let's do it with SQL programming
create the following SQL script and save output file.
Run the output file to create users :
##################################
set serveroutput on;
declare
VAR2 number;

begin
-- server output buffer resize :
dbms_output.enable(20000);

FOR VAR2 in 101..120 LOOP
dbms_output.put_line('create user cap'||VAR2||' identified by cap'||VAR2||' default tablespace siebeldata temporary tablespace temp;');
dbms_output.put_line('grant sse_role,create session to cap'||VAR2||' ;');
END LOOP;
end;
/
##################################

change upper limit accordingly.
note : you may have to change dbms_output.enable limit as well.

Regards,
Jean-Luc
fiat lux
Adrian_72
Advisor

Re: Creating 800 users in oracle

Thank you Jean;

If i have a list of users how i can create this users whit te same password an the same grant for all.

example of my users:

joudart
aguerrero
evalle
leljure
.
.
.
etc


John Wimmer_1
Advisor

Re: Creating 800 users in oracle

You could use simialr logic as above and either
a) Load you names into an Oracle table then
spool creator.sql
select 'create user '||name||' identidied by password default tablespace siebeldata temporary tablespace temp;',
'grant role_1, connect to '|| name
from my_name_table;
spool off
@@creator.sql

b) Use your favorite programming language to do the same thing
Read record
create sql statement
run sql statement
loop

Adrian_72
Advisor

Re: Creating 800 users in oracle

Steven
if I have a file with the usernames and other file with the passwords

How I can create the users

Hein van den Heuvel
Honored Contributor

Re: Creating 800 users in oracle

This quesions was also asked (accidently?) in:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=655054
There I answerred:

Hmmm, scary thought to have two seperate lists for that. They could not generate them into one list?

Anyway... this problem is much similar to:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=650699

One potential solution in awk:

awk '{getline pass < "passwords.dat"; print "create user",$1,"identified by",pass,"default blah blah..."}' usernames.dat

So here the main awk loop reads "usernames" and feeds them into $0, $1 and so on.
THen for every record read, we start py reading a line from "passwords" into symbol pass and print that along with the sql text and $1 (the first and only field from "usernames".

Good luck.

Hein

[Moderator... delete thread 655054 ? ]
Adrian_72
Advisor

Re: Creating 800 users in oracle

Hein Van Den;

Thank you

I execute the next:
awk '{getline pass < "passwords.dat"; print "create user",$1,"identified by",pas
s,"default tablespace siebeldata temporary tablespace temp; grant sse_role,creat
e session to",$1,";"}' usernames.dat > /tmp/users/yo.sql

but I can't execute the yo.sql file

The yo.sql file is:

create user CAP101 identified by DEMO1 default tablespace siebeldata temporary tablespace temp; grant sse_role,create session to CAP101 ;

I need insert a line before the grant
how I can put this line?