Create New MySQL User And Grant Permissions

By /
How To Create New Mysql User With Grant Permission

hello folks, In this article, We will discuss how to create new MySQL users and grant privileges. MySQL is an open-source relational database management system used to store, manage, query, and retrieve data. Normally, we use MySQL as root but in big companies where there are multiple users, We need to manage permission for different users.

Also read: Installing MySQL on Debian/Ubuntu

Installing MySQL Server

To install MySQL server, Execute the following command in a terminal window:

sudo apt-get update
sudo apt-get install mysql-server
mysql-grant-permission-1

You can refer to this article: Installing MySQL on Debian/Ubuntu.

Creating a New User in MySQL

Open a terminal window by pressing Ctrl+Alt+T and enter the following command and open My SQL shell as root. Enter root user password if prompted.

sudo mysql -u root -p
mysql-grant-permission-2

To create a new user, Run the command in the format shown below:

CREATE USER 'username' IDENTIFIED BY 'password';

CREATE USER 'sid' IDENTIFIED BY 'abc';
mysql-grant-permission-3

MySQL is not case-sensitive i.e the above command can also be written as:

create user 'sid' identified by 'abc';

If you need to create a user through a remote host, You can change the command as follows:

  • To create a user by connecting remotely through ip address, Enter the command as follows:
CREATE USER 'username'@'ip address' IDENTIFIED BY 'password';
  • To create a user by connecting to a machine, Enter the command as follows:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Granting Permission In MySQL

Here is the list of privileges you can grant to users depending on the requirement:

  • All privileges :- User has full access to the database
  • Insert :- User can insert rows into tables
  • Create :- User can create new tables and databases
  • Delete :- User can delete rows from the tables
  • Select :- User can read information in the databases
  • Update :- User can update rows in the tables
  • Drop :- User can remove tables and databases
  • Grant Option :- User can modify other user’s permissions

To grant specific permissions to a user, The basic syntax will be:

GRANT permission1, permission 2 ON database_name.tablename TO 'username';

For example, To grant INSERT permission to user sid for the table test in the database abc, Run the following command:

GRANT INSERT ON abc.test TO sid;
mysql-grant-permission-4

To grant multiple permissions, run the following command:

GRANT INSERT,SELECT,DELETE ON abc.test TO sid;
mysql-grant-permission-5

To grant all privileges, Run the following command:

GRANT ALL PRIVILEGES ON abc.test TO sid;
mysql-grant-permission-6

Checking granted permissions

To check the permission of a particular user, Run the following command:

SHOW GRANTS FOR 'username';

SHOW GRANTS FOR 'sid';
mysql-grant-permission-7

Removing granted permissions

To remove particular permission, run the following command:

REVOKE permission1, permission2 ON databasename.tablename TO 'username';

REVOKE SELECT ON abc.test TO 'sid';
mysql-grant-permission-8

To remove all permissions, Run the following command:

REVOKE ALL PRIVILEGES ON abc.test FROM 'sid';

Deleting a MySQL User

To remove a user, Run the following command:

DROP USER 'username';

DROP USER sid;
mysql-grant-permission-9

Conclusion

So, We learned how to install MySQL, create a new user, grant permissions, Check the granted permission, and remove specific permission or a user. To know more about MySQL and databases, you can refer to the official site of MySQL.