
	Professional Software Consulting

showUsers.ksh

Show MySQL users accounts. Prints the user names, hosts, and whether or not they have file priviledges (meaning they can execute LOAD FILE INTO commands). Note that the MySQL client is sometimes smart enough to detect whether it is printing to the screen or its output is being redirected. When printing to the screen, the table has ascii borders, but when capturing the output using back-tick operators, the border is not present and so can be safely ignored.

Usage Statement
USAGE: showUsers.ksh <dbHost> <dbPass> [<dbPort>] where dbHost is the host the server is running on dbPass is the root password dbPort is the optional database port (defaults to 3306)

Sample Invokation
# ksh showUsers.ksh localhost myPass # ksh showUsers.ksh localhost myPass 3309

Sample Output

+--------------+---------------+-----------+
| user | host | file_priv |
+--------------+---------------+-----------+
| root | localhost | Y |
| joe | 192.168.10.70 | N |
| joe | 192.168.10.50 | N |
| joe | 192.168.10.30 | N |
| joe | 192.168.10.10 | N |
| joe | 192.168.10.90 | N |
| joe | localhost | N |
| sally | 192.168.10.90 | N |
| sally | localhost | N |
| backup | 192.168.10.10 | Y |
| backup | 192.168.10.30 | Y |
| backup | 192.168.10.50 | Y |
| backup | localhost | Y |
+--------------+---------------+-----------+

Source Code

#!/usr/local/bin/ksh
/**
* Copyright (c) 2008, Corey's Consulting LLC. All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*
* - Redistributions of source code must retain the above copyright notice,
* this list of conditions and the following disclaimer.
* - Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in the
* documentation and/or other materials provided with the distribution.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
* LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*/

###############################################################################
# check parameters

if [ $# -ne 2 -a $# -ne 3 ]
then
echo ""
echo " USAGE: $0 <dbHost> <dbPass> [<dbPort>]"
echo ""
echo " where dbHost is the host the database is running on"
echo " dbPass is the root password"
echo " dbPort is the optional database port (defaults to 3306)"
echo ""

exit 1
fi

DB_HOST=$1
DB_PASS=$2

if [ $# -eq 3 ]
then
DB_PORT=$3
else
DB_PORT=3306
fi

###############################################################################
# execute SQL command

mysql -u root --password="${DB_PASS}" -h ${DB_HOST} -t -P ${DB_PORT} \
-e "SELECT user, host, file_priv FROM user" mysql
if [ $? -ne 0 ]
then
echo " "
echo " --- MYSQL SERVER ERROR - POSSIBLY WRONG PASSWORD, OR USER NOT AUTHORIZED? ---"
echo " "
exit 1
fi

exit 0

Something wrong with this page or this site? Let the webmaster know by clicking HERE