Create a new user in MySQL and grant it full access to a database

I want to create a new user in MySQL and provide full access to only one database (for example, dbTest, I use create database dbTest; such a command create database dbTest;).What do those MySQL commands do?

#1st floor

Try this to create a user:

CREATE USER 'user'@'hostname';

Try this to access the database dbTest:

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

If you run code/sites that access MySQL on the same computer, the hostname will be a localhost.

Now, it's broken down.

GRANT - This is a command used to create users and grant privileges such as databases, tables, etc.

ALL PRIVILEGES - This tells the user that they will have all standard permissions.However, this does not include the right to use the GRANT command.

Dbtest. * - This instructs MySQL to apply these permissions for use throughout the dbtest database.If you want, you can replace * with a specific table name or stored routine.

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

IDENTIFIED BY'password'- As you might guess, this sets the password for the user.

#2nd floor

You can use CREATE USER Statement to create a new user and use GRANT Grant it permissions.

#3rd floor

Create a user and grant all permissions to the database.

Log in to MySQL:

mysql -u root

Create and grant now

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

Anonymous user (local test only)

Or, if you only want to grant fully unrestricted access to the database (for example, to test instances on your local machine), you can grant access to anonymous users as follows:

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

Awake to

This applies to garbage data in development.Don't do this for anything you care about.

#4th floor

If you want to create a new user, you can use the following command to give him all access to a specific database on the localhost (not all databases in Mysql).

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

This grants the user on the localhost all permissions to a database test_database (dbTest in your case).

Check the permissions issued by the above commands to the user by running the following commands.

SHOW GRANTS FOR 'user'@'localhost'

Just in case, if you want to restrict user access to only one table

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

#5th floor


To be in MySQL / MariaDB version 5.7.6 and later CREATE USER , please use CREATE USER Syntax :

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

Then grant all access to the database (for example, my_db), using GRANT Syntax For example

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

Among them, ALL (priv_type) can be replaced with SELECT, INSERT, UPDATE, ALTER and other specific permissions.

Then reload the newly assigned permissions to run:



To run the above command, you need to run the mysql command and type it into a prompt, then log out with the quit command or Ctrl - D.

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

$ mysql -e "SELECT 1"

Or print a statement from standard input:

$ echo "FOO STATEMENT" | mysql

If you have been denied access above, specify the -u (for user) and -p (for password) parameters, or for long-term access, set your credentials at ~/.my.cnf, for example


Shell Integration

For those unfamiliar with MySQL syntax, there are convenient shell functions that are easy to remember and use (to use them, you need to load the shell functions included below).

This is an example:

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

$ mysql-create-db foo

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

$ 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

To use these commands, you need to copy and paste the following functions into the rc file (for example .bash_profile ) and reload the shell or get the file.In this case, simply enter 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; }

# 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 do not want to leave a trace (such as a password) in the Bash history, check: How do I prevent commands from appearing in bash history?

Tags: MySQL Database MariaDB shell

Posted on Sun, 05 Jan 2020 13:02:36 -0800 by bigfunkychief