PowerShell - Create Users, Databases, and Set Permission on MySQL

SCENARIO

You want to automate the creation of lots of MySQL user accounts, databases, and permissions set on those user accounts.  You can do this through PowerShell.

I modified a script by berniecamus: https://github.com/berniecamus/create-mysql-database/blob/master/createmysql.ps1

Create a csv file with the following headings:

The script imports the .csv and then loops through them to create a user account, a database, and then set full permissions to that database for that user.  You can modify it to set whatever permissions you like.  Also, it's probably better to store the MySQL credentials using the Get-Credentials commandlet, but I was being a bit lazy.  I may post an updated version later.

<#

    Script to Create MySQL user accounts, databases, and grant rights from a .csv file.

    CSV file headers are "user", "database", and "password"

#>

# Create .Net object thingy.

    [void][system.reflection.assembly]::LoadWithPartialName("MySql.Data")

# Get Creds

    $uid = Read-Host -Prompt 'Enter your MySQL username'

    $pwd = Read-Host -Prompt 'Enter your MySQL password'

# Open MySQL Connection

    $connStr = "server=SERVERNAME;port=3306;uid=$uid;pwd=$pwd"

    $conn = new-object MySql.Data.MySqlClient.MySqlConnection($connStr)

    $conn.Open()

# Loop through users

    $UserFile = "C:\_Scripting\UserList\userlist.csv"

    $list = Import-Csv $UserFile

    foreach ($user in $list) {

    

# Variables

    $username = $user.user

    $dbname = $user.database

    $password = $user.password

    

# Create MySQL User

    $createmysqluser = "CREATE USER '" + $username  + "'@'%' " + "IDENTIFIED BY '$password'"

    $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($createmysqluser, $conn)

    $da = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)

    $ds = New-Object System.Data.DataSet

    $da.Fill($ds)

     

# Create Database

    $createmysqldatabase = 'CREATE DATABASE `' + $dbname + '` CHARACTER SET utf8'

    $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($createmysqldatabase, $conn)

    $cmd.ExecuteNonQuery()

     

# Grant Privileges to User

    $grantaccess = 'GRANT ALL ON ' + $dbname + '.* TO ' + "'$username'@'%'"

    #$grantaccess = 'GRANT ALL ON *.* TO ' + "'$username'@'%'"

    $cmd = new-object MySql.Data.MySqlClient.MySqlCommand($grantaccess, $conn)

    $cmd.ExecuteNonQuery()

}

   $conn.Close()