Re: Script for creating users anyone?
Date: 1996/08/15
Message-ID: <32136384.6B2B_at_co.honolulu.hi.us>#1/1
N.S. Smith wrote:
> Does any organisation (probably University) have a script
> that creates new users from a text file of user names?
> Basically we need a mechanism to create large numbers of users
> very quickly, and, conversely, drop them as well. I've seen
> the web page for RevealNet but the University is loathe to
> invest any further with Oracle support.
>
Neville,
Before you assign privileges to a mass of users, you'd be well advised to read the 'SQL Reference Manual' on 'create profile' and 'create role'.
Ideally, on paper, you should set up a list of roles to administer, such as:
user_general user_student user_undergraduate user_graduate user_administrative user_faculty dba_trainee
dba_work
dba_trusted
or even do an (object-oriented like) hierarchy:
user_general
| |----------------|----------- etc | | | | user_student user_administrative | | |--------------------------| | | user_undergraduate user_graduate|
etc.
The 'attributes' of each role would be resource limits (listed under 'create profile') and system level privileges (connect, create table, create index, drop table, drop view).
Once you find common profiles, you can then define a list of profiles:
prof_tiny prof_small prof_medium prof_large prof_unlimited
and a mapping between the two:
ROLES PROFILES DEFAULT DEFAULT TEMPORARY TABLESPACE TABLESPACE user_general prof_limited USER 100K TEMP1 100K user_student prof_tiny USER 100K TEMP1 100K user_undergraduate prof_small USER 1M TEMP1 1M user_graduate prof_small USER 1M TEMP1 1M user_administrative prof_medium ADMIN 10M TEMP2 10M user_faculty prof_small USER 2M TEMP2 2M dba_trainee prof_medium DEV 10M TEMP2 10M dba_work prof_large DEV 1000M TEMP3 1000M dba_trusted prof_unlimited DEV unlimited TEMP4 unlimited
Then as dba, you'd:
(1) Create roles and grant the roles various system privileges, and (2) Create profiles and specify profile attributes.
Now say you have an (ascii) list of students:
# List of students Fall, 1996
uxx00251 Lucy Bowles
uxx00372 Phillip D'Arcy
...
# END LIST
You have a script (call this create_student.awk), that does the
following:
#! /bin/awk
# Create sql script for students
BEGIN {
t1 = "identified externally"
t2 = "default tablespace USER"
t3 = "temporary tablespace TEMP1"
t4 = "quota 100 K on USER"
t5 = "quote 100 K on TEMP1"
t6 = "profile prof_tiny;"
}
(substr($1,1,1) == "#") {next} # Skip comments
(NF == 0) {next} # Skip blank lines
{
printf "create user %s %s\n%s\n%s\n%s\n%s\n%s\n", $1, t1, t2, t3, t4, t5, t6
next
}
# DONE and after entering
awk -f create_student.awk <list of students> > foobar.sql
you'd (under sql*plus) do:
SQL>_at_foobar
[Note: I used awk to create the sql script. You can use any other scripting language (perl is a good one) to do the same.
Your unix system guru should be able to create the scripts for you.
A good reference for awk is:
Aho, Kernighan, Weinberger "The AWK Programming Language", Addison-Wesley, ISBN 0-201-07981-X (Another one is the "sed & awk" book by Dougherty, ISBN 0-937175-59-5 from O'Reilly [go to www.ora.com on the internet].) ]
Finally, you need to create a similar script, say grant_student.awk, to create a list of sql statements like:
grant user_student to uxx00251;
grant user_student to uxx00372;
...
[You can do this at the unix command line:
>awk ' {printf "grant user_student to %s;\n", $1; next}' <list of students> > goobar.sql
Mutatis mutandis for 'revoke' and 'drop user'.]
I highly recommend you investigate the use of roles and profiles because they make the administration of a large number of users very do-able.
I hope this helps.
aloha
ayn
Received on Thu Aug 15 1996 - 00:00:00 CEST