34

How can I check if a user exists?

Im doing an installer for a mysql database, and I need to check if a user exits, if not create user, if yes delete user and create it again.

this so i can execute the script without worries.

thanks.

sergiogx
  • 1,562
  • 1
  • 19
  • 36

7 Answers7

76

MySQL stores user data in a table called user in a database named mysql (by default). The following query will return 1 if a user with the specified username exists, 0 otherwise.

SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = 'username')
Lauri Lehtinen
  • 10,647
  • 2
  • 31
  • 29
9

If you're deleting the MySQL user anyways, then there's really no need to check if it exists first. MySQL won't throw any errors if there's nothing to delete:

DELETE FROM mysql.user WHERE User = 'username';
Matt
  • 3,778
  • 9
  • 35
  • 36
  • 10
    This will delete the user just from user table, if you take this approach, you've to delete also from mysql.columns_priv, mysql.db, mysql.tables_priv. Also this list of table may change in future MySQL versions. – stivlo Jun 30 '11 at 15:11
  • 8
    Never, ever, delete a user this way. Use the `DROP USER` command. – micheal65536 Dec 05 '17 at 18:03
  • 1
    What if we need check if a user exists so we can prompt whether the user should be deleted? – Leland Hepworth Aug 27 '20 at 16:19
6

As newer versions of MySQL allow this option:

DROP USER IF EXISTS 'username'@'host';
TheMadCat
  • 107
  • 1
  • 7
2

When in need to check if a user exists without deleting it (for instance when just skipping some instructions instead of executing them in any case), one can use this (where $USER is the user to check):

if [ $(echo "SELECT COUNT(*) FROM mysql.user WHERE user = '$USER'" | mysql | tail -n1) -gt 0 ]
then
  echo "User exists"
else
  echo "User doesn't exist"
fi

NB:

  • mysql command requires extra args and/or configuration for authentication)
  • tail -n1 is used for removing the query result header
fbastien
  • 762
  • 1
  • 9
  • 20
1

This is how I was able to do it:

#!/usr/bin/env bash

set -o errexit
set -o nounset

# Create credentials file
echo -e "[client]\nuser=root\npassword=${MYSQL_ROOT_PASSWORD}" > ~/.my.cnf

# Create standard user and grant permissions
if ! echo "SELECT COUNT(*) FROM mysql.user WHERE user = 'myuser';" | mysql | grep 1 &> /dev/null; then
    echo "Creating database user ..."
    echo "CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
          GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';
          FLUSH PRIVILEGES;" | mysql
else
    echo "Database user already created. Continue ..."
fi

Change myuser, mydatabase and mypassword accordingly.

Havok
  • 5,776
  • 1
  • 35
  • 44
  • This doesn't work well if the username contains the digit 1. So this can causes pitfalls. – Joachim Feb 19 '18 at 15:09
  • @Joachim just tested it with a user with a 1 in its username, and it works perfectly. Are you sure? Why should it not work for a user with a 1? – Havok Feb 20 '18 at 08:40
  • why `grep 1`? what if the `SELECT COUNT(*)` returns `2`? – Fr0zenFyr Aug 20 '19 at 06:08
  • @Fr0zenFyr if you `use mysql; describe user;` you will note that: `User | char(32) | PRI`. Because `user` is a primary key it implies that it is unique. Which means that an exact match `==` it is impossible to return more than 1. – Havok Aug 20 '19 at 06:26
  • It is actually a composite key. `User` column alone is not `PRI`. If you look closely, you will see that the primary key is a composite of `Host`+`User`. `Host char(60) NO PRI` and `User char(32) NO PRI` – Fr0zenFyr Aug 22 '19 at 05:12
  • That means if you have created a user that can access from local host as well as another IP, the count will be more than 1. On my development system, I have the `my_app_user` user setup to allow access from another IP as well (`CREATE USER 'my_app_user'@'localhost' IDENTIFIED BY 'my_pass'; CREATE USER 'my_app_user'@'192.168.1.52' IDENTIFIED BY 'my_pass';`). The result of `SELECT COUNT(*) FROM mysql.user WHERE user = 'my_app_user';` is `2`. which is correct and expected. Hope you understand now – Fr0zenFyr Aug 22 '19 at 05:17
  • @Fr0zenFyr great analysis. Indeed I missed that. My use case was to create a user if it doesn't exists in an entrypoint of a Docker image. So either that single user was created or not. It would be nice to modify this snippet to incorporate that. – Havok Sep 05 '19 at 08:00
1

MySQL lacks DROP USER IF EXISTS construct.

A good workaround is to grant a harmless privilege to the user before dropping it. This will create the user if it doesn't exist, so that it can be dropped safely, like so:

GRANT USAGE ON *.* TO 'username'@'localhost';
DROP USER 'username'@'localhost';
FLUSH PRIVILEGES;

USAGE actually means no privilege.

Source: http://bugs.mysql.com/bug.php?id=19166

stivlo
  • 83,644
  • 31
  • 142
  • 199
  • 2
    For posterity, this workaround no longer works. Quoting from another answer's comment: "This is no longer an option. :( The current version of MySQL no longer creates a new user for the GRANT statement. It was a "Feature" they added. ;)" For original reference, see: http://stackoverflow.com/a/3241918/656243 – Lynn Crumbling Aug 30 '12 at 15:19
0

MySQL 5.7 already includes DROP USER IF EXISTS, but for older versions I use pt-show-grants --drop from percona-toolkit and feed back the DROP USER part to mysql:

pt-show-grants --drop --only=$username | grep '^DROP USER' | mysql -v

If there are multiple username-hostname pairs this removes all of them.

Paul Tobias
  • 1,962
  • 18
  • 18