Create User and Grant Permissions in MySQL.

mysql-database
MySql

Normally we do a whole lot of things with the MySQL root user who have full access to all the Databases.

However, in the cases where more restrictions may be required, there are ways to create users with custom permissions.

In this post we’ll go through how to create a new MySQL user and provide him with specific permissions.

How to create a new user.

Let’s first start by creating a new MySQL user.
[sourcelang =’sql’]
CREATE USER newuser@’localhost’ IDENTIFIED BY ‘password’;
[/sourcelang]

Just creating the user doesn’t mean that the user will now get access to MySQL.Unfortunately, at this point even if the user tries to login will be unsuccessful.

Therefore we need to provide privileges to the user.

Set Privileges

[sourcelang =’sql’]
GRANT ALL PRIVILEGES ON *.* TO ‘newuser’@’localhost’;
[/sourcelang]

Here first asterisk means the database  and second asterisk means the tables.So the above command will provide ALL(read,update,delete etc) privileges to newuser on all Databases and all tables.

You can replace these asterisk according to your requirements Eg : schoolDatabase.* or schoolDatabase.students.

Once done with providing specific privileges remember to reload the privileges.

[sourcelang=’sql’]
FLUSH PRIVILEGES;
[/sourcelang]

To provide particular permission to a user you may use.

[sourcelang=’sql’]
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@’localhost’ (Refer below for possible permissions.)
[/sourcelang]

To revoke the permissions use

[sourcelang=’sql’]
REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@’localhost’;
[/sourcelang]

 

NOTES

  • If you want the MySQL server to be accessed remotely you may replace localhost with the IP address of the remote machine.
  • If you want the database to be accessed remotely from all IP addresses replace localhost with %.
  • Possible permissions are ALL PRIVILEGES,SELECT(read the database),INSERT(insert rows to a table),DELETE(delete records from a table),UPDATE(update rows of a table),DROP(drop the whole table),CREATE(create a new table),GRANT OPTION(grant or remove other user’s privileges).
  • Each time you update or change a permission be sure to use the Flush Privileges command.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s