How to create a New Database-user and Grand Permissions – MySQL

We already discussed about the creation of MySQL DB and DB users. Here I am explaining the commands which are using to Grand Privileges to users for accessing the Databases in your server.

Enter the mysql prompt by following the step below:

# mysql -u root -p

It then prompts for password. Enter the MySQL root password.

Example:

root@server [/]# mysql -u root -p
Enter password:

1, Command to Grand All privileges on DB for a user.

mysql> GRANT ALL PRIVILEGES ON database.* to 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

Refer the example below for more details:

mysql> GRANT ALL PRIVILEGES ON crybit_wp.* to 'crybit_wp'@'localhost' IDENTIFIED BY 'with_the_new_password' WITH GRANT OPTION;

2, how to add SELECT privileges to a user using GRANT.

mysql> GRANT SELECT ON database.* to 'username'@'localhost' IDENTIFIED BY 'password';

3, how to add a selection of privileges to a user using GRANT.

mysql> GRANT SELECT, INSERT, DELETE ON database.* to 'username'@'localhost' IDENTIFIED BY 'password';

That’s it 🙂 🙂

Related:
How to create/restore MySQL dump/backup of all the databases.
How to create a database and database user from command line
Reset mysql root password from command line
How to allow mysql client to access remote mysql databases

Arunlal Ashok

Linux Cloud Infrastructure / DevOps Engineer. I'm dealing Linux servers since 2012. I started this blog to share and discuss my ideas.

Always happy for an open discussion! Write to arun (@) crybit (dot) com. Check about me for more details. About this blog and our strong members, check The team CryBit.com

Leave a Reply

Your email address will not be published. Required fields are marked *