Create Mass Users, Roles & Privileges

[richardana]

To all the Script Guru,
I am trying to compile a script to create about 250 users and 3 different roles.

Then
Grant select on T1, T2, T3..to select_role(200 users) We have more 100+ tables) but there are 5 tables that they cannot
select from.

Grant select, insert, update on T1,T2, T3, T4 to select_insert_update_role

and
Grant select, insert, update, delete ANY TABLE to select_insert_update_delete_role.

I created the script to create users as follow:
create user '&username' identified by '&password';
enter value for username: john
enter value for password: doe
THEN
I received an error message:
ERROR at line
ORA-01935: missing user or role name
I need your help. I know there is a way to do this but I just don't HOW.
Please help.
Richard

[dknight]

Try this. Remove the quotes from

create user '&username' identified by '&password';

and specify a default tablespace and temp tablespace.

i.e.

create user &username identified by password
default tablespace &tbsp
temporary tablespace &ttbsp;

Good luck.

[richardana]

Hi All,
I REALLY NEED YOUR HELP ON THIS.

I am trying to compile a script to create 250 users and 3 different roles in Oracle (NT).

I HAVE THE 250 NAMES AND PASSWORDS SAVED ON A TEXT FILE.
How do i go by creating mass users and grant select on T1, T2, T3..to SELECT_ROLE on more 100+ tables) but there are 5 tables that they cannot select from.

THEN
Grant select, insert, update on T1,T2, T3, T4 to SELECT_INSERT_UPDATE_ROLE

and
Grant select, insert, update, delete ANY TABLE to SELECT_INSERT_UPDATE_DELETE_ROLE.

THERE'S GOT TO BE A BETTER WAY TO DO THIS WITHOUT CREATING USERS 1 BY 1.
Please send me step by step guide.
Richard

[marist89]

1. sqlload the text file into the database.
2. create a sql statement that generates your "create user" statement and save to a file
3. run that script