Creating 800 users in oracle

[Adrian_72]

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;

[Steven E. Protter · Accepted solution]

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

[Adrian_72]

Steven my database is working in windows2000

how I can run your instructions?

Thank you

[Jean-Luc Oudart]

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

[Jean-Luc Oudart]

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

[Adrian_72]

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]

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]

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]

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]

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?

[Hein van den Heuvel]

Ah, yes. Prior examples alrady showed how you can 'sprinkle' your print statement with "\n" to get a fresh new line.
Or... you can just use two or three print statements, one for each line.

awk '{getline pass < "passwords.dat"; print "create user",$1,"identified by",pass,"default tablespace siebeldata temporary tablespace temp;\n grant sse_role,create session to",$1,";"}' usernames.dat > /tmp/users/yo.sql

Hein.

[Yogeeraj_1]

hi,

You may wish to do it at the database level itself. try:
============================================
CREATE TABLE "MyUsers"
(
musername VARCHAR2(40)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY TEST
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
BADFILE 'nameload.bad'
LOGFILE 'name.log_xt'
READSIZE 1048576
FIELDS LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"DATA" CHAR(40)
)
)
location
(
'nameload.txt'
)
)REJECT LIMIT UNLIMITED
/


CREATE TABLE "MyPasswords"
(
mpassword VARCHAR2(40)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY TEST
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
BADFILE 'passwdload.bad'
LOGFILE 'passwd.log_xt'
READSIZE 1048576
FIELDS LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"DATA" CHAR(40)
)
)
location
(
'passwdload.txt'
)
)REJECT LIMIT UNLIMITED
/


begin
for r1 in (select name, pass from
(select rownum r1,name from MyUsers) tab1,
(select rownum r2,pass from MyPasswords) tab2
where tab1.r1=tab2.r2) loop
execute immediate 'create user '||r1.name||' identified by '||r1.pass||' default tablespace siebeldata temporary tablespace temp';
execute immediate 'grant sse_role,create session to '||r1.name;
--dbms_output.put_line('created user: '||r1.name||' identified by: '||r1.pass);
end loop;
end;
/
============================================

NB. Make appropriate changes to the code and using sqlplus check if the sqls: "select * from MyUsers" and "select * from MyPasswords" are returning the correct values first!

hope this helps too!

regards
Yogeeraj

[Jean-Luc Oudart]

If you have have the 2 files user and password, this will be (assuming in the right order) :

#!/bin/sh

paste userfile passfile | awk '{
printf("create user %s identified by %s default tablespace siebeldata temporary
tablespace temp;\n",$1,$2);
printf("grant sse_role,create session to %s;\n",$1);
}'

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

[Dilip Kumar_4]

Why don't you use PL/SQL to do this. Here is a jist of this.
---
begin
for i in 101..900
loop
execute immediate 'create user cap'||i||' identified by cap'||i;
end loop;
end;
---
May be use the administrator account to execute this.

[R. Allan Hicks]

If you don't want to keep track of individual passwords and you don't want them lying around on the disk in clear text, please consider the following script

create_myuser.sql

create user &1 identified by "password";
grant connect to &1;
alter user &1 password expire;
/


then set up a .bat

that basically runs

sqlplus sys/changeoninstall @create_myuser foome

* The above script creates foome.
* Give connect priv (or you can do roles or whatever. Made it simple for the example)
* Expire the password

The user logs on the first time

sqlplus foome/password

sqlplus says
ORA-280001: the password has expired

Changing password for foo2
New password:

He/She changes her password and you don't have to deal with it.

On the plus side, you don't have passwords laying around in clear text. On the minus side if you have one of your 800 that doesn't log in and change his password, you could have a security problem.

I used password as the original password which is a very bad choice, but makes a good example. You could pick another without any trouble or you could even make the password a variant of the username. Once again not a good security choice, but better than password. Or, add a second parameter and put the password in the script.

create user &1 identified by "&2";

or

create user &1 identified by "pass&1";

create_myuser foome

would create user foome identified by "passfoome"

-good luck

[Petr Simik_1]

Easy way is to use MS Excel and #concatenate function.
You can easily prepare your users and generate the script.
Very easy I recomend.

[Cary Campos_1]

I too have a situation that requires mass upload of users. This is what I am using to do a single user on 8.1 in win2000. I am a novice and would like help making the below script one that I can use to upload 600 users from an MS Excel spreadsheet or text file.

CREATE USER USER1 IDENTIFIED BY USER_PASS
DEFAULT TABLESPACE USER_DATA
TEMPORARY TABLESPACE TEMPORARY_DATA;
GRANT CONNECT TO USER1;
GRANT PW_ALL TO USER1;
COMMIT;

[John Wimmer_1]

Cary,

Check this thread out
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=655054

It has some ideas that you might want to use.