Bulk addition of users and databases
Having googled extensively, I can`t seem to find a way to do the following. I have a mysql table, with around 1200 usernames in it. What I want to do is programmatically add each user, and create a database of the same name that that user has access to. (This is for a school web server, we want to allow students to try out mysql querys with phpmyadm etc) Any suggestions? Thanks Rob Keeling -- -- I love deadlines. I love the whooshing noise they make as they go by. - Douglas Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bulk addition of users and databases
Le mar 07/09/2004 à 11:24, Rob Keeling a écrit :
Having googled extensively, I can`t seem to find a way to do the following.
I have a mysql table, with around 1200 usernames in it.
What I want to do is programmatically add each user, and create a database
of the same name
that that user has access to.
(This is for a school web server, we want to allow students to try out mysql
querys with phpmyadm etc)
Any suggestions?
I recently tried Perl::DBI to create a lot of databases and found it
crashes after 100 databases because it doesn't close properly its
handler. So I would suggest not to do that.
Now I think the best way (not the cleanest) is to use a PHP script with
'exec' command to call:
mysqladmin -u user --password=pass create tablename
I've done that in Perl so if you're more comfortable with Perl, go
ahead.
Also, I have a shell script creating databases by calling mysqladmin,
but it's based on a file containing all databases names (which you need
to output from mysql client first).
The best thing I would suggest is to do it in PHP or Perl in a loop
fetching all students name. You will also need to give the students
sufficient permissions on their own database so you will have to create
1200 mysql users, otherwise they will all have access to the other's
databases.
With PHP this would be something like:
$admin = 'adminuser'; # this is the username allowed to create databases
$pass = 'pass'; # this is his password
$query = SELECT username from user;
$result = mysql_query($query);
foreach($row = mysql_fetch_array($result)){
exec('mysqladmin -u '.$admin.' --password='.$pass.' create
'.$row['username'])
|| die Could not create database .$row['username'].:
.mysql_last_error();
exec(...something that will give the right to that user on that
database...)
|| die Could not grant permissions to .$row['username'].:
.mysql_last_error();
}
Cheers,
Yannick
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bulk addition of users and databases
Rob,
use your imagination: this is a case for a quick and dirty shell script.
You don't have blanks in your usernames, do you? Try something like
this: (no guarantee that it works)
#!/bin/sh
# fetch users from a table and create databases
# creates a new table with preliminary passwords for those users
HOST=localhost # where all your users reside
DBASE=test # set yours
USERTABLE=yourtablewithusernames
OPTS= # options to access your database
MYSQL=mysql -N $OPTS $DBASE # add any further options here
echo CREATE TABLE IF NOT EXISTS usrpwd(\
user VARCHAR(255), pwd VARCHAR(13)); | $MYSQL
USERS=$(echo SELECT user_name FROM $USERTABLE; | $MYSQL)
for USER in $USERS ; do
echo -n User $USER
if ! mysqladmin $OPTS create $USER ; then
echo -e \nfailed to create database for '$USER'
exit 1
fi
PW=$(echo SELECT ENCRYPT('$USER'); | $MYSQL)
if ! echo GRANT ALL ON $USER.* TO '$USER'@'$HOST' \
IDENTIFIED BY '$PW' WITH GRANT OPTION; \
| $MYSQL ; then
echo -e \nfailed to GRANT rights for '$USER'
exit 1
else
echo has Password '$PW'
echo INSERT INTO usrpwd VALUES('$USER','$PW'); \
| $MYSQL
fi
done
echo SELECT * FROM usrpwd ORDER BY user; | $MYSQL
# this is the end of the shell script
Have fun
Thomas Spahni
On Tue, 7 Sep 2004, Rob Keeling wrote:
Having googled extensively, I can`t seem to find a way to do the following.
I have a mysql table, with around 1200 usernames in it.
What I want to do is programmatically add each user, and create a database
of the same name
that that user has access to.
(This is for a school web server, we want to allow students to try out mysql
querys with phpmyadm etc)
Any suggestions?
Thanks
Rob Keeling
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Users and Databases
Ryan, Thursday, November 28, 2002, 9:56:00 PM, you wrote: RM Please pardon my extreme case of the NEWBs on this but I need some help please. RM Ok I just purchased a book to hopefully clear things up for me, but to no RM avail. So this is my dilemma: I want to know about MySQL administration, RM specifically the user management portion of the admining. I want to be able to RM allow users to create whatever DBs they need. One DB for my Multi-media RM collection, one for an address book, one DB for links for a web site. Whatever RM the case may be! But it seems like I can't give a user the option to create RM and/or delete any DBs that only user X made. So I the admin would have to RM create each DB and assign appropriate privileges to appropriate users? RM So to sum up my situation into a question: Is it possible for me to give a user RM the ability to create and/or delete only their DBs? Only with MySQL - nope. RM Or do I have to make the DB RM for them and then Grant appropriate privileges for that user to that DB? It's much easier.. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Users and Databases
Hi everyone, Please pardon my extreme case of the NEWBs on this but I need some help please. Ok I just purchased a book to hopefully clear things up for me, but to no avail. So this is my dilemma: I want to know about MySQL administration, specifically the user management portion of the admining. I want to be able to allow users to create whatever DBs they need. One DB for my Multi-media collection, one for an address book, one DB for links for a web site. Whatever the case may be! But it seems like I can't give a user the option to create and/or delete any DBs that only user X made. So I the admin would have to create each DB and assign appropriate privileges to appropriate users? So to sum up my situation into a question: Is it possible for me to give a user the ability to create and/or delete only their DBs? Or do I have to make the DB for them and then Grant appropriate privileges for that user to that DB? Thanx in advance for the help __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
