[mysql] Create new user in MySQL and give it full access to one database

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?

The below command will work if you want create a new user give him all the access to a specific database(not all databases in your Mysql) on your localhost.

GRANT ALL PRIVILEGES ON test_database.* TO 'user'@'localhost' IDENTIFIED BY 'password';

This will grant all privileges to one database test_database (in your case dbTest) to that user on localhost.

Check what permissions that above command issued to that user by running the below command.

SHOW GRANTS FOR 'user'@'localhost'

Just in case, if you want to limit the user access to only one single table

GRANT ALL ON mydb.table_name TO 'someuser'@'host';

To me this worked.

CREATE USER 'spowner'@'localhost' IDENTIFIED BY '1234'; 
GRANT ALL PRIVILEGES ON test.* To 'spowner'@'localhost'; 
FLUSH PRIVILEGES;

where

• spowner : user name
• 1234 : password of spowner
• test : database 'spowner' has access right to

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'"
}

Note: If you prefer to not leave trace (such as passwords) in your Bash history, check: How to prevent commands to show up in bash history?

To create a user and grant all privileges on a database.

Log in to MySQL:

mysql -u root

Now create and grant

GRANT ALL PRIVILEGES ON dbTest.* To 'user'@'hostname' IDENTIFIED BY 'password';

Anonymous user (for local testing only)

Alternately, if you just want to grant full unrestricted access to a database (e.g. on your local machine for a test instance, you can grant access to the anonymous user, like so:

GRANT ALL PRIVILEGES ON dbTest.* To ''@'hostname'

Be aware

This is fine for junk data in development. Don't do this with anything you care about.

In case the host part is omitted it defaults to the wildcard symbol %, allowing all hosts.

CREATE USER 'service-api';

GRANT ALL PRIVILEGES ON the_db.* TO 'service-api' IDENTIFIED BY 'the_password'

SELECT * FROM mysql.user;
SHOW GRANTS FOR 'service-api'

Try this to create the user:

CREATE USER 'user'@'hostname';

Try this to give it access to the database dbTest:

GRANT ALL PRIVILEGES ON dbTest.* To 'user'@'hostname' IDENTIFIED BY 'password';

If you are running the code/site accessing MySQL on the same machine, hostname would be localhost.

Now, the break down.

GRANT - This is the command used to create users and grant rights to databases, tables, etc.

ALL PRIVILEGES - This tells it the user will have all standard privileges. This does not include the privilege to use the GRANT command however.

dbtest.* - This instructions MySQL to apply these rights for use in the entire dbtest database. You can replace the * with specific table names or store routines if you wish.

TO 'user'@'hostname' - 'user' is the username of the user account you are creating. Note: You must have the single quotes in there. 'hostname' tells MySQL what hosts the user can connect from. If you only want it from the same machine, use localhost

IDENTIFIED BY 'password' - As you would have guessed, this sets the password for that user.

$ mysql -u root -p -e "grant all privileges on dbTest.* to
`{user}`@`{host}` identified by '{long-password}'; flush privileges;"

ignore -p option, if mysql user has no password or just press "[Enter]" button to by-pass. strings surrounded with curly braces need to replaced with actual values.

You can create new users using the CREATE USER statement, and give rights to them using GRANT.