shell script that creates FreeBSD and MySQL account

[Kosala]

Hello everyone!

Sorry if this question is already some where.

I administrate FreeBSD web server in our school. Server is used for student's html, css and so on testing.

I need to add account to base system and sometimes also to mysql.

I have created shell script what read lastname firstname group name and users student number from file.

I have managed to add account to base system, but now I have problem to add accounts to mySQL.

Here is code what I have created.

#!/bin/sh
NEW_USER="/root/komentoja/tunnarit"
HOME_BASE="/home/"
PASSU="Password_here"
TANAAN=$(date "+%Y_%m_%d") #today



#SKEL="/usr/share/skel"
SKEL="/usr/share/skel_os_commerce"
# Twho skeleton because some groups need OsCommerce and others doesn't

cat ${NEW_USER} | \
while read LASTNAME FIRSTNAME GROUP USER
do
NAME="$LASTNAME $FIRSTNAME"
KANSIO="$GROUP"_"$USER"
TUNNUS="$USER"_"$FIRSTNAME"

#Group add
pw groupadd ${GROUP}

#Create user account, pasword expires next day
pw useradd -n ${KANSIO} -g ${GROUP} -p +1d -k ${SKEL} -m -d ${HOME_BASE}${KANSIO}

# muutetaan user accountin kommenttia
pw usermod ${KANSIO} -c "${NAME}" -w random 
#I would like here that PASSU so that all user get first time same password

# mySQL command
mysql -t -p <<STOP
CREATE DATABASE $KANSIO;
CREATE USER '$KANSIO'@'localhost' identified by '$PASSU';
GRANT ALL on $KANSIO.* to '$KANSIO'@'localhost';
\q
STOP

done

exit 0

This code ask mysql root users password for every user account. I would like to give mySQL root password only one time. How I could do that?

How I could give same password for every base account in pw usedadd or usermod command?

Thanks in advance of your help.

Regards:
Mr Keijo Salakari from Finland

 

[idle]

You can put password right after -p option, without space:[CMD=""]-pyourpass[/CMD]
Or into /root/.my.cnf.

[client]
pass = yourpass

 

[Kosala]

Thanks idle!

I got -ppassword part working.

Next problem.

# mySQL command
mysql -t -pmypasswordhere <<STOP
CREATE DATABASE $KANSIO;
CREATE USER '$KANSIO'@'localhost' identified by '$PASSU';
GRANT ALL on $KANSIO.* to '$KANSIO'@'localhost';
\q
STOP

Is there any possibility to ask mypassword from user when I run this script?

 

[idle]

stty -echo
read -p "password: " mysql_passw
stty echo
mysql -p$mysql_passw

 

[Kosala]

I go it working!

I changed also "${KANSIO]" to "'${KANSIO}'"


Like this.

CREATE USER "'${KANSIO}'"@"localhost" identified by "'${PASSU}'";
GRANT ALL PRIVILEGES on ${KANSIO}.* to "'${KANSIO}'"@"localhost";

Thanks idle!

 

[Kosala]

I got it working!

I changed also "${KANSIO]" to "'${KANSIO}'"

Okay, I didn't get it working. Now script add user name like this 'myusername'.

If I use "${KANSIO}" then I got error

ERROR 1396 (HY000) at line 2: Operation CREATE USER failed for

CREATE USER "${KANSIO}"@"localhost" identified by "'${PASSU}'";

 

[Kosala]

So what kind of code I should have create that it works like it should?

 

[Kosala]

Hello again.

I got mysql pqsword asking to work. Script creates database, but I can't create user.

Here is what I have tested.

create user "${KANSIO}"@"localhost" identified by "'${PASSU}'";

This give next error message

ERROR 1396 (HY000) at line 2: Operation CREATE USER failed for 'IT100_12345'@'localhost'

If I change code like next

create user "'${KANSIO}'"@"localhost" identified by "'${PASSU}'";

I get next error

ERROR 1396 (HY000) at line 2: Operation CREATE USER failed for ''IT100_12345''@'localhost'

And if I change code like next

create user '"${KANSIO}"'@"localhost" identified by "'${PASSU}'";

I get next error

ERROR 1396 (HY000) at line 2: Operation CREATE USER failed for '"IT100_12345"'@'localhost'

And if code is like this

create user ${KANSIO}@"localhost" identified by "'${PASSU}'";

Error is like this

ERROR 1396 (HY000) at line 2: Operation CREATE USER failed for 'IT100_12345'@'localhost'

Is it even possibility to create user from freebsd command prompt?

Could it be better solution's write command to sql file, login to mysql with root and call .sql text file from mysql command propmt?

Regards:
Keijo

 

[idle]

Seems like insufficient permissions.
Try this command from shell, like this:

mysql -uIT100_12345 -p -e "create user KANSIO@localhost identified by 'PASSU'"

 

[Kosala]

Seems like insufficient permissions.
Try this command from shell, like this:

mysql -uIT100_12345 -p -e "create user KANSIO@localhost identified by 'PASSU'"

I got same error than before.

 

[Kosala]

Okay, I think what I have been doing wrong.

This script add user account and I have used delete from user command to delete this test user.
I should use drop user command.

I found explanation from this page http://bugs.mysql.com/bug.php?id=28331.

I dropped my test user and tested mys script again. Script worked like it should work.

Thanks idle!

Now I could relax.