Create new user in MySQL and give it full access t

Question

I want to create a new user in MySQL and give it full access only to one database, say dbTest, that I create with a command like create database dbTest;. What would be the MySQL commands to do that?

Answer 1

Syntax

To create user in MySQL/MariaDB 5.7.6 and higher, use CREATE USER syntax:

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';

then to grant all access to the database (e.g. my_db), use GRANT Syntax, e.g.

GRANT ALL ON my_db.* TO 'new_user'@'localhost';

^{Where ALL (priv_type) can be replaced with specific privilege such as SELECT, INSERT, UPDATE, ALTER, etc.}

Then to reload newly assigned permissions run:

FLUSH PRIVILEGES;

---

Executing

To run above commands, you need to run mysql command and type them into prompt, then logout by quit command or Ctrl-D.

To run from shell, use -e parameter (replace SELECT 1 with one of above commands):

$ mysql -e "SELECT 1"

or print statement from the standard input:

$ echo "FOO STATEMENT" | mysql

If you've got Access denied with above, specify -u (for user) and -p (for password) parameters, or for long-term access set your credentials in ~/.my.cnf, e.g.

[client]
user=root
password=root

---

Shell integration

For people not familiar with MySQL syntax, here are handy shell functions which are easy to remember and use (to use them, you need to load the shell functions included further down).

Here is example:

$ mysql-create-user admin mypass
| CREATE USER 'admin'@'localhost' IDENTIFIED BY 'mypass'

$ mysql-create-db foo
| CREATE DATABASE IF NOT EXISTS foo

$ mysql-grant-db admin foo
| GRANT ALL ON foo.* TO 'admin'@'localhost'
| FLUSH PRIVILEGES

$ mysql-show-grants admin
| SHOW GRANTS FOR 'admin'@'localhost'
| Grants for admin@localhost                                                                                   
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4' |
| GRANT ALL PRIVILEGES ON `foo`.* TO 'admin'@'localhost'

$ mysql-drop-user admin
| DROP USER 'admin'@'localhost'

$ mysql-drop-db foo
| DROP DATABASE IF EXISTS foo

To use above commands, you need to copy&paste the following functions into your rc file (e.g. .bash_profile) and reload your shell or source the file. In this case just type source .bash_profile:

# Create user in MySQL/MariaDB.
mysql-create-user() {
  [ -z "$2" ] && { echo "Usage: mysql-create-user (user) (password)"; return; }
  mysql -ve "CREATE USER '$1'@'localhost' IDENTIFIED BY '$2'"
}

# Delete user from MySQL/MariaDB
mysql-drop-user() {
  [ -z "$1" ] && { echo "Usage: mysql-drop-user (user)"; return; }
  mysql -ve "DROP USER '$1'@'localhost';"
}

# Create new database in MySQL/MariaDB.
mysql-create-db() {
  [ -z "$1" ] && { echo "Usage: mysql-create-db (db_name)"; return; }
  mysql -ve "CREATE DATABASE IF NOT EXISTS $1"
}

# Drop database in MySQL/MariaDB.
mysql-drop-db() {
  [ -z "$1" ] && { echo "Usage: mysql-drop-db (db_name)"; return; }
  mysql -ve "DROP DATABASE IF EXISTS $1"
}

# Grant all permissions for user for given database.
mysql-grant-db() {
  [ -z "$2" ] && { echo "Usage: mysql-grand-db (user) (database)"; return; }
  mysql -ve "GRANT ALL ON $2.* TO '$1'@'localhost'"
  mysql -ve "FLUSH PRIVILEGES"
}

# Show current user permissions.
mysql-show-grants() {
  [ -z "$1" ] && { echo "Usage: mysql-show-grants (user)"; return; }
  mysql -ve "SHOW GRANTS FOR '$1'@'localhost'"
}