Create a New User and Grant Permissions in MySQL

Create a New User and Grant Permissions in MySQL

Login to your mysql with username and password through terminal not through phpmyadmin

mysql -u root -p

Get your all databases list
show databases;

How to get all users in mysql ?

select user, host from mysql.user;

Create a mysql user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Grant Permission with All Privileges
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.
FLUSH PRIVILEGES;

How To Grant Different User Permissions ?

  • ALL PRIVILEGES- as we saw previously, this would allow a MySQL user full access to a designated database (or if no database is selected, global access across the system)
  • CREATE- allows them to create new tables or databases
  • DROP- allows them to them to delete tables or databases
  • DELETE- allows them to delete rows from tables
  • INSERT- allows them to insert rows into tables
  • SELECT- allows them to use the SELECT command to read through databases
  • UPDATE- allow them to update table rows
  • GRANT OPTION- allows them to grant or remove other users’ privileges

How to grant permission to a specific table ?

GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost';

How to Revoke permission ?

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost';

Show what permission an user have ?

SHOW GRANTS FOR 'username'@'localhost';

How to remove a user from mysql ?

DROP USER 'username'@'localhost';

How to grant permission to all database ?

GRANT type_of_permission ON *.*TO 'username'@'localhost';

How to grant permission to all tables of database?
GRANT type_of_permission ON database_name.* TO 'username'@'localhost';

How to grant only select permission to a user in mysql ?
GRANT select ON database_name.* TO 'username'@'localhost';
GRANT select, insert ON database_name.* TO 'username'@'localhost';

How to change a user password in mysql ?
ALTER USER 'username'@'localhost' IDENTIFIED BY 'New-Password-Here';
FLUSH PRIVILEGES;

Lets Get Started your project with professional way

Get in touch and let us know
how we can Help


Contact Us