Creating 800 users in oracle

[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.