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 the use onto the full dbtest database. You can replace the * with specific table names or store routines if you wish.

TO 'user'@'hostname' - 'user' is the 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.

If you're allowing network access and want a connection from any host you can change 'hostname' to '%'. For example, ... TO 'dbuser'@'%' IDENTIFIED... '%' is the host wildcard. –

Don't forget to flush privileges when you're working on users and tables!

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON db_database1.* To 'user1'@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;

Is IDENTIFIED BY 'password' clause mandatory, if the user already exists and has a password? –

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:

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

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

mysql-create-db() { [ -z "$1" ] && { echo "Usage: mysql-create-db (db_name)"; return; } mysql -ve "CREATE DATABASE IF NOT EXISTS $1" }

mysql-drop-db() { [ -z "$1" ] && { echo "Usage: mysql-drop-db (db_name)"; return; } mysql -ve "DROP DATABASE IF EXISTS $1" }

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

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