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]
