LinuxQuestions.org - MySQL - Script to create account

- Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)

- - MySQL - Script to create account (https://www.linuxquestions.org/questions/linux-server-73/mysql-script-to-create-account-790287/)

MySQL - Script to create account

Hi,

I use a script to create user's database. The user's account is got from a csv file. This is the script i use to create the account for user that is pulled out from the csv file:

Code:

!/bin/bash

# Description

# Change ownership for each user from  CSV file, grant permissions

#

# CSV File Format

# Each user id is in 1 line

# Description End





export PATH=$PATH:/usr/sbin

out=execute



#Check argument

if [ -z $1 ]; then

  echo "Usage: `basename $0` <csv file>"

  exit 1

fi



test -f $out && rm -f $out



if [ -r $1 ]; then

  cat $1 |

  while read line; do

    userid=$(echo $line | cut -f1 -d\n)  

    echo "create database $userid;" >> $out

    echo "GRANT ALL ON $userid.* To $userid IDENTIFIED BY \"somepass\";" >> $out

  done

  mysql -u root --password="somepass" -e "`cat /execute`"

fi

When i execute the script, i got error related to the MySQL syntax.

Quote:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Acou' at line 1

Can someone here help me to give the right syntax to call the database to create database for users?

It looks like you're building a script and then trying to run the script.

My suggestion would be to name the script with the traditional .sql
extension. This means the file that is referred to as execute and
/execute would become execute.sql. You would run it like this:
mysql -u root -p"somepass" < execute.sql

Try doing this and then posting the first line of the execute.sql file.

What is the content of the file 'execute'?
I think that you are missing some quotes in your SQL statement.

It should look like this:

Code:

CREATE DATABASE IF NOT EXISTS userdb;

CREATE USER 'userid'@'%' IDENTIFIED BY 'some_password';

GRANT ALL ON userdb.* TO 'userid'@'%';

Be aware that I used userdb as the database name and userid for the username. In your case this would be the same. What is important and also the reason why I made this distinction, is when you use userid you need to put single quotes around it. This is not permitted when using the database name. The %-sign is to tell the server that access is allowed from any client.

Arjan