Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

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


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?
Hein van den Heuvel
Honored Contributor

Re: Creating 800 users in oracle

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
Honored Contributor

Re: Creating 800 users in oracle

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Jean-Luc Oudart
Honored Contributor

Re: Creating 800 users in oracle

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
fiat lux

Re: Creating 800 users in oracle

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
Trusted Contributor

Re: Creating 800 users in oracle

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
"Only he who attempts the absurd is capable of achieving the impossible
Petr Simik_1
Valued Contributor

Re: Creating 800 users in oracle

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
Occasional Visitor

Re: Creating 800 users in oracle

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;

Re: Creating 800 users in oracle

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.